MySQL Reference Manual for version 3.23.39. - 19 MySQL ODBC Support
code {color:purple} tt {color:green} samp {color:navy} pre {color:maroon}


Go to the first, previous, next, last section, table of contents.



19 MySQL ODBC Support


MySQL provides support for ODBC by means of the MyODBC
program. This chapter will teach you how to install MyODBC,
and how to use it. Here, you will also find a list of common programs that
are known to work with MyODBC.




19.1 How To Install MyODBC


MyODBC is a 32-bit ODBC (2.50) level 0 (with level 1 and level
2 features) driver for connecting an ODBC-aware application to
MySQL. MyODBC works on Windows95, Windows98, NT, and
on most Unix platforms.



MyODBC is in public domain, and you can find the newest version
at http://www.mysql.com/downloads/api-myodbc.html.



If you have problem with MyODBC and your program also works
with OLEDB, you should try the OLEDB driver that you can find in the
Contrib section. See section D Contributed Programs.



Normally you only need to install MyODBC on Windows machines.
You only need MyODBC for Unix if you have a program like
ColdFusion that is running on the Unix machine and uses ODBC to connect
to the databases.



If you want to install MyODBC on a Unix box, you will also need
an ODBC manager. MyODBC is known to work with
most of the Unix ODBC managers. You can find a list at these in the
ODBC-related links section on the MySQL useful links page.
See section 1.10 Useful MySQL-related Links.



To install MyODBC on Windows, you should download the
appropriate MyODBC .zip file (for Windows or NT/Win2000),
unpack it with WINZIP, or some similar program, and execute the
SETUP.EXE file.



On Windows/NT you may get the following error when trying to install
MyODBC:




An error occurred while copying C:\WINDOWS\SYSTEM\MFC30.DLL. Restart
Windows and try installing again (before running any applications which
use ODBC)



The problem in this case is that some other program is using ODBC and
because of how Windows is designed, you may not in this case be able to
install a new ODBC drivers with Microsoft's ODBC setup program. In most
cases you can continue by just pressing Ignore to copy the rest
of the MyODBC files and the final installation should still work. If
this doesn't work, the solution is to reboot your computer in ``safe
mode`` (Choose this by pressing F8 just before your machine starts
Windows during rebooting), install MyODBC, and reboot to normal
mode.






To make a connection to a Unix box from a Windows box, with an ODBC
application (one that doesn't support MySQL natively), you must
first install MyODBC on the Windows machine.


The user and Windows machine must have the access privileges to the
MySQL server on the Unix machine. This is set up with the
GRANT command. See section 7.35 GRANT and REVOKE Syntax.


You must create an ODBC DSN entry as follows:





Open the Control Panel on the Windows machine.


Double-click the ODBC Data Sources 32 bits icon.


Click the tab User DSN.


Click the button Add.


Select MySQL in the screen Create New Data Source and click
the Finish button.


The MySQL Driver default configuration screen is shown.
See section 19.2 How to Fill in the Various Fields in the ODBC Administrator Program.




Now start your application and select the ODBC driver with the DSN you
specified in the ODBC administrator.



Notice that there are other configuration options on the screen of
MySQL (trace, don't prompt on connect, etc) that you can try if
you run into problems.






19.2 How to Fill in the Various Fields in the ODBC Administrator Program


There are three possibilities for specifying the server name on
Windows95:






Use the IP address of the server.


Add a file `\windows\lmhosts' with the following information:



ip hostname


For example:



194.216.84.21 my_hostname




Configure the PC to use DNS.



Example of how to fill in the ODBC setup:


Windows DSN name: test
Description: This is my test database
MySql Database: test
Server: 194.216.84.21
User: monty
Password: my_password
Port:



The value for the Windows DSN name field is any name that is unique
in your Windows ODBC setup.



You don't have to specify values for the Server, User,
Password, or Port fields in the ODBC setup screen.
However, if you do, the values will be used as the defaults later when
you attempt to make a connection. You have the option of changing the
values at that time.



If the port number is not given, the default port (3306)
is used.



If you specify the option Read options from C:\my.cnf, the groups
client and odbc will be read from the `C:\my.cnf' file.
You can use all options that are usable by mysql_options().
See section 24.1.3.159 mysql_options().




19.3 Connect parameters for MyODBC


One can specify the following parameters for MyODBC on
the [Servername] section of an ODBC.INI file or
through the InConnectionString argument in the
SQLDriverConnect() call.



Parameter Default value Comment

user ODBC (on Windows) The username used to connect to MySQL.

server localhost The hostname of the MySQL server.

database The default database

option 0 A integer by which you can specify how MyODBC should work. See below.

port 3306 The TCP/IP port to use if server is not localhost.

stmt A statement that will be executed when connection to MySQL.

password The password for the server user combination.

socket The socket or Windows pipe to connect to.




The option argument is used to tell MyODBC that the client isn't 100%
ODBC compliant. On Windows, one normally sets the option flag by
toggling the different options on the connection screen but one can also
set this in the opton argument. The following options are listed in the
same order as they appear in the MyODBC connect screen:



Bit Meaning

1 The client can't handle that MyODBC returns the real width of a column.

2 The client can't handle that MySQL returns the true value of affected rows. If this flag is set then MySQL returns 'found rows' instead. One must have MySQL 3.21.14 or newer to get this to work.

4 Make a debug log in c:\myodbc.log. This is the same as putting MYSQL_DEBUG=d:t:O,c::\myodbc.log in `AUTOEXEC.BAT'

8 Don't set any packet limit for results and parameters.

16 Don't prompt for questions even if driver would like to prompt

32 Simulate a ODBC 1.0 driver in some context.

64 Ignore use of database name in 'database.table.column'.

128 Force use of ODBC manager cursors (experimental).

256 Disable the use of extended fetch (experimental)

512 Pad CHAR fields to full column length.

1024 SQLDescribeCol() will return fully qualifed column names

2048 Use the compressed server/client protocol

4096 Tell server to ignore space after function name and before '(' (needed by PowerBuilder). This will make all function names keywords!

8192 Connect with named pipes to a mysqld server running on NT.

16384 Change LONGLONG columns to INT columns (Some applications can't handle LONGLONG).

32768 Return 'user' as Table_qualifier and Table_owner from SQLTables (experimental)

65536 Read parameters from the client and odbc groups from `my.cnf'

131072 Add some extra safety checks (should not bee needed but...)




If you want to have many options, you should add the above flags! For
example setting option to 12 (4+8) gives you debugging without package
limits!



The default `MYODBC.DLL' is compiled for optimal performance. If
you want to to debug MyODBC (for example to enable tracing),
you should instead use MYODBCD.DLL. To install this file, copy
`MYODBCD.DLL' over the installed MYODBC.DLL file.




19.4 How to Report Problems with MyODBC


MyODBC has been tested with Access, Admndemo.exe, C++-Builder,
Borland Builder 4, Centura Team Developer (formerly Gupta SQL/Windows),
ColdFusion (on Solaris and NT with svc pack 5), Crystal Reports,
DataJunction, Delphi, ERwin, Excel, iHTML, FileMaker Pro, FoxPro, Notes
4.5/4.6, SBSS, Perl DBD-ODBC, Paradox, Powerbuilder, Powerdesigner 32
bit, VC++, and Visual Basic.



If you know of any other applications that work with MyODBC, please
send mail to [e-mail] about this!



With some programs you may get an error like:
Another user has modifies the record that you have modified. In most
cases this can be solved by doing one of the following things:






Add a primary key for the table if there isn't one already.


Add a timestamp column if there isn't one already.


Only use double float fields. Some programs may fail when they compare
single floats.



If the above doesn't help, you should do a MyODBC trace file and
try to figure out why things go wrong.




19.5 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 (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!

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 [e-mail] 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.






19.6 How to Get the Value of an AUTO_INCREMENT Column in ODBC


A common problem is how to get the value of an automatically generated ID
from an INSERT. With ODBC, you can do something like this (assuming
that auto is an AUTO_INCREMENT field):




INSERT INTO foo (auto,text) VALUES(NULL,'text');
SELECT LAST_INSERT_ID();



Or, if you are just going to insert the ID into another table, you can do this:




INSERT INTO foo (auto,text) VALUES(NULL,'text');
INSERT INTO foo2 (id,text) VALUES(LAST_INSERT_ID(),'text');



See section 24.1.4.3 How Can I Get the Unique ID for the Last Inserted Row?.



For the benefit of some ODBC applications (at least Delphi and Access),
the following query can be used to find a newly inserted row:


SELECT * FROM tbl_name WHERE auto IS NULL;








19.7 Reporting Problems with MyODBC


If you encounter difficulties with MyODBC, you should start by
making a log file from the ODBC manager (the log you get when requesting
logs from ODBCADMIN) and a MyODBC log.



To get a MyODBC log, you need to do the following:






Ensure that you are using myodbcd.dll and not myodbc.dll.
The easiest way to do this is to get myodbcd.dll from the MyODBC
distribution and copy it over the myodbc.dll, which is probably
in your C:\windows\system32 or C:\winnt\system32 directory.

Note that you probably want to restore the old myodbc.dll file when you
have finished testing, as this is a lot faster than myodbcd.dll.


Tag the `Trace MyODBC' option flag in the MyODBC connect/configure
screen. The log will be written to file `C:\myodbc.log'.

If the trace option is not remembered when you are going back to the
above screen, it means that you are not using the myodbcd.dll
driver (see above).


Start your application and try to get it to fail.



Check the MyODBC trace file, to find out what could be wrong.
You should be able to find out the issued queries by searching after
the string >mysql_real_query in the `myodbc.log' file.



You should also try duplicating the queries in the mysql monitor
or admndemo to find out if the error is MyODBC or MySQL.



If you find out something is wrong, please only send the relevant rows
(max 40 rows) to [e-mail]. Please never
send the whole MyODBC or ODBC log file!



If you are unable to find out what's wrong, the last option is to
make an archive (tar or zip) that contains a MyODBC trace file, the ODBC
log file, and a README file that explains the problem. You can send this
to ftp://support.mysql.com/pub/mysql/secret. Only we at
MySQL AB will have access to the files you upload, and we will
be very discrete with the data!



If you can create a program that also shows this problem, please
upload this too!



If the program works with some other SQL server, you should make an ODBC log
file where you do exactly the same thing in the other SQL server.



Remember that the more information you can supply to us, the more
likely it is that we can fix the problem!



Go to the first, previous, next, last section, table of contents.