Информация по 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, 

'

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

Авторы: Micheal (Monty) Widenius (monty@mysql.com) и Venu Anuganti (venu@mysql.com)

16.02.2006