9.2.5 Programs Known to Work with MyODBC

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:

Program

Comment

Access

To make Access work:

• If you are using Access 2000, you should get and install the newest (version 2.6 or above) 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! 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.
• For all Access versions, you should enable the MyODBC option flag Return matching rows. For Access 2.0, you should additionally enable Simulate ODBC 1.0.
• 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.
• You should have a primary key in the table. If not, new or updated rows may show up as #DELETED#.
• 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 are linking a table through MyODBC, which has BIGINT as one of the column, then the results will be displayed as #DELETED. The work around solution is:
  • Have one more dummy column with TIMESTAMP as the datatype, preferably TIMESTAMP(14).
  • Check the 'Change BIGINT columns to INT' in connection options dialog in ODBC DSN Administrator
  • Delete the table link from access and re-create it.
  It still displays the previous records as #DELETED#, but newly added/updated records will be 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 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.
• 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.
• 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.
• 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!

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 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 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. 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 recognised 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 ODBC manager, 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 recognise 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 the option OPTION=16384 in the ODBC connect string or to 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.

User Comments

re Access & VB w/ MyODBC: If you are updating a
MySQL text field using ADO, you should set column
size optimization OFF in the MyODBC options
screen. Otherwise, the ADO DefinedSize property
incorrectly reflects the optimized size instead of the
defined size. The result is that ADO complains if you
add a record with a text field longer than the
previously-optimized size.

Also, if you use column names with spaces or other
naughty things, you should set the ADO
CursorLocation
to adUseClient to update MySQL records. This avoids
the problem of backquoting field names --
apparently ADO must do it for you if the cursor is
local (haven't looked into the why, but it works).

In ASP, the following error may be generated when
you attempt to populate a field marked as VarChar
using the "objRecordSet.Fields("field_name") =
value" syntax:
"Multiple-step OLE DB operation generated errors.
Check each OLE DB status value, if available. No
work was done."
To correct this problem, check the "Don't optimize
column widths" in your MyODBC configurations.

Well, I have tried to set the Don't Optimize Column
Width checkbox, but it didn't work. Not a real
solution to this problem...

I found that to in Microsoft Access i would
get "#deleted" when inserting a new row into a
table with TEXT or MEDIUMTEXT field. I tried all the
tips on this page and installed all the latest, relevant,
pieces of software. Eventually I found that if I
included a CHAR or VARCHAR field in the table and
gave it a value when inserting the row the row
inserted fine and access did not display #deleted. (I
hope someone finds this useful) (If anyone knows
why i was getting the problem in the first place, or
better fix, or why this one works, I for one would
certainly appreciate their posting a comment here!)

I'm trying to use MySQL (3.23.52) as backend of
my powerbuilder 7 application (maintenance release
2) but i encountered a problem filtering records using
datetime
columns and runtime arguments ( e.g. datetimevar
>= '11-01-1999 and datetimevar <= '11-30-
1999). Any idea how can this be done.

Well, recently I been working in a program to
exporta data from ACCESS to mySQL using Visual
Basic 6, but I founded an strange thing. Visual Basic
does not work with "Create Table" query's, the Error
reportes is "Syntax error" in query, but running this
query from another interface phpmyAdmin by
example, my query runs fine. Aparently, the problem
is Visual Basic, trying to check syntac before sending
the query to mySQL. Further, i'll be in touch to
explain how to solve this problem.

Contents of fields with same name are retrieved correctly in Access, such as the example below:

SELECT * FROM TABLE1,TABLE2 WHERE TABLE1.USUERID=TABLE2.USERID

(in this case, both tables have a USERNAME field)

rs("TABLE1.USERNAME")
rs("TABLE2.USERNAME")

'in access it will return both contents of respective "usename" fields from its respective tables without any problems.

however... in mysql i have the following error:

ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name or ordinal.

/pg_listuser.asp, line 112

Why is that? How to retrieve contents of fields of same name but different tables by their names just like i do in Access without any problems? I donīt want to use numbers to retrieve it.

Is there any help, couldnīt find it in any forum.

Instead of;

SELECT * FROM TABLE1,TABLE2 WHERE TABLE1.USERID=TABLE2.USERID

Try;

SELECT table1.*, table2.* FROM TABLE1,TABLE2 WHERE TABLE1.USERID=TABLE2.USERID

MySQL gets confused and doesn't seem to keep track of same name fields in multiple tables.

Regarding #Deleted. If by some reason it is not possible to fix #Deleted problem with attached MySQL table to MS Access, use Path-Thru query with "Select * from MySQL table" instead of attaching the table. Attaching tables from server databases is not a good practice anyway).
This will display records properly (tested with MySQL 4.0 and Access97).

For more details drop me an email: glush@optushome.com.au

Very peculiar problem experienced within Access 2000. Attempting to convert over to mySQL and necessarily have to migrate data. Those fields currently containing NULL value
are creating an error in appending to mySQL tables:
"You tried to assign the Null value to a variable...etc"
The mySQL tables are set up correctly-primary keys, appropriate indexes and NULL allowed.

Converting NULLS toempty strings during append solves the import problem, then replace the empty strings with NULL again through server based queries to finish the migration. Points to a problem with ODBC?

Jean-Paul

Re: Access and MySQL - write conflict error.

I was able to get rid of this error when trying to edit data in Access by dropping a date field that contained all null values (0000-00-00). I did not have to add a time stamp field or an auto increment field.

OK Wrong subject but who cares...

After installing MyODBC-3.51.06 and setting a new mySQL Connection (testing works 100%) and while using via ASP i get an error:

Error:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
/pub/Personal/Inc/ODBC.asp, line 10

Can anybody help?

After much searching I found the correct connection string to use for people who do not want to rely on a DSN.

strConn = "Provider=MSDASQL; DRIVER={MySQL ODBC 3.51 Driver}; Server=localhost; UID=username; PWD=xxx; database=databasename; Option=16387;"

I've lost the URL I got it from but kudos to the bloke that posted it.

An example of Visual Basic 6.0 sp5 and a mysql server can be found at http://www.ep.net.au/ant/main.htm, it uses ado 2.5 to talk to the server..

There is a MySQL class library included.

Add your own comment.