Chapter 21. 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 21.1.6.1, “Connector/ODBC API Reference”. For general information about ODBC, see http://support.microsoft.com/kb/110093.

21.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 21.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 root
shell> gunzip mysql-connector-odbc-5.2.2-i686-pc-linux.tar.gz
shell> tar xvf mysql-connector-odbc-5.2.2-i686-pc-linux.tar
shell> cd mysql-connector-odbc-5.2.2-i686-pc-linux

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

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

shell> su root
shell> 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 21.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 Release
C:\> 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=Debug
C:\> devenv.com MySQL_Connector_ODBC.sln /build Debug

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

See Section 21.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.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 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 .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

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

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 myodbc
shell> aclocal
shell> autoheader
shell> libtoolize -c -f
shell> autoconf
shell> automake;
shell> ./configure  # Add your favorite options here
shell> 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 21.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 21.1.4.3.1, “Configuring a Connector/ODBC 5.x DSN on Windows”.

21.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 21.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 21.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 21.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 21.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 21.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.so
Description  = Connector/ODBC 3.51 Driver DSN
SERVER       = localhost
PORT         =
USER         = root
Password     =
Database     = test
OPTION       = 3
SOCKET       =

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

Refer to the Section 21.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.ini
export 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 21.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 21.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.Connection
Dim rs As ADODB.Recordset
Dim fld As ADODB.Field
Dim sql As String

'connect to MySQL server using Connector/ODBC
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

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

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

/**
 * @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.Odbc
Imports System

Module 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.Connection
Dim myrs As New Recordset
Dim mySQL As String
Dim myrows As Long

myconn.Open "DSN=MyODBCsample"
mySQL = "SELECT * from user"
myrs.Source = mySQL
Set myrs.ActiveConnection = myconn
myrs.CursorLocation = adUseClient
myrs.Open
myrows = myrs.RecordCount

myrs.Close
myconn.Close

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 21.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 changed
since 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 21.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 = 65001
Response.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 21.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. You can send this to ftp://ftp.mysql.com/pub/mysql/upload/. 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]>.

The Connector/ODBC Change History (Changelog) is located with the main Changelog for MySQL. See Section D.2, “MySQL Connector/ODBC (MyODBC) Change History”.

On Windows, you can install either through a binary installation process or by downloading a zip file with the Connector/Net components.

Before installing, ensure that your system is up to date, including installing the latest version of the .NET Framework.

C:\> msiexec /package connector-net.msi /quiet

There is no installer available for installing the Connector/Net component on your Unix installation. Before installing, ensure that you have a working Mono project installation. To test whether your system has Mono installed, enter:

shell> mono --version

The version of the Mono JIT compiler is displayed.

To compile C# source code, make sure a Mono C# compiler is installed. Note that there are two Mono C# compilers available, mcs, which accesses the 1.0-profile libraries, and gmcs, which accesses the 2.0-profile libraries.

To install Connector/Net on Unix/Mono:

You are now ready to compile your application. You must ensure that when you compile your application you include the Connector/Net component using the -r: command-line option. For example:

shell> gmcs -r:System.dll -r:System.Data.dll -r:MySql.Data.dll HelloWorld.cs

Note, the assemblies that are referenced depend on the requirements of the application, but applications using Connector/Net must provide -r:MySql.Data as a minimum.

You can further check your installation by running the compiled program, for example:

shell> mono HelloWorld.exe

Obtaining the Source Code

To obtain the most recent development source tree, first download and install Bazaar. You can obtain Bazaar from the Bazaar VCS Website. 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.

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/Net sources, change to the directory where you want the copy of the Connector/Net tree to be stored, then use the following command:

shell> bzr branch lp:connectornet/trunk

To download a specific version of Connector/Net, specify the version number instead of trunk. For example, to obtain a copy of the 6.0 version of the source tree:

shell> bzr branch lp:connectornet/6.0

Source packages are also available on the downloads page.

Building the Source Code on Windows

The following procedure can be used to build the connector on Microsoft Windows.

Building the Source Code on Unix

Support for building Connector/Net on Mono/Unix is currently not available.

Once the connector is installed, you can use it to create, modify, and delete connections to MySQL databases. To create a connection with a MySQL database, perform the following steps:

After the connection is successfully established, all settings are saved for future use. When you start Visual Studio for the next time, open the connection node in Server Explorer to establish a connection to the MySQL server again.

To modify and delete a connection, use the Server Explorer context menu for the corresponding node. You can modify any of the settings by overwriting the existing values with new ones. Note that the connection may be modified or deleted only if no active editor for its objects is opened: otherwise, you may lose your data.

IntelliSense support is available starting in Connector/Net 6.5. Once you have established a connection, for example, using the Connect to MySql toolbar button, you can get autocompletion as you type, or by pressing Control+J. Depending on the context, the autocompletion dialog can show the list of available tables, table columns, or stored procedures (with the routine's signature as a tooltip). Typing some characters before pressing Control+J filters the choices to those items starting with that prefix.

Connector/Net contains a table editor, which enables the visual creation and modification of tables.

The Table Designer can be accessed through a mouse action on table-type node of Server Explorer. To create a new table, right-click the Tables node (under the connection node) and choose the Create Table command from the context menu.

To modify an existing table, double-click the node of the table to modify, or right-click this node and choose the Design item from the context menu. Either of the commands opens the Table Designer.

The table editor is implemented in the manner of the well-known Query Browser Table Editor, but with minor differences.

Figure 21.5. Editing New Table

Table Designer consists of the following parts:

Each of these areas is discussed in more detail in subsequent sections.

To save changes you have made in the Table Designer, use either Save or Save All button of the Visual Studio main toolbar, or press Control+S. If you have not already named the table, you will be prompted to do so.

Figure 21.6. Choose Table Name

Once the table is created, you can view it in the Server Explorer.

Figure 21.7. Newly Created Table

The Table Designer main menu lets you set a primary key column, edit relationships such as foreign keys, and create indexes.

Figure 21.8. Table Designer Main Menu

21.2.3.3.2. Editing Indexes

Indexes management is performed using the Indexes/Keys dialog.

To add an index, select Table Designer, Indexes/Keys... from the main menu, and click Add to add a new index. You can then set the index name, index kind, index type, and a set of index columns.

Figure 21.9. Indexes Dialog

To remove an index, select it in the list box on the left, and click the Delete button.

To change index settings, select the needed index in the list box on the left. The detailed information about the index is displayed in the panel on the right hand side. Change the desired values.

21.2.3.3.3. Editing Foreign Keys

You manage foreign keys for InnoDB tables using the Foreign Key Relationships dialog.

To add a foreign key, select Table Designer, Relationships... from the main menu. This displays the Foreign Key Relationship dialog. Click Add. You can then set the foreign key name, referenced table name, foreign key columns, and actions upon update and delete.

To remove a foreign key, select it in the list box on the left, and click the Delete button.

To change foreign key settings, select the required foreign key in the list box on the left. The detailed information about the foreign key is displayed in the right hand panel. Change the desired values.

Figure 21.10. Foreign Key Relationships Dialog

21.2.3.3.5. Table Properties

To bring up Table Properties select the table and right-click to activate the context menu. Select Properties. The Table Properties dockable window will be displayed.

Figure 21.11. Table Properties Menu Item

The following table properties can be set:

• Auto Increment.

• Average Row Length.

• Character Set.

• Collation.

• Comment.

• Data Directory.

• Index Directory.

• Maximum Rows.

• Minimum Rows.

• Name.

• Row Format.

• Schema.

• Storage Engine. Note that in MySQL 5.5 and higher, the default storage engine for new tables is InnoDB. See Section 14.2.1.1, “InnoDB as the Default MySQL Storage Engine” for more information about the choice of storage engine, and considerations if you convert existing tables to InnoDB.

The property Schema is read-only.

Figure 21.12. Table Properties

To create a new view, right-click the Views node under the connection node in Server Explorer. From the node's context menu, choose the Create View command. This command opens the SQL Editor.

Figure 21.13. Editing View SQL

You can then enter the SQL for your view.

Figure 21.14. View SQL Added

To modify an existing view, double-click a node of the view to modify, or right-click this node and choose the Alter View command from a context menu. Either of the commands opens the SQL Editor.

All other view properties can be set in the Properties window. These properties are:

Some of these properties can have arbitrary text values, others accept values from a predefined set. In the latter case, set the desired value with an embedded combobox.

The properties Is Updatable and Schema are readonly.

To save changes you have made, use either Save or Save All buttons of the Visual Studio main toolbar, or press Control+S.

Figure 21.15. View SQL Saved

To create a new stored procedure, right-click the Stored Procedures node under the connection node in Server Explorer. From the node's context menu, choose the Create Routine command. This command opens the SQL Editor.

Figure 21.16. Edit Stored Procedure SQL

To create a new stored function, right-click the Functions node under the connection node in Server Explorer. From the node's context menu, choose the Create Routine command.

To modify an existing stored routine (procedure or function), double-click the node of the routine to modify, or right-click this node and choose the Alter Routine command from the context menu. Either of the commands opens the SQL Editor.

To create or alter the routine definition using SQL Editor, type this definition in the SQL Editor using standard SQL. All other routine properties can be set in the Properties window. These properties are:

Some of these properties can have arbitrary text values, others accept values from a predefined set. In the latter case, set the desired value using the embedded combo box.

You can also set all the options directly in the SQL Editor, using the standard CREATE PROCEDURE or CREATE FUNCTION statement. However, it is recommended to use the Properties window instead.

To save changes you have made, use either Save or Save All buttons of the Visual Studio main toolbar, or press Control+S.

Figure 21.17. Stored Procedure SQL Saved

To observe the runtime behavior of a stored routine and debug any problems, use the Stored Procedure Debugger (available in Connector/Net 6.6 and higher). See Section 21.2.3.8, “Debugging Stored Procedures and Functions” for details.

To create a new trigger, right-click the node of the table in which to add the trigger. From the node's context menu, choose the Create Trigger command. This command opens the SQL Editor.

To modify an existing trigger, double-click the node of the trigger to modify, or right-click this node and choose the Alter Trigger command from the context menu. Either of the commands opens the SQL Editor.

To create or alter the trigger definition using SQL Editor, type the trigger statement in the SQL Editor using standard SQL.

All other trigger properties are set in the Properties window. These properties are:

Some of these properties can have arbitrary text values, others accept values from a predefined set. In the latter case, set the desired value using the embedded combo box.

The properties Event Table, Schema, and Server in the Properties window are read-only.

To save changes you have made, use either Save or Save All buttons of the Visual Studio main toolbar, or press Control+S. Before changes are saved, you will be asked to confirm the execution of the corresponding SQL query in a confirmation dialog.

To observe the runtime behavior of a stored routine and debug any problems, use the Stored Procedure Debugger (available in Connector/Net 6.6 and higher). See Section 21.2.3.8, “Debugging Stored Procedures and Functions” for details.

To create a new User Defined Function (UDF), right-click the UDFs node under the connection node in Server Explorer. From the node's context menu, choose the Create UDF command. This command opens the UDF Editor.

To modify an existing UDF, double-click the node of the UDF to modify, or right-click this node and choose the Alter UDF command from the context menu. Either of these commands opens the UDF Editor.

The UDF editor enables you to set the following properties:

There are text fields for both names, a combo box for the return type, and a check box to indicate if the UDF is aggregate. All these options are also accessible using the Properties window.

The property Server in the Properties window is read-only.

To save changes you have made, use either Save or Save All buttons of the Visual Studio main toolbar, or press Control+S. Before changes are saved, you will be asked to confirm the execution of the corresponding SQL query in a confirmation dialog.

To observe the runtime behavior of a stored routine and debug any problems, use the Stored Procedure Debugger (available in Connector/Net 6.6 and higher). See Section 21.2.3.8, “Debugging Stored Procedures and Functions” for details.

The stored procedure debugger, new in Connector/Net 6.6, provides facilities for setting breakpoints, stepping into individual statements (Step Into, Step Out, Step Over), evaluating and changing local variable values, evaluating breakpoints, and other typical debugging tasks.

Installing the Debugger

To enable the stored procedure debugger, install Connector/Net 6.6 or higher and choose the Complete option.

Privileges

The debugger recreates at the start of each debug session a serversidedebugger database in your server. This database helps to track the instrumented code and implement observability logic in the debugged routine. Your current connection needs to have privileges to create that database, and its associated stored routines, functions, and tables.

The debugger makes changes behind the scenes to temporarily add instrumentation code to the stored routines that you debug. You must have the ALTER ROUTINE privilege for each stored procedure, function, or trigger that you debug. (Including procedures and functions that are called, and triggers that are fired, by a procedure that you are debugging.)

Starting the Debugger

To start the debugger, follow these steps:

At this point, Visual Studio switches to debug mode, opening the source code of the routine being debugged in step mode, positioned on the first statement.

If the initial routine you debug has one or more arguments, a popup will show up with a grid (a row per each argument and three columns: one for the argument, one for the argument value (this is editable) and one for nullifying that argument value (a checkbox)). After setting up all the argument values, you can press OK to start the debug session, or Cancel to cancel the debug session.

Figure 21.20. Setting Arguments (1 of 2)

Figure 21.21. Setting Arguments (2 of 2)

How the Debugger Works

To have visibility into the internal workings of a stored routine, the debugger prepares a special version of the procedure, function, or trigger being debugged, instrumented with extra code to keep track of the current line being stepped into and the values of all the local variables. Any other stored procedures, functions, or triggers called from the routine being debugged are instrumented the same way. The debug versions of the routines are prepared for you automatically, and when the debug session ends (by either pressing F5 or Shift+F5), the original versions of the routines are automatically restored.

A copy of the original version of each instrumented routine (the version without instrumentation) is stored in the AppData\Roaming\MySqlDebuggerCache folder for the current Windows user (the path returned by calling System.Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) in .NET, plus appending MySqlDebuggerCache. There is one file for each instrumented routine, named routine_name.mysql. For example, in Windows 7, for a user named fergs, the path is C:\Users\fergs\AppData\Roaming\MySqlDebuggerCache.

Two threads are used, one for the debugger and one for the routine being debugged. The threads run in strict alternation, switching between the debugger and the routine as each statement is executed in the stored routine.

Basic Debugging Operations

The debugger has the same look and feel as the standard Visual Studio debuggers for C#, VB.NET or C++. In particular, the following are true:

Locals and Watches

Call Stack

Stepping

Breakpoints

Other Features

Limitations

Keyboard Shortcuts

The following list summarizes the keyboard shortcuts for debugging:

Tables, views, stored procedures, and functions can be cloned using the appropriate Clone command from the context menu: Clone Table, Clone View, Clone Routine. The clone commands open the corresponding editor for a new object: the Table Editor for cloning a table, and the SQL Editor for cloning a view or a routine.

The editor is filled with values of the original object. You can modify these values in the usual manner.

To save the cloned object, use either Save or Save All buttons of the Visual Studio main toolbar, or press Control+S. Before changes are saved, you will be asked to confirm the execution of the corresponding SQL query in a confirmation dialog.

Tables, views, stored routines, triggers, and UDFs can be dropped with the appropriate Drop command selected from its context menu: Drop Table, Drop View, Drop Routine, Drop Trigger, Drop UDF.

You will be asked to confirm the execution of the corresponding drop query in a confirmation dialog.

You can only drop a single object at a time.

Connector/Net 6.0 introduced support for the ADO.NET Entity Framework. ADO.NET Entity Framework was included with .NET Framework 3.5 Service Pack 1, and Visual Studio 2008 Service Pack 1. ADO.NET Entity Framework was released on 11th August 2008.

ADO.NET Entity Framework provides an Object Relational Mapping (ORM) service, mapping the relational database schema to objects. The ADO.NET Entity Framework defines several layers, these can be summarized as:

Connector/Net integrates with Visual Studio 2008 to provide a range of helpful tools to assist the developer.

A full treatment of ADO.NET Entity Framework is beyond the scope of this manual. If you are unfamiliar with ADO.NET, review the Microsoft ADO.NET Entity Framework documentation.

Tutorials on getting started with ADO.NET Entity Framework are available. See Section 21.2.4.5, “Tutorial: Using an Entity Framework Entity as a Windows Forms Data Source” and Section 21.2.4.6, “Tutorial: Databinding in ASP.NET using LINQ on Entities”.

MySQL Connector/Net 6.1 introduced the MySQL Website Configuration Tool. This is a facility available in Visual Studio that enables you to configure the Membership, Role, Session State and Profile Provider, without editing configuration files. You set your configuration options within the tool, and the tool modifies your web.config file accordingly.

The MySQL Website Configuration Tool appears as a small icon on the Solution Explorer toolbar in Visual Studio, as show by the following screenshot:

Figure 21.29. MySQL Website Configuration Tool

Clicking the Website Configuration Tool icon launches the wizard and displays the first screen:

Figure 21.30. MySQL Website Configuration Tool - Membership

This allows you to enable use of the MySQL Membership Provider. Click the check box to enable this. You can now enter the name of the application that you are creating the configuration for. You can also enter a description for the application.

You can then click the Edit... button to launch the Connection String Editor:

Figure 21.31. MySQL Website Configuration Tool - Connection String Editor

Note that if you have already defined a connection string for the providers manually in web.config, or previously using the tool, this will be automatically loaded and displayed, and can then be modified in this dialog.

You can also ensure that the necessary schemas are created automatically for you by selecting the Autogenerate Schema check box. These schemas are used to store membership information. The database used to storage is the one specified in the connection string.

You can also ensure that exceptions generated by the application will be written to the Windows event log by selecting the Write exceptions to event log check box.

Clicking the Advanced... button launches a dialog that enables you to set Membership Options. These options dictate such variables as password length required when a user signs up, whether the password is encrypted and whether the user can reset their password or not.

Figure 21.32. MySQL Website Configuration Tool - Advanced Options

Once information has been set up as required for configuration of the Membership Provider, the Next button can be clicked to display the Roles Provider screen:

Figure 21.33. MySQL Website Configuration Tool - Roles

Again the connection string can be edited, a description added and Autogenerate Schema can be enabled before clicking Next to go to the Profiles Provider screen:

Figure 21.34. MySQL Website Configuration Tool - Profiles

This screen display similar options to the previous screens.

Click Next to proceed to the Session State configuration page:

Figure 21.35. MySQL Website Configuration Tool - Session State

Once you have set up the Session State Provider as required, click Finish to exit the wizard.

At this point, select the Authentication Type to From Internet. Launch the ASP.NET Configuration Tool and select the Security tab. Click the Select authentication type link and ensure that the From the internet radio button is selected. You can now examine the database you created to store membership information. All the necessary tables will have been created for you:

Figure 21.36. MySQL Website Configuration Tool - Tables

MySQL Connector/Net 6.3.2 introduced a new MySQL SQL Editor. The easiest way to invoke the editor is by selecting the New, File menu item from the Visual Studio main menu. This displays the New File dialog:

Figure 21.37. MySQL SQL Editor - New File

From the New File dialog, select the MySQL template, and then double-click the MySQL SQL Script document, or click the Open button.

The MySQL SQL Editor will be displayed. You can now enter SQL code as required, or connect to a MySQL server. Click the Connect to MySQL button in the MySQL SQL Editor toolbar. You can enter the connection details into the Connect to MySQL dialog that is displayed. You can enter the server name, user ID, password and database to connect to, or click the Advanced button to select other connection string options. Click the Connect button to connect to the MySQL server. To execute your SQL code against the server, click the Run SQL button on the toolbar.

Figure 21.38. MySQL SQL Editor - Query

The results from any queries are displayed on the Results tab. Any errors are displayed on the Messages tab.

MySQL Connector/Net 6.3 introduced the ability to convert an Entity Framework model to MySQL DDL code. Starting with a blank model, you can develop an entity model in Visual Studio's designer. Once the model is created, you can select the model's properties, and in the Database Script Generation category of the model's properties, the property DDL Generation can be found. Select the value SSDLToMySQL.tt(VS) from the drop-down listbox.

Figure 21.39. DDL T4 Template Macro - Model Properties

Right-clicking the model design area displays a context-sensitive menu. Selecting Generate Database from Model from the menu displays the Generate Database Wizard. The wizard can then be used to generate MySQL DDL code.

Figure 21.40. DDL T4 Template Macro - Generate Database Wizard

This section provides a gentle introduction to programming with Connector/Net. The example code is written in C#, and is designed to work on both Microsoft .NET Framework and Mono.

This tutorial is designed to get you up and running with Connector/Net as quickly as possible, it does not go into detail on any particular topic. However, the following sections of this manual describe each of the topics introduced in this tutorial in more detail. In this tutorial you are encouraged to type in and run the code, modifying it as required for your setup.

This tutorial assumes you have MySQL and Connector/Net already installed. It also assumes that you have installed the World example database, which can be downloaded from the MySQL Documentation page. You can also find details on how to install the database on the same page.

using System;
using System.Data;

using MySql.Data;
using MySql.Data.MySqlClient;

public class Tutorial1
{
    public static void Main()
    {
        string connStr = "server=localhost;user=root;database=world;port=3306;password=******;";
        MySqlConnection conn = new MySqlConnection(connStr);
        try
        {
            Console.WriteLine("Connecting to MySQL...");
            conn.Open();
            // Perform database operations
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.ToString());
        }
        conn.Close();
        Console.WriteLine("Done.");
    }
}

using System;
using System.Data;

using MySql.Data;
using MySql.Data.MySqlClient;

public class Tutorial2
{
    public static void Main()
    {
        string connStr = "server=localhost;user=root;database=world;port=3306;password=******;";
        MySqlConnection conn = new MySqlConnection(connStr);
        try
        {
            Console.WriteLine("Connecting to MySQL...");
            conn.Open();

            string sql = "SELECT Name, HeadOfState FROM Country WHERE Continent='Oceania'";
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            MySqlDataReader rdr = cmd.ExecuteReader();

            while (rdr.Read())
            {
                Console.WriteLine(rdr[0]+" -- "+rdr[1]);
            }
            rdr.Close();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.ToString());
        }

        conn.Close();
        Console.WriteLine("Done.");
    }
}

using System;
using System.Data;

using MySql.Data;
using MySql.Data.MySqlClient;

public class Tutorial3
{
    public static void Main()
    {
        string connStr = "server=localhost;user=root;database=world;port=3306;password=******;";
        MySqlConnection conn = new MySqlConnection(connStr);
        try
        {
            Console.WriteLine("Connecting to MySQL...");
            conn.Open();

            string sql = "INSERT INTO Country (Name, HeadOfState, Continent) VALUES ('Disneyland','Mickey Mouse', 'North America')";
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.ToString());
        }

        conn.Close();
        Console.WriteLine("Done.");
    }
}

using System;
using System.Data;

using MySql.Data;
using MySql.Data.MySqlClient;

public class Tutorial4
{
    public static void Main()
    {
        string connStr = "server=localhost;user=root;database=world;port=3306;password=******;";
        MySqlConnection conn = new MySqlConnection(connStr);
        try
        {
            Console.WriteLine("Connecting to MySQL...");
            conn.Open();

            string sql = "SELECT COUNT(*) FROM Country";
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            object result = cmd.ExecuteScalar();
            if (result != null)
            {
                int r = Convert.ToInt32(result);
                Console.WriteLine("Number of countries in the World database is: " + r);
            }

        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.ToString());
        }

        conn.Close();
        Console.WriteLine("Done.");
    }
}

21.2.4.1.3. Working with Decoupled Data

Previously, when using MySqlDataReader, the connection to the database was continually maintained, unless explicitly closed. It is also possible to work in a manner where a connection is only established when needed. For example, in this mode, a connection could be established to read a chunk of data, the data could then be modified by the application as required. A connection could then be reestablished only if and when the application writes data back to the database. This decouples the working data set from the database.

This decoupled mode of working with data is supported by Connector/Net. There are several parts involved in allowing this method to work:

• Data Set - The Data Set is the area in which data is loaded to read or modify it. A DataSet object is instantiated, which can store multiple tables of data.

• Data Adapter - The Data Adapter is the interface between the Data Set and the database itself. The Data Adapter is responsible for efficiently managing connections to the database, opening and closing them as required. The Data Adapter is created by instantiating an object of the MySqlDataAdapter class. The MySqlDataAdapter object has two main methods: Fill which reads data into the Data Set, and Update, which writes data from the Data Set to the database.

• Command Builder - The Command Builder is a support object. The Command Builder works in conjunction with the Data Adapter. When a MySqlDataAdapter object is created, it is typically given an initial SELECT statement. From this SELECT statement the Command Builder can work out the corresponding INSERT, UPDATE and DELETE statements that would be required to update the database. To create the Command Builder, an object of the class MySqlCommandBuilder is created.

Each of these classes will now be discussed in more detail.

Instantiating a DataSet object

A DataSet object can be created simply, as shown in the following example code snippet:

DataSet dsCountry;
...
dsCountry = new DataSet();

Although this creates the DataSet object, it has not yet filled it with data. For that, a Data Adapter is required.

Instantiating a MySqlDataAdapter object

The MySqlDataAdapter can be created as illustrated by the following example:

MySqlDataAdapter daCountry;
...
string sql = "SELECT Code, Name, HeadOfState FROM Country WHERE Continent='North America'";
daCountry = new MySqlDataAdapter (sql, conn);

Note, the MySqlDataAdapter is given the SQL specifying the data to work with.

Instantiating a MySqlCommandBuilder object

Once the MySqlDataAdapter has been created, it is necessary to generate the additional statements required for inserting, updating and deleting data. There are several ways to do this, but in this tutorial you will see how this can most easily be done with MySqlCommandBuilder. The following code snippet illustrates how this is done:

MySqlCommandBuilder cb = new MySqlCommandBuilder(daCountry);

Note that the MySqlDataAdapter object is passed as a parameter to the command builder.

Filling the Data Set

To do anything useful with the data from your database, you need to load it into a Data Set. This is one of the jobs of the MySqlDataAdapter object, and is carried out with its Fill method. The following example code illustrates this:

DataSet dsCountry;
...
dsCountry = new DataSet();
...
daCountry.Fill(dsCountry, "Country");

Note the Fill method is a MySqlDataAdapter method, the Data Adapter knows how to establish a connec tion with the database and retrieve the required data, and then populates the Data Set when the Fill method is called. The second parameter “Country” is the table in the Data Set to update.

Updating the Data Set

The data in the Data Set can now be manipulated by the application as required. At some point, changes to data will need to be written back to the database. This is achieved through a MySqlDataAdapter method, the Update method.

daCountry.Update(dsCountry, "Country");

Again, the Data Set and the table within the Data Set to update are specified.

Working Example

The interactions between the DataSet, MySqlDataAdapter and MySqlCommandBuilder classes can be a little confusing, so their operation can perhaps be best illustrated by working code.

In this example, data from the World database is read into a Data Grid View control. Here, the data can be viewed and changed before clicking an update button. The update button then activates code to write changes back to the database. The code uses the principles explained above. The application was built using the Microsoft Visual Studio to place and create the user interface controls, but the main code that uses the key classes described above is shown below, and is portable.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

using MySql.Data;
using MySql.Data.MySqlClient;

namespace WindowsFormsApplication5
{
    public partial class Form1 : Form
    {
        MySqlDataAdapter daCountry;
        DataSet dsCountry;

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {

            string connStr = "server=localhost;user=root;database=world;port=3306;password=******;";
            MySqlConnection conn = new MySqlConnection(connStr);
            try
            {
                label2.Text = "Connecting to MySQL...";

                string sql = "SELECT Code, Name, HeadOfState FROM Country WHERE Continent='North America'";
                daCountry = new MySqlDataAdapter (sql, conn);
                MySqlCommandBuilder cb = new MySqlCommandBuilder(daCountry);

                dsCountry = new DataSet();
                daCountry.Fill(dsCountry, "Country");
                dataGridView1.DataSource = dsCountry;
                dataGridView1.DataMember = "Country";
            }
            catch (Exception ex)
            {
                label2.Text = ex.ToString();
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            daCountry.Update(dsCountry, "Country");
            label2.Text = "MySQL Database Updated!";
        }

    }
}

The application running is shown below:

Figure 21.41. World Database Application

string sql = "SELECT Name, HeadOfState FROM Country WHERE Continent = "+user_continent;

string sql = "SELECT Name, HeadOfState FROM Country WHERE Continent = @Continent";

cmd.Parameters.AddWithValue("@Continent", "North America");

using System;
using System.Data;

using MySql.Data;
using MySql.Data.MySqlClient;

public class Tutorial5
{
    public static void Main()
    {
        string connStr = "server=localhost;user=root;database=world;port=3306;password=******;";
        MySqlConnection conn = new MySqlConnection(connStr);
        try
        {
            Console.WriteLine("Connecting to MySQL...");
            conn.Open();

            string sql = "SELECT Name, HeadOfState FROM Country WHERE Continent=@Continent";
            MySqlCommand cmd = new MySqlCommand(sql, conn);

            Console.WriteLine("Enter a continent e.g. 'North America', 'Europe': ");
            string user_input = Console.ReadLine();

            cmd.Parameters.AddWithValue("@Continent", user_input);

            MySqlDataReader rdr = cmd.ExecuteReader();

            while (rdr.Read())
            {
                Console.WriteLine(rdr["Name"]+" --- "+rdr["HeadOfState"]);
            }
            rdr.Close();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.ToString());
        }

        conn.Close();
        Console.WriteLine("Done.");
    }
}

DELIMITER //
CREATE PROCEDURE country_hos
(IN con CHAR(20))
BEGIN
  SELECT Name, HeadOfState FROM Country
  WHERE Continent = con;
END //
DELIMITER ;

CALL country_hos('Europe');

string rtn = "country_hos";
MySqlCommand cmd = new MySqlCommand(rtn, conn);
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@con", "Europe");

using System;
using System.Data;

using MySql.Data;
using MySql.Data.MySqlClient;

public class Tutorial6
{
    public static void Main()
    {
        string connStr = "server=localhost;user=root;database=world;port=3306;password=******;";
        MySqlConnection conn = new MySqlConnection(connStr);
        try
        {
            Console.WriteLine("Connecting to MySQL...");
            conn.Open();

            string rtn = "country_hos";
            MySqlCommand cmd = new MySqlCommand(rtn, conn);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.AddWithValue("@con", "Europe");

            MySqlDataReader rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                Console.WriteLine(rdr[0] + " --- " + rdr[1]);
            }
            rdr.Close();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.ToString());
        }

        conn.Close();
        Console.WriteLine("Done.");
    }
}

Many web sites feature the facility for the user to create a user account. They can then log into the web site and enjoy a personalized experience. This requires that the developer creates database tables to store user information, along with code to gather and process this data. This represents a burden on the developer, and there is the possibility for security issues to creep into the developed code. However, ASP.NET 2.0 introduced the Membership system. This system is designed around the concept of Membership, Profile and Role Providers, which together provide all of the functionality to implement a user system, that previously would have to have been created by the developer from scratch.

Currently, MySQL Connector/Net provides Membership, Role, Profile and Session State Providers.

This tutorial shows you how to set up your ASP.NET web application to use the MySQL Connector/Net Membership and Role Providers. It assumes that you have MySQL Server installed, along with MySQL Connector/Net and Microsoft Visual Studio. This tutorial was tested with MySQL Connector/Net 6.0.4 and Microsoft Visual Studio 2008 Professional Edition. It is recommended you use 6.0.4 or above for this tutorial.

In this tutorial, you have seen how to set up the MySQL Connector/Net Membership and Role Providers for use in your ASP.NET web application.

MySQL Connector/Net from version 6.1 has included a MySQL Session State Provider. This provider enables you to store session state in a MySQL database. The following tutorial shows you how to prepare to use the MySQL Session State Provider, and then store session data into the MySQL database. This tutorial uses Microsoft Visual Studio 2008 Professional Edition, MySQL Connector/Net 6.1.1 and MySQL Server 5.1. This tutorial also assumes you have created an empty database, for example test, where you will store session data. You could do this using the mysql command interpreter.

At this point, you are ready to use the MySQL database to store session data. To test that the set up has worked, you can write a simple program that uses session variables.

At this point, you have installed the Session State Provider and carried out a preliminary test of the installation. You will now work a bit more with the Session State Provider.

In this part of the tutorial, you will set and retrieve a session variable. You can work with your existing project.

This illustrates that the session state data is not destroyed when a page is reloaded.

This tutorial shows you how to use the MySQL Profile Provider to store user profile information in a MySQL database. The tutorial uses MySQL Connector/Net 6.1.1, MySQL Server 5.1 and Microsoft Visual Studio 2008 Professional Edition.

Many modern web sites allow the user to create a personal profile. This requires a significant amount of code, but ASP.NET reduces this considerable by including the functionality in its Profile classes. The Profile Provider provides an abstraction between these classes and a data source. The MySQL Profile Provider enables profile data to be stored in a MySQL database. This enables the profile properties to be written to a persistent store, and be retrieved when required. The Profile Provider also enables profile data to be managed effectively, for example it enables profiles that have not been accessed since a specific date to be deleted.

The following steps show you how you can select the MySQL Profile Provider.

At this point you are now ready to start using the MySQL Profile Provider. With the following steps you can carry out a preliminary test of your installation.

Now that the simple profile has been defined in web.config, the next step is to write some code to test the profile.

In this tutorial you have seen how to using the MySQL Profile Provider with MySQL Connector/Net.

In this tutorial you will learn how to create a Windows Forms Data Source from an Entity in an Entity Data Model. This tutorial assumes that you have installed the World example database, which can be downloaded from the MySQL Documentation page. You can also find details on how to install the database on the same page. It will also be convenient for you to create a connection to the World database after it is installed. For instructions on how to do this see Section 21.2.3.1, “Making a Connection”.

Creating a new Windows Forms application

The first step is to create a new Windows Forms application.

Adding an Entity Data Model

You will now add an Entity Data Model to your solution.

Adding a new Data Source

You will now add a new Data Source to your project and see how it can be used to read and write to the database.