Multi-platform database access with PowerShell

I've recently had need to be able to access data contained in three different database platforms: Microsoft Access, Microsoft SQL Server, and Mysql AB's MySQL. Originally, I thought this was going to be a tough thing to do, but it turned out to be pretty easy. ODBC drivers are available for all three (the Access driver is installed when you install office; for SQL server, it's part of "client components; for MySQL, it is a separate download and install).

Once you have the proper ODBC driver installed, it's a matter of building the connection string for each driver. That's the most challenging piece.

What makes this easy is the ODBC support that is built into the .NET framework. Given a compliant driver and the .NET framework, all the code can be the same (other than the connection string). Note that it would certainly be possible to contruct code that works in the "native" language for each database platform. The ADODB client for Access can be accessed in PowerShell, the .NET SqlClient is available in PowerShell, and Mysql AB also makes a .NET MySqlClient available.

Using the native interfaces may be faster, I don't know. I didn't conduct speed tests and my data volume is pretty low.

There are some "gotchas" that come into play when building INSERT statements, the two biggest being reserved words and date handling. I'll write a separate article about that in the future. Access and MS-SQL are quite similar when it comes to reserved words (no surprise) but MySQL is quite different. All three engines handle dates differently!

Here is my configuration file. I call it dbConstants.ps1. In this example, it's currently configured to talk to a MySQL database server on the local host. Examples for MS-SQL and MS-Access are included.

#
# dbConstants.ps1
#

Set-Variable dbAccess  -option ReadOnly -value 1
Set-Variable dbMSSQL  -option ReadOnly -value 2
Set-Variable dbMySQL  -option ReadOnly -value 3

#
# Microsoft Office Access Example
#
#Set-Variable dbChoice -option ReadOnly -value $dbAccess
#Set-Variable dbName   -option ReadOnly -value Test.mdb
#Set-Variable dbSource -option ReadOnly -value Not-Applicable
#Set-Variable dbUser   -option ReadOnly -value Not-Applicable
#Set-Variable dbPass   -option ReadOnly -value Not-Applicable

#
# Microsoft SQL Server Example
#
#Set-Variable dbChoice -option ReadOnly -value $dbMSSQL
#Set-Variable dbName   -option ReadOnly -value Test
#Set-Variable dbSource -option ReadOnly -value .\SQLExpress
#Set-Variable dbUser   -option ReadOnly -value Not-Applicable
#Set-Variable dbPass   -option ReadOnly -value Not-Applicable

#
# MySQL Example
#
Set-Variable dbChoice -option ReadOnly -value $dbMySQL
Set-Variable dbName   -option ReadOnly -value Test
Set-Variable dbSource -option ReadOnly -value localhost
Set-Variable dbUser   -option ReadOnly -value root
Set-Variable dbPass   -option ReadOnly -value password

# for Access, dbName is assumed to be located in $pwd
# for MySQL, dbSource should be the IP address or FQDN of the computer running MySQL server
# for MSSQL, dbSource should be the name plus the instance of MS-SQL (e.g., SERVER\Instance2)
#            if not present or "Not-Applicable", then "(local)\Default" is presumed
# for MSSQL, if dbUser is something other than "Not-Applicable" then Windows Auth is used

and now for the code itself. It's pretty simple. :-)

#
# dbODBC.ps1
#

. $pwd/dbConstants.ps1

$global:MLMconnection = $null
$global:MLMcommand    = $null
$global:MLMresult     = $null

function dbOpen
{
 $global:MLMconnection = New-Object System.Data.Odbc.OdbcConnection

 $global:MLMcommand = New-Object System.Data.Odbc.OdbcCommand
 $global:MLMcommand.Connection = $global:MLMconnection

 switch ($dbChoice)
 {
  $dbAccess
  {
   $cStr = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=$pwd/$dbName;"
  }
  $dbMSSQL
  {
   $cStr = "Driver={SQL Server};Server=$dbSource;Database=$dbName;"
   if ($dbUser -eq "Not-Applicable")
   {
    $cStr += "Integrated Security=SSPI;"
   }
   else
   {
    $cStr += "UID=$dbUser;PWD=$dbPass;"
   }
  }
  $dbMySQL
  {
   $cStr = "Driver={MySQL ODBC 3.51 Driver};SERVER=$dbSource;DATABASE=$dbName;"
   if ($dbUser -ne "Not-Applicable")
   {
    $cStr += "UID=$dbUser;PWD=$dbPass;"
   }

  }
  Default
  {
   throw ("Unknown value of dbChoice $dbChoice can't open db $dbName")
  }
 }

 $global:MLMconnection.ConnectionString = $cStr
 $global:MLMconnection.Open()
 if ($global:MLMconnection.State -ne [System.Data.ConnectionState]::Open)
 {
  throw ("Cannot open $dbName")
 }
}

function dbPrepareResult
{
 if ($global:MLMresult)
 {
  if ($global:MLMresult.Gettype().Name -ne "Int32")
  {
   $global:MLMresult.Close()
  }
  $global:MLMresult = $null
 }
}

function dbClose
{
 dbPrepareResult
 if ($global:MLMcommand)
 {
  $global:MLMcommand.Cancel()
  $global:MLMcommand = $null
 }

 if ($global:MLMconnection)
 {
  if ($global:MLMconnection.State -ne [System.Data.ConnectionState]::Closed)
  {
   $global:MLMconnection.Close()
  }
  $global:MLMconnection = $null
 }
}

function dbExecute([string]$command)
{
 dbPrepareResult
 if (!$global:MLMcommand)
 {
  dbOpen
 }

 $global:MLMcommand.CommandType = [System.Data.CommandType]::Text
 $global:MLMcommand.CommandText = $command

 $global:MLMresult = $global:MLMcommand.ExecuteReader()

 return $global:MLMresult
}

function dbExecuteNonQuery([string]$command)
{
 dbPrepareResult
 if (!$global:MLMcommand)
 {
  dbOpen
 }

 $global:MLMcommand.CommandType = [System.Data.CommandType]::Text
 $global:MLMcommand.CommandText = $command

 $global:MLMresult = $global:MLMcommand.ExecuteNonQuery()

 return $global:MLMresult
}

Enjoy!

If there are things that you would like for me to write about, please let me know.

Published Monday, January 07, 2008 9:12 AM by michael
Filed under: ,

Comments

Tuesday, January 22, 2008 8:04 PM by Michael's meanderings...

# 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

Sunday, January 27, 2008 12:21 PM by Michael's meanderings...

# Multi-platform Data Manipulation Language (DML) With PowerShell, Part 3

In Part 1 of this mini-series I presented the beginnings of a database layer supporting three different