SQL Server JDBC - Re: Callablestatement.execute() and clearParameter() causing excep

Author

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

rohini

2006-02-17, 7:23 am

This is the procedure
CREATE PROCEDURE test.testprocedure
@p$id$ BIGINT OUTPUT,
@p$Caption NVARCHAR( 64 ) = NULL,
@p$Description NVARCHAR(254) = NULL,
@p$InstallDate NCHAR(25) = NULL,

AS
DECLARE @v$class$ NUMERIC(5);
SET @v$class$ = 216;
EXECUTE test_user.getId @v$class$, @p$id$ OUTPUT

INSERT INTO test.t$testtable ( id$, classOid$, Caption,
VALUES(@p$id$, CONVERT(binary, '1234564657'), @p$Caption);

INSERT INTO test.t$testtable2 ( id$, Description, InstallDate)
VALUES(@p$id$ , @p$Description, @p$InstallDate);

GO

"Joe Weinstein" wrote:

>
>
> rohini wrote:
>
>
> Could you show us the text of the procedure?
> thanks
>
>

Joe Weinstein

2006-02-17, 8:23 pm

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. makeFromDatabaseErro
r(Unknown Source
)
at com.microsoft.sqlserver.jdbc.IOBuffer. processPackets(Unkno
wn Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.sendExecute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecute(Unknown Source)
at com.microsoft.sqlserver.jdbc. SQLServerPreparedSta
tement.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(joepro
c);
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){}
}
}
}

[color=darkred]

David Olix

2006-02-17, 8:23 pm

Hi Joe (and everyone else who's run into this problem),

A fix to the selectMethod=cursor bug will be released, along with a KB
article, within the next week or so.

Thanks for your patience,

--David Olix
JDBC Development

rohini

2006-02-20, 3:23 am

Please it would help if you fix the clearParameter() problem also(I have
mentioned this also earlier). When csmnt.clearparameter() is given after the
prepareCall() statement ,
csmnt.execute()
csmnt.getLong(1) throws an null pointer error.
java.lang. NullPointerException

at com.microsoft.sqlserver.jdbc.StreamRetValue.deserialize(Unknown
Sourc
e)
at
com.microsoft.sqlserver.jdbc. SQLServerCallableSta
tement.indexOutParam
s(Unknown Source)
at
com.microsoft.sqlserver.jdbc. SQLServerCallableSta
tement.getOutParamet
ers(Unknown Source)
at
com.microsoft.sqlserver.jdbc. SQLServerCallableSta
tement.getterGetPara
m(Unknown Source)
at
com.microsoft.sqlserver.jdbc. SQLServerCallableSta
tement.getLong(Unkno
wn Source)
at t.test(t.java:57)
at t.main(t.java:111)

"David Olix" wrote:

> Hi Joe (and everyone else who's run into this problem),
>
> A fix to the selectMethod=cursor bug will be released, along with a KB
> article, within the next week or so.
>
> Thanks for your patience,
>
> --David Olix
> JDBC Development
>
>

David Olix

2006-02-25, 9:28 am

Hi again,

Could you post a short repro with the clearParameters() call included? I'd
like to understand where this call is relative to the others.

Thanks!

--David Olix
JDBC Development

rohini

2006-02-25, 9:28 am

Hi David,
Here is the test code
cstmt = conn.prepareCall("{call test.testprocedure(?,?)}");
cstmt.clearParameters();
cstmt. registerOutParameter
(1, Types.INTEGER);
cstmt.setLong(1,0);
cstmt.setString(2, "testname");
cstmt.executeUpdate(); //since cstmt.execute is throwing error
System.out.println("ID="+cstmt.getLong(1));//Null pointer exception on this
line

Connecting as:jdbc:sqlserver://xxx.xx.xxx. xxx:1433;selectMetho
d=cursor
Successfully connected
java.lang. NullPointerException

at com.microsoft.sqlserver.jdbc.StreamRetValue.deserialize(Unknown
Sourc
e)
at
com.microsoft.sqlserver.jdbc. SQLServerCallableSta
tement.indexOutParam
s(Unknown Source)
at
com.microsoft.sqlserver.jdbc. SQLServerCallableSta
tement.getOutParamet
ers(Unknown Source)
at
com.microsoft.sqlserver.jdbc. SQLServerCallableSta
tement.getterGetPara
m(Unknown Source)
at
com.microsoft.sqlserver.jdbc. SQLServerCallableSta
tement.getLong(Unkno
wn Source)
at t.test(t.java:57)
at t.main(t.java:111)

It would be helpful if you could give a definitive date on when the fix
would be released.

Thanks,
Rohini

"David Olix" wrote:

> Hi again,
>
> Could you post a short repro with the clearParameters() call included? I'd
> like to understand where this call is relative to the others.
>
> Thanks!
>
> --David Olix
> JDBC Development
>
>

David Olix

2006-02-25, 9:28 am

Hi Rohini,

Do you get the same result without selectMethod=cursor?

rohini

2006-02-25, 9:28 am

David,
Yes, Even without selectMethod=cursor,
the result is same with
clearParameters();

Connecting as:jdbc:sqlserver://xxx.xxx.xxx.xxx:1433
Successfully connected
java.lang. NullPointerException

at com.microsoft.sqlserver.jdbc.StreamRetValue.deserialize(Unknown
Sourc
e)
at
com.microsoft.sqlserver.jdbc. SQLServerCallableSta
tement.indexOutParam
s(Unknown Source)
at
com.microsoft.sqlserver.jdbc. SQLServerCallableSta
tement.getOutParamet
ers(Unknown Source)
at
com.microsoft.sqlserver.jdbc. SQLServerCallableSta
tement.getterGetPara
m(Unknown Source)
at
com.microsoft.sqlserver.jdbc. SQLServerCallableSta
tement.getLong(Unkno
wn Source)
at t.test(t.java:57)
at t.main(t.java:111)

"David Olix" wrote:

> Hi Rohini,
>
> Do you get the same result without selectMethod=cursor?
>
>

Khyati

2006-03-07, 3:23 am

Hi,

We are facing a similar issue... we are getting the Exception mentioned by
Rohini in "Callablestatement.execute()" statement.

Is the fix for this released?
If yes, may I please have the location where can I find it?

Thanks,
Khyati

"David Olix" wrote:

> Hi Joe (and everyone else who's run into this problem),
>
> A fix to the selectMethod=cursor bug will be released, along with a KB
> article, within the next week or so.
>
> Thanks for your patience,
>
> --David Olix
> JDBC Development
>
>

David Olix

2006-03-08, 11:23 am

Hi all,

I apologize for the delay. I'll try to be even less specific next time. :-)

The fix to the selectMethod=cursor issue is still working its way through
the QFE process. I can't promise a specific date, but I can say that the
coding and testing are done and that we are in the last phases of building,
verification and release. We have learned some lessons along the way that
hopefully will shorten the turnaround time on any future QFEs.

The fix to the clearParameters() issue is not currently planned for QFE,
though it should be fixed in the next release. As a workaround, can you
avoid calling clearParameters() immediately after creating the
CallableStatement?

Thank you again for your patience,

--David Olix
JDBC Development

David Olix

2006-03-28, 8:23 pm

Hello all,

The selectMethod=cursor bug has been fixed and released as a hotfix.
Please contact CSS to obtain the fix. You will need to ask for the hotfix
for KB article 917054.

As of this writing, only the Windows (zip) package is available. We are
still working on making the tar.gz package available.

Thank you,
--David Olix
JDBC Development

Warrell

2006-03-29, 9:23 am

Thanks David. For the rest of us who don't know what CSS is can you give a
URL for getting hold of this hot fix?

Best regards

"David Olix" wrote:

> Hello all,
>
> The selectMethod=cursor bug has been fixed and released as a hotfix.
> Please contact CSS to obtain the fix. You will need to ask for the hotfix
> for KB article 917054.
>
> As of this writing, only the Windows (zip) package is available. We are
> still working on making the tar.gz package available.
>
> Thank you,
> --David Olix
> JDBC Development
>
>

Evan T. Basalik

2006-03-31, 11:23 am

Sorry for the confusion. CSS is Microsoft support. Just contact support and tell them you are looking for the hotfix in KB917054.

Evna
--------------------
>Thread-Topic: Callablestatement.execute() and clearParameter() causing excep
>thread-index: AcZTPmQq3u+YKAowQX+V
0dIw7JttBQ==
>X-WBNR-Posting-Host: 193.82.99.130
>From: =?Utf-8?B?V2FycmVsbA==?= <Warrell@discussions.microsoft.com>
>References: <1467C68F-A95F-481D-8236- 4ED13D20D10A@microso
ft.com> <43F56578.2070802@bea.com> <0A92F968-0AF9-466E-8627-
ECD06F6FB0E4@microso
ft.com> <43F62933.7070109@bea.com> <Q#tDlLBNGHA.128@TK2MSFTNGXA01.phx.gbl> <A82EDB3D-3CBE-430C-A230-
F7A03174BC3E@microso
ft.com> <g7bHBOtQGHA.5536@TK2MSFTNGXA03.phx.gbl> <FCDQ#NtUGHA.4716@TK2MSFTNGXA01.phx.gbl>
>Subject: Re: Callablestatement.execute() and clearParameter() causing excep
>Date: Wed, 29 Mar 2006 06:38:06 -0800
>
>Thanks David. For the rest of us who don't know what CSS is can you give a
>URL for getting hold of this hot fix?
>
>Best regards
>
>"David Olix" wrote:
>
>

--

This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

Note: For the benefit of the community-at-large, all responses to this message are best directed to the newsgroup/thread from which they originated.

Sponsored Links