Direct mode issues with parametrized queries

lhla · Post by **lhla** » Tue 08 Dec 2009 17:34

Hey,

I'm having some bizarre issues with parametrized queries in direct mode.

Basically if I perform an insert on a table that already contains a unique row I get an insert exception which is expected. After the insert exception I use this same connection to grab the info for which data is already locked down to report the user (I know the connection is still good at this point). This connection is then closed and returned to the pool all of this is fine up to this point as expected.

At a later point in time when this connection is grabbed back from the pool I try to perform a simple update parametrized query and I get the following exception: ORA-01008: not all variables bound. This would make sense to get if all variables were not bound however looking at the OracleParameter collection I can clearly see that all parameters exist and that their values are correct. Not to mention this query gets run multiple times before this connection is grabbed from the pool for other connections in the pool since it's a general update table that gets updated for each client request. Furthermore all of this code works fine with direct mode turned off using the oracle client (exact same code and query execution as in direct mode).

Basically it seems that for some reason in direct mode when the insert is performed and fails and the connection gets closed and placed back into the pool that the connection becomes corrupt and fails when used again with parametrized queries.

I would produce a simple sample except the only problem is that this issue is not reliably reproducible in a simple case.

Info:

- This is a client server app. Issue occurs even with a single client.
- Connection is created and grabbed from the pool for each client request and closed after execution of the request.
- Each connection is used within a single thread (not across multiple threads)
- Using the latest Devart.Data.Oracle 5.35.54 and Devart.Data 5.0.16 and i've also tried with the previous version with the same results.

If any one has come across this or has any ideas your help will be greatly appreciated

thanks

Shalex · Post by **Shalex** » Wed 09 Dec 2009 13:54

There was a known bug in the direct mode in the previous versions of dotConnect for Oracle: the similar error arose if the same parameter was used several times in a query in the direct mode. But it should be fixed at the latest builds. Please try the latest 5.35.57 build of dotConnect for Oracle. If the problem persists, please try creating a small test project to reproduce the issue and send it to us with the DDL script.

lhla · Post by **lhla** » Wed 09 Dec 2009 16:34

Thanks for the reply Shalex. I've seen the previous bug when searching the blog before unfortunately this also occurs whether it's a repeated parameter value or not.

I'll give the latest version a try and go from there...

lhla · Post by **lhla** » Tue 15 Dec 2009 20:10

unfortunately the newest version makes no difference for this issue and it still is not reproducible on a small scale to give a simple example.

Shalex · Post by **Shalex** » Wed 16 Dec 2009 13:56

We are sorry, but we cannot reproduce the issue.

kmj · Post by **kmj** » Wed 16 Dec 2009 17:17

I am a coworker of user lhla who posted this question. He is leaving on vacation.

I've reproduced the error without using connection pooling, and created sample code to demonstrate the error.

I have sent the code and my request using your contact form.

Thanks!

Shalex · Post by **Shalex** » Fri 18 Dec 2009 09:59

The problem is fixed. Look forward to the next build of dotConnect for Oracle.