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.
source to share
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
source to share