The installer packages offer a very simple method for installing the MyODBC drivers. If you have downloaded the zipped installer then you must extract the installer application. The basic installation process is identical for both installers.

You should follow these steps to complete the installation:

Now the installation is complete, you can continue to configure your ODBC connections using Section 23.1.3, “MyODBC Configuration”.

If you have downloaded the Zipped DLL package then you must install the individual files required for MyODBC operation manually. Once you have unzipped the installation files, you can either perform this operation by hand, executing each statement individually, or you can use the included Batch file to perform an installation to the default locations.

To install using the Batch file:

If you want to copy the files to an alternative location - for example, to run or test different versions of the MyODBC driver on the same machine, then you must copy the files by hand. It is however not recommended to install these files in a non-standard location. To copy the files by hand to the default installation location use the following steps:

On Windows, you may get the following error when trying to install the older MyODBC 2.50 driver:

An error occurred while copying C:\WINDOWS\SYSTEM\MFC30.DLL. 
Restart Windows and try installing again (before running any
applications which use ODBC)

The reason for the error is that another application is currently using the ODBC system. Windows may not allow you to complete the installation. In most cases, you can continue by pressing Ignore to copy the rest of the MyODBC files and the final installation should still work. If it doesn't, the solution is to re-boot your computer in “safe mode.” Choose safe mode by pressing F8 just before your machine starts Windows during re-booting, install the MyODBC drivers, and re-boot to normal mode.

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

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

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

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

Then proceed on to Section 23.1.3.4, “Configuring a MyODBC DSN on Unix”, to configure the DSN for MyODBC. For more information, refer to the INSTALL-BINARY file that comes with your distribution.

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

If you are installing for the first time:

shell> su root
 shell> rpm -ivh MyODBC-3.51.12.i386.rpm

If the driver exists, upgrade it like this:

shell> su root
shell> rpm -Uvh MyODBC-3.51.12.i386.rpm

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

This installs the driver libraries and related documents to /usr/local/lib and /usr/share/doc/MyODBC, respectively. Proceed onto Section 23.1.3.4, “Configuring a MyODBC DSN on Unix”.

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

shell> su root
shell> rpm -e MyODBC

You can install MyODBC on a Mac OS X or Mac OS X Server computer by using the binary distribution. The package is available as a compressed disk image (.dmg) file. To install MyODBC on your computer using this method, follow these steps:

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

To build the driver, use this procedure:

Note:

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

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

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

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

shell> ./configure --help

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

The final configuration command looks something like this:

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

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

To build the driver libraries, you have to just execute make.

shell> make

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

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, you must specify the --enable-shared option for configure. By default, configure does not enable this option.

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

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

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

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

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

To build the thread-safe driver library:

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

To install the driver libraries, execute the following command:

shell> make install

That command installs one of the following sets of libraries:

For MyODBC 3.51:

For thread-safe MyODBC 3.51:

For MyODBC 2.5.0:

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

To run the basic samples provided in the distribution with the libraries that you built, 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 build the driver on HP-UX 10.x or 11.x, make use of the following configure example:

If using cc:

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

If using gcc:

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

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

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

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

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

To export a table of data from an Access database to MySQL, follow these instructions:

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

To import or link a table or tables from MySQL to Access, follow these instructions:

Use the following procedure to view or to refresh links when the structure or location of a linked table has changed. The Linked Table Manager lists the paths to all currently linked tables.

To view or refresh links:

Microsoft Access confirms a successful refresh or, if the table wasn't found, displays the Select New Location of <table name> dialog box in which you can specify its the table's new location. If several selected tables have moved to the new location that you specify, the Linked Table Manager searches that location for all selected tables, and updates all links in one step.

To change the path for a set of linked tables:

The following ADO (ActiveX Data Objects) example creates a table my_ado and demonstrates the use of rs.addNew, rs.delete, and rs.update.

Private Sub myodbc_ado_Click()

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim fld As ADODB.Field
Dim sql As String

'connect to MySQL server using MySQL ODBC 3.51 Driver
Set conn = New ADODB.Connection
conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};"_
& "SERVER=localhost;"_
& " DATABASE=test;"_
& "UID=venu;PWD=venu; OPTION=3"

conn.Open

'create table
conn.Execute "DROP TABLE IF EXISTS my_ado"
conn.Execute "CREATE TABLE my_ado(id int not null primary key, name varchar(20)," _
& "txt text, dt date, tm time, ts timestamp)"

'direct insert
conn.Execute "INSERT INTO my_ado(id,name,txt) values(1,100,'venu')"
conn.Execute "INSERT INTO my_ado(id,name,txt) values(2,200,'MySQL')"
conn.Execute "INSERT INTO my_ado(id,name,txt) values(3,300,'Delete')"

Set rs = New ADODB.Recordset
rs.CursorLocation = adUseServer

'fetch the initial table ..
rs.Open "SELECT * FROM my_ado", conn
Debug.Print rs.RecordCount
rs.MoveFirst
Debug.Print String(50, "-") & "Initial my_ado Result Set " & String(50, "-")
For Each fld In rs.Fields
Debug.Print fld.Name,
Next
Debug.Print

Do Until rs.EOF
For Each fld In rs.Fields
Debug.Print fld.Value,
Next
rs.MoveNext
Debug.Print
Loop
rs.Close

'rs insert
rs.Open "select * from my_ado", conn, adOpenDynamic, adLockOptimistic
rs.AddNew
rs!Name = "Monty"
rs!txt = "Insert row"
rs.Update
rs.Close

'rs update
rs.Open "SELECT * FROM my_ado"
rs!Name = "update"
rs!txt = "updated-row"
rs.Update
rs.Close

'rs update second time..
rs.Open "SELECT * FROM my_ado"
rs!Name = "update"
rs!txt = "updated-second-time"
rs.Update
rs.Close

'rs delete
rs.Open "SELECT * FROM my_ado"
rs.MoveNext
rs.MoveNext
rs.Delete
rs.Close

'fetch the updated table ..
rs.Open "SELECT * FROM my_ado", conn
Debug.Print rs.RecordCount
rs.MoveFirst
Debug.Print String(50, "-") & "Updated my_ado Result Set " & String(50, "-")
For Each fld In rs.Fields
Debug.Print fld.Name,
Next
Debug.Print

Do Until rs.EOF
For Each fld In rs.Fields
Debug.Print fld.Value,
Next
rs.MoveNext
Debug.Print
Loop
rs.Close
conn.Close
End Sub

The following DAO (Data Access Objects) example creates a table my_dao and demonstrates the use of rs.addNew, rs.update, and result set scrolling.

Private Sub myodbc_dao_Click()

Dim ws As Workspace
Dim conn As Connection
Dim queryDef As queryDef
Dim str As String

'connect to MySQL using MySQL ODBC 3.51 Driver
Set ws = DBEngine.CreateWorkspace("", "venu", "venu", dbUseODBC)
str = "odbc;DRIVER={MySQL ODBC 3.51 Driver};"_
& "SERVER=localhost;"_
& " DATABASE=test;"_
& "UID=venu;PWD=venu; OPTION=3"
Set conn = ws.OpenConnection("test", dbDriverNoPrompt, False, str)

'Create table my_dao
Set queryDef = conn.CreateQueryDef("", "drop table if exists my_dao")
queryDef.Execute

Set queryDef = conn.CreateQueryDef("", "create table my_dao(Id INT AUTO_INCREMENT PRIMARY KEY, " _
& "Ts TIMESTAMP(14) NOT NULL, Name varchar(20), Id2 INT)")
queryDef.Execute

'Insert new records using rs.addNew
Set rs = conn.OpenRecordset("my_dao")
Dim i As Integer

For i = 10 To 15
rs.AddNew
rs!Name = "insert record" & i
rs!Id2 = i
rs.Update
Next i
rs.Close

'rs update..
Set rs = conn.OpenRecordset("my_dao")
rs.Edit
rs!Name = "updated-string"
rs.Update
rs.Close

'fetch the table back...
Set rs = conn.OpenRecordset("my_dao", dbOpenDynamic)
str = "Results:"
rs.MoveFirst
While Not rs.EOF
str = " " & rs!Id & " , " & rs!Name & ", " & rs!Ts & ", " & rs!Id2
Debug.Print "DATA:" & str
rs.MoveNext
Wend

'rs Scrolling
rs.MoveFirst
str = " FIRST ROW: " & rs!Id & " , " & rs!Name & ", " & rs!Ts & ", " & rs!Id2
Debug.Print str

rs.MoveLast
str = " LAST ROW: " & rs!Id & " , " & rs!Name & ", " & rs!Ts & ", " & rs!Id2
Debug.Print str

rs.MovePrevious
str = " LAST-1 ROW: " & rs!Id & " , " & rs!Name & ", " & rs!Ts & ", " & rs!Id2
Debug.Print str

'free all resources
rs.Close
queryDef.Close
conn.Close
ws.Close

End Sub

The following RDO (Remote Data Objects) example creates a table my_rdo and demonstrates the use of rs.addNew and rs.update.

Dim rs As rdoResultset
Dim cn As New rdoConnection
Dim cl As rdoColumn
Dim SQL As String

'cn.Connect = "DSN=test;"
cn.Connect = "DRIVER={MySQL ODBC 3.51 Driver};"_
& "SERVER=localhost;"_
& " DATABASE=test;"_
& "UID=venu;PWD=venu; OPTION=3"

cn.CursorDriver = rdUseOdbc
cn.EstablishConnection rdDriverPrompt


'drop table my_rdo
SQL = "drop table if exists my_rdo"
cn.Execute SQL, rdExecDirect

'create table my_rdo
SQL = "create table my_rdo(id int, name varchar(20))"
cn.Execute SQL, rdExecDirect

'insert - direct
SQL = "insert into my_rdo values (100,'venu')"
cn.Execute SQL, rdExecDirect

SQL = "insert into my_rdo values (200,'MySQL')"
cn.Execute SQL, rdExecDirect

'rs insert
SQL = "select * from my_rdo"
Set rs = cn.OpenResultset(SQL, rdOpenStatic, rdConcurRowVer, rdExecDirect)
rs.AddNew
rs!id = 300
rs!Name = "Insert1"
rs.Update
rs.Close

'rs insert
SQL = "select * from my_rdo"
Set rs = cn.OpenResultset(SQL, rdOpenStatic, rdConcurRowVer, rdExecDirect)
rs.AddNew
rs!id = 400
rs!Name = "Insert 2"
rs.Update
rs.Close

'rs update
SQL = "select * from my_rdo"
Set rs = cn.OpenResultset(SQL, rdOpenStatic, rdConcurRowVer, rdExecDirect)
rs.Edit
rs!id = 999
rs!Name = "updated"
rs.Update
rs.Close

'fetch back...
SQL = "select * from my_rdo"
Set rs = cn.OpenResultset(SQL, rdOpenStatic, rdConcurRowVer, rdExecDirect)
Do Until rs.EOF
For Each cl In rs.rdoColumns
Debug.Print cl.Value,
Next
rs.MoveNext
Debug.Print
Loop
Debug.Print "Row count="; rs.RowCount

'close
rs.Close
cn.Close

End Sub

The following sample creates a table my_odbc_net and demonstrates its use in C#.

/**
 * @sample    : mycon.cs
 * @purpose   : Demo sample for ODBC.NET using MyODBC
 * @author    : Venu, <myodbc@lists.mysql.com>
 *
 * (C) Copyright MySQL AB, 1995-2006
 *
 **/
          
/* build command
 *
 *  csc /t:exe
 *      /out:mycon.exe mycon.cs
 *      /r:Microsoft.Data.Odbc.dll
 */
          
using Console = System.Console;
using Microsoft.Data.Odbc;
          
namespace myodbc3
{
  class mycon
  {
    static void Main(string[] args)
    {
      try
        {
          //Connection string for MyODBC 2.50
          /*string MyConString = "DRIVER={MySQL};" +
            "SERVER=localhost;" +
            "DATABASE=test;" +
            "UID=venu;" +
            "PASSWORD=venu;" +
            "OPTION=3";
          */
          //Connection string for MyODBC 3.51
          string MyConString = "DRIVER={MySQL ODBC 3.51 Driver};" +
            "SERVER=localhost;" +
            "DATABASE=test;" +
            "UID=venu;" +
            "PASSWORD=venu;" +
            "OPTION=3";
          
          //Connect to MySQL using MyODBC
          OdbcConnection MyConnection = new OdbcConnection(MyConString);
          MyConnection.Open();
          
          Console.WriteLine("\n !!! success, connected successfully !!!\n");
          
          //Display connection information
          Console.WriteLine("Connection Information:");
          Console.WriteLine("\tConnection String:" + 
                            MyConnection.ConnectionString);
          Console.WriteLine("\tConnection Timeout:" + 
                            MyConnection.ConnectionTimeout);
          Console.WriteLine("\tDatabase:" + 
                            MyConnection.Database);
          Console.WriteLine("\tDataSource:" + 
                            MyConnection.DataSource);
          Console.WriteLine("\tDriver:" + 
                            MyConnection.Driver);
          Console.WriteLine("\tServerVersion:" + 
                            MyConnection.ServerVersion);
          
          //Create a sample table
          OdbcCommand MyCommand = 
            new OdbcCommand("DROP TABLE IF EXISTS my_odbc_net",
                            MyConnection);
          MyCommand.ExecuteNonQuery();
          MyCommand.CommandText = 
            "CREATE TABLE my_odbc_net(id int, name varchar(20), idb bigint)";
          MyCommand.ExecuteNonQuery();
          
          //Insert
          MyCommand.CommandText = 
            "INSERT INTO my_odbc_net VALUES(10,'venu', 300)";
          Console.WriteLine("INSERT, Total rows affected:" + 
                            MyCommand.ExecuteNonQuery());;
          
          //Insert
          MyCommand.CommandText = 
            "INSERT INTO my_odbc_net VALUES(20,'mysql',400)";
          Console.WriteLine("INSERT, Total rows affected:" + 
                            MyCommand.ExecuteNonQuery());
          
          //Insert
          MyCommand.CommandText = 
            "INSERT INTO my_odbc_net VALUES(20,'mysql',500)";
          Console.WriteLine("INSERT, Total rows affected:" + 
                            MyCommand.ExecuteNonQuery());
          
          //Update
          MyCommand.CommandText = 
            "UPDATE my_odbc_net SET id=999 WHERE id=20";
          Console.WriteLine("Update, Total rows affected:" + 
                            MyCommand.ExecuteNonQuery());
          
          //COUNT(*)
          MyCommand.CommandText = 
            "SELECT COUNT(*) as TRows FROM my_odbc_net";
          Console.WriteLine("Total Rows:" + 
                            MyCommand.ExecuteScalar());
          
          //Fetch
          MyCommand.CommandText = "SELECT * FROM my_odbc_net";
          OdbcDataReader MyDataReader;
          MyDataReader =  MyCommand.ExecuteReader();
          while (MyDataReader.Read())
            {
              if(string.Compare(MyConnection.Driver,"myodbc3.dll") == 0) {
                //Supported only by MyODBC 3.51
                Console.WriteLine("Data:" + MyDataReader.GetInt32(0) + " " +
                                  MyDataReader.GetString(1) + " " +
                                  MyDataReader.GetInt64(2)); 
              }
              else {
                //BIGINTs not supported by MyODBC
                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");
            }
        }
    }
  }
}

The following sample creates a table my_vb_net and demonstrates the use in VB.

' @sample    : myvb.vb
' @purpose   : Demo sample for ODBC.NET using MyODBC
' @author    : Venu, <myodbc@lists.mysql.com>
'
' (C) Copyright MySQL AB, 1995-2006
'
'

'
' build command
'
' vbc /target:exe
'     /out:myvb.exe
'     /r:Microsoft.Data.Odbc.dll
'     /r:System.dll
'     /r:System.Data.dll
'

Imports Microsoft.Data.Odbc
Imports System

Module myvb
  Sub Main()
    Try

      'MyODBC 3.51 connection string
      Dim MyConString As String = "DRIVER={MySQL ODBC 3.51 Driver};" & _
      "SERVER=localhost;" & _
      "DATABASE=test;" & _
      "UID=venu;" & _
      "PASSWORD=venu;" & _
      "OPTION=3;"

      'Connection
      Dim MyConnection As New OdbcConnection(MyConString)
      MyConnection.Open()

      Console.WriteLine("Connection State::" & MyConnection.State.ToString)

      'Drop
      Console.WriteLine("Dropping table")
      Dim MyCommand As New OdbcCommand()
      MyCommand.Connection = MyConnection
      MyCommand.CommandText = "DROP TABLE IF EXISTS my_vb_net"
      MyCommand.ExecuteNonQuery()

      'Create
      Console.WriteLine("Creating....")
      MyCommand.CommandText = "CREATE TABLE my_vb_net(id int, name varchar(30))"
      MyCommand.ExecuteNonQuery()

      'Insert
      MyCommand.CommandText = "INSERT INTO my_vb_net VALUES(10,'venu')"
      Console.WriteLine("INSERT, Total rows affected:" & _
      MyCommand.ExecuteNonQuery())

      'Insert
      MyCommand.CommandText = "INSERT INTO my_vb_net VALUES(20,'mysql')"
      Console.WriteLine("INSERT, Total rows affected:" & _
      MyCommand.ExecuteNonQuery())

      'Insert
      MyCommand.CommandText = "INSERT INTO my_vb_net VALUES(20,'mysql')"
      Console.WriteLine("INSERT, Total rows affected:" & _
      MyCommand.ExecuteNonQuery())

      'Insert
      MyCommand.CommandText = "INSERT INTO my_vb_net(id) VALUES(30)"
      Console.WriteLine("INSERT, Total rows affected:" & _
                        MyCommand.ExecuteNonQuery())

      'Update
      MyCommand.CommandText = "UPDATE my_vb_net SET id=999 WHERE id=20"
      Console.WriteLine("Update, Total rows affected:" & _
      MyCommand.ExecuteNonQuery())

      'COUNT(*)
      MyCommand.CommandText = "SELECT COUNT(*) as TRows FROM my_vb_net"
      Console.WriteLine("Total Rows:" & MyCommand.ExecuteScalar())

      'Select
      Console.WriteLine("Select * FROM my_vb_net")
      MyCommand.CommandText = "SELECT * FROM my_vb_net"
      Dim MyDataReader As OdbcDataReader
      MyDataReader = MyCommand.ExecuteReader
      While MyDataReader.Read
        If MyDataReader("name") Is DBNull.Value Then
          Console.WriteLine("id = " & _
          CStr(MyDataReader("id")) & "  name = " & _
          "NULL")
        Else
          Console.WriteLine("id = " & _
          CStr(MyDataReader("id")) & "  name = " & _
          CStr(MyDataReader("name")))
        End If
      End While

      'Catch ODBC Exception
    Catch MyOdbcException As OdbcException
      Dim i As Integer
      Console.WriteLine(MyOdbcException.ToString)

      'Catch program exception
    Catch MyException As Exception
      Console.WriteLine(MyException.ToString)
    End Try
  End Sub

To improve the integration between Microsoft Access and MySQL through MyODBC:

Some external articles and tips that may be useful when using Access, ODBC and MyODBC:

If you have problems importing data into Microsoft Excel, particularly numerical, date, and time values, this is probably because of a bug in Excel, where the column type of the source data is used to determine the data type when that data is inserted into a cell within the worksheet. The result is that Excel incorrectly identifies the content and this affects both the display format and the data when it is used within calculations.

To address this issue, use the CONCAT() function in your queries. The use of CONCAT() forces Excel to treat the value as a string, which Excel will then parse and usually correctly identify the embedded information.

However, even with this option, some data may be incorrectly formatted, even though the source data remains unchanged. Use the Format Cells option within Excel to change the format of the displayed information.

To be able to update a table, you must define a primary key for the table.

Visual Basic with ADO can't handle big integers. This means that some queries like SHOW PROCESSLIST do not work properly. The fix is to use OPTION=16384 in the ODBC connect string or to select the Change BIGINT columns to INT option in the MyODBC connect screen. You may also want to select the Return matching rows option.

If you have a BIGINT in your result, you may get the error [Microsoft][ODBC Driver Manager] Driver does not support this parameter. Try selecting the Change BIGINT columns to INT option in the MyODBC connect screen.

You should select the Don't optimize column widths option.

When you are coding with the ADO API and MyODBC, you need to pay attention to some default properties that aren't supported by the MySQL server. For example, using the CursorLocation Property as adUseServer returns a result of –1 for the RecordCount Property. To have the right value, you need to set this property to adUseClient, as shown in the VB code here:

Dim myconn As New ADODB.Connection
Dim myrs As New Recordset
Dim mySQL As String
Dim myrows As Long

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

myrs.Close
myconn.Close

Another workaround is to use a SELECT COUNT(*) statement for a similar query to get the correct row count.

To find the number of rows affected by a specific SQL statement in ADO, use the RecordsAffected property in the ADO execute method. For more information on the usage of execute method, refer to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthcnnexecute.asp.

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

You should select the Return matching rows option in the DSN.

For more information about how to access MySQL via ASP using MyODBC, refer to the following articles:

A Frequently Asked Questions list for ASP can be found at http://support.microsoft.com/default.aspx?scid=/Support/ActiveServer/faq/data/adofaq.asp.

Some articles that may help with Visual Basic and ASP:

When you start a query, you can use the Active property or the Open method. Note that Active starts by automatically issuing a SELECT * FROM ... query. That may not be a good thing if your tables are large.

Also, here is some potentially useful Delphi code that sets up both an ODBC entry and a BDE entry for MyODBC. The BDE entry requires a BDE Alias Editor that is free at a Delphi Super Page near you. (Thanks to Bryan Brunton <bryan@flesherfab.com> for this):

fReg:= TRegistry.Create;
fReg.OpenKey('\Software\ODBC\ODBC.INI\DocumentsFab', True);
fReg.WriteString('Database', 'Documents');
fReg.WriteString('Description', ' ');
fReg.WriteString('Driver', 'C:\WINNT\System32\myodbc.dll');
fReg.WriteString('Flag', '1');
fReg.WriteString('Password', '');
fReg.WriteString('Port', ' ');
fReg.WriteString('Server', 'xmark');
fReg.WriteString('User', 'winuser');
fReg.OpenKey('\Software\ODBC\ODBC.INI\ODBC Data Sources', True);
fReg.WriteString('DocumentsFab', 'MySQL');
fReg.CloseKey;
fReg.Free;

Memo1.Lines.Add('DATABASE NAME=');
Memo1.Lines.Add('USER NAME=');
Memo1.Lines.Add('ODBC DSN=DocumentsFab');
Memo1.Lines.Add('OPEN MODE=READ/WRITE');
Memo1.Lines.Add('BATCH COUNT=200');
Memo1.Lines.Add('LANGDRIVER=');
Memo1.Lines.Add('MAX ROWS=-1');
Memo1.Lines.Add('SCHEMA CACHE DIR=');
Memo1.Lines.Add('SCHEMA CACHE SIZE=8');
Memo1.Lines.Add('SCHEMA CACHE TIME=-1');
Memo1.Lines.Add('SQLPASSTHRU MODE=SHARED AUTOCOMMIT');
Memo1.Lines.Add('SQLQRYMODE=');
Memo1.Lines.Add('ENABLE SCHEMA CACHE=FALSE');
Memo1.Lines.Add('ENABLE BCD=FALSE');
Memo1.Lines.Add('ROWSET SIZE=20');
Memo1.Lines.Add('BLOBS TO CACHE=64');
Memo1.Lines.Add('BLOB SIZE=32');

AliasEditor.Add('DocumentsFab','MySQL',Memo1.Lines);

Tested with BDE 3.0. The only known problem is that when the table schema changes, query fields are not updated. BDE, however, does not seem to recognize primary keys, only the index named PRIMARY, although this has not been a problem.