Capítulo 25. Conectores MySQL

To retrieve data from MySQL to Word/Excel documents, you need to use the MyODBC driver and the Add-in Microsoft Query help.

For example, create a database with a table containing two columns of text:

This is an issue similar to that of Access 97 when your table consists of TEXT or VARCHAR data types. You can fix this error by upgrading your MyODBC driver to version 3.51.02 or higher.

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

A common problem is how to get the value of an automatically generated ID from an INSERT statement. With ODBC, you can do something like this (assuming that auto is an AUTO_INCREMENT field):

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

Or, if you are just going to insert the ID into another table, you can do this:

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

See Sección 24.3.13.3, “Cómo obtener el ID único del último registro insertado”.

For the benefit of some ODBC applications (at least Delphi and Access), the following query can be used to find a newly inserted row:

SELECT * FROM tbl WHERE auto IS NULL;

Yes. MyODBC 3.51 supports Dynamic cursor type along with Forward-only and static.

Due to the performance issues, the driver does not support this feature by default. You can enable this by specifying the connection option flag as OPTION=32 or by checking the Enable Dynamic Cursor option from the DSN configuration.

The driver returns this error when an application issues any transactional call but the underlying MySQL server either does not support transactions or they are not enabled.

To avoid this problem, you must use a server that has either or both of the InnoDB or BDB storage engines enabled, and use tables of those types. MySQL servers from version 4.0 and up support InnoDB by default. MySQL-Max servers also support BDB on platforms where BDB is available.

Also, if your server supports transactional table types (InnoDB and BDB) make sure the disable transactions option is not set from the DSN configuration.

This is because the application is using old MyODBC 2.50 version, and it did not set the cursor name explicitly through SQLSetCursorName. The fix is to upgrade to MyODBC 3.51 version.

Yes. If you find something is not working with MyODBC 3.51 that works with MyODBC 2.50, then send a mail message to <[email protected]>

Yes. You can make use of odbc.net to connect to MySQL through MyODBC. Here are the few basic samples to connect to MySQL from VC.NET and VB.NET.

Here is yet another excellent article "Exploring MySQL on .NET environment" by Venu (MyODBC developer) that covers about all MySQL .NET interfaces along with some useful examples.

Caution: Using ODBC.NET with MyODBC, while fetching empty string (0 length), it starts giving the SQL_NO_DATA exception. You can get the patch for this from http://support.microsoft.com/default.aspx?scid=kb;EN-US;q319243.

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

• Configure the MyODBC DSN

• Connect to MySQL server

• Initialization operations

• Execute SQL statements

• Retrieve results

• Perform Transactions

• Disconnect from the server

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

Private Sub myodbc_ado_Click()

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

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

  conn.Open

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

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

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

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

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

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

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

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

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

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

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

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

Private Sub myodbc_dao_Click()

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

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

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

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

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

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

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

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

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

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

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

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

End Sub

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

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

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

    cn.CursorDriver = rdUseOdbc
    cn.EstablishConnection rdDriverPrompt


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

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

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

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

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

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

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

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

    'close
    rs.Close
    cn.Close

End Sub

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

 /**
 * @sample    : mycon.cs
 * @purpose   : Demo sample for ODBC.NET using MyODBC
 * @author    : Venu, <[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 MyODBC 2.50
        /*string MyConString = "DRIVER={MySQL};" +
                             "SERVER=localhost;" +
                             "DATABASE=test;" +
                             "UID=venu;" +
                             "PASSWORD=venu;" +
                             "OPTION=3";
        */
        //Connection string for MyODBC 3.51
        string MyConString = "DRIVER={MySQL ODBC 3.51 Driver};" +
                             "SERVER=localhost;" +
                             "DATABASE=test;" +
                             "UID=venu;" +
                             "PASSWORD=venu;" +
                             "OPTION=3";

        //Connect to MySQL using MyODBC
        OdbcConnection MyConnection = new OdbcConnection(MyConString);
        MyConnection.Open();

        Console.WriteLine("\n !!! success, connected successfully !!!\n");

        //Display connection information
        Console.WriteLine("Connection Information:");
        Console.WriteLine("\tConnection String:" + MyConnection.ConnectionString);
        Console.WriteLine("\tConnection Timeout:" + MyConnection.ConnectionTimeout);
        Console.WriteLine("\tDatabase:" + MyConnection.Database);
        Console.WriteLine("\tDataSource:" + MyConnection.DataSource);
        Console.WriteLine("\tDriver:" + MyConnection.Driver);
        Console.WriteLine("\tServerVersion:" + MyConnection.ServerVersion);

        //Create a sample table
        OdbcCommand MyCommand = new OdbcCommand("DROP TABLE IF EXISTS my_odbc_net",MyConnection);
        MyCommand.ExecuteNonQuery();
        MyCommand.CommandText = "CREATE TABLE my_odbc_net(id int, name varchar(20), idb bigint)";
        MyCommand.ExecuteNonQuery();

        //Insert
        MyCommand.CommandText = "INSERT INTO my_odbc_net VALUES(10,'venu', 300)";
        Console.WriteLine("INSERT, Total rows affected:" + MyCommand.ExecuteNonQuery());;

        //Insert
        MyCommand.CommandText = "INSERT INTO my_odbc_net VALUES(20,'mysql',400)";
        Console.WriteLine("INSERT, Total rows affected:" + MyCommand.ExecuteNonQuery());

        //Insert
        MyCommand.CommandText = "INSERT INTO my_odbc_net VALUES(20,'mysql',500)";
        Console.WriteLine("INSERT, Total rows affected:" + MyCommand.ExecuteNonQuery());

        //Update
        MyCommand.CommandText = "UPDATE my_odbc_net SET id=999 WHERE id=20";
        Console.WriteLine("Update, Total rows affected:" + MyCommand.ExecuteNonQuery());

        //COUNT(*)
        MyCommand.CommandText = "SELECT COUNT(*) as TRows FROM my_odbc_net";
        Console.WriteLine("Total Rows:" + MyCommand.ExecuteScalar());

        //Fetch
        MyCommand.CommandText = "SELECT * FROM my_odbc_net";
        OdbcDataReader MyDataReader;
        MyDataReader =  MyCommand.ExecuteReader();
        while (MyDataReader.Read())
        {
         if(string.Compare(MyConnection.Driver,"myodbc3.dll") == 0) {
           Console.WriteLine("Data:" + MyDataReader.GetInt32(0) + " " +
                                       MyDataReader.GetString(1) + " " +
                                       MyDataReader.GetInt64(2)); //Supported only by MyODBC 3.51
         }
         else {
           Console.WriteLine("Data:" + MyDataReader.GetInt32(0) + " " +
                                       MyDataReader.GetString(1) + " " +
                                       MyDataReader.GetInt32(2)); //BIGINTs not supported by MyODBC
         }
        }

        //Close all resources
        MyDataReader.Close();
        MyConnection.Close();
      }
      catch (OdbcException MyOdbcException)//Catch any ODBC exception ..
      {
        for (int i=0; i < MyOdbcException.Errors.Count; i++)
        {
          Console.Write("ERROR #" + i + "\n" +
            "Message: " + MyOdbcException.Errors[i].Message + "\n" +
            "Native: " + MyOdbcException.Errors[i].NativeError.ToString() + "\n" +
            "Source: " + MyOdbcException.Errors[i].Source + "\n" +
            "SQL: " + MyOdbcException.Errors[i].SQLState + "\n");
        }
      }
    }
  }
 }

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

' @sample    : myvb.vb
' @purpose   : Demo sample for ODBC.NET using MyODBC
' @author    : Venu, <[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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• Micheal (Monty) Widenius

• Venu Anuganti

• Peter Harvey