22.1.5.7. Connector/ODBC Programming

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.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

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

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

The following sample creates a table my_odbc_net and demonstrates its use in 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");            }        }    }  }}

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