RE: Calling Stored procedures

["Richard Casey" <casey_r@xxxxxxxxxxxxxxxx>]

Curt,

Your stored procedure needs to have output parameters or return a result set
before a calling program can pick up any values.

Try this for the stored procedure:

CREATE PROCEDURE SBGDTATEST.TEST (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 ;

    END  ;

Then in your Java program,

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.registerOutParameter(1,Types.CHAR);
  cs.registerOutParameter(2,Types.CHAR);
  cs.execute();
System.out.println( "Got from stored procedure: " + cs.getString(2));


Hope this helps!
Richard


> > > Curt.Vancalster@xxxxxxxxxx 10/11/2006 12:45 PM >>>

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

--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.408 / Virus Database: 268.13.2/471 - Release Date: 10/10/2006