WSF and ADO with DB2, recordset.MoveNext not supported error in vbscript
I am trying to loop over a recordset returned from db2 using a .wsf file and vbscript.
vbscript libfile (lib.vbs) looks like this
'***************
Const ADOCon="Provider=IBMDADB2.1;Password=*****;User ID=*****;Data Source=yourdatasourc;"
'************************
'ADO environment is Initialised here
'*************************
Function ADOINI(strDB2Cn)
With objConnection
.Open strSQLCn
.CursorLocation=adUseClient
End With
If objConnection.Errors.Count > 0 Then
ErrorOut "Conncetion has Failed."
End If
With objCommand
.ActiveConnection = objConnection
.CommandType = adCmdText
End With
End Function
'********************
'Execute ADO Comand
'strSQL - SQL Statment to execute
'Return ADO RecordSet.
'*******************************
Function Exec(strSQL)
objCommand.CommandText = strSQL
Exec=objCommand.Execute
End Function
'******************************************
Function ErrorOut(errMsg)
Wscript.StdErr.Write Now()&" "&errMsg&vbCrLf
End Function
'****************
Function StdOut(msg)
WScript.StdOut.Write Now()&" "&msg&vbCrLf
End Function
'************************
I am using trial.wsf file to get list of records on which I am trying to loop
<?xml version="1.0" encoding="utf-8" ?>
<package xmlns="http://schemas.microsoft.com/WindowsScriptHost">
<job id="main">
<object id="objConnection" progid="ADODB.Connection" />
<object id="objCommand" progid="ADODB.Command" />
<object id="objError" progid="ADODB.Error" />
<reference object="ADODB.Connection" />
<reference object="ADODB.Command" />
<reference object="ADODB.Error" />
<script language="VBScript" src="lib.vbs">
ADOINI(ADOCon)
Set objRS = Exec("SELECT REF_CRSETTINGS.NAME, REF_CRSETTINGS.VALUE FROM WMRCR.REF_CRSETTINGS REF_CRSETTINGS WHERE TRIM(UPPER(REF_CRSETTINGS.CATEGORY)) IN ('SAMPLE_SETTINGS') ORDER BY REF_CRSETTINGS.CRSETTINGSCODE")
' the above recordset is a name value pair based on the category
StdOut objRS("NAME").Value 'this worked fine
objRS.MoveNext ' this doesnt work neither does check for EOF or BOF
</script>
</job>
</package>
I thought the cursor type might be wrong,
but I cant even set cursosr type to dynamic, could not get vbscript error.
maybe it's a vendor issue, but I can't confirm it.
I want to do something like this, but I can't get stuck on a recordset.
Do While Not objRS.EOF
Select Case UCase(trim(objRS("NAME").Value))
Case "SOAPSERVER" SOAPSERVER=objRS("VALUE").Value
Case "SOAPMESSAGE" SOAPMESSAGE=objRS("VALUE").Value
Case "SOAPACTION" SOAPACTION=objRS("VALUE").Value
Case Else ErrorOut "Error: InCorrect Value"
End Select
objRS.MoveNext
Loop
Sure there is something basic / stupid here, not very knowledgeable about wsf and scripting.
Based on the KB article mentioned in the comment, you might just want to dump the recordset into an array using. GetRows and process the array. check it
A function is a named piece of code that returns something via (in VBScript) by assigning a name to the function. Most of the "functions" are not in your library, and therefore are not.
The Exec function tries to return a set of records, i.e. an object. Object assignment in VBScript is necessary Set
. So:
Function Exec(strSQL)
objCommand.CommandText = strSQL
Set Exec=objCommand.Execute
End Function