StarQuest Technical Documents

Using VBScript and OLEDB with StarSQL

Last Update: 18 July 2011
Product: StarSQL
Version: 5.x
Article ID: SQV00SQ055


This document describes how to execute a DB2 query and call stored procedures from a VBScript application using the StarSQL software and the Microsoft OLEDB Provider for ODBC (MSDASQL).


See below for samples of executing a simple SQL query, calling a simple stored procedure with literal parameters and no output, and calling a stored procedure with bound parameters.

64-bit Considerations:

  • If you are using a 64-bit operating system, the default VBScript interpreter is a 64-bit application, and uses the 64-bit version of StarSQL and 64-bit data sources.
  • If you prefer to use the 32-bit version of StarSQL, create 32-bit data sources with \windows\syswow64\odbcad32.exe and invoke the 32-bit interpreter with

    C> \windows\syswow64\cscript /nologo myscript.vbs

  • If you are using a 64-bit version of Windows 2003, you will need to download and install the Microsoft 64-Bit OLEDB Provider for ODBC (MSDASQL) WindowsServer2003.WindowsXP-KB948459-v2-x64-ENU.exe. MSDASQL is already provided in later 64-bit Windows operating systems (Windows Server 2008, Windows Vista SP1 and Windows 7).
  • The parameter "Provider=MSDASQL" is required when using the 64-bit interpreter (it is optional in a 32-bit environment).

Example 1: Simple SQL query

Dim Conn

' This script must be run from Cscript since it uses WScript.Stdout
If LCase(Right(WScript.FullName, 11)) <> "cscript.exe" Then
WScript.Echo _
"Issue the following command to run this script from" & VbCrLf _
& "the command prompt:" & VbCrLf _
& VbCrLf _
& "Cscript /nologo stat "
End If

Set Conn = CreateObject("ADODB.Connection")
Conn.ConnectionString = "Provider=MSDASQL;DSN=MYDSN;UID=MYUSER;PWD=MYPWD"

Set RS = Conn.Execute("SELECT * from MYTABLE")

Header = ""

For i = 0 to RS.Fields.Count - 1
Header = Header + RS(i).Name + vbTab


do while not RS.EOF

Result = ""
For Each f In (RS.Fields)
Result = Result + Cstr(f.value) + vbTab

Set Conn = Nothing

Example 2: Simple Stored Procedure

This example shows the use of a simple stored procedure call to execute a program on an IBM i (AS/400) host using the program QCMDEXC. See the StarQuest technical note SQV00SQ040: Calling an AS/400 Program with QCMDEXC for details.

Dim Conn
Set Conn = CreateObject("ADODB.Connection")
Conn.ConnectionString = "Provider=MSDASQL;DSN=MYDSN;UID=MYUSER;PWD=MYPWD"
Conn.Execute("CALL QSYS.QCMDEXC('CRTLIB TEMP',0000000011.00000)")
Set Conn = Nothing

Example 3: Paramerized Stored Procedure

This script calls a stored procedure created with:

create procedure MYLIB.MYPROC(IN PA1 INTEGER, IN PA2 CHAR(5), OUT PA3 INTEGER, OUT PA4 CHAR(15), OUT PA5 INTEGER) language SQL BEGIN SET PA4='some output string';SET PA3=0;SET PA5=201101661; END

Dim Conn, Cmd

' These values can be found in the ADO Reference of the MSDN Library under the topic DataTypeEnum under ADO Enumerated Constants.

adInteger = 3
adParamInput = 1
adChar = 129
adParamOutput = 2
adCmdStoredProc = 4
adDecimal = 14
adVarChar= 200

Set Conn = CreateObject("ADODB.Connection")
Set Cmd = CreateObject("ADODB.Command")
Conn.ConnectionString = "Provider=MSDASQL;DSN=MYDSN;UID=MYUSER;PWD=MYPWD"
Cmd.ActiveConnection = Conn

Cmd.CommandText = "MYLIB.MYPROC"
Cmd.CommandType = adCmdStoredProc

Cmd.Parameters.Append Cmd.CreateParameter("PA1", adInteger, adParamInput, , 0)
Cmd.Parameters.Append Cmd.CreateParameter("PA2", adChar, adParamInput, 5, "TRANS")
Cmd.Parameters.Append Cmd.CreateParameter("PA3", adInteger, adParamOutput, , 0)
Cmd.Parameters.Append Cmd.CreateParameter("PA4", adChar, adParamOutput, , 0)
Cmd.Parameters.Append Cmd.CreateParameter("PA5", adInteger, adParamOutput, , 0)


' Display the output parameters
MsgBox Cmd.Parameters(2).Value
MsgBox Cmd.Parameters(3).Value
MsgBox Cmd.Parameters(4).Value

Set Cmd = Nothing
Set Conn = Nothing





The information in technical documents comes without any warranty or applicability for a specific purpose. The author(s) or distributor(s) will not accept responsibility for any damage incurred directly or indirectly through use of the information contained in these documents. The instructions may need to be modified to be appropriate for the hardware and software that has been installed and configured within a particular organization.  The information in technical documents should be considered only as an example and may include information from various sources, including IBM, Microsoft, and other organizations.