Quality Testing

Quality is delighting customers

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

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

Views: 6878

Reply to This

Replies to This Discussion

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.
Thank you.
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
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.
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
Thanks a lot Aruna.
thanks

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

 

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.

RSS

TTWT Magazine


Advertisement

Advertisement

Advertisement

Advertisement

© 2020   Created by Quality Testing.   Powered by

Badges  |  Report an Issue  |  Terms of Service