How to write Qtp script to import data from SQL database to QTP Datatable?

Replies to This Discussion

Permalink Reply by shirish on July 5, 2010 at 10:50pm

here first of all you need to write connection string to connect to database first from qtp.
ex.
Set Conn = Server.createobject(“ADODB.Connection”)
Conn.Open “abc;” & _
“Data Source=xyz;” & _
“Initial Catalog=pqr;” & _
“User Id=stp;” & _
“Password=qlr”

once the connection is established,fire a query on database table from where you need to take the data,store the data in a variable. And then store it to appropriate datatable.

• ▶ Reply

Permalink Reply by Nivas on July 8, 2010 at 11:43am

Thank you.

• ▶ Reply

Permalink Reply by Thonukunuri Aruna on July 6, 2010 at 12:17pm

Hi Nivas,

Below script is for connecting to DB and getting data and storing username and password in datatable.

Set dbObject = CreateObject("ADODB.Connection")
'Set the connection string parameters
ConnectionString = "DSN=TestScript;Description=TestScriptTraining;UID=;PWD=;APP=QuickTest Professional;WSID=;DATABASE="
'Assign the connection string
dbObject.ConnectionString = ConnectionString
'Open the db connection
dbObject.Open
Set dbRecordset = dbObject.Execute("Select Top 1 * from AgentLogin")

datatable.GetSheet(1).addparameter "UserName", ""
datatable.GetSheet(1).addparameter "Password", ""

If NOT(dbRecordSet.EOF or dbRecordSet.BOF) Then

row = 1

While Not dbRecordSet.EOF

datatable.setcurrentrow(row)
datatable.Value(1, 1) = dbRecordSet.fields("UserName")
datatable.Value(2, 1) = dbRecordSet.fields("Password")
row = row +1
dbRecordSet.movenext
Wend
End If


Thanks,
Aruna

• ▶ Reply

Permalink Reply by Nivas on July 8, 2010 at 11:43am

Thanks very much Aruna......the script you gave is working fine.

I need one clarification i.e., the script you have given is for getting only specified fields(UserName & Password). Suppose if I don't know how many fields are there in database and I want to get all the fields from database and store them in datatable. Then how can we write script for that?

Please help me in this too.

Thanks in advance.

Regards,
Srinivas.

• ▶ Reply

Permalink Reply by Thonukunuri Aruna on July 8, 2010 at 12:42pm

Hi Nivas,

Below scripts gets all the fields from DB and writes to QTP results log file.

Set dbObject = CreateObject("ADODB.Connection")
'Set the connection string parameters
ConnectionString = "DSN=TestScript;Description=TestScriptTraining;UID=;PWD=;APP=QuickTest Professional;WSID=;DATABASE=TestDB"
dbObject.ConnectionString = ConnectionString
dbObject.Open
Set dbRecordset = dbObject.Execute("Select Top 1 * from AgentLogin")
Call represent_values_of_RecordSet(dbRecordset)

Function represent_values_of_RecordSet( myrs)

dim curRowString
myrs.MoveFirst
reporter.ReportEvent 4,"Fields quantity" , myrs.fields.count
count_fields = myrs.fields.count-1
curRow=0
Do Until myrs.EOF
curRowString= ""
curRow = curRow+1
For ii=0 to count_fields
curRowString = curRowString& "Field " &"==> " & myrs.fields(ii).Name &" : Value ==>" & myrs.fields(ii).Value & vbCrLf
Next
myrs.MoveNext
reporter.ReportEvent 4,"Current row"& curRow , curRowString
Loop
End Function


Thanks,
Aruna

• ▶ Reply

Permalink Reply by Nivas on July 11, 2010 at 1:15pm

Thanks a lot Aruna.

• ▶ Reply

Permalink Reply by Sarang Dalal on July 20, 2010 at 11:51am

thanks

• ▶ Reply

Permalink Reply by Manthan shah on October 17, 2012 at 11:41pm

how to create script for

 

Run backup script to reset history to contain necessary data to trigger desired notification.   Backup Script Path : P:\ABCD\OJIS\ED\AllShared\CJRDM_reset_NYSIDs\CCH_Notiification_TSTIJ\1404.sql

• ▶ Reply

Permalink Reply by Venkataramana on October 18, 2012 at 4:48pm

Set objCon=CreateObject("ADODB.Connection")
Set objRs=CreateObject("ADODB.RecordSet")
conStr="Provider=SQLOLEDB.1;Password=pa55w0rd!;Persist Security Info=False;User ID=pboas;Initial Catalog=DBTraining;Data Source=<source>  "
objCon.ConnectionString =conStr
objCon.Open
Set objCom=CreateObject("ADODB.Command")
objCom.ActiveConnection=objCon
Set fso=createobject("scripting.filesystemobject")
Set app=createobject("quicktest.application")
root=fso.GetParentFolderName(environment("TestDir"))
projectfolder=fso.GetParentFolderName(root)
print projectfolder
executefile "Filepath"
g=inputbox("enter user id ")
query=("select * from flightlog where userid="+g)
Set objRs=objCon.Execute(query)
Do Until objRs.EOF=True
    f=objRs.Fields("userName").Value
    e=objRs.Fields("pasword").Value
    Print "The User Name and Password are : " & f & e

From the values in f and e we can assign it to data table.

• ▶ Reply