RE: Calling Stored procedures -- JAVA400-L

No.

For a stored procedure to return a result set, it need to declare a
cursor.

Basically, the cursor _IS_ the result set to be returned.

Here's an example:

CREATE PROCEDURE GetCusName()
RESULT SETS 1
LANGUAGE SQL
BEGIN
  DECLARE c1 CURSOR WITH RETURN FOR 
    SELECT cusnam FROM customer ORDER BY cusnam;
  OPEN c1;
END

With the statement the Curt is using, he should have VAR1 and VAR2
defined as output parameters of the procedure as Richard suggested.

_HOWEVER_  For the SELECT ... INTO syntax to work, the statement must
only return a single row.  I highly doubt that a select from a customer
master file would do that without a where.

So Curt you need to either, pass in a customerID.
CREATE PROCEDURE SBGDTATEST.TEST (IN CUSTID CHAR, OUT VAR1 CHAR, OUT
VAR2 CHAR)
    LANGUAGE SQL
    SPECIFIC SBGDTATEST.TEST
    NOT DETERMINISTIC
    READS SQL DATA
    CALLED ON NULL INPUT

    BEGIN
    SELECT CUSTOMER_NUMBER, CUSTOMER_NAME INTO VAR1, VAR2
    FROM SBGDTATEST.CUSTOMER_MASTER
    WHERE SBGDTATEST.CUSTOMER_ID = CUSTID;

    END  ;

Or return a result set
CREATE PROCEDURE SBGDTATEST.TEST ( )
    LANGUAGE SQL
    RESULT SETS 1
    SPECIFIC SBGDTATEST.TEST
    NOT DETERMINISTIC
    READS SQL DATA
    CALLED ON NULL INPUT

    BEGIN
      declare c1 cursor with return for
        SELECT CUSTOMER_NUMBER, CUSTOMER_NAME INTO VAR1, VAR2
        FROM SBGDTATEST.CUSTOMER_MASTER;
      open c1;  
    END  ;

HTH,

Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121

-----Original Message-----
From: java400-l-bounces@xxxxxxxxxxxx 
[mailto:java400-l-bounces@xxxxxxxxxxxx] On Behalf Of Nancy Cochran
Sent: Wednesday, October 11, 2006 3:36 PM
To: java400-l@xxxxxxxxxxxx
Subject: RE: Calling Stored procedures

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

-- 
This is the Java Programming on and around the iSeries / AS400
(JAVA400-L) mailing list
To post a message email: JAVA400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/java400-l
or email: JAVA400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/java400-l.


-- 
This is the Java Programming on and around the iSeries / 
AS400 (JAVA400-L) mailing list
To post a message email: JAVA400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/java400-l
or email: JAVA400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/java400-l.