Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MySQL

Posted April 6, 2012

Automate MySQL Queries with PowerShell

By Rob Gravelle

If you're hosting your database(s) on a Windows Operating System, you may want to consider performing batch jobs using PowerShell, rather than DOS or Wsh scripts.  It's an interactive shell and scripting tool from Microsoft that combines the capabilities of the MS-DOS Command Prompt, batch files, Windows Scripting Host and even UNIX shells!  In the Connect to MySQL using PowerShell article, we saw how to connect to MySQL from the PowerShell console using the MySQL.Net Connector. Today we're going to learn how to create a PowerShell script that can run queries against your MySQL database and process the results with a press of the Enter key.

Using the PowerShell Integrated Scripting Environment

When you install the PowerShell console, a second tool called the PowerShell Integrated Scripting Environment (ISE) is also installed.  It's a graphical host application for PowerShell that lets you run commands directly, as well as write, edit, run, test, and debug scripts:

PowerShell Integrated Scripting Environment (ISE)
PowerShell Integrated Scripting Environment (ISE)

Before you say "Oh no; another tool that I have to learn", realize that the ISE is not required at all in order to run scripts in PowerShell.  You can write your scripts in any text editor; all you need to do is save your scripts with a .ps1 extension.

Although you don't need to use the ISE to follow this tutorial, I would encourage you to take this opportunity to give it a try.  Here are some reasons why:

Windows PowerShell ISE was designed for users at all levels of proficiency, from novice to expert. Beginners can take advantage of syntax color highlighting and the context-sensitive Help while advanced developers will benefit from the multiple execution environment support, built-in debugger, and extensibility of the Windows PowerShell ISE object model.

Administrator: Windows PowerShell ISE
Administrator: Windows PowerShell ISE

In the image above, you can see that there are three main panes:

  • A Script pane for writing, editing, and running scripts. (top)
  • An output pane that displays a transcript of commands from the Command and Script panes and their results. (middle)
  • A Command pane for running interactive commands. (bottom)

Here are some additional features:

  • You can have up to eight tabs open at the same window, each with its own Command, Script, and Output panes.
  • There's a built-in debugger for commands, functions, and scripts. You can set and remove breakpoints, step through your code, check the values of variables, and display a call-stack trace.
  • You can customize and extend Windows PowerShell ISE via a scriptable object model.

To summarize, the PowerShell ISE is designed to facilitate the writing of PowerShell commands by including PowerShell-specific keyboard shortcuts, tab completion, opening files by using drag-and-drop, and context-sensitive Help.

Note that the Windows PowerShell ISE requires the Microsoft .NET Framework 3.5 with Service Pack 1.  If this version of the Microsoft .NET Framework is not installed on your system, Server Manager installs it when you install Windows PowerShell ISE.

Generating the Test Data

The script that we will be writing can be executed against any table, but if you don't have any good test data or want to reproduce the same output as here, you can use this SQL file, which I generated using the generatedata.com site.

Writing the Script Code

You can write all your script code at once, but I would suggest testing each line before running it so as to make sure that potential bugs are ironed out.

You can execute any line(s) of code by highlighting it/them and choosing "Run Selection" from the popup menu:

Run Selection
Run Selection

The output will appear in the center pane.  Any errors generated by the command will appear in red:

The command output
The command output

Here's a rundown of each line of code:

1.   We'll need to load the MySQL.Net connector into memory by entering the following command with the full path to the MySQL.Data.dll file (note that your path may differ):

[void][system.reflection.Assembly]::LoadFrom("C:\Program Files\MySQL\Connector NET 6.4.4\Assemblies\v2.0\MySQL.Data.dll")

Here is the default path for 64 bit systems:

[void][system.reflection.Assembly]::LoadFrom("C:\Program Files (x86)\MySQL\MySQL Connector Net 6.4.4\Binaries\.NET 2.0\MySQL.Data.dll")

The LoadWithPartialName() function also does the job:

[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data") 

2.   Create a variable to hold the connection:

$myconnection = New-Object MySql.Data.MySqlClient.MySqlConnection

3.   Set the connection string (replace the user id and password with your own):

$myconnection.ConnectionString = "database=test;server=localhost;Persist Security Info=false;user id=myid;pwd=mypw"

4.   Call the Connection object's Open() method:

$myconnection.Open()

5.   Create a new Command and set the text to your query:

$command = $myconnection.CreateCommand()
$command.CommandText = "select * from powershell_tests";

6.   Create a new Dataset to store the results of the query:

$dataSet = New-Object System.Data.DataSet 

7.   Process the results. In this example we're just going to display all of the table contents in the console. We can fetch each field value using the GetValue() function, passing in the zero-based column index. The writing of each value to the console is done using the write-output command:

write-output $reader.GetValue(0).ToString()

A While loop is utilized to iterate over all the results:

while
($reader.Read()) {
  for ($i= 0; $i -lt $reader.FieldCount; $i++) {
    write-output $reader.GetValue($i).ToString()
  }
}

Closing the Database Connection

In theory the database connection will be closed when PowerShell finishes. However, it is always a good practice to close any open connections explicitly:

$connection.Close()

Running the Script

Running scripts is restricted by default so you have to set the execution policy first.  Most people do that by including both the following command followed by the call to their script in a batch file and call that from the Task Scheduler. 

set-executionpolicy Unrestricted

This is not necessary as the execution policy can be set via a command line flag.  Likewise, a policy of RemoteSigned is a little safer as this will allow the system to run Powershell scripts that are created locally, while Remote Powershell scripts that may be downloaded must be signed.

To run your script, type the following from your computer's command prompt (not the PowerShell console):

powershell.exe -noprofile -executionpolicy RemoteSigned -file "<path to script>select-all-query.ps1"

You should see something like the following in the console:

...
2622 Sollicitudin Ave
42
Levi Hansen
non@infaucibusorci.org
 
P.O. Box 991, 2829 Maecenas Ave
43
Dominic Ramirez
Vivamus@bibendumullamcorperDuis.org
 
797-6188 Blandit St
44
Quamar Larsen
egestas.Aliquam@sit.com
 
166-3031 Ut Rd.
45
Chadwick Montoya
sem.elit.pharetra@ornareplaceratorci.edu

 

...

Today's select-all-query.ps1 file is included in the download.

Conclusion

The PowerShell ISE makes it easy for database administrators to write MySQL database batch jobs in PowerShell, no matter what their level of programming know-how.  In the next article, we'll see how it's just as easy to create a MySQL table from a PowerShell script as to extract data from one.

Download the test-table-sql

See all articles by Rob Gravelle

 



MySQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MySQL Forum
Topic By Replies Updated
MySQL in high availability and transction secure banking application klamor 1 August 28th, 10:24 AM
MySQL rollback UAL225 1 August 28th, 10:15 AM
Browsing a DB file that uses MySql finleytech 1 July 26th, 10:51 AM
php cookie won't pass variable to next page ITdevGirl 0 June 22nd, 12:13 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date