Re: Don't do DDL with PreparedStatement with Oracle!

On Feb 14, 9:27 am, "joeNOS...@xxxxxxx" <joe.weinst...@xxxxxxxxx>
wrote:
On Feb 14, 9:02 am, "kuassi.men...@xxxxxxxxx"





<kuassi.men...@xxxxxxxxx> wrote:
On Feb 13, 3:43 pm, "joeNOS...@xxxxxxx" <joe.weinst...@xxxxxxxxx>
wrote:

(I don't know why the original was so badly formatted)
Hi all.

I found a customer who wanted to repeat the SQL "TRUNCATE TABLE
MY_TABLE", so they did:

ps = c.prepareStatement("TRUNCATE TABLE THEIR_TABLE");

This statement *worked fine* the first time they executed it, but
fascinatingly, every subsequent execution of that statement
*silently failed, doing *nothing*! The driver got the expected
response packet from the DBMS, but Oracle did nothing. The
customer could even continue to execute this statement, seemingly
successfully, after they completely dropped the table!
   The case was complicated because their code actually created,
executed, and closed the statement each time, but because the
customer was using a connection pooling system (like BEA's or
Oracle's own JDBC driver), that will transparently cache and
re-use PreparedStatements (a big performance feature), the
application would get the same statement under the covers, and
start getting the odd NO-OP behavior.
  Oracle's driver and our pooling are both unable to do anything
about this, when the SQL is created at runtime by the customer,
and this DBMS bug would force the non-caching of statements for
anyone sending DDL, like that. To reiterate, this is an Oracle
DBMS bug. It can be reproduced with a simple JDBC client or an
OCI+C program, but not SQL-PLUS because SQL-PLUS never prepares
statements....

FYI,
Joe Weinstein at BEA Systems

It is documented that DDLs should be re-prepared however, I found out
that the OCI driver manages to avoid re-parsing of DDL, throug its
statement cache. The Oracle JDBC team will look into doing the same.

Kuassi
Oracle JDBC product managemenenthttp://db360.blogspot.com

Hi Kuassi! Thanks. I don't understand though... If I am correct,
the SQL "TRUNCATE TABLE MYTABLE" *needs* to be re-prepared, else
the DBMS will do nothing. Any client-side caching would simply
aggravate the problem, making a user's re-prepare get the same
now-unfunctional statement from the cache.
Joe- Hide quoted text -

- Show quoted text -

Mea culpa; in fact OCI mark the DDL for re-parsing, which avoids the
no-op on subsequent execution.

Kuassi
.

Relevant Pages