Batch inserts dont work with PreparedStatement and selectMethod=cu

[Johannes]

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]]

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" <Joha...@discussions.microsoft.com> wrote in message
news:A55AED19-4ED8-43A0...@microsoft.com...

[Mark]

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???

[David Olix]

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,
--Davi d Olix
JDBC Development

[Vivek]

When will the fix for KB 917054 be available on the support website for
our customers to download the patch?

*** Sent via Developersdex http://www.developersdex.com ***

[Evan T. Basalik (MSFT)]

If you contact us via the 800 number listed at http://www.microsoft.com/services/microsoftservices/srv_support.mspx, you can open up a hotfix case with us to
get this fix.
--------------------
>From: Vivek <nos...@devdex.com>
>References: <EEA9qNtU...@TK2MSFTNGXA01.phx.gbl>
>Subject: Re: Batch inserts dont work with PreparedStatement and selectMetho
>Message-ID: <OpVsE2lb...@TK2MSFTNGP03.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.jdbcdriver
>Date: Tue, 02 May 2006 19:58:54 -0700

>
>When will the fix for KB 917054 be available on the support website for
>our customers to download the patch?
>
>
>
>*** Sent via Developersdex http://www.developersdex.com ***
>

--

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.

[Joseph Syjuco]

Hi all,
Just downloaded MSSQL JDBC version 2 and unfortunately Im encountering this
error:

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.

Tried googling around but cant seem to find any resource that will explain
the error in I guess basic terms. I am assuming that the bug defined above
has been applied in this version and bottom line I would like to find out
what I did wrong - why did I get that error

url:http://www.ureader.com/msg/1148237.aspx

[David Olix]

Hi Joseph,

What does your T-SQL query look like? A simple SELECT or something more
complicated?
What Statement/PreparedStatement/CallableStatement method are you using to
execute it (execute()/executeUpdate()/executeQuery())?
Is selectMethod=cursor set as one of your connection properties?
What scrollability/updatability did you request when creating the statement?

How to deal with that error really depends on the answers to these
questions.

Regards,
--David Olix [SQL Server]