[/font][font=Verdana]
Hi Guys,
Is there any way to Automate the feature provided by QTP to import data from a Database Table to DataSheet(Sheet>>Import>>From Database) where we can establish the connection and fetch the Data from a database table.
Can we acheive this using QTP object Model?
I have the information that data can be fetched from DB in following manner, just wondering if there's a Way to acheive Import Database feature of QTP DataSheet.
----------------------------------------------------------
set con = CreateObject("adodb.connection")
Set rs = CreateObject("adodb.recordset")
con.Open("Provider=sqloledb.1;Data Source=RAJESHWAR;Initial Catalog=Northwind;User Id='sa';Password='sa'")
rs.Open "select Top(10) * from customers",con
DataTable.GlobalSheet.AddParameter "CustomerId",""
DataTable.GlobalSheet.AddParameter "CompanyName",""
DataTable.GlobalSheet.AddParameter "CustomerName",""
i=1
Do while Not rs.EOF
DataTable.GlobalSheet.SetCurrentRow(i)
DataTable.Value("CustomerId") =rs.Fields.Item(0)
DataTable.Value("CompanyName") =rs.Fields.Item(1)
DataTable.Value("CustomerName") =rs.Fields.Item(2)
i=i+1
rs.MoveNext
Loop
con.Close
Yes, you are in the right direction. this will import the data set to QTP Data table
Hi Saket,
We are looking for a better approach than this, where we can just specify the table name and the entire table content gets imported to datatable along with column headers at runtime?
Like we do Importsheet with excel.
Is this possible using QTP object model? If yes can somebody give an example.
Thanks,
Raj.
(05-26-2010 02:48 PM)rajeshwar Wrote: [ -> ]Hi Saket,
We are looking for a better approach than this, where we can just specify the table name and the entire table content gets imported to datatable along with column headers at runtime?
Like we do Importsheet with excel.
Is this possible using QTP object model? If yes can somebody give an example.
Thanks,
Raj.
Hi,
You can use the below code to work like Datatable.Import i.e which will add all the field names (available in data base) to excel i.e outsheet
set outsheet=Datatable.Addsheet("output")
'This for loop is used to add the fields to excel
For each fld in adoRecordset.Fields
outsheet.AddParameter(fld.name)
Next
While adorecordset.eof( )
'This for loop is used to add the field values to respective field names.
For each fld in adorecordset.Fields
outsheet.GetParameter(fld.name).value=fld.value
Next
Loop
Please let me know if you need any clarification
Regards,
Venkat.Batchu
Hi Rajeshwar,
Please find the below code which i have done on flight application.
The following code fetch the data from database to data table.
Code:
Dim adocon,adorecordset
Set adocon=CreateObject("ADODB.Connection")
constr="DSN=QT_Flight32;DBQ=C:\Program Files\HP\QuickTest Professional\samples\flight\app\flight32.mdb;Driver=C:\WINDOWS\system32\odbcjt32.dll;DriverId=281;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5"
adocon.Open constr
Set adorecordset=CreateObject("ADODB.Recordset")
Set adorecordset.ActiveConnection=adocon
Strsql="Select * from orders"
adorecordset.Source=Strsql
adorecordset.Open
set outsheet=Datatable.Addsheet("output")
For each fld in adoRecordset.Fields
outsheet.AddParameter fld.name,""
Next
i=1
Do while Not adorecordset.EOF
DataTable.SetCurrentRow(i)
For each fld in adorecordset.Fields
outsheet.GetParameter(fld.name).value=fld.value
Next
adorecordset.MoveNext
i=i+1
Loop
Wait 5
Please let me know for further clarification.
Regards,
Venkat. Batchu
hi rajeshwar, try this method:
Code:
Public Function DBQuery
Set DBQuery = New DBConnect
End Function
Class DBConnect
'querying a 'SELECT' event within DB
Public Function DBSelect(sqlSelect, itemCode)
If Browser("URL:=http://environment.at/project/.*").Exist Then
set conn = createobject("adodb.connection")
conn.open "DSN=ENV_DEV_DB; UserID=userID; Password=password;"
set rs = createobject("adodb.recordset")
'get the date value from DB for an Event
rs.open sqlSelect, conn
dBitem = rs(itemCode)
rs.close
reporter.ReportEvent 2, "Data BASE:", "the Data within DB is: '" & dBitem & "'"
ElseIf Browser("URL:=http://environment.st/project/.*").Exist Then
set conn = createobject("adodb.connection")
conn.open "DSN=ENV_TST_DB; UserID=userID; Password=password;"
set rs = createobject("adodb.recordset")
'get the date value from DB for an Event
rs.open sqlSelect, conn
dBitem = rs(itemCode)
rs.close
reporter.ReportEvent 2, "Data BASE:", "the Data within DB is: '" & dBitem & "'"
ElseIf Browser("URL:=http://environment.ut/project/.*").Exist Then
set conn = createobject("adodb.connection")
conn.open "DSN=ENV_UAT_DB; UserID=userID; Password=password;"
set rs = createobject("adodb.recordset")
'get the date value from DB for an Event
rs.open sqlSelect, conn
dBitem = rs(itemCode)
rs.close
reporter.ReportEvent 2, "Data BASE:", "the Data within DB is: '" & dBitem & "'"
Else
reporter.ReportEvent micFail, "ERROR:", "The browser was not found!"
End If
' assigning the output value
DBSelect = dBitem
End Function
End Class
And call it within your steps/actions as here:
Code:
' get the Requested value from DB
DBRequested_Value = DBQuery.DBSelect (sqlSelect, "ValueFromDB")
' (sqlSelect, itemCode)
where for e.g:
sqlSelect = UCASE("SELECT CLB5VA as ValueFromDB FROM CSCLREP WHERE CLAINB = '80808' AND CLBHDT = '1100909' and CLWTNB='01'")
itemCode = "ValueFromDB"
or
itemCode = "CLB5VA".
The
'IF' statements are used if you are testing on more than one environments. Remove IF if it is not necessary for your project.
On my project this code is working very good.
(07-14-2010 03:15 PM)venkatbatchu Wrote: [ -> ] (05-26-2010 02:48 PM)rajeshwar Wrote: [ -> ]Hi Saket,
We are looking for a better approach than this, where we can just specify the table name and the entire table content gets imported to datatable along with column headers at runtime?
Like we do Importsheet with excel.
Is this possible using QTP object model? If yes can somebody give an example.
Thanks,
Raj.
Hi,
You can use the below code to work like Datatable.Import i.e which will add all the field names (available in data base) to excel i.e outsheet
set outsheet=Datatable.Addsheet("output")
'This for loop is used to add the fields to excel
For each fld in adoRecordset.Fields
outsheet.AddParameter(fld.name)
Next
While adorecordset.eof( )
'This for loop is used to add the field values to respective field names.
For each fld in adorecordset.Fields
outsheet.GetParameter(fld.name).value=fld.value
Next
Loop
Please let me know if you need any clarification
Regards,
Venkat.Batchu
I have not actually tried /work on it but what you can do is
1) Export Functionality of SQL to export the given data in table to excel sheet.
2)Once your xls file is ready, you can import it to QTP with DataTable.Import "filepath"
As compared to your solution this will take less time.
Regards,
Seema Gupta