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]

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…

_______________________________________________
Informix-list mailing list
Inform...@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list