Spec-Zone .ru
спецификации, руководства, описания, API
|
С подходящим менеджером по ODBC и установленным драйвером Соединителя/ODBC, любой язык программирования или среда, которая может поддерживать ODBC, могут соединиться с базой данных MySQL через Соединитель/ODBC.
Это включает, но не ограничивается, языки поддержки Microsoft (включая Visual Basic, C# и интерфейсы такой как ODBC.NET), Perl (через модуль DBI, и DBD:: драйвер ODBC).
Этот раздел содержит простые примеры использования Соединителя/ODBC с ADO, DAO и RDO.
Следующий ADO (ActiveX Data Objects) пример составляет таблицу my_ado
и
демонстрирует использование rs.addNew
, rs.delete
, и rs.update
.
Private Sub myodbc_ado_Click()Dim conn As ADODB.ConnectionDim rs As ADODB.RecordsetDim fld As ADODB.FieldDim sql As String'connect to MySQL server using Connector/ODBCSet conn = New ADODB.Connectionconn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};"_& "SERVER=localhost;"_& " DATABASE=test;"_& "UID=venu;PWD=venu; OPTION=3"conn.Open'create tableconn.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 insertconn.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.Recordsetrs.CursorLocation = adUseServer'fetch the initial table ..rs.Open "SELECT * FROM my_ado", connDebug.Print rs.RecordCountrs.MoveFirstDebug.Print String(50, "-") & "Initial my_ado Result Set " & String(50, "-")For Each fld In rs.FieldsDebug.Print fld.Name,NextDebug.PrintDo Until rs.EOFFor Each fld In rs.FieldsDebug.Print fld.Value,Nextrs.MoveNextDebug.PrintLooprs.Close'rs insertrs.Open "select * from my_ado", conn, adOpenDynamic, adLockOptimisticrs.AddNewrs!Name = "Monty"rs!txt = "Insert row"rs.Updaters.Close'rs updaters.Open "SELECT * FROM my_ado"rs!Name = "update"rs!txt = "updated-row"rs.Updaters.Close'rs update second time..rs.Open "SELECT * FROM my_ado"rs!Name = "update"rs!txt = "updated-second-time"rs.Updaters.Close'rs deleters.Open "SELECT * FROM my_ado"rs.MoveNextrs.MoveNextrs.Deleters.Close'fetch the updated table ..rs.Open "SELECT * FROM my_ado", connDebug.Print rs.RecordCountrs.MoveFirstDebug.Print String(50, "-") & "Updated my_ado Result Set " & String(50, "-")For Each fld In rs.FieldsDebug.Print fld.Name,NextDebug.PrintDo Until rs.EOFFor Each fld In rs.FieldsDebug.Print fld.Value,Nextrs.MoveNextDebug.PrintLooprs.Closeconn.CloseEnd Sub
Следующий DAO (Объекты Доступа к данным) пример составляет таблицу my_dao
и
демонстрирует использование rs.addNew
, rs.update
, и прокрутка набора результатов.
Private Sub myodbc_dao_Click()Dim ws As WorkspaceDim conn As ConnectionDim queryDef As queryDefDim str As String'connect to MySQL using MySQL ODBC 3.51 DriverSet 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_daoSet queryDef = conn.CreateQueryDef("", "drop table if exists my_dao")queryDef.ExecuteSet 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.addNewSet rs = conn.OpenRecordset("my_dao")Dim i As IntegerFor i = 10 To 15rs.AddNewrs!Name = "insert record" & irs!Id2 = irs.UpdateNext irs.Close'rs update..Set rs = conn.OpenRecordset("my_dao")rs.Editrs!Name = "updated-string"rs.Updaters.Close'fetch the table back...Set rs = conn.OpenRecordset("my_dao", dbOpenDynamic)str = "Results:"rs.MoveFirstWhile Not rs.EOFstr = " " & rs!Id & " , " & rs!Name & ", " & rs!Ts & ", " & rs!Id2Debug.Print "DATA:" & strrs.MoveNextWend'rs Scrollingrs.MoveFirststr = " FIRST ROW: " & rs!Id & " , " & rs!Name & ", " & rs!Ts & ", " & rs!Id2Debug.Print strrs.MoveLaststr = " LAST ROW: " & rs!Id & " , " & rs!Name & ", " & rs!Ts & ", " & rs!Id2Debug.Print strrs.MovePreviousstr = " LAST-1 ROW: " & rs!Id & " , " & rs!Name & ", " & rs!Ts & ", " & rs!Id2Debug.Print str'free all resourcesrs.ClosequeryDef.Closeconn.Closews.CloseEnd Sub
Следующие RDO (Объекты Удаленных данных) пример составляют таблицу my_rdo
и
демонстрирует использование rs.addNew
и rs.update
.
Dim rs As rdoResultsetDim cn As New rdoConnectionDim cl As rdoColumnDim 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 = rdUseOdbccn.EstablishConnection rdDriverPrompt'drop table my_rdoSQL = "drop table if exists my_rdo"cn.Execute SQL, rdExecDirect'create table my_rdoSQL = "create table my_rdo(id int, name varchar(20))"cn.Execute SQL, rdExecDirect'insert - directSQL = "insert into my_rdo values (100,'venu')"cn.Execute SQL, rdExecDirectSQL = "insert into my_rdo values (200,'MySQL')"cn.Execute SQL, rdExecDirect'rs insertSQL = "select * from my_rdo"Set rs = cn.OpenResultset(SQL, rdOpenStatic, rdConcurRowVer, rdExecDirect)rs.AddNewrs!id = 300rs!Name = "Insert1"rs.Updaters.Close'rs insertSQL = "select * from my_rdo"Set rs = cn.OpenResultset(SQL, rdOpenStatic, rdConcurRowVer, rdExecDirect)rs.AddNewrs!id = 400rs!Name = "Insert 2"rs.Updaters.Close'rs updateSQL = "select * from my_rdo"Set rs = cn.OpenResultset(SQL, rdOpenStatic, rdConcurRowVer, rdExecDirect)rs.Editrs!id = 999rs!Name = "updated"rs.Updaters.Close'fetch back...SQL = "select * from my_rdo"Set rs = cn.OpenResultset(SQL, rdOpenStatic, rdConcurRowVer, rdExecDirect)Do Until rs.EOFFor Each cl In rs.rdoColumnsDebug.Print cl.Value,Nextrs.MoveNextDebug.PrintLoopDebug.Print "Row count="; rs.RowCount'closers.Closecn.CloseEnd Sub
Этот раздел содержит простые примеры, которые демонстрируют использование драйверов Соединителя/ODBC с ODBC.NET.
Следующая выборка составляет таблицу my_odbc_net
и демонстрирует его
использование в C#.
/** * @sample : mycon.cs * @purpose : Demo sample for ODBC.NET using Connector/ODBC * @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 Connector/ODBC 3.51 string MyConString = "DRIVER={MySQL ODBC 3.51 Driver};" + "SERVER=localhost;" + "DATABASE=test;" + "UID=venu;" + "PASSWORD=venu;" + "OPTION=3"; //Connect to MySQL using Connector/ODBC 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 Connector/ODBC 3.51 Console.WriteLine("Data:" + MyDataReader.GetInt32(0) + " " + MyDataReader.GetString(1) + " " + MyDataReader.GetInt64(2)); } else { //BIGINTs not supported by Connector/ODBC 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"); } } } }}
Следующая выборка составляет таблицу my_vb_net
и демонстрирует
использование в VB.
' @sample : myvb.vb' @purpose : Demo sample for ODBC.NET using Connector/ODBC' @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.OdbcImports SystemModule myvb Sub Main() Try 'Connector/ODBC 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