|
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
Microsoft MDAC 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.
|
