wood burning stoves 2.0*
The moose likes JDBC and the fly likes Retrieiving OUT parameters & return value from a Sybase Stored Procedure Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Retrieiving OUT parameters & return value from a Sybase Stored Procedure" Watch "Retrieiving OUT parameters & return value from a Sybase Stored Procedure" New topic
Author

Retrieiving OUT parameters & return value from a Sybase Stored Procedure

Bhavin Shah
Greenhorn

Joined: Dec 10, 2000
Posts: 7
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?
DAYANAND BURAMSHETTY
Ranch Hand

Joined: Aug 06, 2001
Posts: 34

//This is stored procedure
----------------------------
Stored procedure Name : MyProcedure
TABLENAME : EXTB
IN PARAMETERS : ALL PARAMETER EXCEPT D,E
OUT PARAMETER : D,E
D SEQUENCE NAME : DSEQ

CREATE OR REPLACE PROCEDURE MyProcedure(
A NUMBER,
B NUMBER,
C NUMBER,
D OUT NUMBER, //4,5 th filed out parameter
E OUT NUMBER) AS
BEGIN
SELECT DSEQ.NEXTVAL INTO D FROM DUAL;
SELECT ESEQ.NEXTVAL INTO E FROM DUAL;
INSERT INTO EXTB VALUES(A,B,C,D,E);
COMMIT;
END;

----------------------------
//This is java program
----------------------------
.........
......................
//in stored procedure 5 arguments ......that's I put 5 question //marks

String sStatement = new String("{call MyProcedure(?,?,?,?,?)}");
CallableStatement cs = conn.prepareCall(sStatement);
cs.setInt(1, 2);
cs.setInt(2, 2);
cs.setInt(3, 2);
cs.registerOutParameter (4, Types.INTEGER);//4,5 th fields getting from database
cs.registerOutParameter (5, Types.INTEGER);
int i=cstmt.executeUpdate();
if(i!=0)
{
...........throw the exception(error)........

}else{
// retrieve the OUT params
System.out.println("4 Th Value :"+ cs.getInt(4));
System.out.println("5 Th Value :"+ cs.getInt(5));

}
......
............


Dil se....,<BR>Dayanand<BR>0065-8839071(off)<BR>0065-7547034(Res0
 
 
subject: Retrieiving OUT parameters & return value from a Sybase Stored Procedure
 
Similar Threads
How to create Oracle stored procedure and return ResultSet as OUT param...?
stored procedure: Parameter index out of range
How can we call a stored procedure from java file
Callable Statement
callable statement