MySQL ODBC Support
==================
known to
work with `MyODBC'.
How to Install MyODBC
---------------------
most Unix
platforms. `MyODBC' 3.51 is an enhanced version with ODBC 3.5x
specification level 1 (complete core API + level 2 features).
are
`GPL' licensed.
If you have problem with `MyODBC' and your program also works with
OLEDB, you should try the OLEDB driver.
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.
To install `MyODBC' on Windows, you should download the appropriate
`MyODBC' `.zip' file, unpack it with `WinZIP' or some similar program,
and execute the `SETUP.EXE' file.
On Windows/NT/XP 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 mode.
* To make a connection to a 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 on the Unix machine. This is set up with the `GRANT'
command. *Note `GRANT': GRANT.
* You must create an ODBC DSN entry as follows:
- Open the Control Panel on the Windows machine.
- Double-click the ODBC Data Sources 32-bit 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 MySQL Driver default configuration screen is shown.
*Note ODBC administrator::.
* 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 on the screen of
MySQL (trace, don't prompt on connect, etc) that you can try if you run
into problems.
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 `\windows\lmhosts' with the following information:
ip hostname
For example:
194.216.84.21 my_hostname
* Configure the PC to use DNS.
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.
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()'. *Note
`mysql_options()': mysql_options.
Connect parameters for MyODBC
-----------------------------
One can specify the following parameters for `MyODBC' on the
`[Servername]' section of an `ODBC.INI' file or through the
`InConnectionString' argument in the `SQLDriverConnect()' call.
*Parameter* *Default *Comment*
value*
user ODBC (on The username used to connect to MySQL.
Windows)
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 `server' is not
`localhost'.
stmt A statement that will be executed when
connecting to `MySQL'.
password The password for the `server' `user'
combination.
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 option argument. The following options are listed in the
same order as they appear in the `MyODBC' connect screen:
*Bit* *Description*
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
`MYSQL_DEBUG=d:t:O,c::\myodbc.log' in `AUTOEXEC.BAT'
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 qualified column names
2048 Use the compressed server/client protocol
4096 Tell server to ignore space after function name and before `'(''
(needed by PowerBuilder). This will make all function names
keywords!
8192 Connect with named pipes to a `mysqld' server running on NT.
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 `client' and `odbc' groups from `my.cnf'
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!
over the installed `MYODBC.DLL' file.
How to Report Problems with MyODBC
----------------------------------
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 the `myodbc' mailing list about this! *Note
Mailing-list::.
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:
* Add a primary key for the table if there isn't one already.
* Add a timestamp column if there isn't one already.
* Only use double float fields. Some programs may fail when they
compare single floats.
If the above doesn't help, you should do a `MyODBC' trace file and try
to figure out why things go wrong.
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'.
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.
* 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.
near you. (Thanks to Bryan Brunton for
this):
');
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('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);
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'.
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');
*Note Getting unique ID::.
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;
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, you need to do the following:
1. Ensure that you are using `myodbcd.dll' and not `myodbc.dll'. The
easiest way to do this is to get `myodbcd.dll' from the MyODBC
distribution and copy it over the `myodbc.dll', which is probably
in your `C:\windows\system32' or `C:\winnt\system32' directory.
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.dll'.
2. Tag the `Trace MyODBC' option flag in the `MyODBC'
connect/configure screen. The log will be written to file
`C:\myodbc.log'.
If the trace option is not remembered when you are going back to
the above screen, it means that you are not using the `myodbcd.dll'
driver (see the item above).
3. Start your application and try to get it to fail.
Check the `MyODBC trace file', to find out what could be wrong. You
should be able to find out the issued queries 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' mailing list. *Note Mailing-list::.
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!
[Назад] [Содержание] [Вперед]
| Главная |