SPJ w result set failed with ERROR[11220], SQLCODE of -29261, SQLSTATE of HY000

[Chong Hsu]

Tested with Trafodion build, 20140801-0830.

Calling a SPJ that calls another SPJ with result set:

   public static void RS363()
     throws Exception
   {
     String str = "jdbc:default:connection";

     Connection localConnection = DriverManager.getConnection(str);
     Statement localStatement = localConnection.createStatement();

     CallableStatement localCallableStatement = localConnection.prepareCall("{call RS200()}");
     localCallableStatement.execute();
   }

   public static void RS200(ResultSet[] paramArrayOfResultSet)
   throws Exception
   {
     String str1 = "jdbc:default:connection";

     String str2 = "select * from t1";
     Connection localConnection = DriverManager.getConnection(str1);
     Statement localStatement = localConnection.createStatement();
     paramArrayOfResultSet[0] = localStatement.executeQuery(str2);
   }

it failed with ERROR:

*** ERROR[11220] A Java method completed with an uncaught java.sql.SQLException with invalid SQLSTATE. The uncaught exception had a SQLCODE of -29261 and SQLSTATE of HY000. Details: java.sql.SQLException: No error message in SQL/MX diagnostics area, but sqlcode is non-zero [2014-08-04 22:57:28]

The SPJ Jar file is attached. Here are the steps to produce the error:

set schema testspj;

create library spjrs file '/<Jar location>/Testrs.jar';

create procedure RS363()
       language java
       parameter style java
       external name 'Testrs.RS363'
       dynamic result sets 0
       library spjrs;

--- SQL operation complete.

create procedure RS200()
       language java
       parameter style java
       external name 'Testrs.RS200'
       dynamic result sets 1
       library spjrs;

create table T1
  (
    A INT DEFAULT NULL
  , B INT DEFAULT NULL
  ) no partitions;

Call RS363();

*** ERROR[11220] A Java method completed with an uncaught java.sql.SQLException with invalid SQLSTATE. The uncaught exception had a SQLCODE of -29261 and SQLSTATE of HY000. Details: java.sql.SQLException: No error message in SQL/MX diagnostics area, but sqlcode is non-zero [2014-08-04 22:57:28]

[Justin Du (justin-du-2)]

The problem happens with stored procedure calling another. We noticed that the procedure RS200 used result sets.

Can you try a test that the inner one without the result sets?

[Justin Du (justin-du-2)]

Chong tried the case that inner procedure without result sets and no java error was returned.

Our T2 drive may not handle the result set parameters properly.