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

September 15, 2010

Mapguide Enterprise 2011 – Fusion and Selection XML

Filed under: Mapguide,RADE — Tags: , , — Darrin Maidlow @ 3:00 pm

I’ve been working to migrate the  Mapguide 2010 code to work with 2011.  In 2010, I only implemented support for the DWF and Ajax viewer.  With the release of 2011, the new  ability to include mapping data from Google, Yahoo and Microsoft in Fusion are huge so I opted to migrate everything over.  The DWF viewer is already deprecated and I suspect the Ajax viewer will likely go next.

With 2010 and the Ajax viewer we had to get the selection from the MapFrame using the GetSelectionXML() method.  Then we had to encode, and send this value up to the web tier for processing.  I had all kinds of business logic that I was hoping to re-use with a minimal amount of modifications so I was trying to find how to recreate this functionality with Fusion.  I figured out how the selections worked on the client side using the new callback system and was getting the selection object – but I could not find anything definitively saying “There is no selection XML on the client side anymore”.    So…

There is no selection XML on the client side any more when using Fusion.

There I said it.  It makes sense too – first getting this large XML string and sending it up was a pain in the ass.  Encoding it, creating a form to submit it, etc.   I should have clued into this sooner – Fusion is updating the selection on the server side as needed. 

So in order to minimally impact the existing code – I added an additional check to some of the calls that looks a little something like this:

   1: if (String.IsNullOrEmpty(selectionXml))
   2: {
   3:     MgResourceService resSvc = (MgResourceService)siteConn.CreateService(MgServiceType.ResourceService);
   4:     sel.Open(resSvc, map.GetName());
   5:     selectionXml = sel.ToXml();
   6: }

Those four lines of code are a lot easier than all the screwing around needed in 2010 to get the selection XML.   With that small modification my existing code is working perfectly!

hope this helps someone =)

February 18, 2009

Geospecialling v2.0 and an introduction to FullCircle

Filed under: General — Tags: , , — Darrin Maidlow @ 1:11 pm

Hello!  Welcome back to Geospecialling, and welcome to the new site!  This new site has been my existence for the past several weeks– but more on that later. 

It’s been a crazy couple of months (since ), and we are getting closer to the end of the craziness.  Let me sum things up, and hopefully gain your forgiveness for the lack of posts for two months. =)  At AU we unveiled , and the reception has been phenomenal.  FullCircle is our newest product built .   So after returning from AU, and getting all that feedback from partners and customers we set to work finishin, testing, and polishing the first release of FullCircle – Standard Edition.

FullCircle has two main functions.  First, it allows users to login and query data from their Oracle, SQL Server, and Access databases.  The tabular information is then sent down as an Excel spreadsheet where users may edit or append data.  This spreadsheet can then uploaded to FullCircle and processed.  The updates/inserts are applied to the appropriate database table.

The second key function of FullCircle is for users.   FullCircle allows Excel forms to be defined and saved.  Users can then download the form templates from the repository, print the forms with Capturx, and fill them out.  When the digital pen is docked, and the hand writing is processed back into Excel – these Excel files can then be processed with FullCircle.  The inserts (or updates if needed) are processed and put into the appropriate database table(s).  No temporary tables are used that require processing after the fact (unless of course, this is what you want to do), no custom coding.

On top of that we’ve been hard at work building this new site, setting up the new blogs, doing all that good stuff.  I can finally see the light at the end of the tunnel…

More soon!

June 29, 2008

RADE – Build custom web applications, without writing custom code

Filed under: RADE — Tags: — Darrin Maidlow @ 7:21 am

In response to numerous queries, I put together this post to help answer the question – what is RADE (formerly known as WebRADE). RADE is a development framework that delivers custom web applications using existing data without having to write a single line of code.  RADE provides a rich set of functions including security, prompting queries, configurable reports and charting using existing database and mapping data, without the need for expensive consultants and programmers.

RADE includes a configurable web mapping interface that incorporates queries, reports, charts and basic editing tools. These “components” work with the data you have stored in one or more databases (Oracle, SQL Server, Access) and can be used by any application made with RADE. Right now RADE supports MapGuide 6.x, we are in the process however of extending the mapping interface to allow RADE applications to use multiple web mapping engines including MapGuide 6.x, MapGuide Enterprise 2009 / Mapguide Open Source 2.0 and Google Maps. Other mapping engines may be implemented in the future.  

An application made with RADE is a collection of queries, reports, charts, and of course users/groups managed by the integrated RADE security model. They are your custom solutions founded upon the bedrock that RADE provides.

A user with basic knowledge of their underlying database structure can design customized queries, reports, and charts in a matter of hours. Solutions can be as broad or as narrow as needed depending on the requirements of the organization. All of this work is done using RADE’s web-based administration tools.  

Before RADE, I worked for a provider of customizable desktop GIS, automated map standardization, and facilities management solutions for both government and industry. GIS/Geospatial implementations involved a lot of customized work, specific to particular clients, and could seldom be easily reused. This is when and where I came to realize that there had to be a better way to develop solutions.  

As RADE developed, we put it to the test and have been developing a series of standalone applications build on its framework. With RADE, we are saving time on projects which results in significant cost savings and rapid delivery for our clients.  

Out of curiosity one day, I connected RADE to my accounting and bug-tracking databases just to see what would happen. With a little research into the underlying databases — I was able to create both queries and reports on both data sources. No map required.  

As a developer I have projects that require me to extend RADE with more proprietary code. Take for example. FloorView is a complete vertical application built on top of RADE. It uses the RADE security/authentication system in addition to the RADE queries and reports — but is in itself a full application. This allowed us to leverage the existing functionality saving time, and money. In addition to this, FloorView appears as a RADE application when authorized users login to the site keeping all of the RADE based applications in one convenient location.  

In addition to FloorView a number of other vertical applications have been developed on top of, or using RADE. They include solutions for address management, disaster planning and recovery, sexual predator and offender tracking, crime mapping and analysis, oil and gas lease management and service request tracking and management.  

The best analogy I have for RADE is “Developer in a Box”.  Much of the work is already done for you…

Powered by WordPress

Switch to our mobile site