mtserver - spjs with resultsets failing - no results returned

[Aruna Sadashiva]

SPJ tests with result sets failed because there are no result sets returned from the procedure. the same SPJ works from sqlci, but fails from trafci.

Steps:
---------
SQL>create table t1 (a int not null primary key, b varchar(20));
SQL>insert into t1 values(111, 'aaaaa');
SQL>insert into t1 values(222, 'bbbbb');
SQL>create library testrs file '/opt/home/trafodion/SPJ/testrs.jar';
SQL>create procedure RS200()
       language java
       parameter style java
       external name 'Testrs.RS200'
       dynamic result sets 1
       library testrs;
SQL>call rs200();
--- SQL operation complete.
---------

The expected result is:

SQL >call rs200();
A B
----------- --------------------
        111 aaaaa
        222 bbbbb
--- 2 row(s) selected.
--- SQL operation complete.

The jar file, testrs.jar, is on amber7 under /opt/home/trafodion/SPJ. It has the SPJ procedure:

   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);
   }

[Zbigniew Omanski (zbigniew-omanski)]

SPJ with Result Set doesn't work with T2 Driver.

1. Created SPJ that calls T2 Driver

import java.io.*;
import java.sql.*;
import java.math.*;
import java.util.*;

public class ZProcs
{
  public static void ZN0220(java.sql.ResultSet[] columns, java.sql.ResultSet[] select)
  {
      Connection conn = null;
      Statement stmt = null;
      Properties props = null;
      String url = null;
      String usr = null;
      String pwd = null;
      String catalog = null;
      String schema = null;
      String hpjdbc_version = null;

      try {
          String propFile = "/opt/home/zomanski/spj/t2prop";
          if (propFile != null) {
              FileInputStream fs = new FileInputStream(new File(propFile));
              props = new Properties();
              props.load(fs);

              url = props.getProperty("url");
              catalog = props.getProperty("catalog");
              schema = props.getProperty("schema");

          } else {
              System.out.println("ERROR: hpt2jdbc.properties is not set. Exiting.");
              System.exit(0);
          }
          Properties myProp = new Properties();
          myProp.put("catalog", catalog);
          myProp.put("schema", schema);

          Class.forName("org.trafodion.jdbc.t2.T2Driver");
          conn = DriverManager.getConnection(url, myProp);
          stmt = conn.createStatement();
          stmt.executeUpdate("set schema TRAFODION.T4QA");

          PreparedStatement ps1 = conn.prepareStatement("select * from TRAFODION.T4QA.T");
          columns[0] = ps1.executeQuery();

          PreparedStatement ps2 = conn.prepareStatement("select * from TRAFODION.T4QA.T");
          select[0] = ps2.executeQuery();
      }
      catch (Exception e) {
          System.out.println(e);
     }
  }
  public static void main(String[] paramArrayOfString)
  {
  }
}
2. t2prop

catalog=TRAFODION
schema=T2QA
url=jdbc:t2jdbc:
hpjdbc_version=org.trafodion.jdbc.t2.T2Driver
usr=sqluser_admin
pwd=redhat06
batchBinding=500

3. When executed from SQL got correct output:
>>create library spjzcall file '/opt/home/zomanski/spj/lib/zcall.jar';

--- SQL operation complete.

>>Create procedure ZN0220 () external name 'ZProcs.ZN0220' library spjzcall language java parameter style java DYNAMIC RESULT SETS 2;

--- SQL operation complete.

>>call ZN0220();

C1 C2 C3 C4 C5 C6 C7 C8 C9 C10 C11 C12
-------------------- ------ ----------- -------------------- ------------------------------------------------------------------------------------------------------------------------ --------------------- ------------ ---------- -------- -------------------------- ------------------------- -------------------------

Moe 100 12345678 123456789012 Moe 100.12 100.12...