A stored procedure can have both IN and OUT parms and result sets.  I
missed this before..to return data in a result set(s) you have to
specify that you are doing so in the procedure definition with  'RESULT
SETS X' where X is the number of result sets you are returning.  So your
procedure definition would look something like the following.  I'm not
an expert on SQL stored procedures, we use External stored procedures
that call RPG programs and return result sets that way. I'm not sure if
you have to declare a cursor in an SQL result set or not, perhaps
someone else could help there.
 
Anyway, adding RESULT SETS 1 may solve your problem.  

CREATE PROCEDURE SBGDTATEST.TEST ( )
    LANGUAGE SQL
    SPECIFIC SBGDTATEST.TEST
    RESULT SETS 1 
    NOT DETERMINISTIC
    READS SQL DATA
    CALLED ON NULL INPUT
 
    BEGIN
    DECLARE VAR1 CHAR ;
    DECLARE VAR2 CHAR ;
    SELECT CUSTOMER_NUMBER, CUSTOMER_NAME INTO VAR1, VAR2 FROM
SBGDTATEST.CUSTOMER_MASTER ;
 
    END  ;


Curt.Vancalster@xxxxxxxxxx 10/11/2006 2:54 PM >>>

Richard,

I was able to get your suggestion to work.  This was the next piece I
was going to try after a got the first part working without using
parms.
Thanks for you help!! 

Nancy,

Your suggestion makes perfect sense to me, but now I am getting a
NullPointerException.  To make sure I understand what I am trying to
do:
When I have a stored procedure that doesn't have any IN or OUT parms
then in my java I have to define a resultset to retrieve the data from
my procedure?

Here is my updated code, any more suggestions?  It gives me the error
at
my while statement.

cs.execute();
  ResultSet rs = cs.getResultSet();
  while (rs.next ())
  {
  String customerName = new String();
  String customerNumber = new String();
  customerName = rs.getString("VAL2");
  customerNumber = rs.getString("VAL1");
  System.out.println("Got from stored procedure:" + " " +
customerNumber
+ " " + customerName);
}

Thanks for the help thus far!!


Curt VanCalster
Lead Programmer
Smithfield Beef Group., - Green Bay, WI
(920) 406-1174
curt.vancalster@xxxxxxxxxx


-----Original Message-----
From: Curt Vancalster 
Sent: Wednesday, October 11, 2006 11:46 AM
To: 'java400-l@xxxxxxxxxxxx'
Subject: Calling Stored procedures

I am trying to create my first stored procedure(which I have done),
but
now I am trying to use it in java.  I am getting an error on the last
statement which is "An undefined column name was detected".

Can anyone see anything that I am doing wrong?  

  DriverManager.registerDriver(new
com.ibm.as400.access.AS400JDBCDriver());
Connection myConnection =
DriverManager.getConnection("jdbc:as400://S104M74M","","");
  Statement myStatement = myConnection.createStatement();
  String SQLString = new String("{CALL SBGDTATEST.TEST}");
  CallableStatement cs = myConnection.prepareCall(SQLString);    
  cs.execute();
String Column = new String("CUSTOMER_NAME");  
System.out.println( "Got from stored procedure: " +
cs.getString(Column)
);

Could it be something in my stored procedure?  I wanted to try
something
easy so I only selected two columns in my stored procedure and without
any parameters.  Here is how I created it:

CREATE PROCEDURE SBGDTATEST.TEST ( ) 
    LANGUAGE SQL 
    SPECIFIC SBGDTATEST.TEST 
    NOT DETERMINISTIC 
    READS SQL DATA 
    CALLED ON NULL INPUT 

    BEGIN 
    DECLARE VAR1 CHAR ; 
    DECLARE VAR2 CHAR ; 
    SELECT CUSTOMER_NUMBER, CUSTOMER_NAME INTO VAR1, VAR2 FROM
SBGDTATEST.CUSTOMER_MASTER ; 

    END  ; 

I would appreciate any suggestions.

Thanks,

Curt VanCalster
Lead Programmer
Smithfield Beef Group., - Green Bay, WI
(920) 406-1174
curt.vancalster@xxxxxxxxxx


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2022 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.