QTP - DB Functions (MySQL DB)

'' DB Functions for MySql DB

Set dbObj = CreateObject("ADODB.Connection")
Set myrecordset = CreateObject("ADODB.Recordset")
Set outputSheet = DataTable.AddSheet("output")

'' Connection string as below
conString = "DATABASE=testDB;DRIVER={MySQL ODBC 5.1 Driver};OPTION=0;PWD=sundar;PORT=3306;SERVER=127.0.0.1;UID=root"
dbObj.open conString
strSQL = "Select * from emp"
myrecordset.open strSQL, dbObj, adOpenStatic
print myrecordset.Fields.Count

myrecordset.MoveFirst

While not myrecordset.EOF
print "Employee Name:" & myrecordset.Fields("empname").value ''' By Column name
print "Employee ID:" & myrecordset.Fields(1).value '' By Index. Note: Index starts with 0
myrecordset.MoveNext
Wend

'' Method 2- Use connection object directly to get the recordset
Dim conRecordSet
Set conRecordSet = dbObj.Execute(strSQL)
'print conRecordSet.Count

'' Exporting the results to DataTable
''  Adding column names
For each fld in conRecordSet.Fields
print fld.name
outputSheet.AddParameter Replace(fld.name," ","_"), fld.value
Next

conRecordset.MoveFirst
i =1
While not conRecordset.EOF
For each fld in conRecordset.Fields
print "Adding"&fld.name & fld.value
outputSheet.SetCurrentRow(i)
outputSheet.GetParameter(Replace(fld.name," ","_")).value = fld.value
Next
i = i+1
conRecordset.MoveNext
Wend

'' closing the DB connection
dbObj.close
Set dbObj = Nothing
Set myrecordset = Nothing


Useful Links http://www.connectionstrings.com/mysql