ODBC (Open Database Connectivity) provides a way for client programs to access a wide range of databases or data sources. ODBC is a standardized API that allows connections to SQL database servers. It was developed according to the specifications of the SQL Access Group and defines a set of function calls, error codes, and data types that can be used to develop database-independent applications. ODBC usually is used when database independence or simultaneous access to different data sources is required.

For more information about ODBC, refer to http://www.microsoft.com/data/.

Connector/ODBC is the term designating the MySQL AB product family of MySQL ODBC drivers. These are known as the MyODBC drivers.

MyODBC 2.50 is a 32-bit ODBC driver from MySQL AB that is based on ODBC 2.50 specification level 0 (with level 1 and 2 features). This is one of the most popular ODBC drivers in the open source market, used by many users to access the MySQL functionality.

MyODBC 3.51 is a 32-bit ODBC driver, also known as the MySQL ODBC 3.51 driver. This version is enhanced compared to the existing MyODBC 2.50 driver. It has support for ODBC 3.5x specification level 1 (complete core API + level 2 features) in order to continue to provide all functionality of ODBC for accessing MySQL.

MySQL AB distributes all its products under the General Public License (GPL). You can get a copy of the latest version of MyODBC binaries and sources from the MySQL AB Web site http://dev.mysql.com/downloads/.

For more information about MyODBC, visit http://www.mysql.com/products/myodbc/.

MyODBC can be used on all major platforms supported by MySQL, such as:

If a binary distribution is not available for downloading for a particular platform, you can build the driver yourself by downloading the driver sources. You can contribute the binaries to MySQL by sending a mail message to mailto:myodbc@@lists.mysql.com, so that it becomes available for other users.

MySQL AB provides assistance to the user community by means of its mailing lists. For MyODBC-related issues, you can get help from experienced users by using the mailto:myodbc@@lists.mysql.com mailing list.

For information about subscribing to MySQL mailing lists or to browse list archives, visit http://lists.mysql.com/.

For the success of any product, support from the product vendor or the development team plays a key role. If you want any of the following, you must get a support contract from MySQL AB:

MySQL AB offers different types of support licenses in order to help you by keeping your requirements and ability to purchase in mind. For more information about MySQL support, visit https://order.mysql.com/ or send a mail message to mailto:sales@@mysql.com.

Once you have the support means, you can send queries or bug reports using the MySQL support wizard from http://support.mysql.com/. Then the MySQL team will work on your problem immediately until it gets resolved.

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. The procedure for doing this is described in the section called “Getting an ODBC Trace File”.

Check the MyODBC trace file to find out what could be wrong. You should be able to determine what statements were issued by searching for the string >mysql_real_query in the myodbc.log file.

You should also try issuing the statements from the mysql client program or from admndemo. This will help you determine whether the error is in MyODBC or MySQL.

If you find out something is wrong, please only send the relevant rows (maximum 40 rows) to the myodbc mailing list. See the section called “The MySQL Mailing Lists”. 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 create an archive in tar or Zip format that contains a MyODBC trace file, the ODBC log file, and a README file that explains the problem. You can send this to ftp://ftp.mysql.com/pub/mysql/upload/. Only we at MySQL AB will have access to the files you upload, and we will be very discreet with the data.

If you can create a program that also demonstrates the problem, please include it in the archive as well.

If the program works with some other SQL server, you should include 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.

You must have a support contract from MySQL AB in order to request a new feature that is not in the TO-DO list, or if you want a patch prior to its release. For more information on obtaining support, see the section called “How to Get MyODBC Support”.

You can send a patch or suggest a better solution for any existing code or problems by sending a mail message to mailto:myodbc@@lists.mysql.com.

Open Database Connectivity (ODBC) is a widely accepted application-programming interface (API) for database access. It is based on the Call-Level Interface (CLI) specifications from X/Open and ISO/IEC for database APIs and uses Structured Query Language (SQL) as its database access language.

A survey of ODBC functions supported by MyODBC is given at the section called “MyODBC API Reference”. For general information about ODBC, see http://www.microsoft.com/data/.

The MyODBC architecture is based on five components, as shown in the following diagram:

An ODBC Driver Manager is a library that manages communication between the ODBC aware application and driver(s). Its main functionality includes:

The following driver managers are commonly used:

MyODBC 3.51 also is shipped with UnixODBC beginning with version 2.1.2.

MySQL AB supports two Open Source ODBC drivers for accessing MySQL functionality through the ODBC API: MyODBC (MyODBC 2.50) and MySQL ODBC 3.51 Driver (MyODBC 3.51).

Note: From this section onward, we refer both the drivers generically as MyODBC. Whenever there is a difference, we use the original names.

To install or upgrade MyODBC from an RPM distribution on Linux, simply download the RPM distribution of the latest version of MyODBC and follow the instructions below. Use su root to become root, then install the RPM file.

If you are installing for the first time:

shell> su root
shell> rpm -ivh MyODBC-3.51.01.i386-1.rpm

If the driver already exists, upgrade like this:

shell> su root
shell> rpm -Uvh MyODBC-3.51.01.i386-1.rpm

If there is any dependancy error for MySQL client library, libmysqlclient, simply ignore it by supplying the --nodeps option, and then make sure the MySQL client shared library is in the path or set through LD_LIBRARY_PATH.

This installs the driver libraries and related documents to /usr/local/lib and /usr/share/doc/MyODBC respectively. Now proceed onto configuring the DSN on Unix.

To uninstall the driver, become root and execute an rpm command:

shell> su root
shell> rpm -e MyODBC

To install the driver from a tarball distribution (.tar.gz file), download the latest version of the driver for your operating system and follow these steps:

shell> su root
shell> gunzip MyODBC-3.51.01-i686-pc-linux.tar.gz
shell> tar xvf MyODBC-3.51.01-i686-pc-linux.tar
shell> cd MyODBC-3.51.01-i686-pc-linux

Read the installation instructions in the INSTALL-BINARY file and execute these commands.

shell> cp libmyodbc* /usr/local/lib
shell> cp odbc.ini /usr/local/etc
shell> export ODBCINI=/usr/local/etc/odbc.ini

Then proceed on to how to configure the DSN on unix to configure the DSN for MyODBC. For more information, refer to the INSTALL-BINARY file that comes with your distribution.

MyODBC 3.51 source distributions include Makefiles that uses nmake. In the distribution, you can find Makefile for building the release version and Makefile_debug for building debugging versions of the driver libraries and DLLs.

To build the driver, use this procedure:

Note:

After the driver libraries are copied/installed to the system directory, you can test whether the libraries are properly built by using the samples provided in the samples subdirectory:

C:\> cd samples
C:\> nmake -f Makefile all

The MyODBC 2.50 source distribution includes VC workspace files. You can build the driver using these files (.dsp and .dsw) directly by loading them from Microsoft Visual Studio 6.0 or higher.

Once you have all the required files, unpack the source files to a separate directory and follow the instructions as given below:

The configure script gives you a great deal of control over how you configure your MyODBC build. Typically you do this using options on the configure command line. You can also affect configure using certain environment variables. For a list of options and environment variables supported by configure, run this command:

shell> ./configure --help

Some of the more commonly used configure options are described here:

The final configuration command will look something like this:

shell> ./configure --prefix=/usr/local \
           --with-iodbc=/usr/local \
           --with-mysql-path=/usr/local/mysql

In order to link the driver with MySQL thread safe client libraries libmysqlclient_r.so or libmysqlclient_r.a, you must specify the following configure option:

--enable-thread-safe

and can be disabled(default) using

--disable-thread-safe

This option enables the building of driver thread-safe library libmyodbc3_r.so from by linking with mysql thread-safe client library libmysqlclient_r.so (The extensions are OS dependent).

In case while configuring with thread-safe option, and gotten into a configure error; then look at the config.log and see if it is due to the lack of thread-libraries in the system; and supply one with LIBS options i.e.

LIBS="-lpthread" ./configure ..

You can enable or disable the shared and static versions using these options:

--enable-shared[=yes/no]
--disable-shared
--enable-static[=yes/no]
--disable-static

By default, all the binary distributions are built as non-debugging versions (configured with --without-debug).

To enable debugging information, build the driver from source distribution and use the --with-debug) when you run configure.

This option is available only for BK clone trees; not for normal source distributions.

By default, the driver is built with (--without-docs); And in case if you want the documentation to be taken care in the normal build, then configure with:

 --with-docs

To build the driver libraries, you have to just execute make, which takes care of everything.

shell> make

If any errors occur, correct them and continue the build process. If you aren't able to build, then send a detailed email to mailto:myodbc@@lists.mysql.com for further assistance.

On most platforms, MySQL doesn't build or support .so (shared) client libraries by default, because building with shared libraries has caused us problems in the past.

In cases like this, you have to download the MySQL distribution and configure it with these options:

--without-server --enable-shared

To build shared driver libraries, you must specify the --enable-shared option for configure. By default, configure does not enable this option.

If you have configured with the --disable-shared option, you can build the .so file from the static libraries using the following commands:

shell> cd MyODBC-3.51.01
shell> make
shell> cd driver
shell> CC=/usr/bin/gcc \
       $CC -bundle -flat_namespace -undefined error \
           -o .libs/libmyodbc3-3.51.01.so \
           catalog.o connect.o cursor.o dll.o error.o execute.o \
           handle.o info.o misc.o myodbc3.o options.o prepare.o \
           results.o transact.o utility.o \
           -L/usr/local/mysql/lib/mysql/ \
           -L/usr/local/iodbc/lib/ \
           -lz -lc -lmysqlclient -liodbcinst

Make sure to change -liodbcinst to -lodbcinst if you are using unixODBC instead of iODBC, and configure the library paths accordingly.

This builds and places the libmyodbc3-3.51.01.so file in the .libs directory. Copy this file to MyODBC library directory (/usr/local/lib (or the lib directory under the installation directory that you supplied with the --prefix).

shell> cd .libs
shell> cp libmyodbc3-3.51.01.so /usr/local/lib
shell> cd /usr/local/lib
shell> ln -s libmyodbc3-3.51.01.so libmyodbc3.so

To build the thread-safe driver library:

shell> CC=/usr/bin/gcc \
       $CC -bundle -flat_namespace -undefined error
	      -o .libs/libmyodbc3_r-3.51.01.so
	      catalog.o connect.o cursor.o dll.o error.o execute.o
	      handle.o info.o misc.o myodbc3.o options.o prepare.o
	      results.o transact.o utility.o
	      -L/usr/local/mysql/lib/mysql/
	      -L/usr/local/iodbc/lib/
	      -lz -lc -lmysqlclient_r -liodbcinst

To install the driver libraries, execute the following command:

shell> make install

That command installs one of the following sets of libraries:

For MyODBC 3.51:

For thread-safe MyODBC 3.51:

For MyODBC 2.5.0:

For more information on build process, refer to the INSTALL file that comes with the source distribution. Note that if you are trying to use the make from Sun, you may end up with errors. On the other hand, GNU gmake should work fine on all platforms.

To run the basic samples provided in the distribution with the libraries that you built, just execute:

shell> make test

Make sure the DSN 'myodbc3' is configured first in odbc.ini and environment variable ODBCINI is pointing to the right odbc.ini file; and MySQL server is running. You can find a sample odbc.ini with the driver distribution.

You can even modify the samples/run-samples script to pass the desired DSN, UID, and PASSWORD values as the command line arguments to each sample.

To build the driver on Mac OS X (Darwin), make use of the following configure example:

shell> ./configure --prefix=/usr/local
			       --with-unixODBC=/usr/local
			       --with-mysql-path=/usr/local/mysql
			       --disable-shared
			       --enable-gui=no
			       --host=powerpc-apple

The command assumes that the unixODBC and MySQL are installed in the default locations. If not, configure accordingly.

On Mac OS X, --enable-shared builds .dylib files by default. You can build .so files like this:

shell> make
shell> cd driver
shell> CC=/usr/bin/gcc \
       $CC -bundle -flat_namespace -undefined error
           -o .libs/libmyodbc3-3.51.01.so *.o
           -L/usr/local/mysql/lib/
           -L/usr/local/iodbc/lib
           -liodbcinst -lmysqlclient -lz -lc

To build the thread-safe driver library:

shell> CC=/usr/bin/gcc \
       $CC -bundle -flat_namespace -undefined error
       -o .libs/libmyodbc3-3.51.01.so *.o
       -L/usr/local/mysql/lib/
       -L/usr/local/iodbc/lib
       -liodbcinst -lmysqlclienti_r -lz -lc -lpthread

Make sure to change the -liodbcinst to -lodbcinst in case of using unixODBC instead of iODBC and configure the libraries path accordingly.

In Apple's version of GCC, both cc and gcc are actually symbolic links to gcc3.

Now copy this library to the $prefix/lib directory and symlink to libmyodbc3.so.

You can cross-check the output shared-library properties using this command:

shell> otool -LD .libs/libmyodbc3-3.51.01.so

To build the driver on HP-UX 10.x or 11.x, make use of the following configure example:

If using cc:

shell> CC="cc" \
       CFLAGS="+z" \
       LDFLAGS="-Wl,+b:-Wl,+s" \
       ./configure --prefix=/usr/local
			       --with-unixodbc=/usr/local
			       --with-mysql-path=/usr/local/mysql/lib/mysql
			       --enable-shared
			       --enable-thread-safe

If using gcc:

shell> CC="gcc" \
       LDFLAGS="-Wl,+b:-Wl,+s" \
       ./configure --prefix=/usr/local
			       --with-unixodbc=/usr/local
			       --with-mysql-path=/usr/local/mysql
			       --enable-shared
			       --enable-thread-safe

Once the driver is built, cross-check its attributes using chatr .libs/libmyodbc3.sl to see whether or not you need to have the MySQL client libraries path using the SHLIB_PATH environment variable. For static versions, ignore all shared-library options and run configure with the --disable-shared option.

To build the driver on AIX, make use of the following configure example:

shell> ./configure --prefix=/usr/local
			       --with-unixodbc=/usr/local
			       --with-mysql-path=/usr/local/mysql
			       --disable-shared
			       --enable-thread-safe

NOTE: For more information about how to build and set up the static and shared libraries across the different platforms refer to ' Using static and shared libraries across platforms'.

A "data source" is a place where data comes from. The data source must have a persistent identifier, the Data Source Name. Using the Data Source Name, MySQL can access initialization information. With the initialization information, MySQL knows where to access the database and what settings to use when the access starts.

In effect, the data source is the path to the data. In different contexts this might mean different things, but typically it identifies a running MySQL server (for example via a network address or service name), plus the default database for that server at connection time, plus necessary connection information such as the port. The MySQL drivers (and, on Windows systems, the ODBC Driver Manager) will use the data source for connecting. An administrative utility called the Microsoft ODBC Data Source Administrator may be useful for this purpose.

There are two places where the initialization information might be: in the Windows registry (on a Windows system), or in a DSN file (on any system).

If the information is in the Windows registry, it is called a "Machine data source". It might be a "User data source", in which case only one user can see it. Or it might be a "System data source" in which case it is accessible to all users on the computer, or indeed to all users connected to the computer, if the users are connected by Microsoft Windows NT services. When you run the ODBC Data Administration program, you will have a choice whether to use "User" or "System" – there are separate tabs.

If the information is in a DSN file, it is called a "File data source". This is a text file. Its advantages are: (a) it is an option for any kind of computer, not just a computer with a Windows operating system; (b) its contents can be transmitted or copied relatively easily.

To add and configure a new MyODBC data source on Windows, use the ODBC Data Source Administrator. The ODBC Administrator updates your data source connection information. As you add data sources, the ODBC Administrator updates the registry information for you.

To open the ODBC Administrator from the Control Panel:

To add a data source on Windows:

Note: Upon clicking OK, the Data Sources dialog box appears, and the ODBC Administrator updates the registry information. The username and connect string that you entered become the default connection values for this data source when you connect to it.

You can also test whether your settings are suitable for connecting to the server using the button Test Data Source. This feature is available only for the MyODBC 3.51 driver. A successful test results in the following window:

A failed test results in an error:

The DSN configuration dialog also has an Options button. If you select it, the following options dialog appears displaying that control driver behavior. Refer to the section called “Connection Parameters” for information about the meaning of these options.

Note: The options listed under Driver Trace Options are disabled (grayed out) unless you are using the debugging version of the driver DLL.

To modify a data source on Windows:

When you have finished modifying the information in this dialog box, the ODBC Administrator updates the registry information.

On Unix, you configure DSN entries directly in the odbc.ini file. Here is a typical odbc.ini file that configures myodbc and myodbc3 as the DSN names for MyODBC 2.50 and MyODBC 3.51, respectively:

;
;  odbc.ini configuration for MyODBC and MyODBC 3.51 drivers
;

[ODBC Data Sources]
myodbc      = MyODBC 2.50 Driver DSN
myodbc3     = MyODBC 3.51 Driver DSN

[myodbc]
Driver       = /usr/local/lib/libmyodbc.so
Description  = MyODBC 2.50 Driver DSN
SERVER       = localhost
PORT         =
USER         = root
Password     =
Database     = test
OPTION       = 3
SOCKET       =

[myodbc3]
Driver       = /usr/local/lib/libmyodbc3.so
Description  = MyODBC 3.51 Driver DSN
SERVER       = localhost
PORT         =
USER         = root
Password     =
Database     = test
OPTION       = 3
SOCKET       =

[Default]
Driver       = /usr/local/lib/libmyodbc3.so
Description  = MyODBC 3.51 Driver DSN
SERVER       = localhost
PORT         =
USER         = root
Password     =
Database     = test
OPTION       = 3
SOCKET       =

Refer to the the section called “Connection Parameters”, for the list of connection parameters that can be supplied.

Note: If you are using unixODBC, you can use the following tools in order to set up the DSN:

In some cases when using unixODBC, you might get this error:

Data source name not found and no default driver specified

If this happens, make sure the ODBCINI and ODBCSYSINI environment variables are pointing to the right odbc.ini file. For example, if your odbc.ini file is located in /usr/local/etc, set the environment variables like this:

export ODBCINI=/usr/local/etc/odbc.ini
export ODBCSYSINI=/usr/local/etc

You can specify the following parameters for MyODBC in the [Data Source Name] section of an ODBC.INI file or through the InConnectionString argument in the SQLDriverConnect() call.

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:

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 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:

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 the section called “Connection Parameters”, for the list of connection parameters that can be supplied.

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:

On system B, follow these steps:

You can also find a simple HOWTO at http://www.phphelp.com/tutorial/using-myodbc-to-connect-to-a-remote-database.html.

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:

MyODBC has been tested with the following applications:

If you know of any other applications that work with MyODBC, please send mail to mailto:myodbc@@lists.mysql.com about them.

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.

For more information, refer to MS KnowledgeBase Article(Q260558). Also, make sure you have the latest valid ctl3d32.dll in your system directory.

Refer to the section called “Causes of Access denied Errors”.

Refer to this document about connection pooling: http://support.microsoft.com/default.aspx?scid=kb;EN-US;q169470.

The following must be done on your client PC in order to make Microsoft Access work with MyODBC.

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:

Microsoft Access connects to the MySQL Server through this data source and exports new tables and or data.

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:

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:

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:

If the inserted or updated records are shown as #DELETED# in the access, then:

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.

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.

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:

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 the section called “Getting an ODBC Trace File”.

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&amp;SD=gn&amp;FR=0 .

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:

These add up to a value of 10 (OPTION=10).

Read “Set the QueryTimeout Value for ODBC Connections” at http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B153756.

Refer to converters section for list of available tools.

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).

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

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.

Here is an excellent article from Mike Hillyer (mailto: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.

Here is yet another good article from Mike Hillyer (mailto:m.hillyer@@telusplanet.net): How to map Visual basic data type to MySQL types.

A simple examples for the usage of ADO, DAO and RDO with VB can be found her:

If you find any other good example or HOW-TO on ADO/DAO/RDO, then please send the details to mailto:myodbc@@lists.mysql.com

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.

For information, see ActiveX Data Objects(ADO) Freqently Asked Questions.

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:

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.

Refer to MySQL ColdFusion unixODBC MyODBC and Solaris - how to succeed

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 the section called “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;

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.

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.

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.

Yes. If you find something is not working with MyODBC 3.51 that works with MyODBC 2.50, then send a mail message to mailto:myodbc@@lists.mysql.com

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.

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.

MyODBC is a lot faster than any other ODBC driver. Slowness might be due to not using the following options.

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

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

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

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, mailto: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");
        }
      }
    }
  }
 }

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, mailto: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