4 Replies Latest reply: May 15, 2014 9:56 AM by Ron_b _ RSS

    ODBC connection/MySQL database

    Ron_b _

      I'm using Monarch Pro V9 and I'm attempting to connect to a MySQL database.  Monarch appears to be finding the database fine but when I get to the "Select a dataset" dialog there are no tables from the database listed.  Outside of Monarch I am able to use the ODBC connector test in the Administrative Tools area and I get a successful connection response.  I'm able to use Excel to connect to the same database and Excel is able to see the tables of the database.  The data in the connection string that I can see in Monarch appears pretty standard.  The one item in the connect string that I'm not sure about is an "option" setting.  Can I manipulate this?

      DSN=DE93;OPTION=4;PORT=3306;SERVER=192.168.1.117;UID=root

       

      Any suggestions on tweaks that I can do to Monarch to enable it to successfully see the datatables?

        • ODBC connection/MySQL database
          Dee Moore

          Hi Ron,

           

          What Driver did you use in creating your DSN?

           

          Regards,

          Dee Moore

          Datawatch Tech Support

          • ODBC connection/MySQL database
            Ron_b _

            At this point I've tried three different versions of MySQL ODBC connector.  The first time I tried I had ODBC Connector version 3.51.12.  When it didn't work I assumed I might need an update so I downloaded/installed 3.51.14.  When that didn't work I tried the older 2.50 version and that didn't work either.  Any help in solving this is greatly appreciated.

            • ODBC connection/MySQL database
              Gareth Horton

              Ron,

               

              I was able to connect to MySQL successfully using the 3.51.12 driver, see the tables and get the data successfully.

               

              It may be that a different way of connecting is worth a try.

               

              When you go to File|Open Database, hit Browse and choose OLE DB connection, instead of ODBC connection.

               

              Choose the Data Link Wizard and select the "Microsoft OLE DB Provider for ODBC Drivers" from the list.

               

              Hit next, then choose the DSN that you use for MySQL.

               

              Hit test connection to make sure that it is valid.

               

              Hit OK.

               

              Now you will see that the connection string in Monarch looks something like this:

               

              [font="courier"]Provider=MSDASQL.1;Persist Security Info=False;Data Source=mysql;Extended Properties="DATABASE=classicmodels;DSN=mysql;OPTION=16384;PORT=0;SERVER=localhost;UID=root"[/font][/quote](Note that I did try it with OPTION set to 4, as in your example and it worked fine)

               

              When you click Next, you should see the tables.

               

              A couple of things to note - in my DSN, the specific database under MySQL is specified.  In this case, it is called classicmodels.

               

              You must specify the database under the DSN for Monarch to connect.

               

              I was not able to get the 5.x MySQL ODBC driver to work with Monarch, Excel or Access.

               

               

              Gareth

               

               

              Originally posted by Ron_b:

              I'm using Monarch Pro V9 and I'm attempting to connect to a MySQL database.  Monarch appears to be finding the database fine but when I get to the "Select a dataset" dialog there are no tables from the database listed.  Outside of Monarch I am able to use the ODBC connector test in the Administrative Tools area and I get a successful connection response.  I'm able to use Excel to connect to the same database and Excel is able to see the tables of the database.  The data in the connection string that I can see in Monarch appears pretty standard.  The one item in the connect string that I'm not sure about is an "option" setting.  Can I manipulate this?

              DSN=DE93;OPTION=4;PORT=3306;SERVER=192.168.1.117;UID=root

               

              Any suggestions on tweaks that I can do to Monarch to enable it to successfully see the datatables? /b[/quote]

              • ODBC connection/MySQL database
                Ron_b _

                I used the suggestion and I'm now able to connect to the database and see datatables successfully.  Thank you.