Editorials

DB2 and the ADO OleDbClient

Today I had the pleasure of working with DB2. I was trying to do something that is quite simple with SQL Server. I wanted to use a transaction at the ADO level, using the OleDbTransaction object. I also wanted to protect from SQL Injection. So, even though I was not using stored procedures, I wanted to use parameters to supply my variable values. Using the SqlClient for SQL Server this is very easy. With the OleDbClient it’s not quite the same.

Using a Transaction was essentially the same as my experience with the SqlClient for SQL Server, so I won’t go into that with any detail. However, using a parameterized query worked a little bit differently. One big difference I noticed was in the definition of the replaceable parameters in the SQL statement. With SQL Server you can simply put the named parameters in the query text in any order you wish. Then when you specify the parameters to use for the statement, the SQL Client matches the parameters from your parameter collection to the parameters that were defined in your SQL statement.

When you use OleDb, at least with DB2, you specify your parameters differently. Each Parameter in in your SQL statement is simply a placeholder using the ? character. The order they are in the SQL statement is the order it replaces them from your parameters collection. There are no named parameters in the SQL statement, so it links the parameters from your command to the question marks in the ordinal order they are defined in the SQL.

One other thing that is different. If you’re parameter placeholder, ?, is going to be replaced by a string parameter from the parameters collection, then it has to be enclosed in the string identifier as a single quote. You’ll write it as ‘?’ in the query.

So, working with parameterized queries is still quite useful, and a great way to reduce the risk of SQL Injection, even when working with an OleDbClient. The concepts are still quite familiar. You simply have to be a little more careful in the way you declare your parameter place holders in your SQL statements, and the order in which you add them to the Parameters collection of your SQL command. One thing to note as well; because your placeholders are replaced with the value from the parameters collection based on the ordinal position, you may have to specify a value more than once, if it is used more than once in the SQL statement. You will need to give it a separate key name, which doesn’t matter because the keys are not used. As long as the correct value exists in the parameters in the correct ordinal position, you are good to go.

Happy hunting with DB2 and the OleDbClient.

Cheers,

Ben