November 8, 2011

OracleCommand and parameterized update statements

Filed under: Development,Oracle,RADE — Tags: , , , , — Darrin Maidlow @ 4:07 pm

This week I encountered an irritating situation with Oracle while working on code in the 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 .  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):

  1. Get table metadata
  2. Loop through fields in table
  3. for each field retrieve the value from the UI
  4. Add field to parameterized SQL statement with placeholder
  5. Create new parameter with appropriate name and value.   Add parameter to collection
  6. Loop through parameters in the collection and add to the DbCommand
  7. 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:

  1. Get Table
  2. Loop through fields in table
  3. for each field retrieve the value from the UI
  4. 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
  5. Create new parameter with appropriate name and value.  Add parameter to collection
  6. Loop through parameters in the collection and add to the DbCommand
  7. Finally execute the parameterized SQL statement.

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

The Problem

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 :

   1: String parameterizedSQL = "UPDATE " + table.Name + " SET " + updateStatement + " WHERE " + whereStatement;

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!

   1: /// <summary>
   2: /// Execute the parameterized query
   3: /// </summary>
   4: /// <param name="conn">open and active DbConnection</param>
   5: /// <param name="trans">Active DbTransaction</param>
   6: /// <param name="parameterizedSQL">the parameterized SQL</param>
   7: /// <param name="paramList">List of OledDbParameter</param>
   8: /// <returns>DataTable containing the results</returns>
   9: public static void RunParameterizedInsertUpdate(DbConnection conn, DbTransaction trans, String parameterizedSQL, List<DbParameter> paramList)
  10: {
  11:     //create the db command and set the parameterized SQL as a property
  12:     DbCommand command = conn.CreateCommand();
  13:     if(trans != null)
  14:     {
  15:         command.Transaction = trans;
  16:     }
  17:     //hack attack!  By default, Oracle requires its parameters to be placed into the command
  18:     //in the order the parameters appear in the parameterized SQL.  Little hackery here
  19:     //to set the Oracle Command to bind by name
  20:     if (command is Oracle.DataAccess.Client.OracleCommand)
  21:     {
  22:         OracleCommand oraCmd = (OracleCommand) command;
  23:         oraCmd.BindByName = true;
  24:         command = oraCmd;
  25:     }
  26:     command.CommandText = parameterizedSQL;
  27:     command.CommandType = CommandType.Text;
  28:  
  29:     //loop through the params and add them to the command
  30:     foreach (DbParameter parameter in paramList)
  31:     {
  32:         command.Parameters.Add(parameter);
  33:     }
  34:     try
  35:     {
  36:         command.Prepare();
  37:         command.ExecuteNonQuery();
  38:     }
  39:     catch (Exception ex)
  40:     {
  41:         command.Dispose();
  42:         throw;
  43:     }
  44: }

October 23, 2008

Oracle Client/ODAC on Vista x64 Mostly Working… Finally

Filed under: Oracle — Tags: — Darrin Maidlow @ 11:25 pm

So, finally I got fed up with working from a 32 bit XP virtual machine and spent a little more time on this issue.  I’ve managed to get my Vista Ultimate a la x64 mostly working.  Where I went wrong (aside from trying to figure this out in the middle of the night in a shitty mood because I had spent so much time head banging with a wall) was using the 64 bit client. 

The correct path to Oracle happiness in my case was the 11g client.  It works great with my 10.2 server.  The important bit in was to use the 32 bit install rather than the 64 bit install.  One of my primary tools for accessing Oracle is 9.x.  Quest Software states that Toad for Oracle 9.6 is the first version to really support the 11g client.  However, it does NOT support the x64 client.

So the last time I tried to solve this, after a long day, and a fun night of head banging with a wall – when I tried the 11g client, I used x64 cbuild – and saw that Toad wouldn’t work, I threw a little hissyfit and went to bed.  Long story short, installing both the 32bit and seems to have done the trick.  Now – there is one important little piece o’ information that you will need to know.  Though I have not confirmed this, I suspect that x64 applications will not be able to use the driver. 

That said, from a development point of view you will need to set IIS to run at 32 bit.  On IIS 5.x/6.x this is a server wide setting from a dos window:

   1: cscript %SYSTEMDRIVE%\inetpub\adminscripts\adsutil.vbs SET W3SVC/AppPools/Enable32bitAppOnWin64 1
   2: %SYSTEMROOT%\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis.exe -i

 

If you’re running Vista or Server 2008 with IIS7, you have things a little better.  You can configure each application pool to run as 32 or 64 bit.  For winform development, change the compile properties of the project to set the target platform to be x86.

I’m really glad that’s over…but I still want a proper x64 ODAC.  Come on Oracle .. =)

Update February 2009 – I have made a happy ending post on .  Got Oracle client working in both .  Check it out.

July 11, 2008

ODAC/ODP.NET on Vista x64

Filed under: Oracle — Tags: , , , — Darrin Maidlow @ 7:11 am

Since moving to Vista x64 I’ve had a heck of a time with Oracle clients.  The one thing I could not get working until tonight was ODP with Visual Studio / .NET.  Finally I found a solution.

First, download and install . (Link requires registration)   This should get the 32bit stuff installed.   I’m still using an Oracle 10g R2 server.  You will likely need to grab a copy of the TNSnames.ora for your existing client folder and place it in the appropriate tree of the 11g product home.

This however is not enough to get .NET working with ODP.  Go to the folder where you extracted the zip.  We need to find the Oracle.DataAccess.dll.  This can be found in the file named filegroup4.jar, in the stage\components\oracle.ntoledb.odp_net_2.  Winrar will open .jar files if needed.  Extract the Oracle.DataAscess.dll file.

For now, I’ve put a copy of this file in my projects lib folder.  I then added a reference directly to this file from all projects that need ODP access. 

Keep in mind – before you ship you may want to remove this reference and ensure that the .DLL file doesn’t get included in your build.  This should get your Vista x64 box developing with ODP.

To Oracle – come on guys.  Give us some Vista x64 love!

Technorati Tags: ,,,,

Powered by WordPress

Switch to our mobile site