× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Setting up the cursor and the result set worked.  I appreciate the
detailed explanations from everyone since this is my first day
working/creating stored procedures it helped tremendously.

 

Now I am on to expand this new knowledge!!!

 

Curt VanCalster

 

 

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
 

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-2024 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.