#44 Problem with Oracle, statement caching and generated keys

[al0]

Hello,

I have encountered following problem:

"Missing IN or OUT parameter at index:: ..."
if I use ComboPooledDataSource, activate statement caching (with MaxStatements or MaxStatementsPerConnection) and use prepared statement that execute insert and returns auto-generated keys.

Oracle version - 10.2.1XE (both server and JDBC driver).

See attached gkc3p0.zip for all details.

mumu_create.sql - creates table and sequence for test programs.

gk.java - program that operates without c3p0 (gets connection from DriverManager) and reexecutes the same prepared statement many times; run without errors.

gkC3P0.java - program that uses CombpPooled datasource;
in setMaxStaements(180) is commented out, then run without errors, if it is commented in, above mentioned exception occurs.

Just for completness - error seems to be Oracle-specific, for Derby and DB2/400 it does not occur.

Regards,

Oleksandr Alesinskyy

[al0]

testcase

[Steve Waldman]

Logged In: YES
user_id=175530
Originator: NO

Oleksandr,

So this is an interesting one.

Can I ask you to try two things?

1) If you insert into the non-c3p0 test program gk.java the following line...

ps.clearParameters();

...at the end of the for loop (after the while loop), do you still see the Exception?

2) If you insert the line...

ps.setNull(1, Types.NUMERIC);

...before ps.executeUpdate() in gkC3P0.java, does it somehow, against all reason, work? (This line should throw an Exception, as there are no settable parameters in the statement, but you never know.)

3) Against all reason again, does your program somehow work if you write null as uppercase NULL when you prepare the statement?

Thanks!

Steve

p.s. I'm guessing that the first test will fail, and I can fix this by checking the ParameterMetaData, and not calling clearParameter() on PreparedStatements with no parameters. But I don't love this -- I think clearParameters() should be a no-op on a parameter-free Statement, and I worry about the overhead of constructing a ParameterMetaData The second two tests are based on the unlikely idea that Oracle is parsing the java keyword "null" as a replaceable parameter in a non-standard format.

[al0]

Logged In: YES
user_id=1618512
Originator: YES

Hello,

it has nothing to do with prepared statement with no parameters - original statement in my program that has shown this behavior had a lot of parameters (7, if I remember correctly). I just dropped them to produce minimal test case. Just add one column into the table, one parameter to the prepared statement in the test - error would be the same. Case of NULL constant in sql has nothing to do with it as well.

But your first assumption is correct - if I add "ps.clearParameters();" at the end of the loop of the non-c3p0 test program gk.java then it exhibits the same error as c3p0-test program. So it seems that this call causes the problem.

IMHO it is a bug of Oracle JDBC driver, so I would log it on metalink.oracle.com, but anyway, it would be nice if you will develop some workaround. I dare to mention once more, that it has nothing to do with number of parameters in statement, so proposed workaround with checking ParameterMetaDat would not work.
The easiest workaround is to omit clearParameters() call for statement that wish to return generated keys at all (at least for Oracle). There are some drawbacks, but they are tolerable (IMHO).

Regards,
Oleksandr

[al0]

Logged In: YES
user_id=1618512
Originator: YES

It appears to be known bug in Oracle JDBC driver (5499751 from 26.08.2006 and 5658207 from 13.11.2006 in Oracle Bugs Database), but fix is not yet available. Seems that it will be fixed in next major version only (Oracle 11).

Regards,
Oleksandr

[Steve Waldman]

Logged In: YES
user_id=175530
Originator: NO

So, our resolution was this was an Oracle bug, right? Feel free to reopen the bug report if you think it's something c3p0 has to do something about.

[al0]

Logged In: YES
user_id=1618512
Originator: YES

Yes, seems that nothing can be done in the c3p0 - save mentioning this problem in the documentation (at the place on which caching of the prepared statements is discussed).

Regards,
Oleksandr

[al0]

Sorry that did not put it earlier - it appeared to be known problem in the Oracle JDBC driver, supposedly fixed in 11g (have not tested yet).