StarQuest Technical Documents

Sample Visual Basic ADO Code to Access Static SQL

Last Update: 26 June 2008
Product: StarSQL
Version: 5.x
Article ID: SQV00SQ045

Abstract

Below is sample code written in Visual Basic 6.0 that demonstrates how to access a static SQL statement embedded in a static package bound on the host. This method accesses the statement directly by statement name, rather than the more typical technique, which relies on the StarSQL driver to match SQL statements in the code to those in the package.

Solution

First, make sure your StarSQL data source has "enabled matching" and that the appropriate .sbp file is specified under Static SQL options. This is crucial to ensuring static SQL works properly. For more details on creating and binding the static SQL package, see the StarSQL Help, Configuring SQL Options.

Second, to use this code, do the following:

1. Create a form named Form1.
2. Add command button named Command1.
3. Add a listbox called List1.
4. Add "Microsoft ActiveX Data Objects 2.0 Library" to Project | References.
5. Add the code below to the command button's click event.
6. Modify the connect string to match your data source name and uid and pwd.
7. Modify the CALL STATEMENT to reflect the statement in your .sbp file that you wish to match (this statement should already be recorded; the static package should be saved and bound -- refer to the StarScribe Package Editor help for details.)
8. Modify the parameter properties as appropriate to the SQL statement you are accessing in the .sbp file

Sample Code

Private Sub Command1_Click()

On Error GoTo Command3_ErrorProc:
Dim adoConn As New ADODB.Connection
Dim adoCmd As New ADODB.Command
Dim objErr As ADODB.Error
Dim adoParm As ADODB.Parameter
Dim adoRsc As ADODB.Recordset
Dim RecordsAffected As Long
Dim SqlStmt As String
Dim j As Integer

Me.MousePointer = 11
List1.Clear

SqlStmt = "CALL STATEMENT00001(?)"

adoConn.Open "DSN=MY_DSN;UID=MY_UID;PWD=MY_PWD"
adoConn.CursorLocation = adUseClient

adoCmd.Name = "TestParm"
Set adoCmd.ActiveConnection = adoConn
adoCmd.CommandText = SqlStmt
adoCmd.CommandType = adCmdText
adoCmd.CommandTimeout = 15

Set adoParm = New ADODB.Parameter
adoParm.Name = "MY_PARM"
adoParm.Type = adVarChar
adoParm.Size = 15
adoCmd.Parameters.Append adoParm

adoParm.Value = "TEST"

Set adoRsc = adoCmd.Execute(RecordsAffected)
For j = 1 To adoRsc.RecordCount
List1.AddItem "[" & adoRsc(0) & "] " & Len(adoRsc(0))
adoRsc.MoveNext
Next

Set adoCmd = Nothing
Set adoConn = Nothing
Me.MousePointer = 0
Exit Sub

Command3_ErrorProc:
Me.MousePointer = 0

If Not adoCmd Is Nothing Then
Set adoCmd = Nothing
End If

If Not adoConn Is Nothing Then
Set adoConn = Nothing
End If

MsgBox Err.Description

Exit Sub

End Sub