Hi all!!
I am using JConnect52
JDBC driver to connect to Sybase Adaptive Server Anywhere 6.0.
My problem is this: I am unable to retrieve the OUT parameter from a Stored Procedure which returns multiple resultsets, a return value & an OUT parameter. Only the resultset & return value is retrieved. On attempting to retrieve the OUT parameter, i get an SQL Exception.
Consider a Sybase Stored Procedure :
procedure dba.MyProcedure(@SId integer,@VId integer,@outparam integer)
as
begin
select @outparam=48
select* from shippers where shippers.shipperid>@SId
select* from vineyards where vineyards.vineyardrid>@VId
return 678
end
This is the code i use to execute the above Stored Procedure & then retrieve the parameters:
// assuming i am already connected to sybase
String sStatement = new String("{?= call MyProcedure(?, ?, ?)}");
CallableStatement cs = conn.prepareCall(sStatement);
cs.setInt(2, 2);
cs.setInt(3, 2);
cs.setInt(4, 2);
cs.registerOutParameter (1, Types.INTEGER);
cs.registerOutParameter (4, Types.INTEGER);
System.out.println ("now executing the stored procedure... ");
if(!cs.execute())
{
System.out.println("Does not return a resultset");
}
while (cs.getMoreResults())
{
System.out.println ("retrieving a resultset.....");
ResultSet rs = cs.getResultSet();
ResultSetMetaData rsmd = rs.getMetaData();
int totalcols = rsmd.getColumnCount();
int firsttime = 1;
while(rs.next())
{
if(firsttime == 1)
{
for(int i =1;i<=totalcols;i++)
{
System.out.print(" "+rsmd.getColumnName(i));
}
firsttime = 0;
System.out.println("");
}
for(int i = 1; i<=totalcols;i++)
{
System.out.print(" "+rs.getString(i));
}
System.out.println("");
}
}
int iRetval = cs.getInt (1);
System.out.println ("Return Value = " + iRetval);
// retrieve the OUT params
int iOutparam = cs.getInt(4);
System.out.println ("OUT param = " + iOutparam);
The code execute fine upto printing the return value, but then an SQL Exception is thrown.
Is there some way that i can retrieve the parameters & return value & resultset?