title: iseries - java external stored procedure returning result sets
body: <p>I am trying to build a SP to return a result set from a remote iSeries and I just can't seem to do it. I can return result sets from the local database, but not remote when I use JT400native.jar (also tried just the jt400.jar) to connect. Anyone have any idea what I am doing wrong?</p>
  
  <p>My SP is defined as this.</p>
  
  <pre><code>CREATE PROCEDURE MYLIB.TEST   
  (INOUT I INTEGER)                     
  EXTERNAL NAME 'jproc1.returnTEST'  
  PARAMETER STYLE JAVA                 
  MODIFIES SQL DATA                    
  DYNAMIC RESULT SETS 1                
  LANGUAGE JAVA 
  </code></pre>
  
  <p>Here the java code behind it which works, it will return result sets from the local database to the client (run sql scripts in iNavigator)</p>
  
  <pre><code>import java.sql.*;
  import com.ibm.db2.app.*;
  
  public class jproc1 {      
       public static void returnTEST(int[] myInputInteger, ResultSet[] myResultSet) throws Exception {
            Connection con = DriverManager.getConnection("jdbc:default:connection");
            Statement stmt1 = con.createStatement();
            String sql1 = "select TEST FROM MYLIB.TEST";
            myInputInteger[0] = 5;
            myResultSet[0]  = stmt1.executeQuery(sql1);
       }    
  } 
  </code></pre>
  
  <p>When I change the SP to to replace the connection with one to a remote iSeries it won't return the result set back to the client, it does however return the first out variable myInputInteger just fine. I believe I have everything setup correctly, I have all the Jars I need registered.  The important part here is that internally to the java program I get the result set from the remote iSeries, I can loop through it, count it, dump it to the IFS, it just won't pass it back to the client (Run SQL Scripts in iNavigator).  </p>
  
  <pre><code>import java.sql.*;
  import com.ibm.db2.app.*;
  
  public class jproc1 {      
      public static void returnTEST(int[] myInputInteger, ResultSet[] myResultSet) throws Exception {
           Class.forName ("com.ibm.as400.access.AS400JDBCDriver").newInstance ();
           String url  = "jdbc:as400://remoteiseries;naming=sql;prompt=false;user=myuser;password=mypass;translate binary=true";                
           Connection con = DriverManager.getConnection(url);   
           Statement stmt1 = con.createStatement();
           String sql1 = "select TEST FROM MYLIB.TEST";
           myInputInteger[0] = 5
           myResultSet[0] = stmt1.executeQuery(sql1);
      } 
  }        
  </code></pre>
  
  <p>So, what am I missing?  I have tried a ton of variations including using DB2GENERAL parameter types (and corresponding changes to java program per chapter 7 on the DB2 UDB for iSeries manual).  No matter what I do it won't return those remote result sets back to client, and I don't get any errors.</p>
  
  <p>TIA.</p>