StarQuest Technical Documents

Using VBScript and OLEDB with StarSQL

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

Abstract

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).

Solution

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 "
WScript.Quit
End If

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

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

Header = ""

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

WScript.StdOut.WriteLine(Header)

do while not RS.EOF

Result = ""
For Each f In (RS.Fields)
Result = Result + Cstr(f.value) + vbTab
Next
WScript.StdOut.WriteLine(Result)
RS.MoveNext
loop

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.Open
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"
Conn.Open
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)

Cmd.Execute

' 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