Re: execute() vs executeUpdate() on a prepared statement

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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:769E91A9-5062-43E3-9644-2C0F1BB8C46E@xxxxxxxxxxxxxxxx
> 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(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 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@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
>> >>>
>> >>>
>> >
>>
>>
>>


.

Relevant Pages

  • Re: execute() vs executeUpdate() on a prepared statement
    ... The issue was that a cursor may error out if the back-end function is too ... execute may attempt to create a cursor. ... the 2005 driver is NOT using the same code ... See bug FDBK44661. ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: execute() vs executeUpdate() on a prepared statement
    ... where the execute would throw the exception as stated with the 2005 drivers. ... to get a connection from a pool, ... > request a cursor. ... I'm surprised that the driver ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: execute() vs executeUpdate() on a prepared statement
    ... Server 2005 JDBC Driver ... I'm connection to a SQLServer 2000 database and I'm using ... > request a cursor. ... execute may attempt to create a cursor. ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: Cursor problems Wd 2003
    ... Graham, I haven't actually updated the driver yet, but it is a recent driver ... Whether the cursor is at the ... Graham Mayor - Word MVP ... Word MVP web site http://word.mvps.org ...
    (microsoft.public.word.docmanagement)
  • Sound, dual soundcards, second attempt (better I hope)
    ... PCI: PCI BIOS revision 2.10 entry at 0xfdb01, ... Journalled Block Device driver loaded ... usb.c: new USB bus registered, ... alias char-major-10-175 agpgart ...
    (comp.os.linux)
Loading