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: }

January 31, 2011

Installing AutoCAD Map 3D 2011 x86 on Vista x64

Filed under: AutoCAD,Oracle — Tags: , , — Darrin Maidlow @ 11:02 pm

Long ago I gave up trying to get both the .  I have for now adopted the approach that if it talks to Oracle – it needs to be 32 bit.  Even my development projects are set to x86 only to ensure only the 32 bit Oracle client is loaded on my development machine. 

I needed to install   to debug some code I’ve been working on so I downloaded the 32 bit installer and fired it up only to be informed that my platform was unsupported and I would need to install the 64 bit version.  grrrr.

Google had very little to say on the topic.  More junk about hacking MSI files with Orca.  I took a look at setup.ini and there were far too many references to x64 – I don’t have time for this…Finally I came across the .  The product claims to modify your setups to allow the 32 bit installers to work on x64 operating systems, 40$.  I couldn’t find any reviews or testimonials so I figured I’d give it a shot…

So here is your first testimonial Longbow Software =)

This actually worked great.  Bought the convert, installed it to a virtual machine.   The interface is dead simple – paste or browse to the location of the AutoCAD installation files and press Convert.  Keep in mind if you’re running from a DVD you’ll need to copy the contents of the DVD to your PC or a network share so the files can be edited.  I pointed the converter to an install on a network share.  It chugged away for about a minute (keeping in mind this was on an underpowered VM).  Within a matter of minutes I had AutoCAD Map 3D 2011 installing on my Vista x64 box with no problems.  Ran AutoCAD and it seems to fire up no problem. 

Bear in mind – for AutoCAD to work with anything Oracle – you need to have the 32 bit Oracle client running on your machine.

40$ well spent IMO.

April 9, 2010

NHibernate 2.1 throws System.InvalidCastException on Oracle 10g R1 client

Filed under: Development — Tags: , — Darrin Maidlow @ 3:52 pm

When deploying an early build of an up and coming product on a customers machine I came across the following error:

System.InvalidCastException: Unable to cast object of type ‘Oracle.DataAccess.Client.OracleConnection’ to type ‘System.Data.Common.DbConnection’

This was one of those awesome “doesn’t happen on any of my machine” errors.  After some mucking around we determined that the client machine was using the Oracle 10g  R1 client.  The machines and VMs here we used for testing were all running either 11g, or 10g R2.  Doh!

The simple resolution to this was to modify the NHhibernate config and add the following property:

   1: <property name="hbm2ddl.keywords">none</property>

Depending on your underlying databases and mappings this could cause problems with your code project.  Hopefully you’re not using reserved words, or bad Oracle syntax =).

Under the hood, the problem seems to be that the Oracle10gDialect does not provide an implementation of IDataBaseSchema for 10gR1.   Fabio Maulo has provided some sample code and the steps on for your database and submit it to the NHibernate project for inclusion.

May 22, 2009

Finally an Oracle x64 client that works on Vista – AKA Getting Map 2010 x64 running with Oracle…

Filed under: Uncategorized — Tags: , , — Darrin Maidlow @ 4:01 pm

As my legion of dedicated readers know, I’m an 64 Bit zealot.  I would love to see a Windows task manager free of *32.  It keeps me up at night…well not really but, whatever. =) Autodesk recently released – the first native x64 release.  How could I resist installing that?  I had this funny feeling I would regret this decision.  Somehow, somewhere I just knew I would end up in once again.  So I installed Map 2010, started it up and tried to connect to my Oracle server.  You guessed it.  Connection Failed.

The problem is this.   x64 Applications cannot use the x86 driver.  In my previous attempts to get basic Oracle/ODP connectivity on my x64 Vista machine I ended up running the 11g x86 Oracle client.  Today I was back to the Oracle download site – and lookie lookie.  They’ve released an !  2008 is basically Vista so, maybe, just maybe – I thought Oracle will give us some x64 love.  Here are some steps that will help you get running:

First thing is first.  Backup your TNSnames.ora.  Its found in the network\admin folder. 

Next I un-installed the x86 11.1.0.6 client.   Delete the leftovers with windows explorer.

Next download both the 11.1.0.7 client for both x86 and x64 (yes, we need to run with both…)

First I installed the x64 client.  I like to do full runtime installations. 

Next, I copy my backed up tnsnames.ora into the network\admin folder.

Now fire up Map 2010 x64 – connect to Oracle server – Success!  Happy Happy Days.  Unfortunately, fire up (which is still 32 bit) and it fails with an error of “Cannot find OCI.DLL”.  Doh.

So, I go ahead and install the Oracle 11.1.0.7  x86 client.  I install it to the client_2 folder.  Then copy my tnsnames backup into the network\admin folder in the client_2 folder.  Restart Toad, and success!

Now I want to check one last thing.  Last time I was dealing with this fun issue – I had to set my development projects to only run in x86 mode.  So, I fire up my development project and try it out.  Running IIS in x86 mode works.   Running IIS in x64 mode failed..

The ‘MSDAORA’ provider is not registered on the local machine

It seems the Microsoft MSDAORA provider doesn’t exist in x64 – so change your connection to use the Oracle OraOLEDB.Oracle instead and x64 projects run no problem.  It would be perfect to one day get one install that does both x86 and x64 – but for now, I’m content with this setup.

Technorati Tags: ,,

Powered by WordPress

Switch to our mobile site