How to Connect to a MySQL Database

Hi, this is amazing - connect and work with a MySQL Database, shown in VBS.

First we need to install MySQL and create a database, a table and insert some Data. If you already created a db and a table you can skip this step.

1. Creating a Database:

Open a DOS-Prompt and change into the \bin directory of MySQL.
We run mysql.exe

Code:

C:\MySQL\>mysql

Now you can insert some SQL commands, and we create a database 'Users'.

Code:

mysql> CREATE DATABASE Users;

We need to change into the created database with :

Code:

mysql> \u Users

After this we need to create a table 'TabUsers' with :

Code:

mysql> CREATE TABLE TabUsers (
UID INT2 PRIMARY KEY auto_increment,
Name VARCHAR(25),
Vorname VARCHAR(25),
INDEX(LastName),
INDEX(Name)
);

The last step is to create a user in mysql to allow our script establish a connection with the mysql daemon. We need to change the database to 'mysql' which is created by default.

Code:

mysql> \u mysql

2. Insert some data:[/b]

... and insert a user :

Code:

mysql> INSERT INTO USER (Host, USER, Password, Select_priv)
VALUES('localhost', 'me', PASSWORD('nopwd'), 'Y');

This will create a user with username 'me' and password 'nopwd' (which will be stored crypted by mysql) for the host 'localhost'.
Now enter QUIT an exit the mysql console.
Enter at the DOS-prompt mysqladmin reload and restart the mysql console with entering mysql.

Change into the database 'Users' and insert some data into it with this

Code:

mysql> \u Users
INSERT INTO TabUsers (LastName, Name) VALUES('Simpson', 'Mike');
INSERT INTO TabUsers (LastName, Name) VALUES('Jackson', 'Michael');

Now quit the console with 'quit' and now we can code our VBScript Wink

3. Create the VBScript :[/b]

Code:

Option Explicit

'---- CursorTypeEnum Values ----
Const adOpenForwardOnly = 0
' Const adOpenKeyset = 1
' Const adOpenDynamic = 2
' Const adOpenStatic = 3

'---- LockTypeEnum Values ----
Const adLockReadOnly = 1
' Const adLockPessimistic = 2
' Const adLockOptimistic = 3
' Const adLockBatchOptimistic = 4

'---- CursorLocationEnum Values ----
' Const adUseServer = 2
Const adUseClient = 3

'---- ConnectModeEnum Values ----
' Const adModeUnknown = 0
Const adModeRead = 1
' Const adModeWrite = 2
' Const adModeReadWrite = 3
' Const adModeShareDenyRead = 4
' Const adModeShareDenyWrite = 8
' Const adModeShareExclusive = &Hc
' Const adModeShareDenyNone = &H10
' Const adModeRecursive = &H400000

Dim Conn, RS
Set Conn = CreateObject("ADODB.Connection")
Set RS = CreateObject("ADODB.Recordset")
Conn.Provider = "MSDASQL"
Conn.Mode = adModeRead
Conn.Open "Driver=MySQL; Server=localhost; Database=Users;", "me", "nopwd"
' For the new ODBC 3.51 driver !
' Conn.ConnectionString = _
' "DRIVER={MySQL ODBC 3.51 Driver};DATABASE=DBName;SERVER=localhost;"

Conn.CursorLocation = adUseClient

RS.CursorLocation = adUseClient
RS.Open "Select * from TabUsers", Conn, adOpenForwardOnly, adLockReadOnly

Do While Not RS.EOF
WScript.Echo RS.Fields("UID") & " " & _
RS.Fields("LastName") & ", " & RS.Fields("Name")
RS.MoveNext
Loop

RS.Close
Conn.Close
Set RS = Nothing
Set Conn = Nothing

If we run this script on DOS-promt we can see our both user we inserted in step 2 Wink

Code:

csript C:\mysql.vbs