Skip to Main Content

Java Database Connectivity (JDBC)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Retrieving output parameters from SQL Server stored procedure

843854Nov 8 2001 — edited Jan 30 2004
Hi,

I'm unable to retrieve an output parameter from a SQL Server 7 stored procedure.

Here is my stored procedure (this works fine when run from the SQL Server Query Analyser):
CREATE Procedure JavaProcTEST
(	
	@poIdField integer  OUTPUT,
	@piValue integer	
)
As
insert into AVTEST(VALUE) values (@piValue)
set @poIdField =@@identity
I've tried creating the callable statement in two different ways:

First:
	proc = cnx.prepareCall("{?= call JavaProcTEST(?)}");
	try
	{
		proc.registerOutParameter(1,java.sql.Types.INTEGER);
		proc.setInt(2, 27);
		proc.execute();
		rtn = proc.getInt(1);
	}
	catch(Exception e)
	{
		//etc
	}
This returns a SQL Server error saying 'Procedure 'JavaProcTEST' expects parameter '@piValue', which was not supplied' and the row is not inserted into AVTEST.

Second:
same as above only the prepareCall line is:
	
	proc = cnx.prepareCall("{call JavaProcTEST(?,?)}");
This time the row is inserted into AVTEST but the driver returns an error saying 'The requested data is not available'.

As the stored procedure runs fine on the database, I wondered whether the error was with the JDBC driver but I've tried both the JRun JDBC type 4 driver and the beta MS SQL Server 2000 JDBC driver and the errors are the same with both.

Could anyone please help??!
Thanks.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 27 2004
Added on Nov 8 2001
12 comments
541 views