This week's book giveaway is in the Android forum.
We're giving away four copies of Learning Java by Building Android Games and have Jean-Marcel Belmont on-line!
See this thread for details.
Win a copy of Learning Java by Building Android Games this week in the Android forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Bear Bibeault
  • Jeanne Boyarsky
  • Tim Cooke
Sheriffs:
  • Knute Snortum
  • Junilu Lacar
  • Devaka Cooray
Saloon Keepers:
  • Ganesh Patekar
  • Tim Moores
  • Carey Brown
  • Stephan van Hulst
  • salvin francis
Bartenders:
  • Ron McLeod
  • Frits Walraven
  • Pete Letkeman

Problems with Callable Statement in Sybase  RSS feed

 
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.
 
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 ]
 
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.
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!