Re: execute() vs executeUpdate() on a prepared statement
- From: "raymondxiu" <raymondxiu@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Sat, 25 Feb 2006 21:56:27 -0800
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:
Yes, I'm using the correct driver....
2006-01-27 10:43:06,138 DataSourceService INFO - URL:
jdbc:sqlserver://localhost:1433;applicationName=Interlace;selectMethod=cursor;sendStringParametersAsUnico
de=false;database=interlace
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(ResultSet.TYPE_SCROLL_SENSITIVE,
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 ofI can't emphasize this enough, the 2005 driver is NOT using the same code
execute() when it did before?
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@xxxxxxx> wrote in message
news:43D91608.3050901@xxxxxxxxxx
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_absent);
stmt.execute(insert_if_absent);
stmt.execute(insert_if_absent);
stmt.execute(insert_if_absent);
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
"Joe Weinstein" wrote:
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
- Follow-Ups:
- Re: execute() vs executeUpdate() on a prepared statement
- From: Angel Saenz-Badillos[MS]
- Re: execute() vs executeUpdate() on a prepared statement
- Prev by Date: Re: Getting FOR XML into Java
- Next by Date: jdbc connection
- Previous by thread: Getting FOR XML into Java
- Next by thread: Re: execute() vs executeUpdate() on a prepared statement
- Index(es):
Relevant Pages
|
Loading
