6 Дополнительная информация по MyODBC

6.1 Типы данных в 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