Re: Don't do DDL with PreparedStatement with Oracle!
- From: "kuassi.mensah@xxxxxxxxx" <kuassi.mensah@xxxxxxxxx>
- Date: Thu, 14 Feb 2008 09:02:05 -0800 (PST)
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 managemenent
http://db360.blogspot.com
.
- Follow-Ups:
- Re: Don't do DDL with PreparedStatement with Oracle!
- From: joeNOSPAM@xxxxxxx
- Re: Don't do DDL with PreparedStatement with Oracle!
- References:
- Don't do DDL with PreparedStatement with Oracle!
- From: joeNOSPAM@xxxxxxx
- Re: Don't do DDL with PreparedStatement with Oracle!
- From: joeNOSPAM@xxxxxxx
- Don't do DDL with PreparedStatement with Oracle!
- Prev by Date: Re: Encoding conversion problem
- Next by Date: Re: Don't do DDL with PreparedStatement with Oracle!
- Previous by thread: Re: Don't do DDL with PreparedStatement with Oracle!
- Next by thread: Re: Don't do DDL with PreparedStatement with Oracle!
- Index(es):
Relevant Pages
|
|
