Multi-platform Data Manipulation Language (DML) With PowerShell, Part 1
In this article I discussed how to used ODBC to access three different database platforms: Microsoft Access, Microsoft SQL Server, and MySQL. In that article, I noted that there were some interesting challenges that come into play when generating SQL statements.
Today I want to talk about the next layer up - the construction of the intermediate data access layer. In case it isn't clear, I am developing what I consider to be an "industrial strength" application that handles errors and recovery as much as possible.
Each database platform has a separate set of reserved words. This can significantly complicate the construction of statements that select, update, create, and/or delete data. These statements are collectively known as DML - Data Manipulation Language. It is possible to investigate every single command, table names, and column names - and try to keep it current for all versions of each piece of software - but you might (I did) find it easier to escape all of the potential reserved words. That means - escape all the table names and column names.
You can review the reserved words for several versions of the databases at these websites:
In Microsoft Access and Microsoft SQL, square brackets will work. In MySQL, grave accents (that is, left single quotes) are required. A sample escaped select statement for Microsoft Access and SQL Server might look like this (you should join this all on one line):
SELECT * FROM [Subscriber] WHERE ([EMailAddress]='email@example.com' AND [MailingListID]=2)
For MySQL, the statement would look like this:
SELECT * FROM `Subscriber` WHERE (`EMailAddressfirstname.lastname@example.org' AND `MailingListID`=2)
Personally, I find the Microsoft Access/SQL Server statement easier to read. The difference between single quotes and grave accents are small and difficult for my eyes to recognize. In my data access layer, I've chosen to hide the quoting in a function I named dbQuote. It is quite direct. Continuing to use the $dbChoice variable defined in the first post, we can decide which quoting elements to use for our database platform. The routine comes out like this:
# This routine ensures that a table or column name is properly
# quoted for the database platform being utilized. Unlike the
# column values, $word may not be NULL.
throw ("dbQuote: table/column name may not be null.")
# square brackets work for MS-SQL and MS-Access
$start = '['
$end = ']'
if ($dbChoice -eq $dbMySQL)
$start = $end = '`'
$result = $start + $word + $end
log "dbQuote:" ("result='" + $result + "'")
The log function has not yet been presented. However, it simply writes a record to a log file on disk that looks like this:
2008-01-21 23:33:11Z dbQuote: result='[Subscriber]'
The dbQuote function is used in the construction of all DML statements. For example:
$q = "SELECT "
$q += (dbQuote "EMailAddress" ) + ","
$q += (dbQuote "MailingListID") + " "
$q += "FROM "
$q += (dbQuote "Subscriber")
which creates a select statement like this:
SELECT [EMailAddress], [MailingListID] FROM [Subscriber]
for Microsoft Access and Microsoft SQL Server. For MySQL we get this:
SELECT `EMailAddress`, `MailingListID` FROM `Subscriber` Of course, you don't have to use as many source-lines in PowerShell to create the DML as I did. You can put it all in one. However, you may find when you come back to edit that select statement that if it's all in one line it's harder to find and edit!
However, dealing with table names and column names in DML is only half the story. Dealing with arbitrarily valued row items which may be NULL or contain escaped characters or managled dates are the other part of the story.
We'll deal with those in the next post in this series.