PowerShell and MySQL : POSH_Ado_MySQL

Posted by on

Using PowerShell and MySQL together with POSH_Ado is just as easy as SQL Server. You’ll need the POSH_Ado and POSH_Ado_MySQL Modules, and use this command to get started:

Import-Module POSH_Ado_MySQL

Once you’ve done that you’ll have the following functions at your disposal:

  • New-MySQLCommand
  • New-MySQLConnectionString
  • New-MySQLCommand
  • Invoke-MySQLCommand
  • Invoke-MySQLQuery
  • Invoke-MySQLStoredProcedure

These functions work just like the ones for SQLServer in AdoLib or POSH_Ado_SQLServer, except that they work with MySQL.

Inside POSH_Ado_MySQL, you’ll see that (just like POSH_Ado_SQLServer), it is simply importing the POSH_Ado module, specifying the MySQL ADO.NET provider name and the prefix (MySQL). Then, it calls the Set-MySQLADONetParameters function to add an option to the connection strings that are generated and to specify that there is no prefix for parameter names.

import-module POSH_Ado -args MySql.Data.MySqlClient -Prefix MySQL -force

# .NET (and PowerShell) do not like zero datetime values by default.  This option helps with that.
# http://dev.mysql.com/doc/refman/5.5/en/connector-net-connection-options.html
Set-MySQLADONetParameters -option @{'Allow Zero Datetime'='true'} -ParameterPrefix ''

Export-ModuleMember *-MySQL*

Hopefully by now you can see the power of the POSH_Ado project:

  1. All of the ADO.NET logic is in one place
  2. Consistent (but distinctly named) cmdlets for working with each platform
  3. Flexibility to set platform-specific options

There are currently 3 other platform-specific POSH_Ado modules: POSH_Ado_Oracle, POSH_Ado_Firebird, and POSH_Ado_DB2. It should be no trouble to create Postgres, SQLite, and OLEDB modules as well.

Do you have any projects where POSH_Ado could come in handy? What about other platforms to explore?

Let me know your thoughts in the comments.

-Mike

P.S. POSH_Ado and the platform-specific modules can be found here