I can not get output parameter values from procedure, was: missed moderated message

[Rick Hillegas <ri...@oracle.com>]

The following code works fine on 10.10.2.0 for me:

import java.sql.*;
import java.util.*;

public class zz
{
     public  static  void    main( String... args ) throws Exception
     {
         Connection  conn = DriverManager.getConnection( 
"jdbc:derby:memory:db;create=true" );

         conn.prepareStatement
             (
              "create type list external name 'java.util.List' language 
java"
              ).execute();
         conn.prepareStatement
             (
              "create procedure getUserName(out userName list) parameter 
style java reads sql data language java external name 'zz.getUserName'"
              ).execute();

         CallableStatement cs = conn.prepareCall( "call getUserName( ? )" );
         cs.registerOutParameter( 1, Types.JAVA_OBJECT );
         cs.execute();

         System.out.println( cs.getObject( 1 ) );
     }

     public  static  void    getUserName( List<String>[] outParam ) 
throws Exception
     {
         ArrayList<String>   list = new ArrayList<String>();

         Connection  conn = DriverManager.getConnection( 
"jdbc:default:connection" );
         ResultSet   rs = conn.prepareStatement( "select tableName from 
sys.systables" ).executeQuery();

         while ( rs.next() )
         {
             list.add( rs.getString( 1 ) );
         }
         rs.close();

         outParam[ 0 ] = list;
     }

}

Hope this helps,
-Rick

On 8/21/14 2:23 PM, Myrna van Lunteren wrote:
> Hi,
>
> I noticed there were a few moderated messages that I do not think made 
> it to the list. I tried to moderate it through, but that failed - 
> perhaps it's too long ago...
>
> Here's one from July 3...
>
> Myrna
> -------------
>
> From: china_wang <2596121187@qq.com <ma...@qq.com>>
> To: derby-user@db.apache.org <ma...@db.apache.org>
> Cc:
> Date: Thu, 3 Jul 2014 02:36:09 -0700 (PDT)
> Subject: I can not get output parameter values from procedure
> hi,all: my procedure code is follow:
> *****************************************
> "create procedure app.getUserName(out userName list) parameter style 
> java reads sql data language java external name 
> 'tool.ProcedureClass.getUserName';" Output parameters type-- 'list' is 
> user-defined type,Defined as follows: " CREATE TYPE list EXTERNAL NAME 
> 'java.util.List' LANGUAGE JAVA " 
> ******************************************* 
> tool.ProcedureClass.getUserName code is public static void 
> getUserName(List[] list) { Connection connection =null; 
> PreparedStatement p=null; ResultSet rs =null; try { list=new 
> ArrayList[1]; list[0]=new ArrayList(); connection = 
> DriverManager.getConnection("jdbc:default:connection"); p = 
> connection.prepareStatement(" select name from pub_user "); rs = 
> p.executeQuery(); while (rs.next()) { list[0].add(rs.getString(1));// 
> I can get value of rs.getString(1) } } catch (Exception e) { 
> e.printStackTrace(); } finally { try { rs.close(); p.close(); 
> connection.close(); } catch (Exception e2) { } } } 
> ******************************************************* i call 
> procedure by follow code: public String getDataSet(String dsId,String 
> dtId)throws Exception { String driver = 
> "org.apache.derby.jdbc.EmbeddedDriver"; String url = 
> "jdbc:derby:metabase;create=true"; try { Class.forName(driver); 
> Connection connection = DriverManager.getConnection(url); 
> CallableStatement cs = connection.prepareCall("CALL 
> APP.getUserName(?)"); cs.registerOutParameter(1, Types.JAVA_OBJECT); 
> cs.execute(); //String retVal=cs.getString(3); cs.getObject(1); // 
> value is null by debug's inspect fucntion return ""; } catch(Exception 
> e) { e.printStackTrace(); throw e; } }
> i don't know why cs.getObject(1) return null? Thanks a lot in advance 
> china_wang
> ------------------------------------------------------------------------
>