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.
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
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
GRANTcommand. See section 7.35
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.
There are three possibilities for specifying the server name on Windows95:
ip hostnameFor example:
Example of how to fill in the
Windows DSN name: test Description: This is my test database MySql Database: test Server: 22.214.171.124 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
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
odbc will be read from the `C:\my.cnf' file.
You can use all options that are usable by
See section 126.96.36.199
One can specify the following parameters for MyODBC on
[Servername] section of an
ODBC.INI file or
InConnectionString argument in the
|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 |
|stmt|| A statement that will be executed when connection to |
|password|| The password for the |
|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:
|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 |
|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 |
|8192|| Connect with named pipes to a |
|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 |
|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 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 email@example.com 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:
If the above doesn't help, you should do a
MyODBC trace file and
try to figure out why things go wrong.
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:
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.
TIMESTAMPis recommended instead of other
#Deleted#or that you can't find or update rows.
Another user has changed your dataafter adding a
TIMESTAMPcolumn, the following trick may help you: Don't use
tabledata sheet view. Create instead a form with the fields you want, and use that
formdata sheet view. You should set the
DefaultValueproperty for the
NOW(). It may be a good idea to hide the
TIMESTAMPcolumn from view so your users are not confused.
OLE OBJECTS. If you want to have
MEMOcolumns instead, you should change the column to
DATEcolumns properly. If you have a problem with these, change the columns to
"Query|SQLSpecific|Pass-Through"from the Access menu.
TINYINT UNSIGNED. This will give you problems if you have values > 127 in the column!
Return matching rows.
Return matching rowsand
Simulate ODBC 1.0.
adUseServerwill return for the
RecordCount Propertya 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.CloseAnother workaround is to use a
SELECT COUNT(*)statement for a similar query to get the correct row count.
Return matching rows.
Don't optimize column widthsand
Return matching rows.
Activeor use the method
Open. Note that
Activewill start by automatically issuing a
SELECT * FROM ...query that may not be a good thing if your tables are big!
ENUM, as it exports the latter in a manner that causes MySQL grief.
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.
MyODBCdriver and the Add-in Microsoft Query help. For example, create a db with a table containing 2 columns of text:
mysqlclient command-line tool.
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);
Return matching rows.
SHOW PROCESSLISTwill not work properly. The fix is to set add the option
OPTION=16834in the ODBC connect string or set the
Change BIGINT columns to INToption in the MyODBC connect screen. You may also want to set the
Return matching rowsoption.
[Microsoft][ODBC Driver Manager] Driver does not support this parameterthe reason may be that you have a
BIGINTin your result. Try setting the
Change BIGINT columns to INToption in the MyODBC connect screen.
Don't optimize column widths.
AUTO_INCREMENTColumn in ODBC
A common problem is how to get the value of an automatically generated ID
INSERT. With ODBC, you can do something like this (assuming
auto is an
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 188.8.131.52 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;
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:
myodbc.dll. The easiest way to do this is to get
myodbcd.dllfrom the MyODBC distribution and copy it over the
myodbc.dll, which is probably in your
C:\winnt\system32directory. 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.dlldriver (see above).
MyODBC trace file, to find out what could be wrong.
You should be able to find out the issued queries by searching after
>mysql_real_query in the `myodbc.log' file.
You should also try duplicating the queries in the
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 firstname.lastname@example.org. 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.