SQL Server JDBC - Batch inserts dont work with PreparedStatement and selectMethod=cu

Author

Batch inserts dont work with PreparedStatement and selectMethod=cu

Johannes

2006-02-09, 11:23 am

Hi,

we downloaded the final release of the JDBC driver for 2005 and tested it
with our application.

Unfortunately, we found the following problem:

If a batch insert is performed with a PreparedStatement on a connection
which was opened with the property selectMethod=cursor,
then you get the
following error:

With SQL Server 2000: java.sql. BatchUpdateException
:
com.microsoft.sqlserver.jdbc.SQLServerException:
sp_cursoropen/sp_cursorprepare: The statement parameter can only be a single
select or a single stored procedure.

(Normally you see this error when you do a stmt.executeQuery() where the
statement is actually an insert/update statement, i.e. you should have used
stmt.executeUpdate() instead.)

With SQL Server 2005: java.sql. BatchUpdateException
:
com.microsoft.sqlserver.jdbc.SQLServerException:
sp_cursoropen/sp_cursorprepare: The statement parameter can only be a batch
or a stored procedure with a single select, without FOR BROWSE, COMPUTE BY,
or variable assignments.

Here's some simple code that reproduces this problem:

Statement stmt1 = connection.createStatement();
stmt1.executeUpdate("CREATE TABLE TESTTABLE (TEST_INT INT)");
stmt1.close();
PreparedStatement stmt2 = connection.prepareStatement("insert into TESTTABLE
values (?)");
stmt2.setInt(1, 1);
stmt2.addBatch();
stmt2.executeBatch();
stmt2.close();

(this is of course a trivial example meant only for illustration of the
problem)

Note, that the error only appears if "connection" was opened with the
"selectMethod=cursor" property. It does *not* appear if this property was set
to "direct" (which is also the default). Also note, that if you rewrite the
above example so that stmt2 is a normal Statement (not a PreparedStatement),
then the error does also no appear (neither with selectMethod=cursor nor
selectMethod=direct)
.

I really hope that this is not the intended behavior of the driver but
rather a bug, because it would make the driver unusable for our purposes (and
it used to work with the older JDBC driver for SQL Server 2000 and it also
works with other drivers for SQL Server 2005...).

Johannes

P.S. I hope this is the right channel for reporting such problems. If not,
could you please point me to the correct one?

Angel Saenz-Badillos[MS]

2006-02-09, 1:23 pm

Thank you for your extensive description of the problem, the
selectMethod=cursor information is key here.

We are actively investigating this issue and I will let you know as soon as
we have 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/

"Johannes" < Johannes@discussions
.microsoft.com> wrote in message
news:A55AED19-4ED8-43A0-9AF2- 26026C08E0DA@microso
ft.com...
> Hi,
>
> we downloaded the final release of the JDBC driver for 2005 and tested it
> with our application.
>
> Unfortunately, we found the following problem:
>
> If a batch insert is performed with a PreparedStatement on a connection
> which was opened with the property selectMethod=cursor,
then you get the
> following error:
>
> With SQL Server 2000: java.sql. BatchUpdateException
:
> com.microsoft.sqlserver.jdbc.SQLServerException:
> sp_cursoropen/sp_cursorprepare: The statement parameter can only be a
> single
> select or a single stored procedure.
>
> (Normally you see this error when you do a stmt.executeQuery() where the
> statement is actually an insert/update statement, i.e. you should have
> used
> stmt.executeUpdate() instead.)
>
> With SQL Server 2005: java.sql. BatchUpdateException
:
> com.microsoft.sqlserver.jdbc.SQLServerException:
> sp_cursoropen/sp_cursorprepare: The statement parameter can only be a
> batch
> or a stored procedure with a single select, without FOR BROWSE, COMPUTE
> BY,
> or variable assignments.
>
> Here's some simple code that reproduces this problem:
>
> Statement stmt1 = connection.createStatement();
> stmt1.executeUpdate("CREATE TABLE TESTTABLE (TEST_INT INT)");
> stmt1.close();
> PreparedStatement stmt2 = connection.prepareStatement("insert into
> TESTTABLE
> values (?)");
> stmt2.setInt(1, 1);
> stmt2.addBatch();
> stmt2.executeBatch();
> stmt2.close();
>
> (this is of course a trivial example meant only for illustration of the
> problem)
>
> Note, that the error only appears if "connection" was opened with the
> "selectMethod=cursor" property. It does *not* appear if this property was
> set
> to "direct" (which is also the default). Also note, that if you rewrite
> the
> above example so that stmt2 is a normal Statement (not a
> PreparedStatement),
> then the error does also no appear (neither with selectMethod=cursor nor
> selectMethod=direct)
.
>
> I really hope that this is not the intended behavior of the driver but
> rather a bug, because it would make the driver unusable for our purposes
> (and
> it used to work with the older JDBC driver for SQL Server 2000 and it also
> works with other drivers for SQL Server 2005...).
>
> Johannes
>
> P.S. I hope this is the right channel for reporting such problems. If not,
> could you please point me to the correct one?

Sponsored Links