Introduction
Automate can easily interact with a mySQL server and its associated databases through the SQL Query and SQL Stored Procedure actions. The document explains how to setup a mySQL data source connection through Automate that can be used to run queries against a mySQL database. While there appears to be a number of steps, the bulk of the work involves setting up an initial ODBC connection to the mySQL database, which is a necessary procedure in Microsoft Windows to execute SQL statements on database engines provided by various database vendors.
What Will You Need
- A properly configured and running mySQL installation
- Any necessary information required to connect to the database, such as a valid username and password
- Completed the installation of the mySQL ODBC Drivers for Windows, which can be found here: mySQL Connector/ODBC 3.51 Driver
Note: This article was written and tested against version 3.51 of the mySQL ODBC driver.
Running A Query
Executing a query on a mySQL database involves creating the ODBC connection to the mySQL server and executing the desired SQL statement. For this example, assume we have a mySQL server running on the local machine with a database called NetAuto. The database contains a table called Customers, which contains three columns (an auto-generated ID used as the primary key, last name and first name) and any number of rows. To return a dataset that contains all the records from this table:
- In the Automate Task Builder, double-click the SQL Query action in the Database folder of the Available Actions
- Click the document button to the right of the editbox labeled "Connection String". This will help build the connection string through a set of wizards.
- On the "Data Link Properties" window, select the Provider tab, and select "Microsoft OLE DB Provider for ODBC Drivers". Click Next.
- Select the "Use connection string" radio button, and click the Build button to the right of the editbox.
- Click the New button near the bottom of the dialog.
- From the "Create New Data Source" dialog that appears, locate the "mySQL ODBC 3.51 Driver" entry and click Next.
- Name the Data Source entry. This will be used to identify the connection in the future so it can be used again by Automate (or any other application that an interact with mySQL's ODBC driver) to connect to this database. Click Next, verify the information is correct, and click Finish.
- At the mySQL Provider properties, fill out the information applicable to the database being connected to. For this example, use localhost for "Host/Server Name (or IP)" (although if the database was located remotely, the hostname or IP address would also work), database name is "NetAuto", and the user is "root". The rest is left blank so the default values are used. Test the information supplied is valid by clicking "Test Data Source". If everything is acceptable, Click OK.
- Back on the "Select Data Source" dialog, the new data source should be available. Make sure it is selected, and click OK.
- Lastly, at the "Data Link Properties" dialog in Automate, the "Use connection string" editbox should now be populated with text automatically generated by this entire process. Click OK.
- Finally, specify the query to execute. For the "Create and populate dataset" editbox, enter "TestQuery". Then, enter a valid SQL statement, such as "SELECT * FROM Customers;" in the "SQL Statement" area. Click OK.
- Run the task. Once completed, the columns returned from the query appear in the Debug Window under the Variables tab.
Notes
While this example used mySQL, Automate supports the use of any database system, provided the machine running Automate has installed a valid OLE DB or ODBC driver that allows access to that database. For OLE DB drivers, the process above is simplified by selecting the appropriate driver from the "Providers" tab of the Data Link Properties dialog.
The above steps, however, are necessary if the drivers are legacy ODBC, in which case the Microsoft OLE DB interfacing driver is used to provide backward compatibility.