November 8, 2011

This week I encountered an irritating situation with Oracle while working on code in the RADE application logic. In a nutshell I am building dynamically created parameterized insert and update statements based on the RADE metadata and the values entered by the user. The first call InsertNewRecord works perfectly. The next call UpdateExistingRecord however was not. The parameterized SQL was being created. The parameters were being created and assigned to the DbCommand. The ExecuteNonQuery() call was executing without returning an error. Oracle just would not update. Even more frustrating – this “just worked” in SQL server.

What were the differences?

The basic logic for insert was this (parts omitted because you probably just don’t care):

Get table metadata

Loop through fields in table

for each field retrieve the value from the UI

Add field to parameterized SQL statement with placeholder

Create new parameter with appropriate name and value. Add parameter to collection

Loop through parameters in the collection and add to the DbCommand

Finally execute the parameterized SQL statement

As I mentioned this worked great. Fields were inserted and there was much rejoicing.

The logic for an update was similar but there was one big difference:

Get Table

Loop through fields in table

for each field retrieve the value from the UI

If the field is a key add the placeholder to the where condition, otherwise add the field name and value to the update fields part of the SQL

Create new parameter with appropriate name and value. Add parameter to collection

Loop through parameters in the collection and add to the DbCommand

Finally execute the parameterized SQL statement.

The branch in step 4 and the if statement ended up causing the problem.

The Problem

OracleCommand defaults to “bind by order” – making the order in which the parameters exist in the SQL statement match the order in which the parameters are added to the OracleCommand. This was happening during the insert because of the structure of an insert statement being so linear. However in the update statement I was building the SQL in a more dynamic way. I was maintaining a list field=value conditions and a separate where condition. In the ended up merging them :

So unless my key field( s) all lined up at the end of the table metadata definitions, appending that where condition at the end my parameter order got all out of whack in the DbCommand. So my where condition was actually being set to the wrong value – which could have resulted in the wrong records being updated. Nasty. Fortunately this can be resolved.

The Fix – BindByName=true

To correct this I had to set the Oracle specific BindByName property to true. (btw this being the default behavior is just silly. All the other big data providers default to bind by name and Oracle should too. That’s a rant for another day though.) My initial solution was to check if the command is an OracleCommand and if found do a little casting to set the BindByName property then recast it back to DbCommand before executing the query. Constructive feedback is always welcome!