SQL Server JDBC - execute() vs executeUpdate() on a prepared statement

Author

execute() vs executeUpdate() on a prepared statement

BJinDallas

2006-01-26, 4:53 pm

We have been using JDBC drivers for 2000 for some time without problems using
an execute() call against a prepared statement when performing an sql update
with where clause. After upgrading to the new 2005 drivers this call now
receives the error: "sp_cursoropen/sp_cursorprepare: The statement parameter
can only be a single select or a single stored procedure." If I change it to
call executeUpdate() instead, it works fine. What's changed in the 2005
drivers that no longer supports use of execute() when it did before?

Joe Weinstein

2006-01-26, 4:53 pm

BJinDallas wrote:
> We have been using JDBC drivers for 2000 for some time without problems using
> an execute() call against a prepared statement when performing an sql update
> with where clause. After upgrading to the new 2005 drivers this call now
> receives the error: "sp_cursoropen/sp_cursorprepare: The statement parameter
> can only be a single select or a single stored procedure." If I change it to
> call executeUpdate() instead, it works fine. What's changed in the 2005
> drivers that no longer supports use of execute() when it did before?

Please show the JDBC that prepares the statement for execution, and
if you can show us the SQL as well, that would be very useful. At
first blush this sounds like a definite driver bug. Execute() is
supposed to work for *any* SQL or procedure.
Joe Weinstein at BEA

AArora

2006-01-26, 4:53 pm

I ran into this issue when running ANY update or delete statements. I also
ran into this when running any DDL statements.

This also happens with executeBatch(). See bug FDBK44661.
There should be a simple example there.

I'm surprised that the driver was actually released with such bugs.

Anil

"Joe Weinstein" wrote:

>
>
> BJinDallas wrote:
>
> Please show the JDBC that prepares the statement for execution, and
> if you can show us the SQL as well, that would be very useful. At
> first blush this sounds like a definite driver bug. Execute() is
> supposed to work for *any* SQL or procedure.
> Joe Weinstein at BEA
>
>

Joe Weinstein

2006-01-26, 4:53 pm

AArora wrote:

> I ran into this issue when running ANY update or delete statements. I also
> ran into this when running any DDL statements.
>
> This also happens with executeBatch(). See bug FDBK44661.
> There should be a simple example there.
>
> I'm surprised that the driver was actually released with such bugs.
>
> Anil

Again, please show the JDBC. I was unable to duplicate this
problem with this code:

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

Statement stmt = c.createStatement();
stmt.execute("create table #foo(bar int)");

String insert_if_absent =
" declare @qwe int "
+ " select @qwe = count(*) from #foo where bar = 1 "
+ " if (@qwe = 0) insert into #foo values(1) ";

stmt. execute(insert_if_ab
sent);
stmt. execute(insert_if_ab
sent);
stmt. execute(insert_if_ab
sent);
stmt. execute(insert_if_ab
sent);

stmt.execute("select count(*) from #foo");
ResultSet r = stmt.getResultSet();
while(r.next())
System.out.println("We inserted " + r.getString(1) + " row." );

I get:
Driver version is 1.0.809.102
We inserted 1 row.

as expected.
Joe

[color=darkred]
>
> "Joe Weinstein" wrote:
>
>

Angel Saenz-Badillos[MS]

2006-01-26, 4:53 pm

AArora,
This was a bug we had in Beta2, can you verify with
DatabaseMetaData dd = conn.getMetaData();
System.out.println("Driver version is " + dd.getDriverVersion() );

That you are using the RTW bits ("1.0.809.102") from
http://msdn.microsoft.com/data/jdbc/default.aspx

The issue was that a cursor may error out if the back-end function is too
complex, a workarround for this is to use executeUpdate which will never
request a cursor. execute may attempt to create a cursor.

The repro that we had for this issue was:
static void Repro412111() throws Exception
{
Connection conn = MainEngine.connFactory.getConnection();
Statement stmt = conn. createStatement(Resu
ltSet. TYPE_SCROLL_SENSITIV
E,
ResultSet.CONCUR_UPDATABLE);
try {stmt.executeUpdate("drop function f_Repro412111");}catch (Exception
e) {}
try {stmt.executeUpdate("drop table t_Repro412111");}catch (Exception e)
{}
stmt.executeUpdate("create function f_Repro412111 (@p1 int) returns table
as " +
"return (SELECT @p1 as x1, @p1*2 as x2, @p1*3 as x3)");
ResultSet rs = stmt.executeQuery("select isnull(x1,0) as v1, isnull(x2,0)
as v2, isnull(x3,0) as v3 into t_Repro412111 from f_Repro412111(2)");
rs.close();
stmt.close();
}

As you can tell the most important part of the repro is the SQL used (we
have to force the cursor to error) so it would be very helpfull if you could
provide a working repro for this.

> What's changed in the 2005 drivers that no longer supports use of
> execute() when it did before?
I can't emphasize this enough, the 2005 driver is NOT using the same code
base as the 2000 driver, _everything_ has changed from the 2000 driver.

Thanks,
--
Angel Saenz-Badillos [MS] DataWorks
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.
I am now blogging: http://weblogs.asp.net/angelsb/

"Joe Weinstein" <joeNOSPAM@bea.com> wrote in message
news:43D91608.3050901@bea.com...
>
>
> AArora wrote:
>
>
> Again, please show the JDBC. I was unable to duplicate this
> problem with this code:
>
> DatabaseMetaData dd = c.getMetaData();
> System.out.println("Driver version is " +
> dd.getDriverVersion() );
>
> Statement stmt = c.createStatement();
> stmt.execute("create table #foo(bar int)");
>
> String insert_if_absent =
> " declare @qwe int "
> + " select @qwe = count(*) from #foo where bar = 1 "
> + " if (@qwe = 0) insert into #foo values(1) ";
>
> stmt. execute(insert_if_ab
sent);
> stmt. execute(insert_if_ab
sent);
> stmt. execute(insert_if_ab
sent);
> stmt. execute(insert_if_ab
sent);
>
> stmt.execute("select count(*) from #foo");
> ResultSet r = stmt.getResultSet();
> while(r.next())
> System.out.println("We inserted " + r.getString(1) + " row." );
>
> I get:
> Driver version is 1.0.809.102
> We inserted 1 row.
>
> as expected.
> Joe
>
>
>

BJinDallas

2006-01-26, 4:53 pm

The code I'm having problems with is like:
PreparedStatment stmt = connection.prepareStatement("update ec_bdd set
status = ? where bdd_name = ?");
stmt.setInt(1, 2);
stmt.setString(2, "X12-850-004030");
stmt.execute();
where the execute would throw the exception as stated with the 2005 drivers.
If I changed the execute() to an executeUpdate() and change nothing else, it
worked.
HOWEVER, when I tried just now to create a standalone example for you and
hard wired the connection instead of using our application specific "stuff"
to get a connection from a pool, I can't reproduce the problem. It works
either way. So it must be something in the way we're creating or reusing
connections or not closing something or ???. If I can't reproduce the
problem in a simple example, I don't expect you to be able to help me. I'll
have to narrow down the failure in a different way.
Thanks anyway.
BJ

"Angel Saenz-Badillos[MS]" wrote:

> AArora,
> This was a bug we had in Beta2, can you verify with
> DatabaseMetaData dd = conn.getMetaData();
> System.out.println("Driver version is " + dd.getDriverVersion() );
>
> That you are using the RTW bits ("1.0.809.102") from
> http://msdn.microsoft.com/data/jdbc/default.aspx
>
> The issue was that a cursor may error out if the back-end function is too
> complex, a workarround for this is to use executeUpdate which will never
> request a cursor. execute may attempt to create a cursor.
>
> The repro that we had for this issue was:
> static void Repro412111() throws Exception
> {
> Connection conn = MainEngine.connFactory.getConnection();
> Statement stmt = conn. createStatement(Resu
ltSet. TYPE_SCROLL_SENSITIV
E,
> ResultSet.CONCUR_UPDATABLE);
> try {stmt.executeUpdate("drop function f_Repro412111");}catch (Exception
> e) {}
> try {stmt.executeUpdate("drop table t_Repro412111");}catch (Exception e)
> {}
> stmt.executeUpdate("create function f_Repro412111 (@p1 int) returns table
> as " +
> "return (SELECT @p1 as x1, @p1*2 as x2, @p1*3 as x3)");
> ResultSet rs = stmt.executeQuery("select isnull(x1,0) as v1, isnull(x2,0)
> as v2, isnull(x3,0) as v3 into t_Repro412111 from f_Repro412111(2)");
> rs.close();
> stmt.close();
> }
>
> As you can tell the most important part of the repro is the SQL used (we
> have to force the cursor to error) so it would be very helpfull if you could
> provide a working repro for this.
>
> I can't emphasize this enough, the 2005 driver is NOT using the same code
> base as the 2000 driver, _everything_ has changed from the 2000 driver.
>
> Thanks,
> --
> Angel Saenz-Badillos [MS] DataWorks
> This posting is provided "AS IS", with no warranties, and confers no
> rights.Please do not send email directly to this alias.
> This alias is for newsgroup purposes only.
> I am now blogging: http://weblogs.asp.net/angelsb/
>
>
>
>
> "Joe Weinstein" <joeNOSPAM@bea.com> wrote in message
> news:43D91608.3050901@bea.com...
>
>
>

Angel Saenz-Badillos[MS]

2006-01-26, 5:16 pm

Dallas,
The driver has tracing built in to help with these types of issues, you can
find more information under:
Contents->Diagnosing Problems with JDBC driver-> Tracing Driver Operation.

If you get a ALL level trace and a Profiler trace I would be happy to help
debug the issue. Feel free to contact me at this alias minus the "online"
part.

--
Angel Saenz-Badillos [MS] DataWorks
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.
I am now blogging: http://weblogs.asp.net/angelsb/

"BJinDallas" < BJinDallas@discussio
ns.microsoft.com> wrote in message
news:769E91A9-5062-43E3-9644- 2C0F1BB8C46E@microso
ft.com...[color=darkred]
> The code I'm having problems with is like:
> PreparedStatment stmt = connection.prepareStatement("update ec_bdd set
> status = ? where bdd_name = ?");
> stmt.setInt(1, 2);
> stmt.setString(2, "X12-850-004030");
> stmt.execute();
> where the execute would throw the exception as stated with the 2005
> drivers.
> If I changed the execute() to an executeUpdate() and change nothing else,
> it
> worked.
> HOWEVER, when I tried just now to create a standalone example for you and
> hard wired the connection instead of using our application specific
> "stuff"
> to get a connection from a pool, I can't reproduce the problem. It works
> either way. So it must be something in the way we're creating or reusing
> connections or not closing something or ???. If I can't reproduce the
> problem in a simple example, I don't expect you to be able to help me.
> I'll
> have to narrow down the failure in a different way.
> Thanks anyway.
> BJ
>
> "Angel Saenz-Badillos[MS]" wrote:
>

Angel Saenz-Badillos[MS]

2006-01-26, 8:23 pm

Sorry for the double post, but I fogot to mention that for the profiler
trace, please turn on all errors when running the trace.
Thanks,

--
Angel Saenz-Badillos [MS] DataWorks
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.
I am now blogging: http://weblogs.asp.net/angelsb/

"Angel Saenz-Badillos[MS]" <angelsa@online.microsoft.com> wrote in message
news:eyP$3wsIGHA.2928@TK2MSFTNGP10.phx.gbl...
> Dallas,
> The driver has tracing built in to help with these types of issues, you
> can find more information under:
> Contents->Diagnosing Problems with JDBC driver-> Tracing Driver Operation.
>
>
>
> If you get a ALL level trace and a Profiler trace I would be happy to help
> debug the issue. Feel free to contact me at this alias minus the "online"
> part.
>
>
> --
> Angel Saenz-Badillos [MS] DataWorks
> This posting is provided "AS IS", with no warranties, and confers no
> rights.Please do not send email directly to this alias.
> This alias is for newsgroup purposes only.
> I am now blogging: http://weblogs.asp.net/angelsb/
>
>
>
>
> "BJinDallas" < BJinDallas@discussio
ns.microsoft.com> wrote in message
> news:769E91A9-5062-43E3-9644- 2C0F1BB8C46E@microso
ft.com...
>
>

AArora

2006-01-27, 1:23 pm

Yes, I'm using the correct driver...

2006-01-27 10:43:06,138 DataSourceService INFO - URL:
jdbc:sqlserver:// localhost:1433;appli
cationName=Interlace
;selectMethod=cursor
;sendStringParameter
sAsUnico
de=false;database=in
terlace
2006-01-27 10:43:06,154 DataSourceService INFO - Username: interlace
2006-01-27 10:43:06,154 DataSourceService INFO - Driver: Microsoft SQL
Server 2005 JDBC Driver
2006-01-27 10:43:06,169 DataSourceService INFO - Driver version: 1.0.809.102
2006-01-27 10:43:06,169 DataSourceService INFO - Database: Microsoft SQL
Server
2006-01-27 10:43:06,169 DataSourceService INFO - Database version: 8.00.194

Note, I'm connection to a SQLServer 2000 database and I'm using
selectMethod=cursor as a connection property. If I remove the
selectMethod=cursor,
it seems to work.
We used selectMethod=cursor with the old driver for memory reasons.

Anil

"Angel Saenz-Badillos[MS]" wrote:

> AArora,
> This was a bug we had in Beta2, can you verify with
> DatabaseMetaData dd = conn.getMetaData();
> System.out.println("Driver version is " + dd.getDriverVersion() );
>
> That you are using the RTW bits ("1.0.809.102") from
> http://msdn.microsoft.com/data/jdbc/default.aspx
>
> The issue was that a cursor may error out if the back-end function is too
> complex, a workarround for this is to use executeUpdate which will never
> request a cursor. execute may attempt to create a cursor.
>
> The repro that we had for this issue was:
> static void Repro412111() throws Exception
> {
> Connection conn = MainEngine.connFactory.getConnection();
> Statement stmt = conn. createStatement(Resu
ltSet. TYPE_SCROLL_SENSITIV
E,
> ResultSet.CONCUR_UPDATABLE);
> try {stmt.executeUpdate("drop function f_Repro412111");}catch (Exception
> e) {}
> try {stmt.executeUpdate("drop table t_Repro412111");}catch (Exception e)
> {}
> stmt.executeUpdate("create function f_Repro412111 (@p1 int) returns table
> as " +
> "return (SELECT @p1 as x1, @p1*2 as x2, @p1*3 as x3)");
> ResultSet rs = stmt.executeQuery("select isnull(x1,0) as v1, isnull(x2,0)
> as v2, isnull(x3,0) as v3 into t_Repro412111 from f_Repro412111(2)");
> rs.close();
> stmt.close();
> }
>
> As you can tell the most important part of the repro is the SQL used (we
> have to force the cursor to error) so it would be very helpfull if you could
> provide a working repro for this.
>
> I can't emphasize this enough, the 2005 driver is NOT using the same code
> base as the 2000 driver, _everything_ has changed from the 2000 driver.
>
> Thanks,
> --
> Angel Saenz-Badillos [MS] DataWorks
> This posting is provided "AS IS", with no warranties, and confers no
> rights.Please do not send email directly to this alias.
> This alias is for newsgroup purposes only.
> I am now blogging: http://weblogs.asp.net/angelsb/
>
>
>
>
> "Joe Weinstein" <joeNOSPAM@bea.com> wrote in message
> news:43D91608.3050901@bea.com...
>
>
>

raymondxiu

2006-02-28, 8:24 pm

I have the same problem.I use the rtw(1.0.809.102) jdbc driver connect
sql2005 server.when i use selectMethod=cursor,
execute() must error, hope fix
the bug quickly,thanks.

"AArora" wrote:
[color=darkred]
> Yes, I'm using the correct driver...
>
> 2006-01-27 10:43:06,138 DataSourceService INFO - URL:
> jdbc:sqlserver:// localhost:1433;appli
cationName=Interlace
;selectMethod=cursor
;sendStringParameter
sAsUnico
> de=false;database=in
terlace
> 2006-01-27 10:43:06,154 DataSourceService INFO - Username: interlace
> 2006-01-27 10:43:06,154 DataSourceService INFO - Driver: Microsoft SQL
> Server 2005 JDBC Driver
> 2006-01-27 10:43:06,169 DataSourceService INFO - Driver version: 1.0.809.102
> 2006-01-27 10:43:06,169 DataSourceService INFO - Database: Microsoft SQL
> Server
> 2006-01-27 10:43:06,169 DataSourceService INFO - Database version: 8.00.194
>
> Note, I'm connection to a SQLServer 2000 database and I'm using
> selectMethod=cursor as a connection property. If I remove the
> selectMethod=cursor,
it seems to work.
> We used selectMethod=cursor with the old driver for memory reasons.
>
> Anil
>
>
> "Angel Saenz-Badillos[MS]" wrote:
>

Angel Saenz-Badillos[MS]

2006-02-28, 8:24 pm

Thank you for your post, we are actively investigating this issue. I will
post here as soon as I have some more information.

--
Angel Saenz-Badillos [MS] DataWorks
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.
I am now blogging: http://weblogs.asp.net/angelsb/

"raymondxiu" < raymondxiu@discussio
ns.microsoft.com> wrote in message
news:66EF48C3-14BD-461F-A34B- 9DFF3FB68534@microso
ft.com...[color=darkred]
>I have the same problem.I use the rtw(1.0.809.102) jdbc driver connect
> sql2005 server.when i use selectMethod=cursor,
execute() must error, hope
> fix
> the bug quickly,thanks.
>
> "AArora" wrote:
>

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

Sponsored Links