IBM DB2 connection with VB script

[bmurali]

Hi,

Can anyone please tell me how we can connect to IBM DB2 database using VB Scripts.?

Please explain with the detailed steps.

Thanks in advance.

[SRP]

this works with UFT 11.53 and IE 10

Dim objConnection,objRecordset,strSearchCriteria

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3

Dim signonuser ,signonpass, current_region, regionTable
        logInfo = getLogInfo()
        signonuser  = logInID ' set to real logon ID
        signonpass  = logInPW 'Set to real logon Password
        current_region  = "[email=DB2Region@logInfo(]DB2Region[/email]" ' set to Mainframe DB2 region iD

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")

Dim strCon, SQLStr

strCon = "driver={IBM DB2 ODBC DRIVER - DB2};Database=TestDB;hostname=DB2CONNSRV;port=50000;protocol=TCPIP; uid=" & signonuser & "; pwd=" & signonpass

objConnection.Open strCon

'format SQL
SQLStr = "select * ..."

objConnection.Execute SQLStr

objRecordset.Open SQLStr , objConnection, adOpenStatic '
objConnection.Execute SQLStr
isqlCols = objRecordset.Fields.count
If isqlCols > 0 and NOT objRecordset.EOF Then
    ReDim Account(CInt(isqlCols - 1))
    For i = 0 To isqlCols - 1
        ReturnedField(i) = objRecordset(i)
    Next
Else
    ReDim ReturnedField(0)
    ReturnedField(0) = 999999999 'this is what I send back when nothing is returned from the query
End If
objRecordset.Close
objConnection.Close
Set objRecordset = Nothing
Set objConnection = Nothing

[vinod123]

Code:

Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = ConnVar
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "Test"
cmd.Parameters.Append(cmd.CreateParameter("@my_date", adVarChar, adParamInput,10))
cmd.Parameters("@my_date") = "10/12/2012"

Set rsResults = Server.CreateObject("ADODB.Recordset")
rsResults.CursorLocation = adUseClient
rsResults.Open cmd,,adOpenForwardOnly,adLockBatchOptimistic

Using CursorLocation = adUseClient means you can navigate the rsResults RecordSet using MoveNext, MoveFirst etc.