Bug #49897 for DBD-JDBC: problem with executing DELETE statement

Tue Sep 22 00:51:26 2009 jlp+cpan [...] peterson.ath.cx - Ticket created

Subject:

problem with executing DELETE statement

I have a program using DBD::JDBC that executes a particular statement against an Oracle database. The statement looks like this: DELETE FROM tabA WHERE EXISTS (SELECT 1 FROM tabB WHERE tabA.aID = tabB.aID AND tabB.accountName IS NULL) When run with DBD::JDBC version 0.71, you get this error: operation not allowed but with version 0.70, it works just fine. The attached file contains a sample schema that exhibits the problem, along with test programs that show the success with version 0.70 and the failure with version 0.71. I've been testing this against Oracle XE installed on Ubuntu 9.04 Linux.

Subject:

ora_test.tar.gz

Download ora_test.tar.gz
application/x-gzip 4.2m

Message body not shown because it is not plain text.

Wed Sep 23 14:21:24 2009 gemerson [...] vizdom.com - Correspondence added

Subject:	Re: [rt.cpan.org #49897] problem with executing DELETE statement
Date:	Wed, 23 Sep 2009 11:21:01 -0700
To:	bug-DBD-JDBC [...] rt.cpan.org
From:	Gennis Emerson <gemerson [...] vizdom.com>

Hi, I don't have Oracle available at the moment to test with, so I'm basically guessing. The only new thing in 0.71 is support for last_insert_id/getGeneratedKeys. The DBD::JDBC server doesn't try to parse the SQL and look at what kind of statement was executed before asking for the generated keys. Is it possible that the Oracle JDBC driver is throwing an exception on that call after the delete statement instead of just returning an empty result set (the way I expected it would based on the JDBC spec)? Gennis Jan L. Peterson via RT wrote: Show quoted text

> Tue Sep 22 00:51:26 2009: Request 49897 was acted upon. > Transaction: Ticket created by JANP > Queue: DBD-JDBC > Subject: problem with executing DELETE statement > Broken in: 0.71 > Severity: Normal > Owner: Nobody > Requestors: jlp+cpan@peterson.ath.cx > Status: new > Ticket <URL: https://rt.cpan.org/Ticket/Display.html?id=49897 > > > > I have a program using DBD::JDBC that executes a particular statement > against an Oracle database. The statement looks like this: > > DELETE FROM tabA WHERE EXISTS > (SELECT 1 FROM tabB WHERE > tabA.aID = tabB.aID AND > tabB.accountName IS NULL) > > When run with DBD::JDBC version 0.71, you get this error: > operation not allowed > but with version 0.70, it works just fine. > > The attached file contains a sample schema that exhibits the problem, > along with test programs that show the success with version 0.70 and the > failure with version 0.71. > > I've been testing this against Oracle XE installed on Ubuntu 9.04 Linux.

Wed Sep 23 14:21:25 2009 The RT System itself - Status changed from 'new' to 'open'

Wed Sep 23 17:55:58 2009 jlp+cpan [...] peterson.ath.cx - Correspondence added

Show quoted text

> I don't have Oracle available at the moment to test with, so I'm > basically guessing. The only new thing in 0.71 is support for > last_insert_id/getGeneratedKeys. The DBD::JDBC server doesn't try to > parse the SQL and look at what kind of statement was executed before > asking for the generated keys. Is it possible that the Oracle JDBC > driver is throwing an exception on that call after the delete statement > instead of just returning an empty result set (the way I expected it > would based on the JDBC spec)?

Hi Glennis... your thought is certainly possible. I'm not familiar with the Oracle JDBC driver's internals, but the problem definitely doesn't occur when running the same query against a PostgreSQL database. I also wrote a small java program that attempts to run the same SQL and it does not catch any errors (but it's not trying to do anything fancy with last_insert_id/getGeneratedKeys, either). The only information I've been able to get out of it so far is an error 17090 from the Oracle JDBC driver, but I have not been able to locate any information about that error other than "operation not permitted". FYI, if you have a linux system, you can install Oracle Express Edition (XE)... it's absolutely free to install and use. That's what I was using to produce a simple test case for this issue.