Информация по MyODBC

Алексей Паутов

Типы данных в MyODBC

Следующая таблица иллюстрирует, как драйвер отображает типы данных сервера к заданным по умолчанию SQL и C-типам данных:

6.2 Коды ошибок MyODBC

Следующая таблица вносит в список коды ошибок, возвращаемые драйвером кроме ошибок сервера.

6.3 MyODBC с VB: ADO, DAO и RDO

Эта глава содержит простые примеры использования MySQL ODBC 3.51 с ADO, DAO и RDO.

6.3.1 ADO: rs.addNew, rs.delete и rs.update

Следующий пример для ADO (ActiveX Data Objects) создает таблицу my_ado и показывает использование rs.addNew, rs.delete и 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=35"
  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

6.3.2 DAO: rs.addNew, rs.update и прокрутка

Следующий пример для DAO (Data Access Objects) создает таблицу my_dao и показывает использование rs.addNew, rs.update, а также прокрутку (скроллинг) набора результатов.

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=35"
  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

6.3.3 RDO: rs.addNew и rs.update

Следующий пример для RDO (Remote Data Objects) создает таблицу my_rdo и показывает использование rs.addNew и 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=35"
  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

6.4 MyODBC и Microsoft .NET

Эта глава содержит простые примеры, чтобы показать использование драйверов MyODBC с ODBC.NET.

6.4.1 ODBC.NET: CSHARP(C#)

Следующий пример создает таблицу my_odbc_net и показывает использование в C#.

/**
 * @sample  : mycon.cs
 * @purpose : Demo sample for ODBC.NET using MyODBC or MyODBC 3.51
 * @author  : Venu, venu@mysql.com
 *
 * (C) Copyright MySQL AB, 1995-2002
 *
**/

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

6.4.2 ODBC.NET: VB

Следующий пример создает таблицу my_vb_net и показывает использование в VB.

' @sample  : myvb.vb
' @purpose : Demo sample for ODBC.NET using MyODBC or MyODBC 3.51
' @author  : Venu, venu@mysql.com
'
' (C) Copyright MySQL AB, 1995-2002
'
' 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

6.5 Авторы

Это разработчики, которые работают в MySQL AB полный рабочий день над драйверами MyODBC и MyODBC 3.51.

• Micheal (Monty) Widenius, monty@mysql.com
• Venu Anuganti, venu@mysql.com