Connect MySQL with VB Script

There's someone asked a question in a community about how to connect MySQL with VB Script (VBS).

I think it is an interesting topic and I've never tried it before. So I just created a prove of concept to connect MySQL with ODBC driver on Windows machine.

Here is the script:

' To run the script in PowerShell, use cscript.exe .\ConnectMySQL.vbs
dim connection, recordSet
set connection = CreateObject("ADODB.Connection")
set recordSet = CreateObject("ADODB.Recordset")

' To list all MySQL ODBC drivers installed on your machine,
' Use PowerShell Get-OdbcDriver -Name "MySQL*"
connectionString = "Driver={MySQL ODBC 8.0 Unicode Driver};Server=localhost;" & _
    "Database=thailand-administrative;User=root;Password=change-to-your-password;"

connection.Open connectionString
recordSet.Open "SELECT * FROM provinces limit 0, 10", connection
recordSet.MoveFirst

while not recordSet.EOF
    paddingSpace = string(2 - Len(recordSet.Fields(0)), " ")
    value = paddingSpace & recordSet.Fields(0) & " " &  recordSet.Fields(3)  
    WScript.StdOut.WriteLine value 
    recordSet.MoveNext
wend

connection.Close

Run the script

> cscript.exe .\ConnectMySQL.vbs

Output

 1 Bangkok
 2 Samut Prakarn
 3 Nonthaburi
 4 Pathum Thani
 5 Phra Nakhon Si Ayutthaya
 6 Ang Thong
 7 Lop Buri
 8 Sing Buri
 9 Chai Nat
10 Saraburi

Link to the source code in Github