Chapter 22. Connectors and APIs

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 Connector/ODBC is given at Section 22.1.6.1, "Connector/ODBC API Reference". For general information about ODBC, see http://support.microsoft.com/kb/110093.

22.1.2.1.1. Connector/ODBC Architecture

The Connector/ODBC architecture is based on five components, as shown in the following diagram:

• Application:

  The Application uses the ODBC API to access the data from the MySQL server. The ODBC API in turn communicates with the Driver Manager. 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). The DSN is a configuration string that identifies a given database driver, database, database host and optionally authentication information that enables an ODBC application to connect to a database using a standardized reference.

    Because the database connectivity information is identified by the DSN, any ODBC compliant application can connect to the data source using the same DSN reference. This eliminates the need to separately configure each application that needs access to a given database; instead you instruct the application to use a pre-configured DSN.

  • Loading and unloading of the driver required to access a specific database as defined within the DSN. For example, if you have configured a DSN that connects to a MySQL database then the driver manager will load the Connector/ODBC driver to enable the ODBC API to communicate with the MySQL host.

  • Processes ODBC function calls or passes them to the driver for processing.

• Connector/ODBC Driver:

  The Connector/ODBC driver is a library that implements the functions supported by 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 MySQL.

• DSN Configuration:

  The ODBC configuration file 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 according to the definition in the DSN. The driver uses this to read connection parameters based on the DSN specified. For more information, Section 22.1.4, "Configuring Connector/ODBC".

• MySQL Server:

  The MySQL database where the information is stored. The database is used as the source of the data (during queries) and the destination for data (during inserts and updates).

Before installing the Connector/ODBC drivers on Windows, ensure that your Microsoft Data Access Components (MDAC) are up to date. You can obtain the latest version from the Microsoft Data Access and Storage Web site.

There are different distribution types to use when installing for Windows. The software that is installed is identical in each case, only the installation method is different.

There are two methods available for installing Connector/ODBC on Unix from a binary distribution. For most Unix environments, you will use the tarball distribution. For Linux systems, there is also an RPM distribution available.

shell> su rootshell> gunzip mysql-connector-odbc-5.2.2-i686-pc-linux.tar.gzshell> tar xvf mysql-connector-odbc-5.2.2-i686-pc-linux.tarshell> cd mysql-connector-odbc-5.2.2-i686-pc-linux

shell> su rootshell> rpm -ivh mysql-connector-odbc-5.2.2.i386.rpm

shell> su rootshell> rpm -Uvh mysql-connector-odbc-5.2.2.i386.rpm

shell> su rootshell> rpm -e mysql-connector-odbc

Mac OS X is based on the FreeBSD operating system, and you can normally use the MySQL network port for connecting to MySQL servers on other hosts. Installing the Connector/ODBC driver lets you connect to MySQL databases on any platform through the ODBC interface. If your application requires an ODBC interface, install the Connector/ODBC driver. Applications that require or can use ODBC (and therefore the Connector/ODBC driver) include ColdFusion, Filemaker Pro, 4th Dimension and many other applications.

Mac OS X includes its own ODBC manager, based on the iODBC manager. Mac OS X includes an administration tool that provides easier administration of ODBC drivers and configuration, updating the underlying iODBC configuration files.

For Connector/ODBC 5.x, the package is provided as a compressed tar archive that you manually install.

In either case, the driver is designed to work with the iODBC driver manager included with Mac OS X.

To install Connector/ODBC 5.x:

To verify the installed drivers, either use the ODBC Administrator application or the myodbc3i utility:

shell> myodbc3i -q -d

You only need to build Connector/ODBC from source on Windows to modify the source or installation location. If you are unsure whether to install from source, please use the binary installation detailed in Section 22.1.3.1, "Installing Connector/ODBC on Windows".

Building Connector/ODBC from source on Windows requires a number of different tools and packages:

Build Steps

Set the environment variables for the Visual Studio toolchain. Visual Studio includes a batch file to set these for you, and installs a Start menu shortcut that opens a command prompt with these variables set.

Set MYSQL_DIR to the MySQL server installation path, while using the short-style file names. For example:

C:\> set MYSQL_DIR=C:\PROGRA~1\MySQL\MYSQLS~1.0

Build Connector/ODBC using the cmake command-line tool by executing the following from the source root directory (in a command prompt window):

C:\> cmake -G "Visual Studio 8 2005"

This produces a project file that you can open with Visual Studio, or build from the command line with either of the following commands:

C:\> devenv.com MySQL_Connector_ODBC.sln /build ReleaseC:\> devenv.com MySQL_Connector_ODBC.sln /build RelWithDebInfo

To compile a debug build, set the cmake build type so that the correct versions of the MySQL client libraries are used:

C:\> cmake -G "Visual Studio 8 2005" -DCMAKE_BUILD_TYPE=DebugC:\> devenv.com MySQL_Connector_ODBC.sln /build Debug

Upon completion, the executables are in the bin/ and lib/ subdirectories.

See Section 22.1.3.1.2, "Installing the Windows Connector/ODBC Driver using the Zipped DLL Package" for the copy commands to complete the installation.

You need the following tools to build MySQL from source on Unix:

Once you have all the required files, unpack the source files to a separate directory, then run configure and build the library using make.

Typical cmake Options

iODBC is the default ODBC library used by Connector/ODBC. Alternatively, unixODBC may be used by passing in the appropriate option to cmake. For example:

shell> cmake -G "Unix Makefiles" -DWITH_UNIXODBC=1

When you run cmake, you might add options to the command line. Here are some examples:

Additional configure Options

There are a number of other options that you need, or want, to set when configuring the Connector/ODBC driver before it is built.

Build Steps for Unix

To build the driver libraries, execute make:

shell> make

If any errors occur, correct them and continue the build process. If you are not able to build, then send a detailed email to <[email protected]> for further assistance.

Building Shared Libraries

On most platforms, MySQL does not build or support .so (shared) client libraries by default. This is based on our experience of problems when building shared libraries.

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, 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 mysql-connector-odbc-3.51.01shell> makeshell> cd drivershell> 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 the Connector/ODBC library installation directory (/usr/local/lib, or the lib directory under the installation directory that you supplied with the --prefix option).

shell> cd .libsshell> cp libmyodbc3-3.51.01.so /usr/local/libshell> cd /usr/local/libshell> 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

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 Connector/ODBC 3.51:

For thread-safe Connector/ODBC 3.51:

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.

Testing Connector/ODBC on Unix

To run the basic samples provided in the distribution with the libraries that you built, use the following command:

shell> make test

Before running the tests, create the DSN 'myodbc3' in odbc.ini and set the environment variable ODBCINI to the correct 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> makeshell> cd drivershell> 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

To obtain the most recent development source tree, first download and install Bazaar from the Bazaar VCS Web site. Bazaar is supported by any platform that supports Python, and is therefore compatible with any Linux, Unix, Windows or Mac OS X host. Instructions for downloading and installing Bazaar on the different platforms are available on the Bazaar Web site.

Building from the source trees requires the following tools:

The most recent development source tree is available from our public Subversion trees at http://dev.mysql.com/tech-resources/sources.html.

To check out out the Connector/ODBC sources, change to the directory where you want the copy of the Connector/ODBC tree to be stored, then use the following command:

shell> bzr branch lp:myodbc

You should now have a copy of the entire Connector/ODBC source tree in the directory connector-odbc3. To build from this source tree on Unix or Linux, follow these steps:

shell> cd myodbcshell> aclocalshell> autoheadershell> libtoolize -c -fshell> autoconfshell> automake;shell> ./configure  # Add your favorite options hereshell> make

When the build is done, run make install to install the Connector/ODBC driver on your system.

If you have gotten to the make stage and the distribution does not compile, please report it to <[email protected]>.

On Windows, make use of Windows Makefiles WIN-Makefile and WIN-Makefile_debug in building the driver. For more information, see Section 22.1.3.4, "Building Connector/ODBC from a Source Distribution on Windows".

After the initial checkout operation to get the source tree, run bzr pull periodically to update your source according to the latest version.

A Data Source Name associates the configuration parameters for communicating with a specific database. Generally, a DSN consists of the following parameters:

In addition, different ODBC drivers, including Connector/ODBC, may accept additional driver-specific options and parameters.

There are three types of DSN:

DSN information is stored in different locations depending on your platform and environment.

You can specify the parameters in the following tables for Connector/ODBC when configuring a DSN:

Users on Windows can use the Options and Advanced panels when configuring a DSN to set these parameters; see the table for information on which options relate to which fields and check boxes. On Unix and Mac OS X, use the parameter name and value as the keyword/value pair in the DSN configuration. Alternatively, you can set these parameters within the InConnectionString argument in the SQLDriverConnect() call.

The option argument is used to tell Connector/ODBC that the client is not 100% ODBC-compliant. On Windows, you normally select options by toggling the check boxes 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 Connector/ODBC connect screen.

To select multiple options, add together their values, using the numbers from the Constant Value column in the table.

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

The ODBC Data Source Administrator within Windows lets you create DSNs, check driver installation and configure ODBC systems such as tracing (used for debugging) and connection pooling.

Different editions and versions of Windows store the ODBC Data Source Administrator in different locations depending on the version of Windows that you are using.

To open the ODBC Data Source Administrator in Windows Server 2003:

To open the ODBC Data Source Administrator in Windows 2000 Server or Windows 2000 Professional:

To open the ODBC Data Source Administrator on Windows XP:

Irrespective of your Windows version, you should be presented the ODBC Data Source Administrator window:

Within Windows XP, you can add the Administrative Tools folder to your Start menu to make it easier to locate the ODBC Data Source Administrator. To do this:

Within both Windows Server 2003 and Windows XP, consider permanently adding the ODBC Data Source Administrator to your Start menu. To do this, locate the Data Sources (ODBC) icon using the methods shown, then right-click on the icon and then choose Pin to Start Menu.

The interfaces for the 3.51 and 5.x versions of the Connector/ODBC driver are different, although the fields and information that you need to enter remain the same.

To configure a DSN using Connector/ODBC 5.2, see Section 22.1.4.3.1, "Configuring a Connector/ODBC 5.x DSN on Windows".

22.1.4.3.1. Configuring a Connector/ODBC 5.x DSN on Windows

Due to the native Unicode support within Connector/ODBC, you do not need to specify the initial character set to be used with your connection.

To add or configure a Connector/ODBC 5.x DSN on Windows, use either the command-line, or the ODBC Data Source Administrator GUI.

1. Open the ODBC Data Source Administrator.

2. To create a System DSN (which will be available to all users), select the System DSN tab. To create a User DSN, which will be unique only to the current user, click the Add... button.

3. Select the ODBC driver for this DSN.

   

   Select MySQL ODBC 5.x Driver for the appropriate level of Connector/ODBC, then click Finish.

4. You now need to configure the specific fields for the DSN you are creating through the Connection Parameters dialog.

   

   In the Data Source Name box, enter the name of the data source to access. It can be any valid name that you choose.

5. In the Description box, enter some text to help identify the connection.

6. In the Server field, enter the name of the MySQL server host to access. By default, it is localhost.

7. In the User field, enter the user name to use for this connection.

8. In the Password field, enter the corresponding password for this connection.

9. The Database pop-up should automatically populate with the list of databases that the user has permissions to access.

10. To communicate over a different TCP/IP port than the default (3306), change the value of the Port.

11. Click OK to save the DSN.

To verify the connection using the parameters you have entered, click the Test button. If the connection could be made successfully, you will be notified with a Success; connection was made! dialog.

You can configure a number of options for a specific DSN by using the Details button.

The Details button opens a tabbed display where you set additional options:

• Flags 1, Flags 2, and Flags 3 enable you to select the additional flags for the DSN connection. For more information on these flags, see Section 22.1.4.2, "Connector/ODBC Connection Parameters".

• Debug lets you turn on ODBC debugging to record the queries you execute through the DSN to the myodbc.sql file. For more information, see Section 22.1.4.8, "Getting an ODBC Trace File".

• SSL Settings configures the additional options required for using the Secure Sockets Layer (SSL) when communicating with MySQL server. Note that you must also enable SSL and configure the MySQL server with suitable certificates to communicate over SSL.

  

The Advanced tab lets you configure Connector/ODBC connection parameters. Refer to Section 22.1.4.2, "Connector/ODBC Connection Parameters", for information about the meaning of these options.

To configure a DSN on Mac OS X, you can either use the command-line utility (myodbc-installer with connector/ODBC 5.1, or myodbc3i with connector/OCBC 3.51), edit the odbc.ini file within the Library/ODBC directory of the user, or use the ODBC Administrator GUI. If you have Mac OS X 10.2 or earlier, refer to Section 22.1.4.5, "Configuring a Connector/ODBC DSN on Unix". Select whether to create a User DSN or a System DSN. When adding a System DSN, you might need to authenticate with the system. Click the padlock and enter a user and password with administrator privileges.

For correct operation of ODBC Administrator, ensure that the /Library/ODBC/odbc.ini file used to set up ODBC connectivity and DSNs are writable by the admin group. If this file is not writable by this group, then the ODBC Administrator may fail, or may appear to work but not generate the correct entry.

To create a DSN using the myodbc3i utility, you need only specify the DSN type and the DSN connection string. For example:

shell> myodbc3i -a -s -t"DSN=mydb;DRIVER=MySQL ODBC 3.51 Driver;SERVER=mysql;USER=username;PASSWORD=pass"

To use ODBC Administrator:

A completed DSN configuration may look like this:

You can configure other ODBC options in your DSN by adding further keyword/value pairs and setting the corresponding values. See Section 22.1.4.2, "Connector/ODBC Connection Parameters".

On Unix, you configure DSN entries directly in the odbc.ini file. Here is a typical odbc.ini file that configures myodbc3 as the DSN name for Connector/ODBC 3.51:

;  odbc.ini configuration for Connector/ODBC and Connector/ODBC 3.51 drivers;[ODBC Data Sources]myodbc3 = MyODBC 3.51 Driver DSN[myodbc3]Driver   = /usr/local/lib/libmyodbc3.soDescription  = Connector/ODBC 3.51 Driver DSNSERVER   = localhostPORT =USER = rootPassword =Database = testOPTION   = 3SOCKET   =[Default]Driver   = /usr/local/lib/libmyodbc3.soDescription  = Connector/ODBC 3.51 Driver DSNSERVER   = localhostPORT =USER = rootPassword =Database = testOPTION   = 3SOCKET   =

Refer to the Section 22.1.4.2, "Connector/ODBC Connection Parameters", for the list of connection parameters that can be supplied.

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.iniexport ODBCSYSINI=/usr/local/etc

You can connect to the MySQL server using SQLDriverConnect, by specifying the DRIVER name field. Here are the connection strings for Connector/ODBC using DSN-Less connections:

For Connector/ODBC 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;"

Note. On Mac OS X, you might need to specify the full path to the Connector/ODBC driver library.

Refer to Section 22.1.4.2, "Connector/ODBC Connection Parameters" for the list of connection parameters that can be supplied.

Connection pooling enables the ODBC driver to re-use existing connections to a given database from a pool of connections, instead of opening a new connection each time the database is accessed. By enabling connection pooling you can improve the overall performance of your application by lowering the time taken to open a connection to a database in the connection pool.

For more information about connection pooling: http://support.microsoft.com/default.aspx?scid=kb;EN-US;q169470.

If you encounter difficulties or problems with Connector/ODBC, start by making a log file from the ODBC Manager and Connector/ODBC. This is called tracing, and is enabled through the ODBC Manager. The procedure for this differs for Windows, Mac OS X and Unix.

Interacting with a MySQL server from an applications using the Connector/ODBC typically involves the following operations:

Most applications use some variation of these steps. The basic application steps are shown in the following diagram:

A typical situation where you would install Connector/ODBC is to access a database on a Linux or Unix host from a Windows machine.

As an example of the process required to set up access between two machines, the steps below take you through the basic steps. These instructions assume that you connect to system ALPHA from system BETA with a user name and password of myuser and mypassword.

On system ALPHA (the MySQL server) follow these steps:

On system BETA (the Connector/ODBC client), follow these steps:

Once you have configured your Connector/ODBC DSN, you can access your MySQL database through any application that supports the ODBC interface, including programming languages and third-party applications. This section contains guides and help on using Connector/ODBC with various ODBC-compatible tools and applications, including Microsoft Word, Microsoft Excel and Adobe/Macromedia ColdFusion.

Connector/ODBC has been tested with the following applications:

If you know of any other applications that work with Connector/ODBC, please send mail to <[email protected]> about them.

You can use MySQL database with Microsoft Access using Connector/ODBC. The MySQL database can be used as an import source, an export source, or as a linked table for direct use within an Access application, so you can use Access as the front-end interface to a MySQL database.

You can use Microsoft Word and Microsoft Excel to access information from a MySQL database using Connector/ODBC. Within Microsoft Word, this facility is most useful when importing data for mailmerge, or for tables and data to be included in reports. Within Microsoft Excel, you can execute queries on your MySQL server and import the data directly into an Excel Worksheet, presenting the data as a series of rows and columns.

With both applications, data is accessed and imported into the application using Microsoft Query, which lets you execute a query though an ODBC source. You use Microsoft Query to build the SQL statement to be executed, selecting the tables, fields, selection criteria and sort order. For example, to insert information from a table in the World test database into an Excel spreadsheet, using the DSN samples shown in Section 22.1.4, "Configuring Connector/ODBC":

The same process can be used to import data into a Word document, where the data will be inserted as a table. This can be used for mail merge purposes (where the field data is read from a Word table), or where you want to include data and reports within a report or other document.

Crystal Reports can use an ODBC DSN to connect to a database from which you to extract data and information for reporting purposes.

For example, to create a simple crosstab report within Crystal Reports XI, follow these steps:

Once the ODBC connection has been opened within Crystal Reports, you can browse and add any fields within the available tables into your reports.

With a suitable ODBC Manager and the Connector/ODBC driver installed, any programming language or environment that can support ODBC can connect to a MySQL database through Connector/ODBC.

This includes, but is not limited to, Microsoft support languages (including Visual Basic, C# and interfaces such as ODBC.NET), Perl (through the DBI module, and the DBD::ODBC driver).

Private Sub myodbc_ado_Click()Dim conn As ADODB.ConnectionDim rs As ADODB.RecordsetDim fld As ADODB.FieldDim sql As String'connect to MySQL server using Connector/ODBCSet conn = New ADODB.Connectionconn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};"_& "SERVER=localhost;"_& " DATABASE=test;"_& "UID=venu;PWD=venu; OPTION=3"conn.Open'create tableconn.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 insertconn.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.Recordsetrs.CursorLocation = adUseServer'fetch the initial table ..rs.Open "SELECT * FROM my_ado", connDebug.Print rs.RecordCountrs.MoveFirstDebug.Print String(50, "-") & "Initial my_ado Result Set " & String(50, "-")For Each fld In rs.FieldsDebug.Print fld.Name,NextDebug.PrintDo Until rs.EOFFor Each fld In rs.FieldsDebug.Print fld.Value,Nextrs.MoveNextDebug.PrintLooprs.Close'rs insertrs.Open "select * from my_ado", conn, adOpenDynamic, adLockOptimisticrs.AddNewrs!Name = "Monty"rs!txt = "Insert row"rs.Updaters.Close'rs updaters.Open "SELECT * FROM my_ado"rs!Name = "update"rs!txt = "updated-row"rs.Updaters.Close'rs update second time..rs.Open "SELECT * FROM my_ado"rs!Name = "update"rs!txt = "updated-second-time"rs.Updaters.Close'rs deleters.Open "SELECT * FROM my_ado"rs.MoveNextrs.MoveNextrs.Deleters.Close'fetch the updated table ..rs.Open "SELECT * FROM my_ado", connDebug.Print rs.RecordCountrs.MoveFirstDebug.Print String(50, "-") & "Updated my_ado Result Set " & String(50, "-")For Each fld In rs.FieldsDebug.Print fld.Name,NextDebug.PrintDo Until rs.EOFFor Each fld In rs.FieldsDebug.Print fld.Value,Nextrs.MoveNextDebug.PrintLooprs.Closeconn.CloseEnd Sub

Private Sub myodbc_dao_Click()Dim ws As WorkspaceDim conn As ConnectionDim queryDef As queryDefDim str As String'connect to MySQL using MySQL ODBC 3.51 DriverSet 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_daoSet queryDef = conn.CreateQueryDef("", "drop table if exists my_dao")queryDef.ExecuteSet 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.addNewSet rs = conn.OpenRecordset("my_dao")Dim i As IntegerFor i = 10 To 15rs.AddNewrs!Name = "insert record" & irs!Id2 = irs.UpdateNext irs.Close'rs update..Set rs = conn.OpenRecordset("my_dao")rs.Editrs!Name = "updated-string"rs.Updaters.Close'fetch the table back...Set rs = conn.OpenRecordset("my_dao", dbOpenDynamic)str = "Results:"rs.MoveFirstWhile Not rs.EOFstr = " " & rs!Id & " , " & rs!Name & ", " & rs!Ts & ", " & rs!Id2Debug.Print "DATA:" & strrs.MoveNextWend'rs Scrollingrs.MoveFirststr = " FIRST ROW: " & rs!Id & " , " & rs!Name & ", " & rs!Ts & ", " & rs!Id2Debug.Print strrs.MoveLaststr = " LAST ROW: " & rs!Id & " , " & rs!Name & ", " & rs!Ts & ", " & rs!Id2Debug.Print strrs.MovePreviousstr = " LAST-1 ROW: " & rs!Id & " , " & rs!Name & ", " & rs!Ts & ", " & rs!Id2Debug.Print str'free all resourcesrs.ClosequeryDef.Closeconn.Closews.CloseEnd Sub

Dim rs As rdoResultsetDim cn As New rdoConnectionDim cl As rdoColumnDim 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 = rdUseOdbccn.EstablishConnection rdDriverPrompt'drop table my_rdoSQL = "drop table if exists my_rdo"cn.Execute SQL, rdExecDirect'create table my_rdoSQL = "create table my_rdo(id int, name varchar(20))"cn.Execute SQL, rdExecDirect'insert - directSQL = "insert into my_rdo values (100,'venu')"cn.Execute SQL, rdExecDirectSQL = "insert into my_rdo values (200,'MySQL')"cn.Execute SQL, rdExecDirect'rs insertSQL = "select * from my_rdo"Set rs = cn.OpenResultset(SQL, rdOpenStatic, rdConcurRowVer, rdExecDirect)rs.AddNewrs!id = 300rs!Name = "Insert1"rs.Updaters.Close'rs insertSQL = "select * from my_rdo"Set rs = cn.OpenResultset(SQL, rdOpenStatic, rdConcurRowVer, rdExecDirect)rs.AddNewrs!id = 400rs!Name = "Insert 2"rs.Updaters.Close'rs updateSQL = "select * from my_rdo"Set rs = cn.OpenResultset(SQL, rdOpenStatic, rdConcurRowVer, rdExecDirect)rs.Editrs!id = 999rs!Name = "updated"rs.Updaters.Close'fetch back...SQL = "select * from my_rdo"Set rs = cn.OpenResultset(SQL, rdOpenStatic, rdConcurRowVer, rdExecDirect)Do Until rs.EOFFor Each cl In rs.rdoColumnsDebug.Print cl.Value,Nextrs.MoveNextDebug.PrintLoopDebug.Print "Row count="; rs.RowCount'closers.Closecn.CloseEnd Sub

/** * @sample : mycon.cs * @purpose   : Demo sample for ODBC.NET using Connector/ODBC * @author : Venu, <[email protected]> * * (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 Connector/ODBC 3.51  string MyConString = "DRIVER={MySQL ODBC 3.51 Driver};" + "SERVER=localhost;" + "DATABASE=test;" + "UID=venu;" + "PASSWORD=venu;" + "OPTION=3";  //Connect to MySQL using Connector/ODBC  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) { //Supported only by Connector/ODBC 3.51 Console.WriteLine("Data:" + MyDataReader.GetInt32(0) + " " +  MyDataReader.GetString(1) + " " +  MyDataReader.GetInt64(2));  }  else { //BIGINTs not supported by Connector/ODBC Console.WriteLine("Data:" + MyDataReader.GetInt32(0) + " " +  MyDataReader.GetString(1) + " " +  MyDataReader.GetInt32(2));  } }  //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"); } } }  }}

' @sample : myvb.vb' @purpose   : Demo sample for ODBC.NET using Connector/ODBC' @author : Venu, <[email protected]>'' (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.OdbcImports SystemModule myvb  Sub Main() Try  'Connector/ODBC 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

This section summarizes ODBC routines, categorized by functionality.

For the complete ODBC API reference, please refer to the ODBC Programmer's Reference at http://msdn.microsoft.com/en-us/library/ms714177.aspx.

An application can call SQLGetInfo function to obtain conformance information about Connector/ODBC. To obtain information about support for a specific function in the driver, an application can call SQLGetFunctions.

The following tables list Connector/ODBC API calls grouped by task:

The following table illustrates how Connector/ODBC maps the server data types to default SQL and C data types.

The following tables lists the error codes returned by Connector/ODBC apart from the server errors.

This section provides help with common queries and areas of functionality in MySQL and how to use them with Connector/ODBC.

INSERT INTO tbl (auto,text) VALUES(NULL,'text');SELECT LAST_INSERT_ID();

INSERT INTO tbl (auto,text) VALUES(NULL,'text');INSERT INTO tbl2 (id,text) VALUES(LAST_INSERT_ID(),'text');

SELECT * FROM tbl WHERE auto IS NULL;

Most programs should work with Connector/ODBC, but for each of those listed here, there are specific notes and tips to improve or enhance the way you work with Connector/ODBC and these applications.

With all applications, ensure that you are using the latest Connector/ODBC drivers, ODBC Manager and any supporting libraries and interfaces used by your application. For example, on Windows, using the latest version of Microsoft Data Access Components (MDAC) will improve the compatibility with ODBC in general, and with the Connector/ODBC driver.

Dim myconn As New ADODB.ConnectionDim myrs As New RecordsetDim mySQL As StringDim myrows As Longmyconn.Open "DSN=MyODBCsample"mySQL = "SELECT * from user"myrs.Source = mySQLSet myrs.ActiveConnection = myconnmyrs.CursorLocation = adUseClientmyrs.Openmyrows = myrs.RecordCountmyrs.Closemyconn.Close

fReg:= TRegistry.Create;fReg.OpenKey('\Software\ODBC\ODBC.INI\DocumentsFab', True);fReg.WriteString('Database', 'Documents');fReg.WriteString('Description', ' ');fReg.WriteString('Driver', 'C:\WINNT\System32\myodbc.dll');fReg.WriteString('Flag', '1');fReg.WriteString('Password', '');fReg.WriteString('Port', ' ');fReg.WriteString('Server', 'xmark');fReg.WriteString('User', 'winuser');fReg.OpenKey('\Software\ODBC\ODBC.INI\ODBC Data Sources', True);fReg.WriteString('DocumentsFab', 'MySQL');fReg.CloseKey;fReg.Free;Memo1.Lines.Add('DATABASE NAME=');Memo1.Lines.Add('USER NAME=');Memo1.Lines.Add('ODBC DSN=DocumentsFab');Memo1.Lines.Add('OPEN MODE=READ/WRITE');Memo1.Lines.Add('BATCH COUNT=200');Memo1.Lines.Add('LANGDRIVER=');Memo1.Lines.Add('MAX ROWS=-1');Memo1.Lines.Add('SCHEMA CACHE DIR=');Memo1.Lines.Add('SCHEMA CACHE SIZE=8');Memo1.Lines.Add('SCHEMA CACHE TIME=-1');Memo1.Lines.Add('SQLPASSTHRU MODE=SHARED AUTOCOMMIT');Memo1.Lines.Add('SQLQRYMODE=');Memo1.Lines.Add('ENABLE SCHEMA CACHE=FALSE');Memo1.Lines.Add('ENABLE BCD=FALSE');Memo1.Lines.Add('ROWSET SIZE=20');Memo1.Lines.Add('BLOBS TO CACHE=64');Memo1.Lines.Add('BLOB SIZE=32');AliasEditor.Add('DocumentsFab','MySQL',Memo1.Lines);

The following section details some common errors and their suggested fix or alternative solution. If you are still experiencing problems, use the Connector/ODBC mailing list; see Section 22.1.8.1, "Connector/ODBC Community Support".

Many problems can be resolved by upgrading your Connector/ODBC drivers to the latest available release. On Windows, make sure that you have the latest versions of the Microsoft Data Access Components (MDAC) installed.

64-Bit Windows and ODBC Data Source Administrator

I have installed Connector/ODBC on Windows XP x64 Edition or Windows Server 2003 R2 x64. The installation completed successfully, but the Connector/ODBC driver does not appear in ODBC Data Source Administrator.

This is not a bug, but is related to the way Windows x64 editions operate with the ODBC driver. On Windows x64 editions, the Connector/ODBC driver is installed in the %SystemRoot%\SysWOW64 folder. However, the default ODBC Data Source Administrator that is available through the Administrative Tools or Control Panel in Windows x64 Editions is located in the %SystemRoot%\system32 folder, and only searches this folder for ODBC drivers.

On Windows x64 editions, use the ODBC administration tool located at %SystemRoot%\SysWOW64\odbcad32.exe, this will correctly locate the installed Connector/ODBC drivers and enable you to create a Connector/ODBC DSN.

This issue was originally reported as Bug #20301.

Error 10061 (Cannot connect to server)

When connecting or using the Test button in ODBC Data Source Administrator I get error 10061 (Cannot connect to server)

This error can be raised by a number of different issues, including server problems, network problems, and firewall and port blocking problems. For more information, see Section C.5.2.2, "Can't connect to [local] MySQL server".

"Transactions are not enabled" Error

The following error is reported when using transactions: Transactions are not enabled

This error indicates that you are trying to use transactions with a MySQL table that does not support transactions. Transactions are supported within MySQL when using the InnoDB database engine, which is the default storage engine in MySQL 5.5 and higher. In versions of MySQL before MySQL 5.1, you may also use the BDB engine.

Check the following before continuing:

#DELETED# Records Reported by Access

Access reports records as #DELETED# when inserting or updating records in linked tables.

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

Write Conflicts or Row Location Errors

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 changedsince it was last read.

Importing from Access 97

Exporting data from Access 97 to MySQL reports a Syntax Error.

This error is specific to Access 97 and versions of Connector/ODBC earlier than 3.51.02. Update to the latest version of the Connector/ODBC driver to resolve this problem.

Importing from Microsoft DTS

Exporting data from Microsoft DTS to MySQL reports a Syntax Error.

This error occurs only with MySQL tables using the TEXT or VARCHAR data types. You can fix this error by upgrading your Connector/ODBC driver to version 3.51.02 or higher.

SQL_NO_DATA Exception from ODBC.NET

Using ODBC.NET with Connector/ODBC, while fetching empty string (0 length), it starts giving the SQL_NO_DATA exception.

You can get the patch that addresses this problem from http://support.microsoft.com/default.aspx?scid=kb;EN-US;q319243.

Error with SELECT COUNT(*)

Using SELECT COUNT(*) FROM tbl_name within Visual Basic and ASP returns an error.

This error occurs because the COUNT(*) expression is returning a BIGINT, and ADO cannot make sense of a number this big. Select the Change BIGINT columns to INT option (option value 16384).

Multiple-Step Operation Error

Using the AppendChunk() or GetChunk() ADO methods, the Multiple-step operation generated errors. Check each status value error is returned.

The GetChunk() and AppendChunk() methods from ADO do not 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

Modified Record Error

Access returns Another user had modified the record that you have modified while editing records on a Linked Table.

In most cases, this can be solved by doing one of the following things:

If these strategies do not help, start by making a log file from the ODBC manager (the log you get when requesting logs from ODBCADMIN) and a Connector/ODBC log to help you figure out why things go wrong. For instructions, see Section 22.1.4.8, "Getting an ODBC Trace File".

Direct Application Linking Under Unix or Linux

When linking an application directly to the Connector/ODBC library under Unix or Linux, the application crashes.

Connector/ODBC under Unix or Linux is not compatible with direct application linking. To connect to an ODBC source, use a driver manager, such as iODBC or unixODBC.

Microsoft Office and DATE or TIMESTAMP Columns

Applications in the Microsoft Office suite cannot update tables that have DATE or TIMESTAMP columns.

This is a known issue with Connector/ODBC. Ensure that the field has a default value (rather than NULL) and that the default value is nonzero (that is, something other than 0000-00-00 00:00:00).

INFORMATION_SCHEMA Database

When connecting Connector/ODBC 5.x to a MySQL 4.x server, the error 1044 Access denied for user 'xxx'@'%' to database 'information_schema' is returned.

Connector/ODBC 5.x is designed to work with MySQL 5.0 or later, taking advantage of the INFORMATION_SCHEMA database to determine data definition information. Support for MySQL 4.1 is planned for the final release.

S1T00 Error

When calling SQLTables, the error S1T00 is returned, but I cannot find this in the list of error numbers for Connector/ODBC.

The S1T00 error indicates that a general timeout has occurred within the ODBC system and is not a MySQL error. Typically it indicates that the connection you are using is stale, the server is too busy to accept your request or that the server has gone away.

"Table does not exist" Error in Access 2000

When linking to tables in Access 2000 and generating links to tables programmatically, rather than through the table designer interface, you may get errors about tables not existing.

There is a known issue with a specific version of the msjet40.dll that exhibits this issue. The version affected is 4.0.9025.0. Reverting to an older version will enable you to create the links. If you have recently updated your version, check your WINDOWS directory for the older version of the file and copy it to the drivers directory.

Batched Statements

When I try to use batched statements, the execution of the batched statements fails.

Batched statement support was added in 3.51.18. Support for batched statements is not enabled by default. Enable option FLAG_MULTI_STATEMENTS, value 67108864, or select the Allow multiple statements flag within a GUI configuration.

Packet Errors with ADODB and Excel

When connecting to a MySQL server using ADODB and Excel, occasionally the application fails to communicate with the server and the error Got an error reading communication packets appears in the error log.

This error may be related to Keyboard Logger 1.1 from PanteraSoft.com, which is known to interfere with the network communication between MySQL Connector/ODBC and MySQL.

Outer Join Error

When using some applications to access a MySQL server using Connector/ODBC and outer joins, an error is reported regarding the Outer Join Escape Sequence.

This is a known issue with MySQL Connector/ODBC which is not correctly parsing the "Outer Join Escape Sequence", as per the specs at Microsoft ODBC Specs. Currently, Connector/ODBC will return a value > 0 when asked for SQL_OJ_CAPABILITIES even though no parsing takes place in the driver to handle the outer join escape sequence.

Hebrew/CJK Characters

I can correctly store extended characters in the database (Hebrew/CJK) using Connector/ODBC 5.1, but when I retrieve the data, the text is not formatted correctly and I get garbled characters.

When using ASP and UTF8 characters, add the following to your ASP files to ensure that the data returned is correctly encoded:

Response.CodePage = 65001Response.CharSet = "utf-8"

Duplicate Entry in Installed Programs List

I have a duplicate MySQL Connector/ODBC entry within my Installed Programs list, but I cannot delete one of them.

This problem can occur when you upgrade an existing Connector/ODBC installation, rather than removing and then installing the updated version.

Values Truncated to 255 Characters

When submitting queries with parameter binding using UPDATE, my field values are being truncated to 255 characters.

Ensure that the FLAG_BIG_PACKETS option is set for your connection. This removes the 255 character limitation on bound parameters.

Disabling Data-At-Execution

Is it possible to disable data-at-execution using a flag?

If you do not want to use data-at-execution, remove the corresponding calls. For example:

SQLLEN ylen = SQL_LEN_DATA_AT_EXEC(10);SQLBindCol(hstmt,2,SQL_C_BINARY, buf, 10, &ylen);

Would become:

SQLBindCol(hstmt,2,SQL_C_BINARY, buf, 10, NULL);

Note that in the call to SQLBindCol(), &ylen has been replaced by NULL.

For further information, refer to the MSDN documentation for SQLBindCol().

NULLABLE Attribute for AUTO_INCREMENT Columns

When you call SQLColumns() for a table column that is AUTO_INCREMENT, the NULLABLE column of the result set is always SQL_NULLABLE (1).

This is because MySQL reports the DEFAULT value for such a column as NULL. It means, if you insert a NULL value into the column, you will get the next integer value for the table's auto_increment counter.

Oracle provides assistance to the user community by means of its mailing lists. For Connector/ODBC-related issues, you can get help from experienced users by using the <[email protected]> mailing list. Archives are available online at http://lists.mysql.com/myodbc.

For information about subscribing to MySQL mailing lists or to browse list archives, visit http://lists.mysql.com/. See Section 1.6.1, "MySQL Mailing Lists".

Community support from experienced users is also available through the ODBC Forum. You may also find help from other users in the other MySQL Forums, located at http://forums.mysql.com. See Section 1.6.2, "MySQL Community Support at the MySQL Forums".

If you encounter difficulties or problems with Connector/ODBC, start by making a log file from the ODBC Manager (the log you get when requesting logs from ODBC ADMIN) and Connector/ODBC. The procedure for doing this is described in Section 22.1.4.8, "Getting an ODBC Trace File".

Check the Connector/ODBC trace file to find out what could be wrong. Determine what statements were issued by searching for the string >mysql_real_query in the myodbc.log file.

Also, try issuing the statements from the mysql client program or from admndemo. This helps you determine whether the error is in Connector/ODBC 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.6.1, "MySQL Mailing Lists". Please never send the whole Connector/ODBC or ODBC log file!

Ideally, include the following information with the email:

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 http://lists.mysql.com/myodbc.

If you are unable to find out what is wrong, the last option is to create an archive in tar or zip format that contains a Connector/ODBC trace file, the ODBC log file, and a README file that explains the problem. Initiate a bug report for our bugs database at http://bugs.mysql.com/, then click the Files tab in the bug report for instructions on uploading the archive to the bugs database. Only MySQL engineers have 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 another SQL server, include an ODBC log file where you perform exactly the same SQL statements so that we can compare the results between the two systems.

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

You can send a patch or suggest a better solution for any existing code or problems by sending a mail message to <[email protected]>.