[jdjlist] RE: SQL Server and stored procedure

[Naveen Joshi]

Hi Iwaniec,

Try this. It works for me.

Connection con = null;
CallableStatement csmt = null;
try{
        //specify the stored procedure to execute
            csmt = con.prepareCall("{Call sp_BHT(?, ?, ?, ?)}");

        //set input and output parameters if any
        csmt.setString(1,strEdi_tran_no);
            csmt.setString(2,strTrans_set_cde);
            csmt.setString(3,strHd_tl_id);
            csmt.registerOutParameter(4,Types.VARCHAR);
        
        csmt.execute();
        
        //get the output parameters if any
            strBHT = csmt.getString(4);

        csmt.close();

}catch(Exception e){
        out.println("Exception :"+e);
}


Thanks
Naveen

----------
From:  Witold Iwaniec [SMTP:[EMAIL PROTECTED]]
Sent:  Saturday, June 08, 2002 1:15 AM
To:  JDJList
Subject:  [jdjlist] SQL Server and stored procedure

Hi all

I am using the latest SQL Server 2000 JDBC driver from Microsoft, 
obviously connecting to SQL Server, and have trouble getting the 
return value from stored procedure.

I have no problem to read OUT parameters. For example if I have a 
procedure declared as:

CREATE PROC TEST_PROC 
  @NEW_ID VARCHAR2(10)    

  SELECT @NEW_ID = '123456'
  RETURN 0

I can call and read the value of NEW_ID.

But if I have a procedure

CREATE PROC TEST_PROC2

  DECLARE @RESULT
  SELECT @RESULT = some_value

  RETURN @RESULT

I can not read the returned value. The included documentation 
recommends the syntax:

{[?=]call procedure-name[([parameter][,[parameter]]...)]}
but the driver does not like it. 
Does anyone know the proper syntax to get the returned value?

Thanks

Witold

To change your membership options, refer to:
http://www.sys-con.com/java/list.cfm



To change your membership options, refer to:
http://www.sys-con.com/java/list.cfm