Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Problems with Callable Statement in Sybase

 
Sumeet Anand
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi All,
I am using Callable Statements with SybaseJconnect5_2
CallableStatement cstmt = connection.prepareCall("{ call FEE_accinfo_fee_management_Q(???) }");
The stored proc FEE_accinfo_fee_management_Q takes 2 input parameters and gives 2 output parameters.
For this i am doing the following:
********************************************
cstmt.setInt( 1,2000050 );
cstmt.setInt( 2,6 );
cstmt.registerOutParameter(1,Types.INTEGER);
cstmt.registerOutParameter(2,Types.VARCHAR);
cstmt.execute();
int feeuser = cstmt.getInt( 1 );
String profile = cstmt.getString( 2 );
cstmt.close();
*********************************************
Now when i execute this code it gives me this error.
//////////////////////////////////////////
java.sql.SQLException: JZ0SA: Prepared Statement: Input parameter not set, index: 2.
at com.sybase.jdbc2.jdbc.ErrorMessage.raiseError(Erro rMessage.java:426)
at com.sybase.jdbc2.tds.TdsParam.prepareForSend(TdsParam.java:88)
at com.sybase.jdbc2.jdbc.ParamManager.checkParams(ParamManager.java:574)
at
com.sybase.jdbc2.tds.Tds.rpc(Tds.java:662)
at com.sybase.jdbc2.jdbc.SybCallableStatement.sendRpc(SybCallableStatement.java:416)
at com.sybase.jdbc2.jdbc.SybCallableStatement.execute(SybCallableStatement.java:121)
at TestDatabase.main(TestDatabase.java:39)

PLease help out with this one.
Thanks in advance.
Regards,
Sumeet.
 
Tina Coleman
Ranch Hand
Posts: 150
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I believe that the getX statements use the index of the parameter on the stored procedure, not the index of the set of output parameters. So, instead of getInt(1), you'd getInt(3). Also, you'd registerOutParameter(3, Type.Integer), rather than registerOutParamter(1, Type.Integer). [Repeat pattern for second output parameter.]
 
Sumeet Anand
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks a lot for your timely help.
It works now.
Regards,
Sumeet.
[ December 27, 2002: Message edited by: Sumeet ]
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
"Sumeet",
You have been asked several times to change your display name to comply with the Javaranch naming policy, and it still not correct. The full description can be found here
We require names to have at least two words, separated by a space, and strongly recommend that you use your full real name.
Please change your name immediately. Accounts that have invalid display names get deleted.
thanks,
Dave
 
Sumeet Anand
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
Now the problem is with retrieving the contents of the result set.
I am writing this code.
*********************************************
if(connection != null)
{
cstmt = connection.prepareCall("{ call "+queryName+"(?,?) }");

cstmt.registerOutParameter(1,Types.INTEGER);
cstmt.registerOutParameter(2,Types.VARCHAR);

rs = cstmt.executeQuery();

int errorcode = cstmt.getInt( 1 );
String errmsg = cstmt.getString( 2 );
System.out.println("Error code"+errorcode+" and error msg is "+errmsg);
}

while(rs.next())
{
int id_trans = rs.getInt("id_trans");
System.out.println("Trans id is as"+id_trans);
}
rs.close();
cstmt.close();
Now it gives me an error.
**************************************************
java.sql.SQLException: JZ0R0: ResultSet has already been closed.

Could anyone pull me out of this soup ?
Regards,
Sumeet.
 
Tina Coleman
Ranch Hand
Posts: 150
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
First, I assume that you have a populated ResultSet, given your query? I'd assume that if you got the error code/error message printout, that you might have an empty result set. Try running your stored procedure within a SQL window, and verify that you get back a result set. (You shouldn't get back a SQLException, however, unless somehow your stored procedure is _really_ bombing.)
If you don't get the error code message printout, then you're not actually using the resultSet generated by the CallableStatement - you're using wherever you've created (and possibly closed) earlier.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic