DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH PRINT BOOK
 

(mysql.info) programs-known-to-work-with-myodbc

Info Catalog (mysql.info) myodbc-tested-applications (mysql.info) myodbc-configuration
 
 23.1.9.9 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.
 
    * *Program*
 
      *Comment*
 
    * Access
 
      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
           `http://www.microsoft.com/data/'. This fixes 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
           `http://support.microsoft.com/default.aspx?scid=kb;EN-US;q239114'.
           This fixes some cases where columns are marked as `#DELETED#'
           in Access.
 
           Note: If you are using MySQL 3.22, you must 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 are displayed as `#DELETED'. The
           work around solution is:
 
              * Have one more dummy column with `TIMESTAMP' as the data
                type.
 
              * 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 display as `#DELETED#', but newly
           added/updated records are 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
           confused.
 
         * 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 reports `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
           tries to export this as `TINYINT' instead of `TINYINT
           UNSIGNED'. This gives you problems if you have values larger
           than 127 in the column.
 
    * ADO
 
      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' returns 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
           myrs.Open
           myrows = myrs.RecordCount
 
           myrs.Close
           myconn.Close
 
      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' starts 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
      documentation:
 
      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
      `http://dev.mysql.com/downloads/connector/odbc/'.
 
      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 appears in the ODBC datasources drop-down list, you must
      build and copy the MyODBC driver to
      `/opt/coldfusion/lib/libmyodbc.so'.
 
      The Contrib directory contains the program `mydsn-XXX.zip' which
      allows you to build and remove the DSN registry file for the
      MyODBC driver on Coldfusion applications.
 
    * DataJunction
 
      You have to change it to output `VARCHAR' rather than `ENUM', as
      it exports the latter in a manner that causes MySQL problems.
 
    * Excel
 
      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.
 
    * Word
 
      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'
           button.
 
         * 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
           file.
 
         * 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.
 
    * odbcadmin
 
      Test program for ODBC.
 
    * Delphi
 
      You must use BDE 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 <bryan@flesherfab.com> for
      this):
 
           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');
           fReg.CloseKey;
           fReg.Free;
 
           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('LANGDRIVER=');
           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('SQLPASSTHRU MODE=SHARED AUTOCOMMIT');
           Memo1.Lines.Add('SQLQRYMODE=');
           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');
 
           AliasEditor.Add('DocumentsFab','MySQL',Memo1.Lines);
 
    * C++ Builder
 
      Tested with BDE 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', although this has not been a problem.
 
    * Vision
 
      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' do 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.
 
    * VisualInterDev
 
      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 (mysql.info) myodbc-tested-applications (mysql.info) myodbc-configuration
automatically generated byinfo2html