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



Hi. I have duplicated the bug with a standalone below.
MS will respond soon, I am sure.

Joe Weinstein at BEA Systems

I get:
C:\new_ms_driver>java foo
Driver version is 1.0.809.102
com.microsoft.sqlserver.jdbc.SQLServerException: A server cursor cannot be opened on the given
statement or statements. 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 Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(Unknown Source)
at foo.main(foo.java:52)

from the program below. If I remove the selectMethod, it runs OK.

import java.sql.*;
import java.util.*;

public class foo
{
public static void main(String args[])
throws Exception
{
Connection c = null;
Connection c2 = null;
try
{
Properties props = new Properties();
Driver d = new com.microsoft.sqlserver.jdbc.SQLServerDriver();

props.put("user", "joe");
props.put("password", "joe");
props.put("DatabaseName", "joe");
props.put("selectMethod", "cursor");

c = d.connect("jdbc:sqlserver://joe:1433", props );

DatabaseMetaData dd = c.getMetaData();
System.out.println("Driver version is " + dd.getDriverVersion() );

Statement s = c.createStatement();

String joeproc = "CREATE PROCEDURE joeproc"
+ " @pid BIGINT OUTPUT,"
+ " @pCaption NVARCHAR( 64 ) = NULL,"
+ " @pDescription NVARCHAR(254) = NULL,"
+ " @pInstallDate NCHAR(25) = NULL "
+ " "
+ " AS"
+ " DECLARE @vclass NUMERIC(5)"
+ " SET @vclass = 216"
+ " INSERT INTO joe VALUES(1)"
+ " INSERT INTO joe2 VALUES(1)";



try{ s.executeUpdate("drop table joe");}catch (Exception ignore){}
try{ s.executeUpdate("drop table joe2");}catch (Exception ignore){}
try{ s.executeUpdate("drop procedure joeproc");}catch (Exception ignore){}

s.executeUpdate("create table joe(bar int)");
s.executeUpdate("create table joe2(bar int)");
s.executeUpdate(joeproc);
CallableStatement p = c.prepareCall("{call joeproc(?,?)}");
p.setLong(1,0);
p.setString(2, "testname");
p.execute();

}
catch(Exception exception1)
{
exception1.printStackTrace();
}
finally
{
if (c != null) try {c.close();} catch (Exception ignore){}
if (c2 != null) try {c2.close();} catch (Exception ignore){}
}
}
}





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

  • Re: Stored Procedure error is not catched
    ... >> I am using MS SQL Server JDBC Driver. ... The Stored Procedure does some inserts. ... >> failed but in my java code the SQLException is not thrown. ... The problem is that the SQLException (or really the error message ...
    (microsoft.public.sqlserver.jdbcdriver)
  • RE: odbccommand cant interpret some /*xxx*/ comments
    ... I have tried your query (creating stored procedure) with the API ... SQLExecDirect in the driver "SQL Server". ... The problem doesn't reproduce. ...
    (microsoft.public.data.odbc)
  • Invalid parameter binding
    ... I am having a problem with calling a stored procedure. ... The java code invoking the SP is as follows: ... [SQLServer 2000 Driver for JDBC]Invalid parameter binding. ...
    (comp.lang.java.databases)
  • Invalid parameter binding
    ... I am having a problem with calling a stored procedure. ... The java code invoking the SP is as follows: ... [SQLServer 2000 Driver for JDBC]Invalid parameter binding. ...
    (comp.lang.java.help)
  • Re: Callablestatement.execute() and clearParameter() causing exception
    ... A server cursor cannot be opene ... on a remote stored procedure or stored procedure with more than one SELECT statement. ... a null pointer exception in cstmt.getLong; The same code used to work fine with MSSQL 2000 jdbc drivers without any of these changes. ...
    (microsoft.public.sqlserver.jdbcdriver)