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; /** * @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()); } } }
On Thursday, May 2, 2013 4:25:11 PM UTC-5, Link, David A wrote:
> 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'})
> *
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
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-bounces@iiug.org [mailto:informix-list-bounces@iiug.org] On Behalf Of jrenaut
Sent: Saturday, May 04, 2013 8:10 AM
To: informix-list@iiug.org
Subject: Re: Java question - Calling a stored procedure with a set as an argument
On Thursday, May 2, 2013 4:25:11 PM UTC-5, Link, David A wrote:
> 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'})
> *
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-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list