16 MySQL ODBC Support

MySQL provides support for ODBC by means of the MyODBC program.

16.1 Operating systems supported by MyODBC

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

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.

MyODBC is in public domain and you can find the newest version at http://www.mysql.com/download_myodbc.html.

If you want to install MyODBC on a Unix box, you will also need an ODBC manager. MyODBC is known to work both 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.9 Useful MySQL-related links.

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

An error occurred while copying C:WINDOWSSYSTEMMFC30.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, one can in this case install new ODBC drivers with Microsoft's ODBC setup program :( The solution to this is to reboot your computer in 'safe mod' (You can choose this by pressing F8 just before your machine start windows during rebooting), install MyODBC and reboot to normal mode.

• To make a connection to an 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 the Unix machine. This is set up with the GRANT command. See section 7.26 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 TCX MySQL Driver default configuration screen is shown. See section 16.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 in the screen of MySQL (trace, don't prompt on connect, etc) that you can try if you run into problems.

16.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 `windowslmhosts' 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 20.4.37 mysql_options().

16.3 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 mail myodbc@lists.mysql.com about this!

16.4 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 gotten confirmation from some user that it works:

Program

Comment

Access

To make Access work:

• You should have a primary key in the table.
• You should have a timestamp in all tables you want to be able to update.
• Only use double float fields. Access fails when comparing with single floats.
• Set the `Return matching rows' option field when connecting to MySQL.
• 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.

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 automaticly issue a SELECT * FROM ... query that may not be a good thing if your tables are big!

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.

odbcadmin

Test program for ODBC.

Delphi

You must use DBE 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 which may be had for free at a Delphi Super Page near you.): (Thanks to Bryan Brunton bryan@flesherfab.com for this)

fReg:= TRegistry.Create;
  fReg.OpenKey('SoftwareODBCODBC.INIDocumentsFab', True);
  fReg.WriteString('Database', 'Documents');
  fReg.WriteString('Description', ' ');
  fReg.WriteString('Driver', 'C:WINNTSystem32myodbc.dll');
  fReg.WriteString('Flag', '1');
  fReg.WriteString('Password', '');
  fReg.WriteString('Port', ' ');
  fReg.WriteString('Server', 'xmark');
  fReg.WriteString('User', 'winuser');
  fReg.OpenKey('SoftwareODBCODBC.INIODBC 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 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.

Visual basic

To be able to update a table, you must define a primary key for the table.

16.5 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');

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;

16.6 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, tag the `Trace MyODBC' option flag in the MyODBC connect/configure screen. The log will be written to file `C:myodbc.log'. Note that you must use MYSQL.DLL and not MYSQL2.DLL for this option to work!

Check the queries that MyODBC sends to the MySQL server; You should be able to find this 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 the myodbc@lists.mysql.com. 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 to make a archive (tar or zip) that contains a MyODBC log file, the ODBC log file and a README file that explains the problem. You can send this to ftp://www.mysql.com/pub/mysql/secret. Only we at TCX 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 a 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.