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]

hi man,
you can look here: DB selects from QTP...
also for updates, if you need: DB Updates from QTP...
Hope this will help you!

[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?