第26章:连接器 / 26.1. MySQL Connector/ODBC / 26.1.19. MyODBC与VB:ADO、DAO和RDO
26.1.19.1. ADO: rs.addNew, rs.delete和rs.update
26.1.19.2. DAO: rs.addNew, rs.update和滚动
26.1.19.3. RDO: rs.addNew和rs.update
在本节中,给出了MySQL ODBC 3.51驱动程序与ADO、DAO和RDO一起使用的一些简单示例。

26.1.19.1. ADO: rs.addNew, rs.delete和rs.update

在下面的ADO(ActiveX数据对象)示例中,创建了表my_ado,并演示了rs.addNewrs.deleters.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(使用MySQL ODBC 3.51驱动程序连接到MySQL服务器)
Set conn = New ADODB.Connection
conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};"_
                      & "SERVER=localhost;"_
                      & " DATABASE=test;"_
                      & "UID=venu;PWD=venu; OPTION=3"
 
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 insertrs插入)
rs.Open "select * from my_ado", conn, adOpenDynamic, adLockOptimistic
rs.AddNew
rs!Name = "Monty"
rs!txt = "Insert row"
rs.Update
rs.Close
 
'rs updaters更新)
rs.Open "SELECT * FROM my_ado"
rs!Name = "update"
rs!txt = "updated-row"
rs.Update
rs.Close
 
'rs update second time..rs更新第2次…)
rs.Open "SELECT * FROM my_ado"
rs!Name = "update"
rs!txt = "updated-second-time"
rs.Update
rs.Close
 
'rs deleters删除)
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

26.1.19.2. DAO: rs.addNew, rs.update和滚动

在下面的DAO(数据访问对象)示例中,创建了表my_dao,并演示了rs.addNewrs.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 server using MySQL ODBC 3.51 Driver(使用MySQL ODBC 3.51驱动程序连接到MySQL)
Set 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_dao(创建表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(使用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..rs更新)
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 Scrollingrs滚动)
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

26.1.19.3. RDO: rs.addNew和rs.update

在下面的RDO(远程数据对象)示例中,创建了表my_rdo,并演示了rs.addNewrs.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=3"
 
  cn.CursorDriver = rdUseOdbc
  cn.EstablishConnection rdDriverPrompt
 
 
  'drop table my_rdo(舍弃表my_rdo
  SQL = "drop table if exists my_rdo"
  cn.Execute SQL, rdExecDirect
 
  'create table my_rdo(创建表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 insertrs插入)
  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 insertrs插入)
  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 updaters更新)
  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