Talking to MySQL/MariaDB from powershell using ODBC

This was bit frustrating to me. Let's say you have a MySQL or a MariaDB (whose names I will write in all lowercase from now on because I am typing challenged) database running somewhere in a Windows/Linux/OSX/Solaris/AIX box. And need to reach it, say to retrieve the value of some row in a table somewhere. That does not sound that much of a challenge: unleash Python or even Bash and you should be able to create a script to do the deed. But, what if you had to do so from a Windows machine?

If you have read any of my previous Windows-related posts, you would know that I will probably try to find a way to do the deed in Powershell. And you will also know the reason I do that is so working on Windows feels more Linuxy to me. And this article will not break this precedent: there will definitively be some powershell involved. You have been warned!

The first thing we will need is a way to connect using ODBC, so we need to get a ODBC Connector. The one I used I got directly from mysql, and it was called the Windows (x86, 64-bit), MSI Installer. After installing it we did not configure it. Instead, we will do that from the script itself; all we need is the driver name. Since I forgot the name of the .msi package and driver, I decided to ask it directly. So, I went to Control Panel->Administrative Tools->Data Source (ODBC). There we can click on System DSN->Add. At the time this article was written, the driver name we'll use is "MySQL ODBC 5.3 ANSI Driver" as shown in the picture to the left. Yes, you want to save the entire string verbatim. With that recorded, we close the little non-resizeable ODBC-related dialog boxes and move forward.

Now let's talk about what frustrated me: at first I thought I had to create a System DSN and then feed that to my script. Big mistake; I could not get that working no matter how hard I tried. Realizing I was going nowhere slowly, I instead decided to do the deed directly from Powershell. So, let's start by writing a powershell script that only tries to connect to the database penguium (the penguin continuum?) on IP 10.0.0.113 as user admin and then checks if the connection was successful. Here is how the database looks like:

mysql> select * from penguium.stats;
+-----+-----------------------+-------------+
| pid | name                  | happy_state |
+-----+-----------------------+-------------+
|   1 | Fat Penguin           | Relaxed     |
|   2 | Doctor Penguin, M.D.  | Pensive     |
|   3 | Angry Penguin of Doom | Angry       |
|   4 | Lazy Penguin          | Not Present |
+-----+-----------------------+-------------+
4 rows in set (1.07 sec)

mysql>

We will be asking help from .Net, specifically from the framework data provider for ODBC, System.Data.ODBC. My script looks like this (real IPs and username/password changed to protect the innocent):

$SQLServer = "10.0.0.113"
$SQLDBName = "penguium"
$SQLUser = "admin"
$SQLPW = "supersecret"

# Connect to the database
$SqlConnection = New-Object System.Data.ODBC.ODBCConnection
$SqlConnection.connectionstring = `
   "DRIVER={MySQL ODBC 5.3 ANSI Driver};" +
   "Server = $SQLServer;" +
   "Database = $SQLDBName;" +
   "UID = $SQLUser;" +
   "PWD= $SQLPW;" +
   "Option = 3"
$SqlConnection.Open()

$SqlConnection.State

First thing I want to draw your attention to is the line.

"DRIVER={MySQL ODBC 5.3 ANSI Driver};" +

If it reminds you of the driver name we made a point to write down, you are absolutely correct. We are telling Windows which ODBC driver we want to use. The next few variables -- server, database, UID, and PWD -- normally would be defined if we had create and configure a System DSN. Which we didn't because as I mentioned above I tried to do so and it gleefully ignored me. Do note this is not the only way, or the safest, to authenticate, but it is the laziest. In fact, let me say this once again: my example here is great as an example only; it is insecure (no encryption and the authentication is for all to see). Go read the references, specially the Microsoft docs, for how to make this mess more secure.

Next step is to create/open the connection to the db server.

$SqlConnection.Open()

And then we ask, using the command

$SqlConnection.State

to report on whether the connection was indeed opened. A successful connection will send

Open

as the reply. Now we know the connection works, let's now find out how happy Angry Penguin of Doom is feeling this morning. If logged into the mysql server, we would do that by saying

mysql> select happy_state from penguium.stats where name = 'Angry Penguin of Doom';
+-------------+
| happy_state |
+-------------+
| Angry       |
+-------------+
1 row in set (0.03 sec)

mysql>

Reading from database

Since we have a beautiful script above, let's send that query by adding the following lines to our script

$SqlQuery = "select happy_state from penguium.stats where name = 'Angry Penguin of Doom'"
$Command = New-Object `
    System.Data.ODBC.ODBCCommand($SQLQuery, $SQLConnection)
$Reply = $Command.executescalar()
$Reply

If we run the script again, we would see something like

Open
Angry

where the first line is the output of $SqlConnection.State just like before and the second line (Angry) is the content of the happy_state row associated with our penguin.

If we expected seeing more than one reply to our query, we could do something like this instead:

$Reply = $Command.executereader()
while($reply.Read()){ $reply.GetString(0) }

What we are doing here in the while line is to retrieve the first column ($reply.GetString(0)) from the reply to our query. Since there is only one entry, that is all we get back. I'll leave as an exercise to get all columns or the next one.

Editing database

So far so good? Now, how about making poor Angry Penguin of Doom happier? Since this is not real life, nor a real penguin, we can bring a smile to its lips with the power of sql:

mysql> UPDATE penguium.stats SET happy_state='Happy' WHERE name='Angry Penguin of Doom';
Query OK, 1 row affected (2.67 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from penguium.stats;
+-----+-----------------------+-------------+
| pid | name                  | happy_state |
+-----+-----------------------+-------------+
|   1 | Fat Penguin           | Relaxed     |
|   2 | Doctor Penguin, M.D.  | Pensive     |
|   3 | Angry Penguin of Doom | Happy       |
|   4 | Lazy Penguin          | Not Present |
+-----+-----------------------+-------------+
4 rows in set (0.03 sec)

mysql>

So let's see if we can do that in powershell. I am going to cheat and copy how we made the query in the previous session, changing as needed:

$SQLCommand = "UPDATE penguium.stats SET happy_state='Happy' WHERE name='Angry Penguin of Doom'"
$Command = New-Object `
    System.Data.ODBC.ODBCCommand($SQLCommand, $SQLConnection)
$Command.executescalar()

Putting It All Together or Something Like That

The article is over and we found out how to read and write data to the database using ODBC and powershell. How about if we prettify it a bit and convert that into a script that monitors the database and change it as needed? Say, if the Angry Penguin of Doom is not happy, we will make it so.

$SQLServer = "10.0.0.113"
$SQLDBName = "penguium"
$SQLUser = "admin"
$SQLPW = "supersecret"
$ODBCDriver='{MySQL ODBC 5.3 ANSI Driver}'

$SqlQuery = "select happy_state from penguium.stats where name = 'Angry Penguin of Doom'"
$SQLCommand = "UPDATE penguium.stats SET happy_state='Happy' WHERE name='Angry Penguin of Doom'"

# Connect to the database
function DBConnect ($server, $driver, $dbname, $user, $password)
{
   $Connection = New-Object System.Data.ODBC.ODBCConnection
   $Connection.connectionstring = `
      "DRIVER= $driver;" +
      "Server = $server;" +
      "Database = $dbname;" +
      "UID = $user;" +
      "PWD= $password;" +
      "Option = 3"
   $Connection.Open()
   return $Connection
}

# Send a Command to the database and get the reply
function SendQuery ($Query, $Connection)
{
   $Command = New-Object `
      System.Data.ODBC.ODBCCommand($Query, $Connection)
   return $Command.executescalar()
}

# Query DB and update it as needed
$SQLConnection = DBConnect $SQLServer $ODBCDriver $SQLDBName $SQLUser $SQLPW

$answer = SendQuery $SqlQuery $SQLConnection

if ( $answer -ne "Happy" )
{
   # Let us know. Could email us instead
   "Crap: $answer"

   # Change it!
   SendQuery $SqlCommand $SQLConnection
}

Note that the criteria we used was if the penguin is not happy to do something; we could as well as have tested only if he was angry (if ( $answer -eq "Angry" )). The above code could be improved by adding
try{} catch{} in appropriate locations and optionally contacting (email?) whoever needs to know when a change is required, but I am not in the mood of doing that right now.

References

• System.Data.Odbc Docs (From Microsoft)
  
• MYSQL docs on Using Connector/ODBC with examples
  

Notes

• Just corrected a typo. Shame on me!