(mysql.info.gz) Programs Known to Work With MyODBC
Info Catalog
(mysql.info.gz) MyODBC Tested Applications
(mysql.info.gz) 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 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
`http://support.microsoft.com/default.aspx?scid=kb;EN-US;q239114'.
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
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 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
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 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.
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' 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
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' 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
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
will appear 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 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 <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 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.
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' 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.
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.gz) MyODBC Tested Applications
(mysql.info.gz) MyODBC Configuration
automatically generated byinfo2html