Accessing Sqlite from a VBS script


It is quite easy, if not a little messy, to create a VBS script to create and access data in Sqlite. You just have to be careful with your double quotes and string escaping. This method does not require ODBC or any third party or external libraries.

The first script creates an sqlite database and inserts a few rows of test data. It also outputs a single row of test data by running a hard coded select statement. The second script just uses the database created by the first script and outputs data to the console based on a command line parameter.

sql.vbs

Option ExplicitConst WshFinished = 1
Const WshFailed = 2

Dim strCommand1, strCommand2

strCommand1 = “””sqlite3.exe”” “”mydatabase.sqlite”” “”CREATE TABLE person ( id int, name varchar(30), phone varchar(30) );INSERT INTO person VALUES (1, ‘Jim’, ‘123446223’);INSERT INTO person VALUES (2, ‘Tom’, ‘232124303’);INSERT INTO person VALUES (3, ‘Bill’, ‘812947283’);INSERT INTO person VALUES (4, ‘Alice’, ‘351246233’);”” ”

strCommand2 = “””sqlite3.exe”” “”mydatabase.sqlite”” “”SELECT name from person where id=2;”” ”

Dim WshShell, WshShellExec

Set WshShell = CreateObject(“WScript.Shell”)
Set WshShellExec = WshShell.Exec(strCommand1)
Set WshShellExec = WshShell.Exec(strCommand2)

Do While WshShellExec.Status = 0
WScript.Sleep 100
Loop

Dim strOutput

Select Case WshShellExec.Status
Case WshFinished
strOutput = WshShellExec.StdOut.ReadAll
Case WshFailed
strOutput = WshShellExec.StdErr.ReadAll
End Select

WScript.Echo strOutput

To create the database file named “mydatabase.sqlite” run the above script from a dos command prompt.

C:\sqlite>cscript //nologo sql.vbs
Tom

C:\sqlite>

Below is a data extrtaction script that extracts the name of the person in the sqlite table person, by the id passed as a parameter to the script

select.vbs

Option ExplicitConst WshFinished = 1
Const WshFailed = 2

Dim strCommand

Dim id
id = WScript.Arguments(0)

strCommand = “””sqlite3.exe”” “”mydatabase.sqlite”” “”SELECT name from person where id=”& id &”;””  ”

Dim WshShell, WshShellExec

Set WshShell = CreateObject(“WScript.Shell”)
Set WshShellExec = WshShell.Exec(strCommand)

Do While WshShellExec.Status = 0
WScript.Sleep 100
Loop

Dim strOutput

Select Case WshShellExec.Status
Case WshFinished
strOutput = WshShellExec.StdOut.ReadAll
Case WshFailed
strOutput = WshShellExec.StdErr.ReadAll
End Select

WScript.Echo strOutput

Example output when you run the select script. Note that 3 is a parameter passed in to the select.vbs and determines the id of the row that is returned.

C:\sqlite>cscript //nologo select.vbs 3
Bill

C:\sqlite>

With a little more work you may manipulate the data or save the output to a file using standard VBS functions.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s