MySQL provides support for ODBC by means of the MyODBC program.
MyODBC is a 32-bit ODBC (2.50) level 0 driver for Windows95 and Windows NT. We hope somebody will port it to Windows 3.x.
ODBC has been tested with Access, Admndemo.exe, C++-Builder, Centura Team Developer (formerly Gupta SQL/Windows), ColdFusion (on Solaris), Crystal Reports, Delphi, Excel, iHTML, FileMaker Pro, FoxPro, Notes 4.5/4.6, SBSS, perl DBD-ODBC, Paradox, Powerbuilder, VC++ and Visual Basic.
If you know of any other application that works with MyODBC, please mail myodbc@tcx.se about this!
If you encounter difficulties, we would like to have the log file from the ODBC manager (the log you get when requesting logs from ODBCADMIN) and a MyODBC log. This will help shed some light on any problems.
To get a MyODBC log, please 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!
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:
BLOB
columns as OLE OBJECTS
. If
you want to have MEMO
columns instead, you should change the
column to TEXT
with ALTER TABLE
.
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.
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);
There are three possibilities for specifying the server name on Windows95:
ip hostnameFor example:
194.216.84.21 my
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.
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;
Go to the first, previous, next, last section, table of contents.