<< Главная страница

Chapter 18. Connectors

Table of Contents

18.1. MySQL Connector/ODBC
18.1.1. Introduction to MyODBC
18.1.2. General Information About ODBC and MyODBC
18.1.3. How to Install MyODBC
18.1.4. Installing MyODBC from a Binary Distribution on Windows
18.1.5. Installing MyODBC from a Binary Distribution on Unix
18.1.6. Installing MyODBC from a Source Distribution on Windows
18.1.7. Installing MyODBC from a Source Distribution on Unix
18.1.8. Installing MyODBC from the Development Source Tree
18.1.9. MyODBC Configuration
18.1.10. MyODBC Connection-Related Issues
18.1.11. MyODBC and Microsoft Access
18.1.12. MyODBC and Microsoft VBA and ASP
18.1.13. MyODBC and Third-Party ODBC Tools
18.1.14. MyODBC General Functionality
18.1.15. Basic MyODBC Application Steps
18.1.16. MyODBC API Reference
18.1.17. MyODBC Data Types
18.1.18. MyODBC Error Codes
18.1.19. MyODBC With VB: ADO, DAO and RDO
18.1.20. MyODBC with Microsoft .NET
18.1.21. Credits
18.2. MySQL Connector/NET
18.2.1. Introduction
18.2.2. Downloading and Installing MySQL Connector/NET
18.2.3. Connector/NET Architecture
18.2.4. Using MySQL Connector/NET
18.2.5. MySQL Connector/NET Change History
18.3. MySQL Connector/J
18.3.1. Basic JDBC concepts
18.3.2. Installing Connector/J
18.3.3. JDBC Reference
18.3.4. Using Connector/J with J2EE and Other Java Frameworks
18.3.5. Diagnosing Connector/J Problems
18.3.6. MySQL Connector/J Change History
18.4. MySQL Connector/MXJ
18.4.1. Introduction
18.4.2. Supported Platforms
18.4.3. JUnit Test Requirements
18.4.4. Running the JUnit Tests
18.4.5. Running as part of the JDBC Driver
18.4.6. Running within a Java Object
18.4.7. The MysqldResource API
18.4.8. Running within a JMX Agent (custom)
18.4.9. Deployment in a standard JMX Agent environment (JBoss)
18.4.10. Installation
18.5. Connector/PHP

This chapter describes MySQL Connectors, drivers that provide connectivity to the MySQL server for client programs. There are currently five MySQL Connectors:

  • Connector/ODBC provides driver support for connecting to a MySQL server using the Open Database Connectivity (ODBC) API. Support is available for ODBC connectivity from Windows, Unix and Mac OS X platforms.

  • Connector/NET enables developers to create .NET applications that use data stored in a MySQL database. Connector/NET implement a fully-functional ADO.NET interface and provides support for use with ADO.NET aware tools. Applications that want to use Connector/NET can be written in any of the supported .NET languages.

  • Connector/J provides driver support for connecting to MySQL from a Java application using the standard Java Database Connectivity (JDBC) API.

  • Connector/MXJ is a tool that enables easy deployment and management of MySQL server and database through your Java application.

  • Connector/PHP is a Windows-only connector for PHP that provides the mysql and mysqli extensions for use with MySQL 5.0.18 and later.

For information on connecting to a MySQL server using other languages and interfaces than those detailed above, including Perl, Python and PHP for other platforms and environments, please refer to the Chapter 17, APIs and Libraries chapter.

18.1. MySQL Connector/ODBC

MySQL provides support for ODBC by means of MySQL Connector/ODBC, the family of MyODBC drivers. This is the reference for the Connector/ODBC product family of MyODBC drivers that provide ODBC 3.5x compliant access to the MySQL Database System. It teaches you how to install MyODBC and how to use it. There is also information about common programs that are known to work with MyODBC and answers to some of the most frequently asked questions about MyODBC.

This reference applies to MyODBC 3.51. You can find a manual for an older version of MyODBC in the binary or source distribution for that version.

This is a reference to the MySQL ODBC drivers, not a general ODBC reference. For more information about ODBC, refer to .

The application development part of this reference assumes a good working knowledge of C, general DBMS knowledge, and finally, but not least, familiarity with MySQL. For more information about MySQL functionality and its syntax, refer to .

If you have questions that are not answered in this document, please send a mail message to .

18.1.1. Introduction to MyODBC

18.1.1.1. What is ODBC?

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 .

18.1.1.2. What is Connector/ODBC?

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

18.1.1.3. What is MyODBC 2.50?

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.

18.1.1.4. What is MyODBC 3.51?

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.

18.1.1.5. Where to Get MyODBC

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 .

For more information about MyODBC, visit .

For more information about licensing, visit .

18.1.1.6. Supported Platforms

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

  • Windows 95, 98, Me, NT, 2000, XP, and 2003

  • All Unix Operating Systems

    • AIX

    • Amiga

    • BSDI

    • DEC

    • FreeBSD

    • HP-UX 10, 11

    • Linux

    • Mac OS X Server

    • Mac OS X

    • NetBSD

    • OpenBSD

    • OS/2

    • SGI Irix

    • Solaris

    • SunOS

    • SCO OpenServer

    • SCO UnixWare

    • Tru64 Unix

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 , so that it becomes available for other users.

18.1.1.7. MyODBC Mailing List

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 mailing list.

For information about subscribing to MySQL mailing lists or to browse list archives, visit . See Section 1.7.1, “MySQL Mailing Lists”.

Of particular interest is the ODBC forum in the MySQL Connectors section of the forums.

18.1.1.8. MyODBC Forum

Community support from experienced users is available through the MySQL Forums, located at . See Section 1.7.2, “MySQL Community Support at the MySQL Forums”.

18.1.1.9. How to Report MyODBC Problems or Bugs

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 Section 18.1.9.7, “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 helps 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 Section 1.7.1, “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 has access to the files you upload, and we are 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.

18.1.1.10. How to Submit a MyODBC Patch

You can send a patch or suggest a better solution for any existing code or problems by sending a mail message to .

18.1.2. General Information About ODBC and MyODBC

18.1.2.1. Introduction to ODBC

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 Section 18.1.16, “MyODBC API Reference”. For general information about ODBC, see .

18.1.2.2. MyODBC Architecture

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

MyODBC Architecture
  • Application:

    An application is a program that calls the ODBC API to access the data from the MySQL server. The Application communicates with the Driver Manager using the standard ODBC calls. The Application does not care where the data is stored, how it is stored, or even how the system is configured to access the data. It needs to know only the Data Source Name (DSN).

    A number of tasks are common to all applications, no matter how they use ODBC. These tasks are:

    • Selecting the MySQL server and connecting to it

    • Submitting SQL statements for execution

    • Retrieving results (if any)

    • Processing errors

    • Committing or rolling back the transaction enclosing the SQL statement

    • Disconnecting from the MySQL server

    Because most data access work is done with SQL, the primary tasks for applications that use ODBC are submitting SQL statements and retrieving any results generated by those statements.

  • Driver manager:

    The Driver Manager is a library that manages communication between application and driver or drivers. It performs the following tasks:

    • Resolves Data Source Names (DSN)

    • Driver loading and unloading

    • Processes ODBC function calls or passes them to the driver

  • MyODBC Driver:

    The MyODBC driver is a library that implements the functions in the ODBC API. It processes ODBC function calls, submits SQL requests to MySQL server, and returns results back to the application. If necessary, the driver modifies an application's request so that the request conforms to syntax supported by the MySQL.

  • ODBC.INI:

    ODBC.INI is the ODBC configuration file that stores the driver and database information required to connect to the server. It is used by the Driver Manager to determine which driver to be loaded using the Data Source Name. The driver uses this to read connection parameters based on the DSN specified. For more information, Section 18.1.9, “MyODBC Configuration”.

  • MySQL Server:

    The MySQL server is the source of data. MySQL is:

    • A database management system (DBMS)

    • A relational database management system (RDBMS)

    • Open Source Software

18.1.2.3. ODBC Driver Managers

An ODBC Driver Manager is a library that manages communication between the ODBC-aware application and any drivers. Its main functionality includes:

  • Resolving Data Source Names (DSN)

  • Driver loading and unloading

  • Processing ODBC function calls or passing them to the driver

The following driver managers are commonly used:

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

18.1.2.4. Types of MySQL ODBC Drivers

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.

18.1.3. How to Install MyODBC

MyODBC works on Windows 9x, Me, NT, 2000, XP, and 2003, and on most Unix platforms.

MyODBC is Open Source. You can find the newest version at . Please note that the 2.50.x versions are LGPL licensed, whereas the 3.51.x versions are GPL licensed.

If you have problem with MyODBC and your program also works with OLEDB, you should try the OLEDB driver.

Normally, you need to install MyODBC only on Windows machines. You need MyODBC for Unix only if you have a program like ColdFusion that is running on a Unix machine and uses ODBC to connect for database access.

If you want to install MyODBC on a Unix box, you also need an ODBC manager. MyODBC is known to work with most Unix ODBC managers.

Notice that other configuration options are shown on the MySQL screen that you can try if you run into problems (options such as trace, don't prompt on connect, and so forth).

18.1.5. Installing MyODBC from a Binary Distribution on Unix

18.1.5.1. Installing MyODBC from an RPM Distribution

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 exists, upgrade it like this:

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

If there is any dependency 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. Proceed onto Section 18.1.9.3, “Configuring a MyODBC DSN on Unix”.

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

shell> su root
shell> rpm -e MyODBC

18.1.5.2. Installing MyODBC from a Binary Tarball Distribution

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 Section 18.1.9.3, “Configuring a MyODBC DSN on Unix”, to configure the DSN for MyODBC. For more information, refer to the INSTALL-BINARY file that comes with your distribution.

18.1.6. Installing MyODBC from a Source Distribution on Windows

18.1.6.2. Building MyODBC 3.51

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:

  1. Download and extract the sources to a folder, then change location into that folder. The following command assumes the folder is named myodbc3-src:

    C:\> cd myodbc3-src
    
  2. Edit Makefile to specify the correct path for the MySQL client libraries and header files. Then use the following commands to build and install the release version:

    C:\> nmake -f Makefile
    C:\> nmake -f Makefile install
    

    nmake -f Makefile builds the release version of the driver and places the binaries in subdirectory called Release.

    nmake -f Makefile install installs (copies) the driver DLLs and libraries(myodbc3.dll, myodbc3.lib) to your system directory.

  3. To build the debug version, use Makefile_Debug rather than Makefile, as shown below:

    C:\> nmake -f Makefile_debug
    C:\> nmake -f Makefile_debug install
    
  4. You can clean and rebuild the driver by using:

    C:\> nmake -f Makefile clean
    C:\> nmake -f Makefile install
    

Note:

18.1.6.3. Testing

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

18.1.6.4. Building MyODBC 2.50

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.

18.1.7. Installing MyODBC from a Source Distribution on Unix

18.1.7.2. Typical configure Options

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:

  1. To compile MyODBC, you need to supply the MySQL client include and library files path using the --with-mysql-path=DIR option, where DIR is the directory where the MySQL is installed.

    MySQL compile options can be determined by running DIR/bin/mysql_config.

  2. Supply the standard header and library files path for your ODBC Driver Manager(iodbc or unixobc).

    • If you are using iodbc and iodbc is not installed in its default location (/usr/local), you might have to use the --with-iodbc=DIR option, where DIR is the directory where iodbc is installed.

      If the iodbc headers do not reside in DIR/include, you can use the --with-iodbc-includes=INCDIR option to specify their location.

      The applies to libraries. If they are not in DIR/lib, you can use the --with-iodbc-libs=LIBDIR option.

    • If you are using unixODBC, use the --with-unixODBC=DIR option (case sensitive) to make configure look for unixODBC instead of iodbc by default, DIR is the directory where unixODBC is installed.

      If the unixODBC headers and libraries aren't located in DIR/include and DIR/lib, use the --with-unixODBC-includes=INCDIR and --with-unixODBC-libs=LIBDIR options.

  3. You might want to specify an installation prefix other than /usr/local. For example, to install the MyODBC drivers in /usr/local/odbc/lib, use the --prefix=/usr/local/odbc option.

The final configuration command looks something like this:

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

18.1.7.3. Thread-Safe Client

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, look at the config.log and determine whether the error is due to the lack of thread-libraries in the system; and supply one with LIBS options i.e.

LIBS="-lpthread" ./configure ..

18.1.7.4. Shared or Static Options

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

18.1.7.5. Enabling Debugging Information

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.

18.1.7.6. Enabling the Documentation

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

18.1.7.7. Building and Compilation

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 for further assistance.

18.1.7.8. Building Shared Libraries

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

18.1.7.9. Installing Driver Libraries

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:

  • libmyodbc3.so

  • libmyodbc3-3.51.01.so, where 3.51.01 is the version of the driver

  • libmyodbc3.a

For thread-safe MyODBC 3.51:

  • libmyodbc3_r.so

  • libmyodbc3-3_r.51.01.so

  • libmyodbc3_r.a

For MyODBC 2.5.0:

  • libmyodbc.so

  • libmyodbc-2.50.39.so, where 2.50.39 is the version of the driver

  • libmyodbc.a

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.

18.1.7.10. Testing MyODBC on Unix

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.

18.1.7.11. Mac OS X Notes

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.

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

18.1.7.12. HP-UX Notes

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 determine whether 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.

18.1.7.13. AIX Notes:

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

18.1.9. MyODBC Configuration

This section describes how to configure MyODBC, including DSN creation and the different arguments that the driver takes as an input arguments in the connection string. It also describes how to create an ODBC trace file.

18.1.9.1. What is a Data Source Name?

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

18.1.9.2. Configuring a MyODBC DSN on Windows

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:

  1. Click Start, point to Settings, and then click Control Panel.

  2. On computers running Microsoft Windows 2000 or newer, double-click Administrative Tools, and then double-click Data Sources (ODBC). On computers running older versions of Windows, double-click 32-bit ODBC or ODBC.

    ODBC Data Sources
              Icon

    The ODBC Data Source Administrator dialog box appears, as shown here:

    ODBC Data Source
              Administrator Dialog

    Click Help for detailed information about each tab of the ODBC Data Source Administrator dialog box.

To add a data source on Windows:

  1. Open the ODBC Data Source Administrator.

  2. In the ODBC Data Source Administrator dialog box, click Add. The Create New Data Source dialog box appears.

  3. Select MySQL ODBC 3.51 Driver, and then click Finish. The MySQL ODBC 3.51 Driver - DSN Configuration dialog box appears, as shown here:

    MySQL ODBC DSN
              Configuration Dialog
  4. In the Data Source Name box, enter the name of the data source you want to access. It can be any valid name that you choose.

  5. In the Description box, enter the description needed for the DSN.

  6. For Host or Server Name (or IP) box, enter the name of the MySQL server host that you want to access. By default, it is localhost.

  7. In the Database Name box, enter the name of the MySQL database that you want to use as the default database.

  8. In the User box, enter your MySQL username (your database user ID).

  9. In the Password box, enter your password.

  10. In the Port box, enter the port number if it is not the default (3306).

  11. In the SQL Command box, you can enter an optional SQL statement that you want to issue automatically after the connection has been established.

    The final dialog looks like this:

    Filled-In MySQL ODBC DSN
              Configuration Dialog

    Click OK to add this data source.

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:

MyODBC Successful Connection
          Message

A failed test results in an error:

MyODBC Failed Connection Message

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 Section 18.1.9.4, “Connection Parameters”, for information about the meaning of these options.

MyODBC Options Dialog

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:

  1. Open the ODBC Data Source Administrator. Click the appropriate DSN tab.

  2. Select the MySQL data source that you want to modify and then click Configure. The MySQL ODBC 3.51 Driver - DSN Configuration dialog box appears.

  3. Modify the applicable data source fields, and then click OK.

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

18.1.9.3. Configuring a MyODBC DSN on Unix

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

Note: If you are using unixODBC, you can use the following tools 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

18.1.9.4. Connection Parameters

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.

ParameterDefault ValueComment
userODBC (on Windows)The username used to connect to MySQL.
serverlocalhostThe hostname of the MySQL server.
database The default database.
option0Options that specify how MyODBC should work. See below.
port3306The 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:

ValueDescription
1The client can't handle that MyODBC returns the real width of a column.
2The 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.
4Make 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.)
8Don't set any packet limit for results and parameters.
16Don't prompt for questions even if driver would like to prompt.
32Enable or disable the dynamic cursor support. (Not allowed in MyODBC 2.50.)
64Ignore use of database name in db_name.tbl_name.col_name.
128Force use of ODBC manager cursors (experimental).
256Disable the use of extended fetch (experimental).
512Pad CHAR columns to full column length.
1024SQLDescribeCol() returns fully qualified column names.
2048Use the compressed client/server protocol.
4096Tell server to ignore space after function name and before ‘(’ (needed by PowerBuilder). This makes all function names keywords.
8192Connect with named pipes to a mysqld server running on NT.
16384Change LONGLONG columns to INT columns (some applications can't handle LONGLONG).
32768Return 'user' as Table_qualifier and Table_owner from SQLTables (experimental).
65536Read parameters from the [client] and [odbc] groups from my.cnf.
131072Add some extra safety checks (should not be needed but...).
262144Disable transactions.
524288Enable query logging to c:\myodbc.sql(/tmp/myodbc.sql) file. (Enabled only in debug mode.)
1048576Do 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.
2097152Force the use of Forward-only cursor type. In case of applications setting the default static/dynamic cursor type, and one wants the driver to use non-cache result sets, then this option ensures 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. Note that the myodbc3d.dll isn't included in MyODBC 3.51.07 through 3.51.11. If you are using one of these versions, you should copy that DLL from a previous version (for example, 3.51.06).

For MyODBC 2.50, myodbc.dll and myodbcd.dll are used instead.

The following table shows some recommended option values for various configurations:

ConfigurationOption Value
Microsoft Access3
Microsoft Visual Basic3
Large tables with too many rows2049
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 results3145731

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

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

  1. Start the MySQL server.

  2. Use GRANT to set up an account with a username of myuser that can connect from system B using a password of myuser:

    GRANT ALL ON *.* to 'myuser'@'B' IDENTIFIED BY 'mypassword';
    
  3. The GRANT statement grants all privileges to user myuser for connecting from system B using the password mypassword. To execute this statement, you should be either root on system A (or another user who has appropriate privileges). For more information about MySQL privileges, refer to Section 5.8, “MySQL User Account Management”.

On system B, follow these steps:

  1. 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 18.1.9.5, “Connecting Without a Predefined DSN”.

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

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

You can also find a simple HOWTO at .

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

    1. Click Start, point to Settings, and then click Control Panel.

    2. On computers running Microsoft Windows 2000, XP, or 2003, double-click Administrative Tools, and then double-click Data Sources (ODBC), as shown below.

      ODBC Data Sources
                  Icon

      On computers running an earlier version of Microsoft Windows, double-click 32-bit ODBC or ODBC in the Control Panel.

    3. The ODBC Data Source Administrator dialog box appears, as shown below:

      ODBC Data Source
                  Administrator Dialog
    4. Click Help for detailed information about each tab of the ODBC Data Source Administrator dialog box.

  • Enable the trace option. The procedure for this differs for Windows and Unix.

    To enable the trace option on Windows:

    1. The Tracing tab of the ODBC Data Source Administrator dialog box enables you to configure the way ODBC function calls are traced.

    2. When you activate tracing from the Tracing tab, the Driver Manager logs all ODBC function calls for all subsequently run applications.

    3. ODBC function calls from applications running before tracing is activated are not logged. ODBC function calls are recorded in a log file you specify.

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

      ODBC Tracing
                  Tab

    To enable the trace option on Unix:

    1. On Unix, you need to explicitly set the Trace option in the ODBC.INI file.

      Set the tracing ON or OFF by using TraceFile and Trace parameters in odbc.ini as shown below:

      TraceFile  = /tmp/odbc.trace
      Trace      = 1
      

      TraceFile specifies the name and full path of the trace file and Trace is set to ON or OFF. You can also use 1 or YES for ON and 0 or NO for OFF. If you are using ODBCConfig from unixODBC, then follow the instructions for tracing unixODBC calls at HOWTO-ODBCConfig.

    To generate a MyODBC log, do the following:

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

    2. Enable the Trace MyODBC option flag in the MyODBC connect/configure screen. The log is 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 supply OPTION=4 in the connection string.

    3. 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 (or to 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

    Remember that the more information you can supply to us, the more likely it is that we can fix the problem!

    Also, before posting the bug, check the MyODBC mailing list archive at .

18.1.9.8. Applications Tested with MyODBC

MyODBC has been tested with the following applications:

If you know of any other applications that work with MyODBC, please send mail to about them.

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

18.1.10. MyODBC Connection-Related Issues

This section answers MyODBC connection-related questions.

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

18.1.10.2. While Connecting, an Access denied Error Occurs

Refer to Section 5.7.8, “Causes of Access denied Errors”.

18.1.10.3. INFO: About ODBC Connection Pooling

Refer to this document about connection pooling: .

18.1.11. MyODBC and Microsoft Access

This section of the document answers questions related to MyODBC with Microsoft Access.

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

  1. When you open an Access database or an Access project, a Database window appears. It displays shortcuts for creating new database objects and opening existing objects.

    Access Database
  2. Click the name of the table or query you want to export, and then in the File menu, select Export.

  3. In the Export Object Type Object name To dialog box, in the Save As Type box, select ODBC Databases () as shown here:

    Selecting an ODBC Database
  4. In the Export dialog box, enter a name for the file (or use the suggested name), and then select OK.

  5. 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 18.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.

18.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 or tables from MySQL to Access, follow the instructions:

  1. Open a database, or switch to the Database window for the open database.

  2. To import tables, on the File menu, point to Get External Data, and then click Import. To link tables, on the File menu, point to Get External Data, and then click Link Tables.

  3. In the Import (or Link) dialog box, in the Files Of Type box, select ODBC 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 18.1.9.2, “Configuring a MyODBC DSN on Windows”.

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

  5. Microsoft Access connects to the MySQL server through ODBC data source and displays the list of tables that you can import or link.

  6. Click each table that you want to import or link, and then click OK. If you're linking a table and it doesn't have an index that uniquely identifies each record, Microsoft Access displays a list of the fields in the linked table. Click a field or a combination of fields that uniquely identifies each record, and then click OK.

18.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 view or refresh links:

  1. Open the database that contains links to tables.

  2. On the Tools menu, point to Add-ins (Database Utilities in Access 2000 or newer), and then click Linked Table Manager.

  3. Select the check box for the tables whose links you want to refresh.

  4. 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 change the path for a set of linked tables:

  1. Open the database that contains links to tables.

  2. On the Tools menu, point to Add-ins (Database Utilities in Access 2000 or newer), and then click Linked Table Manager.

  3. Select the Always Prompt For A New Location check box.

  4. Select the check box for the tables whose links you want to change, and then click OK.

  5. In the Select New Location of <table name> dialog box, specify the new location, click Open, and then click OK.

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

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

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

18.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 one doesn't exist.

  • Add a timestamp column if one doesn't exist.

  • 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 18.1.9.7, “Getting an ODBC Trace File”.

18.1.11.9. How to Trap ODBC Login Error Messages in Access?

Read “How to Trap ODBC Login Error Messages in Access” at .

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

18.1.11.12. How to Set the QueryTimeout Value for ODBC Connections?

Read “Set the QueryTimeout Value for ODBC Connections” at .

18.1.11.13. INFO: Tools to Export/Import from/to Access to/from MySQL

Refer to converters section for list of available tools.

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

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

18.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,

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

18.1.12.4. How Do I Handle Blob Data in Visual Basic?

Here is an excellent article from Mike Hillyer (); explaining how to insert or fetch data from blob columns through MyODBC from ADO: MySQL BLOB columns and Visual Basic 6.

18.1.12.5. How Do I Map Visual Basic Data Types to MySQL Types?

Here is yet another good article from Mike Hillyer (): How to map Visual basic data type to MySQL types.

18.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 here:

If you find any other good example or HOW-TO on ADO/DAO/RDO, please send the details to

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

18.1.12.8. INFO: Frequently Asked Questions on ActiveX Data Objects (ADO)

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

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

18.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 mysql client 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 Database tool bar, press the Insert Database button.

  • Press the Get Data button.

  • At the right hand of the Get Data screen, press the Ms Query button.

  • 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 Data and select the records.

  • Click OK and you see the rows in your Word document.

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

18.1.13.3. HOWTO: Configure MySQL+MyODBC+unixODBC+ColdFusion on Solaris

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

18.1.14. MyODBC General Functionality

This section of the document answers questions related to MyODBC general functionality.

18.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 17.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;

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

18.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 storage engines (InnoDB and BDB) make sure the disable transactions option is not set from the DSN configuration.

18.1.14.4. What Causes Cursor not found Errors?

This occurs because 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.

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

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

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 .

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

    ODBC Tracing
              Tab

    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 system directory, right click on the driver DLL and click on properties) as shown below:

    DLL Properties Dialog
  • 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:

    MyODBC Options Dialog

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

MyODBC Programming Flowchart

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

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 nameMyODBCMyODBCConformancePurpose
 2.503.51  
SQLAllocHandleNoYesISO 92Obtains an environment, connection, statement, or descriptor handle.
SQLConnectYesYesISO 92Connects to a specific driver by data source name, user ID, and password.
SQLDriverConnectYesYesODBCConnects to a specific driver by connection string or requests that the Driver Manager and driver display connection dialog boxes for the user.
SQLAllocEnvYesYesDeprecatedObtains an environment handle allocated from driver.
SQLAllocConnectYesYesDeprecatedObtains a connection handle

Obtaining information about a driver and data source:

Function nameMyODBCMyODBCConformancePurpose
 2.503.51  
SQLDataSourcesNoNoISO 92Returns the list of available data sources, handled by the Driver Manager
SQLDriversNoNoODBCReturns the list of installed drivers and their attributes, handles by Driver Manager
SQLGetInfoYesYesISO 92Returns information about a specific driver and data source.
SQLGetFunctionsYesYesISO 92Returns supported driver functions.
SQLGetTypeInfoYesYesISO 92Returns information about supported data types.

Setting and retrieving driver attributes:

Function nameMyODBCMyODBCConformancePurpose
 2.503.51  
SQLSetConnectAttrNoYesISO 92Sets a connection attribute.
SQLGetConnectAttrNoYesISO 92Returns the value of a connection attribute.
SQLSetConnectOptionYesYesDeprecatedSets a connection option
SQLGetConnectOptionYesYesDeprecatedReturns the value of a connection option
SQLSetEnvAttrNoYesISO 92Sets an environment attribute.
SQLGetEnvAttrNoYesISO 92Returns the value of an environment attribute.
SQLSetStmtAttrNoYesISO 92Sets a statement attribute.
SQLGetStmtAttrNoYesISO 92Returns the value of a statement attribute.
SQLSetStmtOptionYesYesDeprecatedSets a statement option
SQLGetStmtOptionYesYesDeprecatedReturns the value of a statement option

Preparing SQL requests:

Function nameMyODBCMyODBCConformancePurpose
 2.503.51  
SQLAllocStmtYesYesDeprecatedAllocates a statement handle
SQLPrepareYesYesISO 92Prepares an SQL statement for later execution.
SQLBindParameterYesYesODBCAssigns storage for a parameter in an SQL statement.
SQLGetCursorNameYesYesISO 92Returns the cursor name associated with a statement handle.
SQLSetCursorNameYesYesISO 92Specifies a cursor name.
SQLSetScrollOptionsYesYesODBCSets options that control cursor behavior.

Submitting requests:

Function nameMyODBCMyODBCConformancePurpose
 2.503.51  
SQLExecuteYesYesISO 92Executes a prepared statement.
SQLExecDirectYesYesISO 92Executes a statement
SQLNativeSqlYesYesODBCReturns the text of an SQL statement as translated by the driver.
SQLDescribeParamYesYesODBCReturns the description for a specific parameter in a statement.
SQLNumParamsYesYesISO 92Returns the number of parameters in a statement.
SQLParamDataYesYesISO 92Used in conjunction with SQLPutData to supply parameter data at execution time. (Useful for long data values.)
SQLPutDataYesYesISO 92Sends part or all of a data value for a parameter. (Useful for long data values.)

Retrieving results and information about results:

Function nameMyODBCMyODBCConformancePurpose
 2.503.51  
SQLRowCountYesYesISO 92Returns the number of rows affected by an insert, update, or delete request.
SQLNumResultColsYesYesISO 92Returns the number of columns in the result set.
SQLDescribeColYesYesISO 92Describes a column in the result set.
SQLColAttributeNoYesISO 92Describes attributes of a column in the result set.
SQLColAttributesYesYesDeprecatedDescribes attributes of a column in the result set.
SQLFetchYesYesISO 92Returns multiple result rows.
SQLFetchScrollNoYesISO 92Returns scrollable result rows.
SQLExtendedFetchYesYesDeprecatedReturns scrollable result rows.
SQLSetPosYesYesODBCPositions 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.
SQLBulkOperationsNoYesODBCPerforms bulk insertions and bulk bookmark operations, including update, delete, and fetch by bookmark.

Retrieving error or diagnostic information:

Function nameMyODBCMyODBCConformancePurpose
 2.503.51  
SQLErrorYesYesDeprecatedReturns additional error or status information
SQLGetDiagFieldYesYesISO 92Returns additional diagnostic information (a single field of the diagnostic data structure).
SQLGetDiagRecYesYesISO 92Returns additional diagnostic information (multiple fields of the diagnostic data structure).

Obtaining information about the data source's system tables (catalog functions) item:

Function nameMyODBCMyODBCConformancePurpose
 2.503.51  
SQLColumnPrivilegesYesYesODBCReturns a list of columns and associated privileges for one or more tables.
SQLColumnsYesYesX/OpenReturns the list of column names in specified tables.
SQLForeignKeysYesYesODBCReturns a list of column names that make up foreign keys, if they exist for a specified table.
SQLPrimaryKeysYesYesODBCReturns the list of column names that make up the primary key for a table.
SQLSpecialColumnsYesYesX/OpenReturns 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.
SQLStatisticsYesYesISO 92Returns statistics about a single table and the list of indexes associated with the table.
SQLTablePrivilegesYesYesODBCReturns a list of tables and the privileges associated with each table.
SQLTablesYesYesX/OpenReturns the list of table names stored in a specific data source.

Performing transactions:

Function nameMyODBCMyODBCConformancePurpose
 2.503.51  
SQLTransactYesYesDeprecatedCommits or rolls back a transaction
SQLEndTranNoYesISO 92Commits or rolls back a transaction.

Terminating a statement:

Function nameMyODBCMyODBCConformancePurpose
 2.503.51  
SQLFreeStmtYesYesISO 92Ends statement processing, discards pending results, and, optionally, frees all resources associated with the statement handle.
SQLCloseCursorYesYesISO 92Closes a cursor that has been opened on a statement handle.
SQLCancelYesYesISO 92Cancels an SQL statement.

Terminating a connection:

Function nameMyODBCMyODBCConformancePurpose
 2.503.51  
SQLDisconnectYesYesISO 92Closes the connection.
SQLFreeHandleNoYesISO 92Releases an environment, connection, statement, or descriptor handle.
SQLFreeConnectYesYesDeprecatedReleases connection handle
SQLFreeEnvYesYesDeprecatedReleases an environment handle

18.1.17. MyODBC Data Types

The following table illustrates how driver maps the server data types to default SQL and C data types:

Native ValueSQL TypeC Type
bitSQL_BITSQL_C_BIT
tinyintSQL_TINYINTSQL_C_STINYINT
tinyint unsignedSQL_TINYINTSQL_C_UTINYINT
bigintSQL_BIGINTSQL_C_SBIGINT
bigint unsignedSQL_BIGINTSQL_C_UBIGINT
long varbinarySQL_LONGVARBINARYSQL_C_BINARY
blobSQL_LONGVARBINARYSQL_C_BINARY
longblobSQL_LONGVARBINARYSQL_C_BINARY
tinyblobSQL_LONGVARBINARYSQL_C_BINARY
mediumblobSQL_LONGVARBINARYSQL_C_BINARY
long varcharSQL_LONGVARCHARSQL_C_CHAR
textSQL_LONGVARCHARSQL_C_CHAR
mediumtextSQL_LONGVARCHARSQL_C_CHAR
charSQL_CHARSQL_C_CHAR
numericSQL_NUMERICSQL_C_CHAR
decimalSQL_DECIMALSQL_C_CHAR
integerSQL_INTEGERSQL_C_SLONG
integer unsignedSQL_INTEGERSQL_C_ULONG
intSQL_INTEGERSQL_C_SLONG
int unsignedSQL_INTEGERSQL_C_ULONG
mediumintSQL_INTEGERSQL_C_SLONG
mediumint unsignedSQL_INTEGERSQL_C_ULONG
smallintSQL_SMALLINTSQL_C_SSHORT
smallint unsignedSQL_SMALLINTSQL_C_USHORT
realSQL_FLOATSQL_C_DOUBLE
doubleSQL_FLOATSQL_C_DOUBLE
floatSQL_REALSQL_C_FLOAT
double precisionSQL_DOUBLESQL_C_DOUBLE
dateSQL_DATESQL_C_DATE
timeSQL_TIMESQL_C_TIME
yearSQL_SMALLINTSQL_C_SHORT
datetimeSQL_TIMESTAMPSQL_C_TIMESTAMP
timestampSQL_TIMESTAMPSQL_C_TIMESTAMP
textSQL_VARCHARSQL_C_CHAR
varcharSQL_VARCHARSQL_C_CHAR
enumSQL_VARCHARSQL_C_CHAR
setSQL_VARCHARSQL_C_CHAR
bitSQL_CHARSQL_C_CHAR
boolSQL_CHARSQL_C_CHAR

18.1.18. MyODBC Error Codes

The following tables lists the error codes returned by the driver apart from the server errors.

Native CodeSQLSTATE 2SQLSTATE 3Error Message
5000100001000General warning
5010100401004String data, right truncated
50201S0201S02Option value changed
50301S0301S03No rows updated/deleted
50401S0401S04More than one row updated/deleted
50501S0601S06Attempt to fetch before the result set returned the first row set
5060700107002SQLBindParameter not used for all parameters
5070700507005Prepared statement not a cursor-specification
5080700907009Invalid descriptor index
5090800208002Connection name in use
5100800308003Connection does not exist
5112400024000Invalid cursor state
5122500025000Invalid transaction state
51325S0125S01Transaction state unknown
5143400034000Invalid cursor name
515S1000HY000General driver defined error
516S1001HY001Memory allocation error
517S1002HY002Invalid column number
518S1003HY003Invalid application buffer type
519S1004HY004Invalid SQL data type
520S1009HY009Invalid use of null pointer
521S1010HY010Function sequence error
522S1011HY011Attribute can not be set now
523S1012HY012Invalid transaction operation code
524S1013HY013Memory management error
525S1015HY015No cursor name available
526S1024HY024Invalid attribute value
527S1090HY090Invalid string or buffer length
528S1091HY091Invalid descriptor field identifier
529S1092HY092Invalid attribute/option identifier
530S1093HY093Invalid parameter number
531S1095HY095Function type out of range
532S1106HY106Fetch type out of range
533S1117HY117Row value out of range
534S1109HY109Invalid cursor position
535S1C00HYC00Optional feature not implemented
021S0121S01Column count does not match value count
02300023000Integrity constraint violation
04200042000Syntax error or access violation
042S0242S02Base table or view not found
042S1242S12Index not found
042S2142S21Column already exists
042S2242S22Column not found
008S0108S01Communication link failure

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

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

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

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

18.1.20. MyODBC with Microsoft .NET

This section contains simple examples that demonstrate the use of MyODBC drivers with ODBC.NET.

18.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, 
*
* (C) Copyright MySQL AB, 1995-2006
*
**/

/* 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");
      }
    }
  }
}
}

18.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, 
'
' (C) Copyright MySQL AB, 1995-2006
'
'

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

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


На главную
Комментарии
Войти
Регистрация