Re: Callablestatement.execute() and clearParameter() causing exception



rohini wrote:

With SQL 2005 jdbc driver i connect to 2005 database using parameter selectMethod=cursor. //The procedure generates an id(long) and returns it.It also inserts id and name into a table
cstmt = conn.prepareCall("{call test.testprocedure(?,?)}");
cstmt.registerOutParameter(1, Types.INTEGER);
cstmt.setLong(1,0);
cstmt.setString(2, "testname"); System.out.println(cst.toString()); cstmt.execute();
id=cstmt.getLong(1);
Throws the error,
com.microsoft.sqlserver.jdbc.SQLServerException: A server cursor cannot be opene
d on the given statement or statements. Use a default result set or client curso
r.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError
(Unknown Source)
at com.microsoft.sqlserver.jdbc.IOBuffer.processPackets(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.sendExecute(Unknown S
ource)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecute(Unknown Sou
rce)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(Unkno
wn Source)
at t.test(t.java:54)
at t.main(t.java:105)

The same code when run against a MSSQL 2000 database, gives this error:
com.microsoft.sqlserver.jdbc.SQLServerException: A server cursor is not allowed
on a remote stored procedure or stored procedure with more than one SELECT statement. Use a default result set or client cursor.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError
(Unknown Source)
at com.microsoft.sqlserver.jdbc.IOBuffer.processPackets(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.sendExecute(Unknown S
ource)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecute(Unknown Sou
rce)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(Unkno
wn Source)
at t.test(t.java:50)
at t.main(t.java:64)

When i connect using selectMethod=direct(the default), the execution goes through. Also when i do it as cstmt.executeUpdate(), it goes through.

Another bug i face is when cstmt.clearParameters() is given after conn.prepareCall, i get
a null pointer exception in cstmt.getLong(1); The same code used to work fine with MSSQL 2000 jdbc drivers without any of these changes.

Could you show us the text of the procedure?
thanks

.

Relevant Pages