Re: Batch inserts dont work with PreparedStatement and selectMetho

Tech-Archive recommends: Fix windows errors by optimizing your registry

I am hitting the same problem. With the previous driver and SQL Server 2000
we had no problems. Since we upgraded to SQL Server 2005 and the sqljdbc.jar
driver we have problems that the jdbc client blows the memory stating to use
server side cursors. When we put the SelectMethod=cursor we get the problem
seen below.

Is there anyway to enable the server side cursors to get things working???

"Angel Saenz-Badillos[MS]" wrote:

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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A55AED19-4ED8-43A0-9AF2-26026C08E0DA@xxxxxxxxxxxxxxxx
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?



.

Relevant Pages