用 VBScript 的 ADODB 串連 MySQL 資料庫

#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 MainDim dbSet db = New MySQLDBdb.Connect "test", "root", "xiwang", "localhost", 3306If Err ThenMsgBox Err.Descriptiondb.UnConnectExit SubEnd Ifdb.q("SELECT * FROM `mydb`")If Err ThenMsgBox Err.Descriptiondb.UnConnectExit SubEnd Ifdb.UnConnectIf db.data(0) > 0 ThenMsgBox "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 IfSet db = NothingEnd SubCall Main'''C:\windows\sysWOW64\odbcad32.exe'''C:\windows\system32\odbcad32.exeClass MySQLDB'''Current Error Code:'''- 1'''- 2'''- 3Private oConnPrivate oRecSet'''data(row-index)("column-name") = 'cell value'Public data()Private Sub Class_InitializeEnd SubPrivate Sub Class_TerminateUnConnectErase dataSet dictErrDef = NothingMsgBox "Clean works complete."End Sub'''' Public Methods ''''Public Sub Connect(db,uid,pwd,host,port)on error resume nextErr.ClearDim sConnStr'''Create CONNECTION STRINGsConnStr = "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 CONNECTIONSet oConn = CreateObject("ADODB.Connection")oConn.Open sConnStrIf Err Or oConn.State = 0 ThenErr.Raise vbObjectError + 1Exit SubEnd IfMsgBox "Connection created."End Sub'''QUERY'Return:'- data(row-index)("column name")Public Function q(sql)on error resume nextErr.ClearDim dictRowDataDim iRowIndexDim i, j'''CLEAN DATAErase data'''CREATE RECORDSETSet oRecSet = CreateObject("ADODB.Recordset")oRecSet.CursorLocation = 3oRecSet.Open sql, oConnIf Err ThenReDim Preserve data(0)data(0) = 0Exit FunctionElseIf oRecSet.RecordCount = 0 Or oRecSet.RecordCount = -1 ThenReDim Preserve data(0)data(0) = 0Exit FunctionEnd IfReDim Preserve data(oRecSet.RecordCount)data(0) = oRecSet.RecordCount'''CURSOR TOPoRecSet.MoveFirstiRowIndex = 1'''CURSOR LOOP (As ROW)Do While Not oRecSet.EOF''will free dictRowData when erase data:arraySet dictRowData = CreateObject("Scripting.Dictionary")For i = 0 To oRecSet.Fields.Count - 1dictRowData(oRecSet.Fields(i).Name) = oRecSet.Fields(i).ValueNextReDim Preserve data(iRowIndex)Set data(iRowIndex) = dictRowDataiRowIndex = iRowIndex + 1oRecSet.MoveNextLoopoRecSet.CloseSet oRecSet = NothingEnd FunctionPublic Sub UnConnect()If oConn Is Nothing ThenExit SubEnd IfoConn.CloseSet oConn = NothingEnd SubEnd Class

#5. 運行結果

依次彈出對話方塊：

- Connection created.

- Row Count: 2

- 1st row: username = xiwang, password = 123456

- 2nd row: username = rizhou, password = 789

- Clean works complete.

----

Have Fun ^_^

----