This page describes the steps to connect to a MySQL database. The particular example connects to PressWise so that data could be extracted for insertion into Control, but the general steps should work for any MySQL database.

Note: Connecting to MySQL or other databases is not standard function of Cyrious and is not covered by standard support contracts. Assistance debugging or setting up this connection can be offered under the Supplemental Support Agreement.

For an additional walk through, see http://www.easysoft.com/support/kb/kb01095.html

1. The JDBC driver must be installed on the MySQL server for ODBC connections to work. This process assumes this driver is already in place. If not, it should be installed by the IT team responsible for the MySQL database.
2. Determine the version of SQL you are running locally. Open SQL Management Console and run:
   1. SELECT SERVERPROPERTY('Edition')
   2. If the output says (64 bit) in the name, it is 64 bit. Otherwise it is 32 bit.
3. Install the ODBC Connector for MySQL.
   • The version installed match the version (32 or 64 bit) of SQL you are using.
   • There is no problem installing both the 32 and 64 bit, but the correct one must be used in the following steps.

You must the ODBC configuration that matches the version (32 or 64 bit) of SQL you are using or you will get an “Architecture Mismatch” error when you try to use it.

1. Click the windows button and type ODBC.
2. Choose the setup corresponding the the version of SQL you are using (32 or 64 bit).
3. Click on the System DSN tab. A User DSN will fail, since SQL is running as a different user.
4. Click on Add … to create a new connection
5. Choose MySQL ODBC 5.x ANSI Driver as the driver and click Finish. If you don't see that choice, you still need to install the MySQL drivers. (See above)

1. Enter these values:
   1. Data Source Name: Presswise-Orders
   2. TCP/IP Server: (use supplied URL)
   3. Port: 3306 (leave default)
   4. User: (use supplied user name)
   5. Password: (use supplied password)
   6. Database: presswise_order
2. Test Connection. Click OK if successful. Correct values if not.

1. Repeat the process for the production connection
2. Enter these values:
   1. Data Source Name: Presswise-Production
   2. TCP/IP Server: (use supplied URL)
   3. Port: 3306 (leave default)
   4. User: (use supplied user name)
   5. Password: (use supplied password)
   6. Database: presswise_production

1. Open SQL Server Management Studio (SSMS) and connect to the SQL database engine with Control.
2. Configure the MSDASQL provider with optios that MySQL supports.
   1. Expand Server Objects | Linked Servers | Providers
   2. Right Click n MSDASQL and choose properties
   3. Check these boxes (if they are not already selected)
      • Nested Queries
      • Level Zero Only
      • Allow Inprocess
      • Supports 'Like' Operator
   4. Click OK.
3. Set up linked server connections for the PW_ORDERS Connection
   1. Right-click on Linked Servers under Server Objects
   2. Choose New Linked Server …
4. Enter PW_Orders as the Linked Server
5. Select Microsoft OLE DB Provider for ODBC Driver for the provider
6. Enter Presswise-Orders (the name of the DSN you created above) in the Datasource

1. Click on the Security page.
2. Select Be made using this security context to set up the default user and password.
3. Enter the MySql user name for the Remote Login
4. Enter the provided password

1. Click OK
2. Set up linked server connections for the PW_PRODUCTION Connection
   1. Right-click on Linked Servers under Server Objects
   2. Choose New Linked Server …
3. Enter PW_Production as the Linked Server
4. Select Microsoft OLE DB Provider for ODBC Driver for the provider
5. Enter Presswise-Production (the name of the DSN you created above) in the Datasource
6. Click on the Security page.
7. Select Be made using this security context to set up the default user and password.
8. Enter the MySql user name for the Remote Login
9. Enter the provided password
10. Click OK
11. Confirm that you see the new Linked Servers in the object explorer

1. Open a new query windows in SSMS.
   1. Note: You can not choose a linked server as the default context, so all queries will need to use the four part table name has the format: server_name.[database_name].[schema_name].table_name.
   2. MySQL does not allow you to specify the database name and schema, so these must be specified in the DSN.
2. Test your connections by running these queries
   • SELECT * from pw_orders…list_device;
   • SELECT top 50 * from pw_production…list_status order by jobid desc;

If you are running SQL Server 2008 R2, you will have the following changes to the above procedures:

1. Download the ODBC 5.1.13 (https://dev.mysql.com/downloads/connector/odbc/5.1.html).
2. Set the Product Name EQUAL to the Data Source name on the General tab for you Linked Server Properties.
3. Set RPC and RPC Out to TRUE on the Server Options tab for your Linked Server Properties.
4. Convert your enum data to char data.

Contributor: Cyrious Software

Date: 6/2016