GNU Info
(mysql.info)MyODBC clients
Programs Known to Work with MyODBC
----------------------------------
Most programs should work with *MyODBC*, but for each of those listed
below, we have tested it ourselves or received confirmation from some
user that it works:
*Program*
*Comment*
Access
To make Access work:
* If you are using Access 2000, you should get and install the
newest (version 2.6 or above) Microsoft MDAC (`Microsoft Data
Access Components') from `http://www.microsoft.com/data'.
This will fix the following bug in Access: when you export
data to MySQL, the table and column names aren't specified.
Another way to around this bug is to upgrade to MyODBC
Version 2.50.33 and MySQL Version 3.23.x, which together
provide a workaround for this bug!
You should also get and apply the Microsoft Jet 4.0 Service
Pack 5 (SP5) which can be found here
`http://support.microsoft.com/support/kb/articles/Q
239/1/14.ASP'. This will fix some cases where columns are
marked as `#deleted#' in Access.
Note that if you are using MySQL Version 3.22, you must to
apply the MDAC patch and use MyODBC 2.50.32 or 2.50.34 and
above to go around this problem.
* Set the "Return matching rows" MyODBC option field when
connecting to MySQL.
* You should have a primary key in the table. If not, new or
updated rows may show up as `#Deleted#'.
* You should have a timestamp in all tables you want to be able
to update. For maximum portability `TIMESTAMP(14)' or simple
`TIMESTAMP' is recommended instead of other `TIMESTAMP(X)'
variations.
* Only use 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 still get the error `Another user has changed your
data' after adding a `TIMESTAMP' column, the following trick
may help you:
Don't use `table' data sheet view. Create instead 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.
* Access on NT will report `BLOB' columns as `OLE OBJECTS'. If
you want to have `MEMO' columns instead, you should change the
column 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'.
* In some cases, Access may generate illegal SQL queries that
MySQL can't understand. You can fix this by selecting
`"Query|SQLSpecific|Pass-Through"' from the Access menu.
* 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 >
127 in the column!
* If you are using Access 7.0, You should use the option flag
`Return matching rows'.
* If you are using Access 2.0, You should use the option flags
`Return matching rows' and `Simulate ODBC 1.0'.
ADO
When you are coding with the ADO API and *MyODBC* you need to put
attention in some default properties that aren't supported by the
MySQL server. For example, using the `CursorLocation Property' as
`adUseServer' will return for the `RecordCount Property' a result
of -1. To have the right value, you need to set this property to
`adUseClient', like is showing in the VB code below:
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 use the option flag `Return matching rows'.
BDE applications
To get these to work, you should set the option flags `Don't
optimize column widths' and `Return matching rows'.
Borland Builder 4
When you start a query you can use the property `Active' or use the
method `Open'. Note that `Active' will start by automatically
issuing a `SELECT * FROM ...' query that may not be a good thing if
your tables are big!
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* Version 2.50.26 works
with MySQL Version 3.22.27 and ColdFusion for Linux. (Any newer
version should also work.) You can download *MyODBC* at
`http://www.mysql.com/downloads/api-myodbc.html'
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 grief.
Excel
Works. Some 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 plain stupid 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 db with a table containing 2 columns of text:
* Insert rows using the `mysql' client command-line tool.
* Create a DSN file using the MyODBC driver, for example, my
for the db above.
* Open the Word application.
* Create a blank new documentation.
* Using the tool bar called Database, press the button insert
database.
* Press the button Get Data.
* At the right hand of the screen Get Data, press the button Ms
Query.
* In the Ms Query create a New Data Source using the DSN file
my.
* 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. Set the `Don't optimize
column width' option field 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
PRIMARY, though this has not been a problem.
Vision
You should use the option flag `Return matching rows'.
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 set add the option `OPTION=16834' in the ODBC connect
string or set the `Change BIGINT columns to INT' option in the
MyODBC connect screen. You may also want to set the `Return
matching rows' option.
VisualInterDev
If you get the error `[Microsoft][ODBC Driver Manager] Driver does
not support this parameter' the reason may be that you have a
`BIGINT' in your result. Try setting the `Change BIGINT columns
to INT' option in the MyODBC connect screen.
Visual Objects
You should use the option flag `Don't optimize column widths'.
automatically generated by info2www version 1.2.2.9