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

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