MySQL 5.1 Database Reference Guide :: 26.1.9.9 Programs Known to Work With MyODBC

Previous / Next / Up / Table of Contents

26.1.9.9. Programs Known to Work With MyODBC

$8.99

Most programs should work with MyODBC, but for each of those listed here, we have tested it ourselves or received confirmation from some user that it works. Many of the descriptions provide workarounds for problems that you might encounter.

Program

Comment
Access

To make Access work:
- If you are using Access 2000, you should get and install the newest (version 2.6 or higher) Microsoft MDAC (Microsoft Data Access Components) from https://www.microsoft.com/data/. This fixes a bug in Access that when you export data to MySQL, the table and column names aren't specified. Another way to work around this bug is to upgrade to MyODBC 2.50.33 and MySQL 3.23.x, which together provide a workaround for the problem.
  
  You should also get and apply the Microsoft Jet 4.0 Service Pack 5 (SP5) which can be found at https://support.microsoft.com/default.aspx?scid=kb;EN-US;q239114. This fixes some cases where columns are marked as #DELETED# in Access.
  
  Note: If you are using MySQL 3.22, you must apply the MDAC patch and use MyODBC 2.50.32 or 2.50.34 and up to work around this problem.
- For all versions of Access, you should enable the MyODBC Return matching rows option. For Access 2.0, you should additionally enable the Simulate ODBC 1.0 option.
- You should have a timestamp in all tables that you want to be able to update. For maximum portability, don't use a length specification in the column declaration. That is, use TIMESTAMP, not TIMESTAMP(N), N < 14.
- You should have a primary key in the table. If not, new or updated rows may show up as #DELETED#.
- Use only 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 are using MyODBC to link to a table that has a BIGINT column, the results are displayed as #DELETED. The work around solution is:
  - Have one more dummy column with TIMESTAMP as the data type.
  - Select the Change BIGINT columns to INT option in the connection dialog in ODBC DSN Administrator.
  - Delete the table link from Access and re-create it.
  Old records still display as #DELETED#, but newly added/updated records are displayed properly.
- 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 a table data sheet view. Instead, create 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.
- In some cases, Access may generate illegal SQL statements that MySQL can't understand. You can fix this by selecting "Query|SQLSpecific|Pass-Through" from the Access menu.
- On NT, Access reports BLOB columns as OLE OBJECTS. If you want to have MEMO columns instead, you should change BLOB columns 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.
- If you have in Access a column defined as BYTE, Access tries to export this as TINYINT instead of TINYINT UNSIGNED. This gives you problems if you have values larger than 127 in the column.
ADO

When you are coding with the ADO API and MyODBC, you need to pay attention to some default properties that aren't supported by the MySQL server. For example, using the CursorLocation Property as adUseServer returns a result of -1 for the RecordCount Property. To have the right value, you need to set this property to adUseClient, as shown in the VB code here:
```
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 select the Return matching rows option.
BDE applications

To get these to work, you should select the Don't optimize column widths and Return matching rows options.
Borland Builder 4

When you start a query, you can use the Active property or the Open method. Note that Active starts by automatically issuing a SELECT * FROM ... query. That may not be a good thing if your tables are large.
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 2.50.26 works with MySQL 3.22.27 and ColdFusion for Linux. (Any newer version should also work.) You can download MyODBC at https://dev.mysql.com/downloads/connector/odbc/.

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 appears 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 problems.
Excel

Works. A few 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 unfortunate 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 database with a table containing two columns of text:
- Insert rows using the mysql client command-line tool.
- Create a DSN file using the ODBC manager, for example, my for the database that was just created.
- Open the Word application.
- Create a blank new document.
- In the Database tool bar, press the Insert Database button.
- Press the Get Data button.
- At the right hand of the Get Data screen, press the Ms Query button.
- In Ms Query, create a new data source using the my DSN file.
- 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 3.2 or newer. Select the Don't optimize column width option 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 <[email protected]> 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 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 named PRIMARY, although this has not been a problem.
Vision

You should select the Return matching rows option.
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 do not work properly. The fix is to use OPTION=16384 in the ODBC connect string or to select the Change BIGINT columns to INT option in the MyODBC connect screen. You may also want to select the Return matching rows option.
VisualInterDev

If you have a BIGINT in your result, you may get the error [Microsoft][ODBC Driver Manager] Driver does not support this parameter Try selecting the Change BIGINT columns to INT option in the MyODBC connect screen.
Visual Objects

You should select the Don't optimize column widths option.
MS Visio Enterprise 2000

We made database model diagram by connecting from MS Vision Enterprise 2000 to MySQL via MyODBC (2.50.37 or greater) and using Visio's reverse engineer function to retrieve information about the DB (Visio shows all the column definitions, primary keys, indexes and so on). Also, we tested by designing new tables in Visio and exported them to MySQL via MyODBC.

Previous / Next / Up / Table of Contents