#1. 下载MySQL Connector
- 转到地址:http://www.mysql.com/products/connector/
- 根据MySQL的平台版本,下载 ODBC 的Connector (我的MySQL是32位的,虽然我的操作系统是64位的,所以我下载了:mysql-connector-odbc-5.2.5-win32.msi)
#2. 安装MySQL Connector,找出它的Driver字符串以备后用
- 我的连接Driver是:MySQL ODBC 5.2 Unicode Driver
#3. 数据库中数据表结构(我的例子)
CREATE TABLE IF NOT EXISTS `mydb` (
`username` varchar(30) NOT NULL,
`password` varchar(30) NOT NULL,
`age` int(11) NOT NULL,
PRIMARY KEY (`username`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
插入2条数据:
INSERT INTO `mydb` (`username`, `password`, `age`) VALUES
('xiwang', '123456', 27),
('rizhou', '789', 26);
#4. 使用下边的测试代码(保存成 TestDB.vbs)
Sub Main
Dim db
Set db = New MySQLDB
db.Connect "test", "root", "xiwang", "localhost", 3306
If Err Then
MsgBox Err.Description
db.UnConnect
Exit Sub
End If
db.q("SELECT * FROM `mydb`")
If Err Then
MsgBox Err.Description
db.UnConnect
Exit Sub
End If
db.UnConnect
If db.data(0) > 0 Then
MsgBox "Row count: " & db.data(0)
MsgBox "1st row: username = " & db.data(1)("username") & ", password = " & db.data(1)("password")
MsgBox "2nd row: username = " & db.data(2)("username") & ", password = " & db.data(2)("password")
End If
Set db = Nothing
End Sub
Call Main
'''C:\windows\sysWOW64\odbcad32.exe
'''C:\windows\system32\odbcad32.exe
Class MySQLDB
'''Current Error Code:
''' - 1
''' - 2
''' - 3
Private oConn
Private oRecSet
'''data(row-index)("column-name") = 'cell value'
Public data()
Private Sub Class_Initialize
End Sub
Private Sub Class_Terminate
UnConnect
Erase data
Set dictErrDef = Nothing
MsgBox "Clean works complete."
End Sub
'''' Public Methods ''''
Public Sub Connect(db,uid,pwd,host,port)
on error resume next
Err.Clear
Dim sConnStr
'''Create CONNECTION STRING
sConnStr = "DRIVER={MySQL ODBC 5.2 Unicode Driver};"
'sConnStr = "DRIVER={MySQL ODBC 5.2 ANSI Driver};"
sConnStr = sConnStr & "Database=" & db & ";"
sConnStr = sConnStr & "User=" & uid & ";"
sConnStr = sConnStr & "Password=" & pwd & ";"
sConnStr = sConnStr & "Server=" & host & ";"
sConnStr = sConnStr & "Port=" & port & ";"
sConnStr = sConnStr & "Option=3;"
'''Create CONNECTION
Set oConn = CreateObject("ADODB.Connection")
oConn.Open sConnStr
If Err Or oConn.State = 0 Then
Err.Raise vbObjectError + 1
Exit Sub
End If
MsgBox "Connection created."
End Sub
'''QUERY
'Return:
' - data(row-index)("column name")
Public Function q(sql)
on error resume next
Err.Clear
Dim dictRowData
Dim iRowIndex
Dim i, j
'''CLEAN DATA
Erase data
'''CREATE RECORDSET
Set oRecSet = CreateObject("ADODB.Recordset")
oRecSet.CursorLocation = 3
oRecSet.Open sql, oConn
If Err Then
ReDim Preserve data(0)
data(0) = 0
Exit Function
ElseIf oRecSet.RecordCount = 0 Or oRecSet.RecordCount = -1 Then
ReDim Preserve data(0)
data(0) = 0
Exit Function
End If
ReDim Preserve data(oRecSet.RecordCount)
data(0) = oRecSet.RecordCount
'''CURSOR TOP
oRecSet.MoveFirst
iRowIndex = 1
'''CURSOR LOOP (As ROW)
Do While Not oRecSet.EOF
''will free dictRowData when erase data:array
Set dictRowData = CreateObject("Scripting.Dictionary")
For i = 0 To oRecSet.Fields.Count - 1
dictRowData(oRecSet.Fields(i).Name) = oRecSet.Fields(i).Value
Next
ReDim Preserve data(iRowIndex)
Set data(iRowIndex) = dictRowData
iRowIndex = iRowIndex + 1
oRecSet.MoveNext
Loop
oRecSet.Close
Set oRecSet = Nothing
End Function
Public Sub UnConnect()
If oConn Is Nothing Then
Exit Sub
End If
oConn.Close
Set oConn = Nothing
End Sub
End Class
#5. 运行结果
依次弹出对话框:- Connection created.
- Row Count: 2
- 1st row: username = xiwang, password = 123456
- 2nd row: username = rizhou, password = 789
- Clean works complete.
----
Have Fun ^_^
----