Direct mode issues with parametrized queries

Direct mode issues with parametrized queries

Postby 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
lhla
 
Posts: 3
Joined: Tue 08 Dec 2009 17:02

Postby 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.
Shalex
Devart Team
 
Posts: 7391
Joined: Thu 14 Aug 2008 12:44

Postby 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
 
Posts: 3
Joined: Tue 08 Dec 2009 17:02

Postby 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.
lhla
 
Posts: 3
Joined: Tue 08 Dec 2009 17:02

Postby Shalex » Wed 16 Dec 2009 13:56

We are sorry, but we cannot reproduce the issue.
Shalex
Devart Team
 
Posts: 7391
Joined: Thu 14 Aug 2008 12:44

Postby 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!
kmj
 
Posts: 1
Joined: Wed 16 Dec 2009 16:03

Postby Shalex » Fri 18 Dec 2009 09:59

The problem is fixed. Look forward to the next build of dotConnect for Oracle.
Shalex
Devart Team
 
Posts: 7391
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle