Importing Data from a Database

[rajeshwar]

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.
----------------------------------------------------------

Code:

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

[Saket]

Yes, you are in the right direction. this will import the data set to QTP Data table

[rajeshwar]

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.

[venkatbatchu]

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

Code:

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

[venkatbatchu]

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

[lotos]

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.

[lotos]

[Seema Gupta]

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

[qtp-qa]

Hi,

I have created a qtp test for an application. Which is:

Code:

Option Explicit Dim var_Exist, x , y Dim rowcount, WshShell Dim rownumber var_Exist = Window("HyperTerminal_3").Exist While var_Exist="True" Call excel() Wend Function excel () datatable.Import"C:\HT_test\Commands.xls" datatable.GetSheet(1) rowcount = datatable.GetRowCount For rownumber=1 to rowcount-1 datatable.SetCurrentRow(rownumber) x= datatable.Value("Command") If x= "e" Then var_Exist="False" else y=datatable.Value("TimeInterval") Window("HyperTerminal_3").WinObject("Term Class").Type x Set WshShell = CreateObject("WScript.Shell") WshShell.SendKeys "~" wait (y) Set WshShell = Nothing End If Next

This is running fine in Qtp but I want to run the script without Qtp. So I pasted the script in notepad and saved it as .vbs file. But when I close Qtp to run the .vbs file it gives me error saying "Variable is undefined : "window"" error code 800A01F4 (Line 6, Char 1) Can anybody please explain me why am I getting this error?