Java question - Calling a stored procedure with a set as an argument

[Link, David A]

We are having issues with getting java to find the stored procedure when the argument is a SET. Below is the stored procedure and the java code. We are running AIX 11.50. FC9XA. Any help for this non-java DBA would be greatly appreciated.

Thanks,
David

-----------------------------------------------------------------------------------------------------------------------------------------
package com.west.wic.test;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.Set;

import javax.swing.text.html.HTMLDocument.HTMLReader.PreAction;

/**
* @author aacharya
*
* @description
* This class uses the CallableStatement to call a stored procedure using
* SET(ROW()).
*
* This stored procedure resides inside ibm387 drain_system database
*
* CREATE PROCEDURE TESTING_SET(TEST_SET SET(CHAR(10) NOT NULL))
RETURNING CHAR(10);
DEFINE element CHAR(10);
FOREACH SELECT * INTO element FROM TABLE(TEST_SET)
RETURN element WITH RESUME;
END FOREACH;
END PROCEDURE;
*
* To Drop the procedure:
* DROP PROCEDURE TESTING_SET
*
* @example
* EXECUTE PROCEDURE TESTING_SET(SET{'GroupTestHash1', 'GroupTestHash2'})
*
*
*/
public class HashSetTest {

/**
* @param args
*/
public static void main(String[] args) {

CallableStatement statement = null;
String sqlStatement = null;
ResultSet resultset = null;
HashSet<String> groupIdSet = new HashSet<String>();
try {
//Prepare the Set
groupIdSet.add("GroupTestHash1");
groupIdSet.add("GroupTSestHash2");

//Loads the driver class
Class.forName ("com.informix.jdbc.IfxDriver");
Connection connection = DriverManager.getConnection("jdbc:informix-sqli://ibmXXX:YYY/test_system:INFORMIXSERVER=ibmXXX;user=USERID;password=PASSWORD");

// Create the statement object
sqlStatement = "{call TESTING_SET(?)}";
statement = connection.prepareCall(sqlStatement);
statement.setQueryTimeout(10);

// Set the parameters in the stored procedure
statement.setObject(1, groupIdSet);

statement.execute();
resultset = statement.getResultSet();

// resultset = statement.executeQuery();

// Traverse the resultset to get response
while(resultset.next())
{
System.out.println(resultset.getString(1));
}

} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println("error message:" +e.getMessage());
System.out.println("error code:" + e.getErrorCode());
}

}

}

[jrenaut]

We are having issues with getting java to find the stored procedure when the argument is a SET.  Below is the stored procedure and the java code. We are running AIX 11.50. FC9XA. Any help for this non-java DBA would be greatly appreciated.
 
Thanks,
David
 
-----------------------------------------------------------------------------------------------------------------------------------------
package com.west.wic.test;
 
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.Set;
 
import javax.swing.text.html.HTMLDocument.HTMLReader.PreAction;
 
/**
*
* This class uses the CallableStatement to call a stored procedure using
* SET(ROW()).
*
 * This stored procedure resides inside ibm387 drain_system database
*
 * CREATE PROCEDURE TESTING_SET(TEST_SET SET(CHAR(10) NOT NULL))
    RETURNING CHAR(10);
                    DEFINE element CHAR(10);
                    FOREACH SELECT * INTO element FROM TABLE(TEST_SET)
                        RETURN element WITH RESUME;
                   END FOREACH;
                END PROCEDURE;
*
* DROP PROCEDURE TESTING_SET
*
* EXECUTE PROCEDURE TESTING_SET(SET{'GroupTestHash1', 'GroupTestHash2'})
*

Well, I'm not much of a client tools type person, but if I changed your TESTING_SET SPL definition from what you have to this, then the supplied java code worked for me on 11.70 (prior to the change I was also getting the -674 cant resolve routine error).

CREATE PROCEDURE TESTING_SET(TEST_SET multiset(lvarchar(20) NOT NULL))

RETURNING CHAR(20);

DEFINE element CHAR(20);

FOREACH SELECT * INTO element FROM TABLE(TEST_SET)

RETURN element WITH RESUME;

END FOREACH;

END PROCEDURE;


So I had to change from using set to multiset, and the types inside the multiset needed to change from char to lvarchar, then the java code ran. The reason I decided to do that was because I got some SQLIDEBUG output from the java program and it looked like jdbc was sending the types over as multiset/lvarchar rather then set/char which affects the routine signature and so it was looking for a testing_set procedure that took a multiset as an arg...not sure if they should be equivalent or not...but there's the output from SQLIDEBUG that I'm refering to:

C->S (46) Time: 2013-05-03 22:30:31.79607
SQ_PREPARE
# values: 1
CMD.....: "execute procedure testing_set(?)" [32]
SQ_NDESCRIBE
SQ_WANTDONE
SQ_EOT

S->C (90) Time: 2013-05-03 22:30:31.79619
SQ_DESCRIBE
Stmt Type...........: 56
Server Stmt Id......: 0


C->S (1120) Time: 2013-05-03 22:30:31.82228
SQ_ID
0
SQ_CURNAME
"_ifxc0000000000001" [18]
SQ_BIND
# values: 1
0) Type.....: SQLSENDRECV; NULLABLE
Extended Type Name: "multiset ( lvarchar not null) " [31]

So that shows the prepare coming across getting statement id 0..then the client doing the bind to the host variable but the types it used were multiset/lvarchar.

Jacques Renaut
IBM Informix Advanced Support
APD Team

[Link, David A]

Thanks, Jacques.

We also found the following...

Informix SET will work if you add the following method to your java code:
connection.setTypeMap(map)  map set to hashset

I tested it and it ran successfully.

Yes, it took a long while to figure it out due to lack of documentation out there! 
As a programmer it is frustrating that 1 line would make the difference…


-----Original Message-----
From: informix-list-***@iiug.org [mailto:informix-list-***@iiug.org] On Behalf Of jrenaut
Sent: Saturday, May 04, 2013 8:10 AM
To: informix-***@iiug.org
Subject: Re: Java question - Calling a stored procedure with a set as an argument

We are having issues with getting java to find the stored procedure when the argument is a SET.  Below is the stored procedure and the java code. We are running AIX 11.50. FC9XA. Any help for this non-java DBA would be greatly appreciated.
 
Thanks,
David
 
-----------------------------------------------------------------------------------------------------------------------------------------
package com.west.wic.test;
 
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.Set;
 
import javax.swing.text.html.HTMLDocument.HTMLReader.PreAction;
 
/**
*
* This class uses the CallableStatement to call a stored procedure using
* SET(ROW()).
*
 * This stored procedure resides inside ibm387 drain_system database
*
 * CREATE PROCEDURE TESTING_SET(TEST_SET SET(CHAR(10) NOT NULL))
    RETURNING CHAR(10);
                    DEFINE element CHAR(10);
                    FOREACH SELECT * INTO element FROM TABLE(TEST_SET)
                        RETURN element WITH RESUME;
                   END FOREACH;
                END PROCEDURE;
*
* DROP PROCEDURE TESTING_SET
*
* EXECUTE PROCEDURE TESTING_SET(SET{'GroupTestHash1', 'GroupTestHash2'})
*

Well, I'm not much of a client tools type person, but if I changed your TESTING_SET SPL definition from what you have to this, then the supplied java code worked for me on 11.70 (prior to the change I was also getting the -674 cant resolve routine error).

CREATE PROCEDURE TESTING_SET(TEST_SET multiset(lvarchar(20) NOT NULL))

RETURNING CHAR(20);

DEFINE element CHAR(20);

FOREACH SELECT * INTO element FROM TABLE(TEST_SET)

RETURN element WITH RESUME;

END FOREACH;

END PROCEDURE;


So I had to change from using set to multiset, and the types inside the multiset needed to change from char to lvarchar, then the java code ran. The reason I decided to do that was because I got some SQLIDEBUG output from the java program and it looked like jdbc was sending the types over as multiset/lvarchar rather then set/char which affects the routine signature and so it was looking for a testing_set procedure that took a multiset as an arg...not sure if they should be equivalent or not...but there's the output from SQLIDEBUG that I'm refering to:

C->S (46) Time: 2013-05-03 22:30:31.79607
SQ_PREPARE
# values: 1
CMD.....: "execute procedure testing_set(?)" [32]
SQ_NDESCRIBE
SQ_WANTDONE
SQ_EOT

S->C (90) Time: 2013-05-03 22:30:31.79619
SQ_DESCRIBE
Stmt Type...........: 56
Server Stmt Id......: 0


C->S (1120) Time: 2013-05-03 22:30:31.82228
SQ_ID
0
SQ_CURNAME
"_ifxc0000000000001" [18]
SQ_BIND
# values: 1
0) Type.....: SQLSENDRECV; NULLABLE
Extended Type Name: "multiset ( lvarchar not null) " [31]

So that shows the prepare coming across getting statement id 0..then the client doing the bind to the host variable but the types it used were multiset/lvarchar.

Jacques Renaut
IBM Informix Advanced Support
APD Team
_______________________________________________
Informix-list mailing list
Informix-***@iiug.org
http://www.iiug.org/mailman/li