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



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 driver has tracing built in to help with these types of issues, ... Feel free to contact me at this alias minus the "online" ... > If I changed the execute() to an executeUpdateand change nothing else, ... >> request a cursor. ...
    (microsoft.public.sqlserver.jdbcdriver)
  • 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
    ... the bug quickly,thanks. ... Server 2005 JDBC Driver ... execute may attempt to create a cursor. ... As you can tell the most important part of the repro is the SQL used ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: Windows Scheduling on I/O bound systems with >2 cores/processo
    ... Each process does the continuous read & write DMA operations ... CPU usage: On <= 2 processor systems, ... We are running Load test on our PCI IO card(WDF driver), ... On machines with>2 processors a single process would execute its read ...
    (microsoft.public.development.device.drivers)
  • Re: execute() vs executeUpdate() on a prepared statement
    ... I have the same problem.I use the rtwjdbc driver connect ... the bug quickly,thanks. ... Server 2005 JDBC Driver ... execute may attempt to create a cursor. ...
    (microsoft.public.sqlserver.jdbcdriver)