( Programs Known to Work With MyODBC

Info Catalog ( MyODBC Tested Applications ( MyODBC Configuration Programs Known to Work With MyODBC
 Most programs should work with MyODBC, but for each of those listed
 here, we have tested it ourselves or received confirmation from some
 user that it works. Many of the descriptions provide workarounds for
 problems that you might encounter.
      To make Access work:
         * If you are using Access 2000, you should get and install the
           newest (version 2.6 or higher) Microsoft MDAC (`Microsoft
           Data Access Components') from
           `'.  This will fix a bug in
           Access that when you export data to MySQL, the table and
           column names aren't specified.  Another way to work around
           this bug is to upgrade to MyODBC 2.50.33 and MySQL 3.23.x,
           which together provide a workaround for the problem.
           You should also get and apply the Microsoft Jet 4.0 Service
           Pack 5 (SP5) which can be found at
           This will fix some cases where columns are marked as
           `#DELETED#' in Access.
           Note: If you are using MySQL 3.22, you must to apply the MDAC
           patch and use MyODBC 2.50.32 or 2.50.34 and up to work around
           this problem.
         * For all versions of Access, you should enable the MyODBC
           `Return matching rows' option. For Access 2.0, you should
           additionally enable the `Simulate ODBC 1.0' option.
         * You should have a timestamp in all tables that you want to be
           able to update.  For maximum portability, don't use a length
           specification in the column declaration.  That is, use
           `TIMESTAMP', not `TIMESTAMP(N)', N < 14.
         * You should have a primary key in the table. If not, new or
           updated rows may show up as `#DELETED#'.
         * Use only `DOUBLE' float fields. Access fails when comparing
           with single floats.  The symptom usually is that new or
           updated rows may show up as `#DELETED#' or that you can't
           find or update rows.
         * If you are using MyODBC to link to a table that has a `BIGINT'
           column, the results will be displayed as `#DELETED'. The work
           around solution is:
              * Have one more dummy column with `TIMESTAMP' as the data
              * Select the `Change BIGINT columns to INT' option in the
                connection dialog in ODBC DSN Administrator.
              * Delete the table link from Access and re-create it.
           Old records still will display as `#DELETED#', but newly
           added/updated records will be displayed properly.
         * If you still get the error `Another user has changed your
           data' after adding a `TIMESTAMP' column, the following trick
           may help you:
           Don't use a `table' data sheet view. Instead, create a form
           with the fields you want, and use that `form' data sheet
           view.  You should set the `DefaultValue' property for the
           `TIMESTAMP' column to `NOW()'.  It may be a good idea to hide
           the `TIMESTAMP' column from view so your users are not
         * In some cases, Access may generate illegal SQL statements that
           MySQL can't understand. You can fix this by selecting
           `"Query|SQLSpecific|Pass-Through"' from the Access menu.
         * On NT, Access will report `BLOB' columns as `OLE OBJECTS'. If
           you want to have `MEMO' columns instead, you should change
           `BLOB' columns to `TEXT' with `ALTER TABLE'.
         * Access can't always handle `DATE' columns properly. If you
           have a problem with these, change the columns to `DATETIME'.
         * If you have in Access a column defined as `BYTE', Access will
           try to export this as `TINYINT' instead of `TINYINT UNSIGNED'.
           This will give you problems if you have values larger than
           127 in the column.
      When you are coding with the ADO API and MyODBC, you need to pay
      attention to some default properties that aren't supported by the
      MySQL server.  For example, using the `CursorLocation Property' as
      `adUseServer' will return a result of -1 for the `RecordCount
      Property'.  To have the right value, you need to set this property
      to `adUseClient', as shown in the VB code here:
           Dim myconn As New ADODB.Connection
           Dim myrs As New Recordset
           Dim mySQL As String
           Dim myrows As Long
           myconn.Open "DSN=MyODBCsample"
           mySQL = "SELECT * from user"
           myrs.Source = mySQL
           Set myrs.ActiveConnection = myconn
           myrs.CursorLocation = adUseClient
           myrows = myrs.RecordCount
      Another workaround is to use a `SELECT COUNT(*)' statement for a
      similar query to get the correct row count.
 Active server pages (ASP)
      You should select the `Return matching rows' option.
 BDE applications
      To get these to work, you should select the `Don't optimize column
      widths' and `Return matching rows' options.
 Borland Builder 4
      When you start a query, you can use the `Active' property or the
      `Open' method.  Note that `Active' will start by automatically
      issuing a `SELECT * FROM ...' query. That may not be a good thing
      if your tables are large.
 ColdFusion (On Unix)
      The following information is taken from the ColdFusion
      Use the following information to configure ColdFusion Server for
      Linux to use the unixODBC driver with MyODBC for MySQL data
      sources.  Allaire has verified that MyODBC 2.50.26 works with
      MySQL 3.22.27 and ColdFusion for Linux. (Any newer version should
      also work.) You can download MyODBC at
      ColdFusion Version 4.5.1 allows you to us the ColdFusion
      Administrator to add the MySQL data source. However, the driver is
      not included with ColdFusion Version 4.5.1. Before the MySQL driver
      will appear in the ODBC datasources drop-down list, you must build
      and copy the MyODBC driver to `/opt/coldfusion/lib/'.
      The Contrib directory contains the program `' which
      allows you to build and remove the DSN registry file for the
      MyODBC driver on Coldfusion applications.
      You have to change it to output `VARCHAR' rather than `ENUM', as
      it exports the latter in a manner that causes MySQL problems.
      Works. A few tips:
         * If you have problems with dates, try to select them as
           strings using the `CONCAT()' function. For example:
                SELECT CONCAT(rise_time), CONCAT(set_time)
                    FROM sunrise_sunset;
           Values retrieved as strings this way should be correctly
           recognized as time values by Excel97.
           The purpose of `CONCAT()' in this example is to fool ODBC
           into thinking the column is of "string type." Without the
           `CONCAT()', ODBC knows the column is of time type, and Excel
           does not understand that.
           Note that this is a bug in Excel, because it automatically
           converts a string to a time. This would be great if the
           source was a text file, but is unfortunate when the source is
           an ODBC connection that reports exact types for each column.
      To retrieve data from MySQL to Word/Excel documents, you need to
      use the MyODBC driver and the Add-in Microsoft Query help.
      For example, create a database with a table containing two columns
      of text:
         * Insert rows using the `mysql' client command-line tool.
         * Create a DSN file using the ODBC manager, for example, `my'
           for the database that was just created.
         * Open the Word application.
         * Create a blank new document.
         * In the `Database' tool bar, press the `Insert Database'
         * Press the `Get Data' button.
         * At the right hand of the `Get Data' screen, press the `Ms
           Query' button.
         * In `Ms Query', create a new data source using the `my' DSN
         * Select the new query.
         * Select the columns that you want.
         * Make a filter if you want.
         * Make a Sort if you want.
         * Select `Return Data to Microsoft Word'.
         * Click `Finish'.
         * Click `Insert Data' and select the records.
         * Click `OK' and you see the rows in your Word document.
      Test program for ODBC.
      You must use BDE Version 3.2 or newer.  Select the `Don't optimize
      column width' option when connecting to MySQL.
      Also, here is some potentially useful Delphi code that sets up
      both an ODBC entry and a BDE entry for MyODBC. The BDE entry
      requires a BDE Alias Editor that is free at a Delphi Super Page
      near you. (Thanks to Bryan Brunton <> for
           fReg:= TRegistry.Create;
             fReg.OpenKey('\Software\ODBC\ODBC.INI\DocumentsFab', True);
             fReg.WriteString('Database', 'Documents');
             fReg.WriteString('Description', ' ');
             fReg.WriteString('Driver', 'C:\WINNT\System32\myodbc.dll');
             fReg.WriteString('Flag', '1');
             fReg.WriteString('Password', '');
             fReg.WriteString('Port', ' ');
             fReg.WriteString('Server', 'xmark');
             fReg.WriteString('User', 'winuser');
             fReg.OpenKey('\Software\ODBC\ODBC.INI\ODBC Data Sources', True);
             fReg.WriteString('DocumentsFab', 'MySQL');
             Memo1.Lines.Add('DATABASE NAME=');
             Memo1.Lines.Add('USER NAME=');
             Memo1.Lines.Add('ODBC DSN=DocumentsFab');
             Memo1.Lines.Add('OPEN MODE=READ/WRITE');
             Memo1.Lines.Add('BATCH COUNT=200');
             Memo1.Lines.Add('MAX ROWS=-1');
             Memo1.Lines.Add('SCHEMA CACHE DIR=');
             Memo1.Lines.Add('SCHEMA CACHE SIZE=8');
             Memo1.Lines.Add('SCHEMA CACHE TIME=-1');
             Memo1.Lines.Add('ENABLE SCHEMA CACHE=FALSE');
             Memo1.Lines.Add('ENABLE BCD=FALSE');
             Memo1.Lines.Add('ROWSET SIZE=20');
             Memo1.Lines.Add('BLOBS TO CACHE=64');
             Memo1.Lines.Add('BLOB SIZE=32');
 C++ Builder
      Tested with BDE Version 3.0. The only known problem is that when
      the table schema changes, query fields are not updated. BDE,
      however, does not seem to recognize primary keys, only the index
      named `PRIMARY', though this has not been a problem.
      You should select the `Return matching rows' option.
 Visual Basic
      To be able to update a table, you must define a primary key for
      the table.
      Visual Basic with ADO can't handle big integers. This means that
      some queries like `SHOW PROCESSLIST' will not work properly.  The
      fix is to use `OPTION=16384' in the ODBC connect string or to
      select the `Change BIGINT columns to INT' option in the MyODBC
      connect screen.  You may also want to select the `Return matching
      rows' option.
      If you have a `BIGINT' in your result, you may get the error
      `[Microsoft][ODBC Driver Manager] Driver does not support this
      parameter' Try selecting the `Change BIGINT columns to INT' option
      in the MyODBC connect screen.
 Visual Objects
      You should select the `Don't optimize column widths' option.
 MS Visio Enterprise 2000
      We made database model diagram by connecting from MS Vision
      Enterprise 2000 to MySQL via MyODBC (2.50.37 or greater) and using
      Visio's reverse engineer function to retrieve information about
      the DB (Visio shows all the column definitions, primary keys,
      Indexes and so on). Also we tested by designing new tables in
      Visio and exported them to MySQL via MyODBC.
Info Catalog ( MyODBC Tested Applications ( MyODBC Configuration
automatically generated byinfo2html