|
| Parameter | Default Value | Comment |
user | ODBC (on Windows) | The username used to connect to MySQL. |
server | localhost | The hostname of the MySQL server. |
database | The default database. | |
option | 0 | Options that specify how MyODBC should work. See below. |
port | 3306 | The TCP/IP port to use if server is not localhost.
|
stmt | A statement to execute when connecting to MySQL. | |
password | The password for the user account on server.
| |
socket | The Unix socket file or Windows named pipe to connect to if server is localhost.
|
The option argument is used to tell MyODBC that the client isn't 100%
ODBC compliant. On Windows, you normally select options by toggling the
checkboxes in the connection screen, but you can also select them in the
option argument. The following options are listed in the order
in which they appear in the MyODBC connect screen:
| Value | 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, MySQL returns ``found rows'' instead. You 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'. (On Unix, the file is `/tmp/myodbc.log'.)
|
| 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 | Enable or disable the dynamic cursor support. (Not allowed in MyODBC 2.50.) |
| 64 | Ignore use of database name in db_name.tbl_name.col_name.
|
| 128 | Force use of ODBC manager cursors (experimental). |
| 256 | Disable the use of extended fetch (experimental). |
| 512 | Pad CHAR columns to full column length.
|
| 1024 | SQLDescribeCol() will return fully qualified column names.
|
| 2048 | Use the compressed client/server 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 be needed but...). |
| 262144 | Disable transactions. |
| 524288 | Enable query logging to `c:\myodbc.sql'(`/tmp/myodbc.sql') file. (Enabled only in debug mode.) |
| 1048576 | Do not cache the results locally in the driver,
instead read from server (mysql_use_result()). This works only for
forward-only cursors. This option is very important in dealing
with large tables when you don't want the driver to cache the
entire result set.
|
| 2097152 | Force the use of Forward-only cursor type. In case
of applications setting the default static/dynamic cursor type, and one
wants driver to use non-cache result sets, then this option will ensure
the forward-only cursor behavior.
|
To select multiple options, add together their values. For example,
setting option to 12 (4+8) gives you debugging without packet limits.
The default `myodbc3.dll' is compiled for optimal performance. If you want to debug MyODBC 3.51 (for example, to enable tracing), you should instead use `myodbc3d.dll'. To install this file, copy `myodbc3d.dll' over the installed `myodbc3.dll' file. Make sure to revert back to the release version of the driver DLL once you are done with the debugging because the debug version may cause performance issues.
For MyODBC 2.50, `myodbc.dll' and `myodbcd.dll' are used instead.
The following table shows some recommended option values for
various configurations:
| Configuration | Option Value |
| Microsoft Access | 3 |
| Microsoft Visual Basic | 3 |
| Large tables with too many rows | 2049 |
| Driver trace generation (Debug mode) | 4 |
| Query log generation (Debug mode) | 524288 |
| Generate driver trace as well as query log (Debug mode) | 524292 |
| Large tables with no-cache results | 3145731 |
22.1.9.5 Connecting Without a Predefined DSN
Yes. You can connect to the MySQL server using SQLDriverConnect, by
specifying the DRIVER name field. Here are the connection strings
for MyODBC using DSN-Less connection:
For MyODBC 2.50:
ConnectionString = "DRIVER={MySQL};\
SERVER=localhost;\
DATABASE=test;\
USER=venu;\
PASSWORD=venu;\
OPTION=3;"
For MyODBC 3.51:
ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};\
SERVER=localhost;\
DATABASE=test;\
USER=venu;\
PASSWORD=venu;\
OPTION=3;"
If your programming language converts backslash followed by whitespace to a space, it is preferable to specify the connection string as a single long string, or to use a concatenation of multiple strings that does not add spaces in between. For example:
ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};"
"SERVER=localhost;"
"DATABASE=test;"
"USER=venu;"
"PASSWORD=venu;"
"OPTION=3;"
Refer to the section 22.1.9.4 Connection Parameters, for the list of connection parameters that can be supplied.
22.1.9.6 Establishing a Remote Connection to System A from System B
If you want to connect to system A from system B with a username
and password of myuser and mypassword, here is a simple
procedure.
On system A, follow these steps:
- Start the MySQL server.
-
Use
GRANTto set up an account with a username ofmyuserthat can connect from system B using a password ofmyuser:GRANT ALL ON *.* to 'myuser'@'B' IDENTIFIED BY 'mypassword';
-
The
GRANTstatement grants all privileges to user `myuser' for connecting from system B using the passwordmypassword. To execute this statement, you should be eitherrooton system A (or another user who has appropriate privileges). For more information about MySQL privileges, refer to section 5.6 MySQL User Account Management.
On system B, follow these steps:
-
Configure a MyODBC DSN using the following connection parameters:
DSN = remote_test SERVER or HOST = A (or IP address of system A) DATABASE = test (The default database or an appropriate one) USER = myuser PASSWORD = mypassword
To set up a DSN-less connection, refer to section 22.1.9.5 Connecting Without a Predefined DSN. - Check whether you are able to access system A from system B by using ping or other means. If you are not able to reach system A, check your network or Internet connections or contact your system administrator.
-
Now, try to connect using
DSN=remote_test. If it fails, trace the MyODBC log, and take the further steps based on the error message from the log. If you need further assistance, send a detailed mail message to myodbc@lists.mysql.com.
You can also find a simple HOWTO at http://www.phphelp.com/tutorial/using-myodbc-to-connect-to-a-remote-database.html.
22.1.9.7 Getting an ODBC Trace File
If you encounter difficulties or problems with MyODBC, you should start
by making a log file from the ODBC Manager (the log you get when
requesting logs from ODBC ADMIN) and MyODBC.
To get an ODBC trace through Driver Manager, do the following:
-
Open ODBC Data source administrator:
-
Click
Start, point toSettings, and then clickControl Panel. -
On computers running Microsoft Windows 2000, XP, or 2003, double-click
Administrative Tools, and then double-clickData Sources (ODBC), as shown below.
On computers running an earlier version of Microsoft Windows, double-click32-bit ODBCorODBCin the Control Panel. -
The
ODBC Data Source Administratordialog box appears, as shown below:
- Click Help for detailed information about each tab of the ODBC Data Source Administrator dialog box.
-
Click
-
Enable the trace option.
The procedure for this differs for Windows and Unix.
To enable the trace option on Windows:
-
The
Tracingtab of the ODBC Data Source Administrator dialog box enables you to configure the way ODBC function calls are traced. -
When you activate tracing from the
Tracingtab, theDriver Managerwill log all ODBC function calls for all subsequently run applications. - ODBC function calls from applications running before tracing is activated are not logged. ODBC function calls are recorded in a log file you specify.
-
Tracing ceases only after you click
Stop Tracing Now. Remember that while tracing is on, the log file continues to increase in size and that tracing affects the performance of all your ODBC applications.
-
On Unix, you need to explicitly set the
Traceoption in the `ODBC.INI' file. Set the tracingONorOFFby usingTraceFileandTraceparameters in `odbc.ini' as shown below:TraceFile = /tmp/odbc.trace Trace = 1
TraceFilespecifies the name and full path of the trace file andTraceis set toONorOFF. You can also use1orYESforONand0orNOforOFF. If you are usingODBCConfigfromunixODBC, then follow the instructions for tracingunixODBCcalls at HOWTO-ODBCConfig.
- Ensure that you are using the driver debug DLL (that is, `myodbc3d.dll' and not `myodbc3.dll' for MyODBC 3.51, and `myodbcd.dll' for MyODBC 2.50). The easiest way to do this is to get `myodbc3d.dll' (or `myodbcd.dll') from the MyODBC 3.51 distribution and copy it over the `myodbc3.dll' (or `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 `myodbc3d.dll' (or `myodbcd.dll'), so do keep a backup copy of original DLLs.
-
Enable the
Trace MyODBCoption 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 above). On Linux or if you are using DSN-Less connection, then you need to supplyOPTION=4in the connection string. -
Start your application and try to get it to fail. Then check the MyODBC
trace file to find out what could be wrong.
If you find out something is wrong, please send a mail message to
myodbc@lists.mysql.com (or to support@mysql.com
if you have a support contract from MySQL AB) with a brief description of
the problem, with the following additional information:
- MyODBC version
- ODBC Driver Manager type and version
- MySQL server version
- ODBC trace from Driver Manager
- MyODBC log file from MyODBC driver
- Simple reproducible sample
-
The
22.1.9.8 Applications Tested with MyODBC
MyODBC has been tested with the following applications:
- MS Access 95, 97, 2000, and 2002
- C++-Builder, Borland Builder 4
- Centura Team Developer (formerly Gupta SQL/Windows)
- ColdFusion (on Solaris and NT with service pack 5), How-to: MySQL and Coldfusion. Troubleshooting Data Sources and Database Connectivity for UnixPlatforms.
- Crystal Reports
- DataJunction
- Delphi
- ERwin
- MS Excel
- iHTML
- FileMaker Pro
- FoxPro
- Notes 4.5/4.6
- MS Visio Enterprise 2000
- Vision
- Visual Objects
- Visual Interdev
- SBSS
- Perl DBD-ODBC
- Paradox
- Powerbuilder
- Powerdesigner 32-bit
- MS Visual C++
- Visual Basic
- ODBC.NET through CSharp(C#), VB and C++
- Data Architect(http://thekompany.com/products/dataarchitect/)
- SQLExpress for Xbase++(http://www.SQLExpress.net)
- Open Office (http://www.openoffice.org) How-to: MySQL + OpenOffice. How-to: OpenOffice + MyODBC + unixODBC.
- Star Office (http://wwws.sun.com/software/star/staroffice/6.0/index.html)
- G2-ODBC bridge (http://www.gensym.com)
- Sambar Server (http://www.sambarserver.info) How-to: MyODBC + SambarServer + MySQL.
If you know of any other applications that work with MyODBC, please send mail to myodbc@lists.mysql.com about them.
22.1.9.9 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. 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 http://www.microsoft.com/data/. This will fix 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 http://support.microsoft.com/default.aspx?scid=kb;EN-US;q239114. This will fix some cases where columns are marked as#DELETED#in Access. Note: If you are using MySQL 3.22, you must to 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 rowsoption. For Access 2.0, you should additionally enable theSimulate ODBC 1.0option. -
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, notTIMESTAMP(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
DOUBLEfloat 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
BIGINTcolumn, the results will be displayed as#DELETED. The work around solution is:-
Have one more dummy column with
TIMESTAMPas the data type. -
Select the
Change BIGINT columns to INToption in the connection dialog in ODBC DSN Administrator. - Delete the table link from Access and re-create it.
#DELETED#, but newly added/updated records will be displayed properly. -
Have one more dummy column with
-
If you still get the error
Another user has changed your dataafter adding aTIMESTAMPcolumn, the following trick may help you: Don't use atabledata sheet view. Instead, create a form with the fields you want, and use thatformdata sheet view. You should set theDefaultValueproperty for theTIMESTAMPcolumn toNOW(). It may be a good idea to hide theTIMESTAMPcolumn 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 will report
BLOBcolumns asOLE OBJECTS. If you want to haveMEMOcolumns instead, you should changeBLOBcolumns toTEXTwithALTER TABLE. -
Access can't always handle
DATEcolumns properly. If you have a problem with these, change the columns toDATETIME. -
If you have in Access a column defined as
BYTE, Access will try to export this asTINYINTinstead ofTINYINT UNSIGNED. This will give you problems if you have values larger than 127 in the column.
-
If you are using Access 2000, you should get and install the newest (version
2.6 or higher) Microsoft MDAC (
- 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 PropertyasadUseServerwill return a result of -1 for theRecordCount Property. To have the right value, you need to set this property toadUseClient, 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 aSELECT COUNT(*)statement for a similar query to get the correct row count. - Active server pages (ASP)
-
You should select the
Return matching rowsoption. - BDE applications
-
To get these to work, you should select the
Don't optimize column widthsandReturn matching rowsoptions. - Borland Builder 4
-
When you start a query, you can use the
Activeproperty or theOpenmethod. Note thatActivewill start by automatically issuing aSELECT * 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 http://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 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
VARCHARrather thanENUM, 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 ofCONCAT()in this example is to fool ODBC into thinking the column is of ``string type.'' Without theCONCAT(), 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.
-
If you have problems with dates, try to select them as strings using the
- 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
mysqlclient 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
Databasetool bar, press theInsert Databasebutton. -
Press the
Get Databutton. -
At the right hand of the
Get Datascreen, press theMs Querybutton. -
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 Dataand select the records. -
Click
OKand you see the rows in your Word document.
-
Insert rows using the
- odbcadmin
- Test program for ODBC.
- Delphi
-
You must use BDE Version 3.2 or newer. Select the
Don't optimize column widthoption 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 recognize primary keys, only the index named
PRIMARY, though this has not been a problem. - Vision
-
You should select the
Return matching rowsoption. - 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 PROCESSLISTwill not work properly. The fix is to useOPTION=16384in the ODBC connect string or to select theChange BIGINT columns to INToption in the MyODBC connect screen. You may also want to select theReturn matching rowsoption. - VisualInterDev
-
If you have a
BIGINTin your result, you may get the error[Microsoft][ODBC Driver Manager] Driver does not support this parameterTry selecting theChange BIGINT columns to INToption in the MyODBC connect screen. - Visual Objects
-
You should select the
Don't optimize column widthsoption. - 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.
22.1.10 MyODBC Connection-Related Issues
This section answers MyODBC connection-related questions.
22.1.10.1 While Configuring a MyODBC DSN, a Could Not Load Translator or Setup Library Error Occurs
For more information, refer to MS KnowledgeBase Article(Q260558). Also, make sure you have the latest valid `ctl3d32.dll' in your system directory.
22.1.10.2 While Connecting, an Access denied Error Occurs
Refer to section 5.5.8 Causes of Access denied Errors.
22.1.10.3 INFO: About ODBC Connection Pooling
Refer to this document about connection pooling: http://support.microsoft.com/default.aspx?scid=kb;EN-US;q169470.
22.1.11 MyODBC and Microsoft Access
This section of the document answers questions related to MyODBC with Microsoft Access.
22.1.11.1 How to Set Up Microsoft Access to Work with MySQL using MyODBC?
The following must be done on your client PC in order to make Microsoft Access work with MyODBC.
-
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 http://www.microsoft.com/data/. This will fix 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 http://support.microsoft.com/default.aspx?scid=kb;EN-US;q239114. This will fix some cases where columns are marked as#DELETED#in Access. Note: If you are using MySQL 3.22, you must to apply the MDAC patch and use MyODBC 2.50.32 or 2.50.34 and up to work around this problem. - Install the latest version of MySQL from http://dev.mysql.com/downloads/.
- Install the latest version of MyODBC 3.51 or 2.50 from http://dev.mysql.com/downloads/connector/odbc/.
-
For all Access versions, you should enable the
Return matching rowsoption. - Now start working with Access as the front-end for MySQL Server through MyODBC.
22.1.11.2 How to Export a Table or Query from Access to MySQL?
You cannot export a table or query to MySQL unless you have installed MyODBC.
To export a table from Access to MySQL, follow these instructions:
-
When you open an Access database or an Access project, a Database window
will appear. It displays shortcuts for creating new database objects and
opening existing objects.
-
Click the name of the
tableorqueryyou want to export, and then in theFilemenu, selectExport. -
In the
Export Object Type Object name Todialog box, in theSave As Typebox, selectODBC Databases ()as shown here:
-
In the
Exportdialog box, enter a name for the file (or use the suggested name), and then selectOK. - The Select Data Source dialog box is displayed; it lists the defined data sources for any ODBC drivers installed on your computer. Click either the File Data Source or Machine Data Source tab, and then double-click the MyODBC or MyODBC 3.51 data source that you want to export to. To define a new data source for MyODBC, please section 22.1.9.2 Configuring a MyODBC DSN on Windows.
Microsoft Access connects to the MySQL Server through this data source and exports new tables and or data.
22.1.11.3 How to Import or Link MySQL Database Tables to Access?
You cannot export a table or query to MySQL database unless you have installed the MyODBC.
To import or link a table(s) from MySQL to Access, follow the instructions:
- Open a database, or switch to the Database window for the open database.
-
To import tables, on the
Filemenu, point toGet External Data, and then clickImport. To link tables, on the File menu, point toGet External Data, and then clickLink Tables. -
In the
Import(orLink) dialog box, in the Files Of Type box, selectODBC Databases (). The Select Data Source dialog box lists the defined data sources The Select Data Source dialog box is displayed; it lists the defined data sources for any ODBC drivers installed on your computer. Click either the File Data Source or Machine Data Source tab, and then double-click the MyODBC or MyODBC 3.51 data source that you want to export to. To define a new data source for the MyODBC or MyODBC 3.51 driver, please section 22.1.9.2 Configuring a MyODBC DSN on Windows. -
If the ODBC data source that you selected requires you to log on, enter your login
ID and password (additional information might also be required), and then click
OK. -
Microsoft Access connects to the MySQL server through
ODBC data sourceand displays the list of tables that you canimportorlink. -
Click each table that you want to
importorlink, and then clickOK. If you're linking a table and it doesn't have an index that uniquely identifies each record, then Microsoft Access displays a list of the fields in the linked table. Click a field or a combination of fields that will uniquely identify each record, and then clickOK.
22.1.11.4 The Structure or Location of a Linked Table has been Changed. Can I See Those Changes Locally in Linked Tables?
Yes. Use the following procedure to view or to refresh links when the structure or location of a linked table has changed. The Linked Table Manager lists the paths to all currently linked tables.
To wiew or refresh links:
- Open the database that contains links to tables.
-
On the
Toolsmenu, point toAdd-ins, and then clickLinked Table Manager. - Select the check box for the tables whose links you want to refresh.
- Click OK to refresh the links.
Microsoft Access confirms a successful refresh or, if the table wasn't
found, displays the Select New Location of <table name> dialog box
in which you can specify its the table's new location.If several selected
tables have moved to the new location that you specify, the Linked Table
Manager searches that location for all selected tables, and updates all
links in one step.
To vhange the path for a set of linked tables:
- Open the database that contains links to tables.
-
On the
Toolsmenu, point toAdd-ins, and then clickLinked Table Manager. -
Select the
Always Prompt For A New Locationcheck box. -
Select the check box for the tables whose links you want to change, and then click
OK. -
In the
Select New Location of<table name> dialog box, specify the new location, clickOpen, and then clickOK.
22.1.11.5 When I Insert or Update a Record in Linked Tables, I Get #DELETED#
If the inserted or updated records are shown as #DELETED# in the access, then:
-
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 http://www.microsoft.com/data/. This will fix 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 http://support.microsoft.com/default.aspx?scid=kb;EN-US;q239114. This will fix some cases where columns are marked as#DELETED#in Access. Note: If you are using MySQL 3.22, you must to 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 rowsoption. For Access 2.0, you should additionally enable theSimulate ODBC 1.0option. -
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, notTIMESTAMP(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
DOUBLEfloat 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
BIGINTcolumn, the results will be displayed as#DELETED. The work around solution is:-
Have one more dummy column with
TIMESTAMPas the data type. -
Select the
Change BIGINT columns to INToption in the connection dialog in ODBC DSN Administrator. - Delete the table link from Access and re-create it.
#DELETED#, but newly added/updated records will be displayed properly. -
Have one more dummy column with
22.1.11.6 How Do I Handle Write Conflicts or Row Location Errors?
If you see the following errors,
select the Return Matching Rows option in the DSN configuration
dialog, or specify OPTION=2, as the connection parameter:
Write Conflict. Another user has changed your data. Row cannot be located for updating. Some values may have been changed since it was last read.
22.1.11.7 Whenever I Export a Table from Access 97, a Strange Syntax Error Occurs
This is a strange issue from Access 97, and doesn't appear with Access 2000 or 2002. You can overcome this by upgrading the MyODBC driver to at least MyODBC 3.51.02.
22.1.11.8 Access Returns Another user has modified the record that you have modified While Editing Records
With some programs, this error may occur:
Another user has modified 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 these strategies don't help, 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 help you figure out why things go wrong. For instructions, see section 22.1.9.7 Getting an ODBC Trace File.
22.1.11.9 How to Trap ODBC Login Error Messages in Access?
Read ``How to Trap ODBC Login Error Messages in Access'' at http://support.microsoft.com/support/kb/articles/Q124/9/01.asp?LN=EN-US&SD=gn&FR=0%3CP%3E.
22.1.11.10 How Do I Optimize Access for Performance with MyODBC?
- Optimizing for Client/Server Performance
- Tips for Converting Applications to Using ODBCDirect
- Tips for Optimizing Queries on Attached SQL Tables
22.1.11.11 I Have Very Long Tables. What is the Best Configuration for MyODBC to Access These Tables?
If you have very large (long) tables in Access, it might take a very long
time to open them. Or you might run low on virtual memory and eventually get
an ODBC Query Failed error and the table will not open. To deal with
this, select the following options:
- Return Matching Rows (2)
- Allow BIG Results (8).
These add up to a value of 10 (OPTION=10).
22.1.11.12 How to Set the QueryTimeout Value for ODBC Connections?
Read ``Set the QueryTimeout Value for ODBC Connections'' at http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B153756.
22.1.11.13 INFO: Tools to Export/Import from/to Access to/from MySQL
Refer to converters section for list of available tools.
22.1.12 MyODBC and Microsoft VBA and ASP
This section answers questions related to using MyODBC with Microsoft Visual Basic(ADO, DAO & RDO) and ASP.
22.1.12.1 Why Does SELECT COUNT(*) FROM tbl_name Return an Error?
It's because the COUNT(*) expression is returning a BIGINT,
and ADO can't make sense of a number this big. Select the Change
BIGINT columns to INT option (option value 16384).
22.1.12.2 Whenever I Use the AppendChunk() or GetChunk() ADO Methods, I Get an Error Multiple-step operation generated errors. Check each status value.
The GetChunk() and AppendChunk() methods from ADO doesn't work as
expected when the cursor location is specified as adUseServer. On the other
hand, you can overcome this error by using adUseClient.
A simple example can be found from, http://www.dwam.net/iishelp/ado/docs/adomth02_4.htm
22.1.12.3 How to Find the Total Number of Rows Affected by a Particular SQL Statement in ADO?
You can make use of RecordsAffected property in the ADO execute method. For more
information on the usage of execute method, refer to
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthcnnexecute.asp.
22.1.12.4 How Do I Handle Blob Data in Visual Basic?
Here is an excellent article from Mike Hillyer (m.hillyer@telusplanet.net); explaining how to insert and/or fetch data from blob columns through MyODBC from ADO: MySQL BLOB columns and Visual Basic 6.
22.1.12.5 How Do I Map Visual Basic Data Types to MySQL Types?
Here is yet another good article from Mike Hillyer (m.hillyer@telusplanet.net): How to map Visual basic data type to MySQL types.
22.1.12.6 SAMPLES: VB with ADO, DAO and RDO
A simple examples for the usage of ADO, DAO and RDO with VB can be found her:
- ADO sample: section 22.1.19 MyODBC With VB: ADO, DAO and RDO
- DAO sample: section 22.1.19 MyODBC With VB: ADO, DAO and RDO
- RDO sample: section 22.1.19 MyODBC With VB: ADO, DAO and RDO
If you find any other good example or HOW-TO on ADO/DAO/RDO, then please send the details to myodbc@lists.mysql.com
22.1.12.7 ASP and MySQL with MyODBC
For more information about how to access MySQL via ASP using MyODBC, refer to the following articles:
A Frequently Asked Questions list for ASP can be found at http://support.microsoft.com/default.aspx?scid=/Support/ActiveServer/faq/data/adofaq.asp.
22.1.12.8 INFO: Frequently Asked Questions on ActiveX Data Objects (ADO)
For information, see ActiveX Data Objects(ADO) Freqently Asked Questions.
22.1.13 MyODBC and Third-Party ODBC Tools
This section answers questions related to MyODBC with various ODBC-related tools; such as Microsoft Word, Excel and ColdFusion.
22.1.13.1 How to Retrieve Data from MySQL into MS-Word/Excel Documents?
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
mysqlclient 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
Databasetool bar, press theInsert Databasebutton. -
Press the
Get Databutton. -
At the right hand of the
Get Datascreen, press theMs Querybutton. -
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 Dataand select the records. -
Click
OKand you see the rows in your Word document.
22.1.13.2 Exporting Tables from MS DTS to MySQL Using MyODBC Results in a Syntax Error
This is an issue similar to that of Access 97 when your table consists
of TEXT or VARCHAR data types. You can fix this error by
upgrading your MyODBC driver to version 3.51.02 or higher.
22.1.13.3 HOWTO: Configure MySQL+MyODBC+unixODBC+ColdFusion on Solaris
Refer to MySQL ColdFusion unixODBC MyODBC and Solaris - how to succeed
22.1.14 MyODBC General Functionality
This section of the document answers questions related to MyODBC general functionality.
22.1.14.1 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 statement. With ODBC, you can do something like this (assuming
that auto is an AUTO_INCREMENT field):
INSERT INTO tbl (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 tbl (auto,text) VALUES(NULL,'text'); INSERT INTO tbl2 (id,text) VALUES(LAST_INSERT_ID(),'text');
See section 21.2.13.3 How to Get the Unique ID for the Last Inserted Row.
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 WHERE auto IS NULL;
22.1.14.2 Does MyODBC Support Dynamic Cursor Type?
Yes. MyODBC 3.51 supports Dynamic cursor type along with
Forward-only and static.
Due to the performance issues, the driver does not support this feature by
default. You can enable this by specifying the connection option flag as
OPTION=32 or by checking the Enable Dynamic Cursor option
from the DSN configuration.
22.1.14.3 What Causes Transactions are not enabled Errors?
The driver returns this error when an application issues any transactional call but the underlying MySQL server either does not support transactions or they are not enabled.
To avoid this problem, you must use a server that has either or both of the
InnoDB or BDB storage engines enabled, and use tables of those
types. MySQL servers from version 4.0 and up support InnoDB by
default. MySQL-Max servers also support BDB on platforms where
BDB is available.
Also, if your server supports transactional table types (InnoDB
and BDB)
make sure the disable transactions option is not set from the DSN
configuration.
22.1.14.4 What Causes Cursor not found Errors?
This is becuase the application is using old MyODBC 2.50 version, and it did not set the cursor name explicitly through SQLSetCursorName. The fix is to upgrade to MyODBC 3.51 version.
22.1.14.5 Can I Use MyODBC 2.50 Applications with MyODBC 3.51?
Yes. If you find something is not working with MyODBC 3.51 that works with MyODBC 2.50, then send a mail message to myodbc@lists.mysql.com
22.1.14.6 Can I Access MySQL from .NET Environment Using MyODBC?
Yes. You can make use of odbc.net to connect to MySQL through MyODBC. Here are the few basic samples to connect to MySQL from VC.NET and VB.NET.
- See section 22.1.20.1 ODBC.NET: CSHARP(C#)
- See section 22.1.20.2 ODBC.NET: VB
Here is yet another excellent article "Exploring MySQL on .NET environment" by Venu (MyODBC developer) that covers about all MySQL .NET interfaces along with some useful examples.
Caution: Using ODBC.NET with MyODBC, while fetching empty string (0 length), it starts giving the SQL_NO_DATA exception. You can get the patch for this from http://support.microsoft.com/default.aspx?scid=kb;EN-US;q319243.
22.1.14.7 Why Does MyODBC Perform Poorly, and Also Make a Lot of Disk Activity for Relatively Small Queries?
MyODBC is a lot faster than any other ODBC driver. Slowness might be due to not using the following options.
-
The ODBC Tracing option is turned on. You can cross-check whether
this option is not turned on by following the instructions from
here.
As shown in the above image, the 'When to trace' option from the ODBC Data Source Administrator 'Tracing' tab should always point to 'Start Tracing Now', instead of 'Stop Tracing Now'. -
The Debug version of the driver is used. If you are using the
debug version of the driver DLL, it can also relatively slow down the
query processing time. You can cross-check whether you are using the
debug or release version of the DLL from the 'Comments' section
of the driver DLL properties (from the sytem directory, right click
on the driver DLL and click on properties) as shown below:
-
The Driver trace and query logs are enabled. Even if you intent
to use the debug version of the driver (you should always use the release
version in the production environment), make sure the driver trace and
query log options(OPTION=4,524288 respectively) are not enabled as
shown below:
22.1.15 Basic MyODBC Application Steps
Interacting with a MySQL server from MyODBC applications involves the following operations:
- Configure the MyODBC DSN
- Connect to MySQL server
- Initialization operations
- Execute SQL statements
- Retrieve results
- Perform Transactions
- Disconnect from the server
Most applications use some variation of these steps. The basic application steps are shown in the following diagram:
22.1.16 MyODBC API Reference
This section summarizes ODBC routines, categorized by functionality.
For the complete ODBC API reference, please refer to the ODBC Programer's Reference at http://msdn.microsoft.com/library/en-us/odbc/htm/odbcabout_this_manual.asp.
An application can call SQLGetInfo function to obtain conformance
information about MyODBC. To obtain information about support for a specific
function in the driver, an application can call SQLGetFunctions.
Note: For backward compatibility, the MyODBC 3.51 driver supports all deprecated functions.
The following tables list MyODBC API calls grouped by task:
Connecting to a data source:
| Function name | MyODBC | MyODBC | Conformance | Purpose |
| 2.50 | 3.51 | |||
SQLAllocHandle | No | Yes | ISO 92 | Obtains an environment, connection, statement, or descriptor handle. |
SQLConnect | Yes | Yes | ISO 92 | Connects to a specific driver by data source name, user ID, and password. |
SQLDriverConnect | Yes | Yes | ODBC | Connects to a specific driver by connection string or requests that the Driver Manager and driver display connection dialog boxes for the user. |
SQLAllocEnv | Yes | Yes | Deprecated | Obtains an environment handle allocated from driver. |
SQLAllocConnect | Yes | Yes | Deprecated | Obtains a connection handle |
Obtaining information about a driver and data source:
| Function name | MyODBC | MyODBC | Conformance | Purpose |
| 2.50 | 3.51 | |||
SQLDataSources | No | No | ISO 92 | Returns the list of available data sources, handled by the Driver Manager |
SQLDrivers | No | No | ODBC | Returns the list of installed drivers and their attributes, handles by Driver Manager |
SQLGetInfo | Yes | Yes | ISO 92 | Returns information about a specific driver and data source. |
SQLGetFunctions | Yes | Yes | ISO 92 | Returns supported driver functions. |
SQLGetTypeInfo | Yes | Yes | ISO 92 | Returns information about supported data types. |
Setting and retrieving driver attributes:
| Function name | MyODBC | MyODBC | Conformance | Purpose |
| 2.50 | 3.51 | |||
SQLSetConnectAttr | No | Yes | ISO 92 | Sets a connection attribute. |
SQLGetConnectAttr | No | Yes | ISO 92 | Returns the value of a connection attribute. |
SQLSetConnectOption | Yes | Yes | Deprecated | Sets a connection option |
SQLGetConnectOption | Yes | Yes | Deprecated | Returns the value of a connection option |
SQLSetEnvAttr | No | Yes | ISO 92 | Sets an environment attribute. |
SQLGetEnvAttr | No | Yes | ISO 92 | Returns the value of an environment attribute. |
SQLSetStmtAttr | No | Yes | ISO 92 | Sets a statement attribute. |
SQLGetStmtAttr | No | Yes | ISO 92 | Returns the value of a statement attribute. |
SQLSetStmtOption | Yes | Yes | Deprecated | Sets a statement option |
SQLGetStmtOption | Yes | Yes | Deprecated | Returns the value of a statement option |
Preparing SQL requests:
| Function name | MyODBC | MyODBC | Conformance | Purpose |
| 2.50 | 3.51 | |||
SQLAllocStmt | Yes | Yes | Deprecated | Allocates a statement handle |
SQLPrepare | Yes | Yes | ISO 92 | Prepares an SQL statement for later execution. |
SQLBindParameter | Yes | Yes | ODBC | Assigns storage for a parameter in an SQL statement. |
SQLGetCursorName | Yes | Yes | ISO 92 | Returns the cursor name associated with a statement handle. |
SQLSetCursorName | Yes | Yes | ISO 92 | Specifies a cursor name. |
SQLSetScrollOptions | Yes | Yes | ODBC | Sets options that control cursor behavior. |
Submitting requests:
| Function name | MyODBC | MyODBC | Conformance | Purpose |
| 2.50 | 3.51 | |||
SQLExecute | Yes | Yes | ISO 92 | Executes a prepared statement. |
SQLExecDirect | Yes | Yes | ISO 92 | Executes a statement |
SQLNativeSql | Yes | Yes | ODBC | Returns the text of an SQL statement as translated by the driver. |
SQLDescribeParam | Yes | Yes | ODBC | Returns the description for a specific parameter in a statement. |
SQLNumParams | Yes | Yes | ISO 92 | Returns the number of parameters in a statement. |
SQLParamData | Yes | Yes | ISO 92 | Used in conjunction with SQLPutData to supply parameter data at execution time. (Useful for long data values.)
|
SQLPutData | Yes | Yes | ISO 92 | Sends part or all of a data value for a parameter. (Useful for long data values.) |
Retrieving results and information about results:
| Function name | MyODBC | MyODBC | Conformance | Purpose |
| 2.50 | 3.51 | |||
SQLRowCount | Yes | Yes | ISO 92 | Returns the number of rows affected by an insert, update, or delete request. |
SQLNumResultCols | Yes | Yes | ISO 92 | Returns the number of columns in the result set. |
SQLDescribeCol | Yes | Yes | ISO 92 | Describes a column in the result set. |
SQLColAttribute | No | Yes | ISO 92 | Describes attributes of a column in the result set. |
SQLColAttributes | Yes | Yes | Deprecated | Describes attributes of a column in the result set. |
SQLFetch | Yes | Yes | ISO 92 | Returns multiple result rows. |
SQLFetchScroll | No | Yes | ISO 92 | Returns scrollable result rows. |
SQLExtendedFetch | Yes | Yes | Deprecated | Returns scrollable result rows. |
SQLSetPos | Yes | Yes | ODBC | Positions a cursor within a fetched block of data and allows an application to refresh data in the rowset or to update or delete data in the result set. |
SQLBulkOperations | No | Yes | ODBC | Performs bulk insertions and bulk bookmark operations, including update, delete, and fetch by bookmark. |
Retrieving error or diagnostic information:
| Function name | MyODBC | MyODBC | Conformance | Purpose |
| 2.50 | 3.51 | |||
SQLError | Yes | Yes | Deprecated | Returns additional error or status information |
SQLGetDiagField | Yes | Yes | ISO 92 | Returns additional diagnostic information (a single field of the diagnostic data structure). |
SQLGetDiagRec | Yes | Yes | ISO 92 | Returns additional diagnostic information (multiple fields of the diagnostic data structure). |
Obtaining information about the data source's system tables (catalog functions) item:
| Function name | MyODBC | MyODBC | Conformance | Purpose |
| 2.50 | 3.51 | |||
SQLColumnPrivileges | Yes | Yes | ODBC | Returns a list of columns and associated privileges for one or more tables. |
SQLColumns | Yes | Yes | X/Open | Returns the list of column names in specified tables. |
SQLForeignKeys | Yes | Yes | ODBC | Returns a list of column names that make up foreign keys, if they exist for a specified table. |
SQLPrimaryKeys | Yes | Yes | ODBC | Returns the list of column names that make up the primary key for a table. |
SQLSpecialColumns | Yes | Yes | X/Open | Returns information about the optimal set of columns that uniquely identifies a row in a specified table, or the columns that are automatically updated when any value in the row is updated by a transaction. |
SQLStatistics | Yes | Yes | ISO 92 | Returns statistics about a single table and the list of indexes associated with the table. |
SQLTablePrivileges | Yes | Yes | ODBC | Returns a list of tables and the privileges associated with each table. |
SQLTables | Yes | Yes | X/Open | Returns the list of table names stored in a specific data source. |
Performing transactions:
| Function name | MyODBC | MyODBC | Conformance | Purpose |
| 2.50 | 3.51 | |||
SQLTransact | Yes | Yes | Deprecated | Commits or rolls back a transaction |
SQLEndTran | No | Yes | ISO 92 | Commits or rolls back a transaction. |
Terminating a statement:
| Function name | MyODBC | MyODBC | Conformance | Purpose |
| 2.50 | 3.51 | |||
SQLFreeStmt | Yes | Yes | ISO 92 | Ends statement processing, discards pending results, and, optionally, frees all resources associated with the statement handle. |
SQLCloseCursor | Yes | Yes | ISO 92 | Closes a cursor that has been opened on a statement handle. |
SQLCancel | Yes | Yes | ISO 92 | Cancels an SQL statement. |
Terminating a connection:
| Function name | MyODBC | MyODBC | Conformance | Purpose |
| 2.50 | 3.51 | |||
SQLDisconnect | Yes | Yes | ISO 92 | Closes the connection. |
SQLFreeHandle | No | Yes | ISO 92 | Releases an environment, connection, statement, or descriptor handle. |
SQLFreeConnect | Yes | Yes | Deprecated | Releases connection handle |
SQLFreeEnv | Yes | Yes | Deprecated | Releases an environment handle |
22.1.17 MyODBC Data Types
The following table illustrates how driver maps the server data types to default SQL and C data types:
| Native Value | SQL Type | C Type |
bit | SQL_BIT | SQL_C_BIT
|
tinyint | SQL_TINYINT | SQL_C_STINYINT
|
tinyint unsigned | SQL_TINYINT | SQL_C_UTINYINT
|
bigint | SQL_BIGINT | SQL_C_SBIGINT
|
bigint unsigned | SQL_BIGINT | SQL_C_UBIGINT
|
long varbinary | SQL_LONGVARBINARY | SQL_C_BINARY
|
blob | SQL_VARBINARY | SQL_C_BINARY
|
longblob | SQL_VARBINARY | SQL_C_BINARY
|
tinyblob | SQL_BINARY | SQL_C_BINARY
|
mediumblob | SQL_LONGVARBINARY | SQL_C_BINARY
|
long varchar | SQL_LONGVARCHAR | SQL_C_CHAR
|
text | SQL_LONGVARCHAR | SQL_C_CHAR
|
mediumtext | SQL_LONGVARCHAR | SQL_C_CHAR
|
char | SQL_CHAR | SQL_C_CHAR
|
numeric | SQL_NUMERIC | SQL_C_CHAR
|
decimal | SQL_DECIMAL | SQL_C_CHAR
|
integer | SQL_INTEGER | SQL_C_SLONG
|
integer unsigned | SQL_INTEGER | SQL_C_ULONG
|
int | SQL_INTEGER | SQL_C_SLONG
|
int unsigned | SQL_INTEGER | SQL_C_ULONG
|
mediumint | SQL_INTEGER | SQL_C_SLONG
|
mediumint unsigned | SQL_INTEGER | SQL_C_ULONG
|
smallint | SQL_SMALLINT | SQL_C_SSHORT
|
smallint unsigned | SQL_SMALLINT | SQL_C_USHORT
|
real | SQL_FLOAT | SQL_C_DOUBLE
|
double | SQL_FLOAT | SQL_C_DOUBLE
|
float | SQL_REAL | SQL_C_FLOAT
|
double precision | SQL_DOUBLE | SQL_C_DOUBLE
|
date | SQL_DATE | SQL_C_DATE
|
time | SQL_TIME | SQL_C_TIME
|
year | SQL_SMALLINT | SQL_C_SHORT
|
datetime | SQL_TIMESTAMP | SQL_C_TIMESTAMP
|
timestamp | SQL_TIMESTAMP | SQL_C_TIMESTAMP
|
text | SQL_VARCHAR | SQL_C_CHAR
|
varchar | SQL_VARCHAR | SQL_C_CHAR
|
enum | SQL_VARCHAR | SQL_C_CHAR
|
set | SQL_VARCHAR | SQL_C_CHAR
|
bit | SQL_CHAR | SQL_C_CHAR
|
bool | SQL_CHAR | SQL_C_CHAR
|
22.1.18 MyODBC Error Codes
The following tables lists the error codes returned by the driver apart from the server errors.
| Native Code | SQLSTATE 2 | SQLSTATE 3 | Error Message |
| 500 | 01000 | 01000 | General warning |
| 501 | 01004 | 01004 | String data, right truncated |
| 502 | 01S02 | 01S02 | Option value changed |
| 503 | 01S03 | 01S03 | No rows updated/deleted |
| 504 | 01S04 | 01S04 | More than one row updated/deleted |
| 505 | 01S06 | 01S06 | Attempt to fetch before the result set returned the first row set |
| 506 | 07001 | 07002 | SQLBindParameter not used for all parameters
|
| 507 | 07005 | 07005 | Prepared statement not a cursor-specification |
| 508 | 07009 | 07009 | Invalid descriptor index |
| 509 | 08002 | 08002 | Connection name in use |
| 510 | 08003 | 08003 | Connection does not exist |
| 511 | 24000 | 24000 | Invalid cursor state |
| 512 | 25000 | 25000 | Invalid transaction state |
| 513 | 25S01 | 25S01 | Transaction state unknown |
| 514 | 34000 | 34000 | Invalid cursor name |
| 515 | S1000 | HY000 | General driver defined error |
| 516 | S1001 | HY001 | Memory allocation error |
| 517 | S1002 | HY002 | Invalid column number |
| 518 | S1003 | HY003 | Invalid application buffer type |
| 519 | S1004 | HY004 | Invalid SQL data type |
| 520 | S1009 | HY009 | Invalid use of null pointer |
| 521 | S1010 | HY010 | Function sequence error |
| 522 | S1011 | HY011 | Attribute can not be set now |
| 523 | S1012 | HY012 | Invalid transaction operation code |
| 524 | S1013 | HY013 | Memory management error |
| 525 | S1015 | HY015 | No cursor name available |
| 526 | S1024 | HY024 | Invalid attribute value |
| 527 | S1090 | HY090 | Invalid string or buffer length |
| 528 | S1091 | HY091 | Invalid descriptor field identifier |
| 529 | S1092 | HY092 | Invalid attribute/option identifier |
| 530 | S1093 | HY093 | Invalid parameter number |
| 531 | S1095 | HY095 | Function type out of range |
| 532 | S1106 | HY106 | Fetch type out of range |
| 533 | S1117 | HY117 | Row value out of range |
| 534 | S1109 | HY109 | Invalid cursor position |
| 535 | S1C00 | HYC00 | Optional feature not implemented |
| 0 | 21S01 | 21S01 | Column count does not match value count |
| 0 | 23000 | 23000 | Integrity constraint violation |
| 0 | 42000 | 42000 | Syntax error or access violation |
| 0 | 42S02 | 42S02 | Base table or view not found |
| 0 | 42S12 | 42S12 | Index not found |
| 0 | 42S21 | 42S21 | Column already exists |
| 0 | 42S22 | 42S22 | Column not found |
| 0 | 08S01 | 08S01 | Communication link failure |
22.1.19 MyODBC With VB: ADO, DAO and RDO
This section contains simple examples of the use of MySQL ODBC 3.51 Driver with ADO, DAO and RDO.
22.1.19.1 ADO: rs.addNew, rs.delete, and rs.update
The following ADO (ActiveX Data Objects) example creates a table
my_ado and demonstrates the use of rs.addNew, rs.delete,
and rs.update.
Private Sub myodbc_ado_Click()
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim fld As ADODB.Field
Dim sql As String
'connect to MySQL server using MySQL ODBC 3.51 Driver
Set conn = New ADODB.Connection
conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};"_
& "SERVER=localhost;"_
& " DATABASE=test;"_
& "UID=venu;PWD=venu; OPTION=3"
conn.Open
'create table
conn.Execute "DROP TABLE IF EXISTS my_ado"
conn.Execute "CREATE TABLE my_ado(id int not null primary key, name varchar(20)," _
& "txt text, dt date, tm time, ts timestamp)"
'direct insert
conn.Execute "INSERT INTO my_ado(id,name,txt) values(1,100,'venu')"
conn.Execute "INSERT INTO my_ado(id,name,txt) values(2,200,'MySQL')"
conn.Execute "INSERT INTO my_ado(id,name,txt) values(3,300,'Delete')"
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseServer
'fetch the initial table ..
rs.Open "SELECT * FROM my_ado", conn
Debug.Print rs.RecordCount
rs.MoveFirst
Debug.Print String(50, "-") & "Initial my_ado Result Set " & String(50, "-")
For Each fld In rs.Fields
Debug.Print fld.Name,
Next
Debug.Print
Do Until rs.EOF
For Each fld In rs.Fields
Debug.Print fld.Value,
Next
rs.MoveNext
Debug.Print
Loop
rs.Close
'rs insert
rs.Open "select * from my_ado", conn, adOpenDynamic, adLockOptimistic
rs.AddNew
rs!Name = "Monty"
rs!txt = "Insert row"
rs.Update
rs.Close
'rs update
rs.Open "SELECT * FROM my_ado"
rs!Name = "update"
rs!txt = "updated-row"
rs.Update
rs.Close
'rs update second time..
rs.Open "SELECT * FROM my_ado"
rs!Name = "update"
rs!txt = "updated-second-time"
rs.Update
rs.Close
'rs delete
rs.Open "SELECT * FROM my_ado"
rs.MoveNext
rs.MoveNext
rs.Delete
rs.Close
'fetch the updated table ..
rs.Open "SELECT * FROM my_ado", conn
Debug.Print rs.RecordCount
rs.MoveFirst
Debug.Print String(50, "-") & "Updated my_ado Result Set " & String(50, "-")
For Each fld In rs.Fields
Debug.Print fld.Name,
Next
Debug.Print
Do Until rs.EOF
For Each fld In rs.Fields
Debug.Print fld.Value,
Next
rs.MoveNext
Debug.Print
Loop
rs.Close
conn.Close
End Sub
22.1.19.2 DAO: rs.addNew, rs.update, and Scrolling
The following DAO (Data Access Objects) example creates a table my_dao
and demonstrates the use of rs.addNew, rs.update, and result
set scrolling.
Private Sub myodbc_dao_Click()
Dim ws As Workspace
Dim conn As Connection
Dim queryDef As queryDef
Dim str As String
'connect to MySQL using MySQL ODBC 3.51 Driver
Set ws = DBEngine.CreateWorkspace("", "venu", "venu", dbUseODBC)
str = "odbc;DRIVER={MySQL ODBC 3.51 Driver};"_
& "SERVER=localhost;"_
& " DATABASE=test;"_
& "UID=venu;PWD=venu; OPTION=3"
Set conn = ws.OpenConnection("test", dbDriverNoPrompt, False, str)
'Create table my_dao
Set queryDef = conn.CreateQueryDef("", "drop table if exists my_dao")
queryDef.Execute
Set queryDef = conn.CreateQueryDef("", "create table my_dao(Id INT AUTO_INCREMENT PRIMARY KEY, " _
& "Ts TIMESTAMP(14) NOT NULL, Name varchar(20), Id2 INT)")
queryDef.Execute
'Insert new records using rs.addNew
Set rs = conn.OpenRecordset("my_dao")
Dim i As Integer
For i = 10 To 15
rs.AddNew
rs!Name = "insert record" & i
rs!Id2 = i
rs.Update
Next i
rs.Close
'rs update..
Set rs = conn.OpenRecordset("my_dao")
rs.Edit
rs!Name = "updated-string"
rs.Update
rs.Close
'fetch the table back...
Set rs = conn.OpenRecordset("my_dao", dbOpenDynamic)
str = "Results:"
rs.MoveFirst
While Not rs.EOF
str = " " & rs!Id & " , " & rs!Name & ", " & rs!Ts & ", " & rs!Id2
Debug.Print "DATA:" & str
rs.MoveNext
Wend
'rs Scrolling
rs.MoveFirst
str = " FIRST ROW: " & rs!Id & " , " & rs!Name & ", " & rs!Ts & ", " & rs!Id2
Debug.Print str
rs.MoveLast
str = " LAST ROW: " & rs!Id & " , " & rs!Name & ", " & rs!Ts & ", " & rs!Id2
Debug.Print str
rs.MovePrevious
str = " LAST-1 ROW: " & rs!Id & " , " & rs!Name & ", " & rs!Ts & ", " & rs!Id2
Debug.Print str
'free all resources
rs.Close
queryDef.Close
conn.Close
ws.Close
End Sub
22.1.19.3 RDO: rs.addNew and rs.update
The following RDO (Remote Data Objects) example creates
a table my_rdo and demonstrates the use of rs.addNew
and rs.update.
Dim rs As rdoResultset
Dim cn As New rdoConnection
Dim cl As rdoColumn
Dim SQL As String
'cn.Connect = "DSN=test;"
cn.Connect = "DRIVER={MySQL ODBC 3.51 Driver};"_
& "SERVER=localhost;"_
& " DATABASE=test;"_
& "UID=venu;PWD=venu; OPTION=3"
cn.CursorDriver = rdUseOdbc
cn.EstablishConnection rdDriverPrompt
'drop table my_rdo
SQL = "drop table if exists my_rdo"
cn.Execute SQL, rdExecDirect
'create table my_rdo
SQL = "create table my_rdo(id int, name varchar(20))"
cn.Execute SQL, rdExecDirect
'insert - direct
SQL = "insert into my_rdo values (100,'venu')"
cn.Execute SQL, rdExecDirect
SQL = "insert into my_rdo values (200,'MySQL')"
cn.Execute SQL, rdExecDirect
'rs insert
SQL = "select * from my_rdo"
Set rs = cn.OpenResultset(SQL, rdOpenStatic, rdConcurRowVer, rdExecDirect)
rs.AddNew
rs!id = 300
rs!Name = "Insert1"
rs.Update
rs.Close
'rs insert
SQL = "select * from my_rdo"
Set rs = cn.OpenResultset(SQL, rdOpenStatic, rdConcurRowVer, rdExecDirect)
rs.AddNew
rs!id = 400
rs!Name = "Insert 2"
rs.Update
rs.Close
'rs update
SQL = "select * from my_rdo"
Set rs = cn.OpenResultset(SQL, rdOpenStatic, rdConcurRowVer, rdExecDirect)
rs.Edit
rs!id = 999
rs!Name = "updated"
rs.Update
rs.Close
'fetch back...
SQL = "select * from my_rdo"
Set rs = cn.OpenResultset(SQL, rdOpenStatic, rdConcurRowVer, rdExecDirect)
Do Until rs.EOF
For Each cl In rs.rdoColumns
Debug.Print cl.Value,
Next
rs.MoveNext
Debug.Print
Loop
Debug.Print "Row count="; rs.RowCount
'close
rs.Close
cn.Close
End Sub
22.1.20 MyODBC with Microsoft .NET
This section contains simple examples that demonstrate the use of MyODBC drivers with ODBC.NET.
22.1.20.1 ODBC.NET: CSHARP(C#)
The following sample creates a table my_odbc_net and
demonstrates the use in C#.
/** * @sample : mycon.cs * @purpose : Demo sample for ODBC.NET using MyODBC * @author : Venu, venu@mysql.com * * (C) Copyright MySQL AB, 1995-2004 * **/ /* build command * * csc /t:exe * /out:mycon.exe mycon.cs * /r:Microsoft.Data.Odbc.dll */ using Console = System.Console; using Microsoft.Data.Odbc; namespace myodbc3 { class mycon { static void Main(string[] args) { try { //Connection string for MyODBC 2.50 /*string MyConString = "DRIVER={MySQL};" + "SERVER=localhost;" + "DATABASE=test;" + "UID=venu;" + "PASSWORD=venu;" + "OPTION=3"; */ //Connection string for MyODBC 3.51 string MyConString = "DRIVER={MySQL ODBC 3.51 Driver};" + "SERVER=localhost;" + "DATABASE=test;" + "UID=venu;" + "PASSWORD=venu;" + "OPTION=3"; //Connect to MySQL using MyODBC OdbcConnection MyConnection = new OdbcConnection(MyConString); MyConnection.Open(); Console.WriteLine("\n !!! success, connected successfully !!!\n"); //Display connection information Console.WriteLine("Connection Information:"); Console.WriteLine("\tConnection String:" + MyConnection.ConnectionString); Console.WriteLine("\tConnection Timeout:" + MyConnection.ConnectionTimeout); Console.WriteLine("\tDatabase:" + MyConnection.Database); Console.WriteLine("\tDataSource:" + MyConnection.DataSource); Console.WriteLine("\tDriver:" + MyConnection.Driver); Console.WriteLine("\tServerVersion:" + MyConnection.ServerVersion); //Create a sample table OdbcCommand MyCommand = new OdbcCommand("DROP TABLE IF EXISTS my_odbc_net",MyConnection); MyCommand.ExecuteNonQuery(); MyCommand.CommandText = "CREATE TABLE my_odbc_net(id int, name varchar(20), idb bigint)"; MyCommand.ExecuteNonQuery(); //Insert MyCommand.CommandText = "INSERT INTO my_odbc_net VALUES(10,'venu', 300)"; Console.WriteLine("INSERT, Total rows affected:" + MyCommand.ExecuteNonQuery());; //Insert MyCommand.CommandText = "INSERT INTO my_odbc_net VALUES(20,'mysql',400)"; Console.WriteLine("INSERT, Total rows affected:" + MyCommand.ExecuteNonQuery()); //Insert MyCommand.CommandText = "INSERT INTO my_odbc_net VALUES(20,'mysql',500)"; Console.WriteLine("INSERT, Total rows affected:" + MyCommand.ExecuteNonQuery()); //Update MyCommand.CommandText = "UPDATE my_odbc_net SET id=999 WHERE id=20"; Console.WriteLine("Update, Total rows affected:" + MyCommand.ExecuteNonQuery()); //COUNT(*) MyCommand.CommandText = "SELECT COUNT(*) as TRows FROM my_odbc_net"; Console.WriteLine("Total Rows:" + MyCommand.ExecuteScalar()); //Fetch MyCommand.CommandText = "SELECT * FROM my_odbc_net"; OdbcDataReader MyDataReader; MyDataReader = MyCommand.ExecuteReader(); while (MyDataReader.Read()) { if(string.Compare(MyConnection.Driver,"myodbc3.dll") == 0) { Console.WriteLine("Data:" + MyDataReader.GetInt32(0) + " " + MyDataReader.GetString(1) + " " + MyDataReader.GetInt64(2)); //Supported only by MyODBC 3.51 } else { Console.WriteLine("Data:" + MyDataReader.GetInt32(0) + " " + MyDataReader.GetString(1) + " " + MyDataReader.GetInt32(2)); //BIGINTs not supported by MyODBC } } //Close all resources MyDataReader.Close(); MyConnection.Close(); } catch (OdbcException MyOdbcException)//Catch any ODBC exception .. { for (int i=0; i < MyOdbcException.Errors.Count; i++) { Console.Write("ERROR #" + i + "\n" + "Message: " + MyOdbcException.Errors[i].Message + "\n" + "Native: " + MyOdbcException.Errors[i].NativeError.ToString() + "\n" + "Source: " + MyOdbcException.Errors[i].Source + "\n" + "SQL: " + MyOdbcException.Errors[i].SQLState + "\n"); } } } } }
22.1.20.2 ODBC.NET: VB
The following sample creates a table my_vb_net and
demonstrates the use in VB.
' @sample : myvb.vb ' @purpose : Demo sample for ODBC.NET using MyODBC ' @author : Venu, venu@mysql.com ' ' (C) Copyright MySQL AB, 1995-2004 ' ' ' ' build command ' ' vbc /target:exe ' /out:myvb.exe ' /r:Microsoft.Data.Odbc.dll ' /r:System.dll ' /r:System.Data.dll ' Imports Microsoft.Data.Odbc Imports System Module myvb Sub Main() Try 'MyODBC 3.51 connection string Dim MyConString As String = "DRIVER={MySQL ODBC 3.51 Driver};" & _ "SERVER=localhost;" & _ "DATABASE=test;" & _ "UID=venu;" & _ "PASSWORD=venu;" & _ "OPTION=3;" 'Connection Dim MyConnection As New OdbcConnection(MyConString) MyConnection.Open() Console.WriteLine ("Connection State::" & MyConnection.State.ToString) 'Drop Console.WriteLine ("Dropping table") Dim MyCommand As New OdbcCommand() MyCommand.Connection = MyConnection MyCommand.CommandText = "DROP TABLE IF EXISTS my_vb_net" MyCommand.ExecuteNonQuery() 'Create Console.WriteLine ("Creating....") MyCommand.CommandText = "CREATE TABLE my_vb_net(id int, name varchar(30))" MyCommand.ExecuteNonQuery() 'Insert MyCommand.CommandText = "INSERT INTO my_vb_net VALUES(10,'venu')" Console.WriteLine("INSERT, Total rows affected:" & MyCommand.ExecuteNonQuery()) 'Insert MyCommand.CommandText = "INSERT INTO my_vb_net VALUES(20,'mysql')" Console.WriteLine("INSERT, Total rows affected:" & MyCommand.ExecuteNonQuery()) 'Insert MyCommand.CommandText = "INSERT INTO my_vb_net VALUES(20,'mysql')" Console.WriteLine("INSERT, Total rows affected:" & MyCommand.ExecuteNonQuery()) 'Insert MyCommand.CommandText = "INSERT INTO my_vb_net(id) VALUES(30)" Console.WriteLine("INSERT, Total rows affected:" & MyCommand.ExecuteNonQuery()) 'Update MyCommand.CommandText = "UPDATE my_vb_net SET id=999 WHERE id=20" Console.WriteLine("Update, Total rows affected:" & MyCommand.ExecuteNonQuery()) 'COUNT(*) MyCommand.CommandText = "SELECT COUNT(*) as TRows FROM my_vb_net" Console.WriteLine("Total Rows:" & MyCommand.ExecuteScalar()) 'Select Console.WriteLine ("Select * FROM my_vb_net") MyCommand.CommandText = "SELECT * FROM my_vb_net" Dim MyDataReader As OdbcDataReader MyDataReader = MyCommand.ExecuteReader While MyDataReader.Read If MyDataReader("name") Is DBNull.Value Then Console.WriteLine ("id = " & CStr(MyDataReader("id")) & " name = " & _ "NULL") Else Console.WriteLine ("id = " & CStr(MyDataReader("id")) & " name = " & _ CStr(MyDataReader("name"))) End If End While 'Catch ODBC Exception Catch MyOdbcException As OdbcException Dim i As Integer Console.WriteLine (MyOdbcException.ToString) 'Catch program exception Catch MyException As Exception Console.WriteLine (MyException.ToString) End Try End Sub End Module
22.1.21 Credits
These are the developers that have worked on the MyODBC and MyODBC 3.51 Drivers from MySQL AB.
- Micheal (Monty) Widenius
- Venu Anuganti
- Peter Harvey
22.2 MySQL Java Connectivity (JDBC)
There are two supported JDBC drivers for MySQL:
-
MySQL Connector/Jfrom MySQL AB, implemented in 100% native Java. This product was formerly known as themm.mysqldriver. You can downloadMySQL Connector/Jfrom http://dev.mysql.com/downloads/connector/j/. - The Resin JDBC driver, which can be found at http://www.caucho.com/projects/jdbc-mysql/index.xtp.
For more information, consult any general JDBC documentation, plus each driver's own documentation for MySQL-specific features.
Documentation for MySQL Connector/J is available online at the
MySQL AB Web site at http://dev.mysql.com/doc/.
Go to the first, previous, next, last section, table of contents.
Webhosting
Registrácia domény
Standard Webhosting
Classic Webhosting
Biznis Webhosting
DNS parking PLUS
Mini Webhosting
Mikro hosting
Email hosting
Porovnanie hostingov
Cenník Objednávka webhostingu
Registrácia do vyhľadávačov Referencie Reklama Kontakty
Webmail
Zaujímavé linky
nový vzhľad
Salón Andrea
Slovak companies and manufacturers directory
Katalog slovenskych firiem a vyrobkov
Suppliers and products directory
Ceny nezahŕňajú ročný udržiavací poplatok za doménu, ktorý platíme jednotlivým správcom domén.
stránka vygenerovaná za 1.1608s
