Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Importing Data from a Database
#1
Solved: 11 Years, 3 Months ago
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
Reply
#2
Solved: 11 Years, 3 Months ago
Yes, you are in the right direction. this will import the data set to QTP Data table

Reply
#3
Solved: 11 Years, 3 Months ago
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.
Reply
#4
Solved: 11 Years, 3 Months ago
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
Reply
#5
Solved: 11 Years, 3 Months ago
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
Reply
#6
Solved: 11 Years, 3 Months ago
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.
Reply
#7
Solved: 11 Years, 3 Months ago
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!
Reply
#8
Solved: 11 Years, 3 Months ago
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
Reply
#9
Solved: 11 Years, 3 Months ago
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?
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Importing excel not working Adam82 6 13,543 11-05-2012, 05:22 PM
Last Post: Adam82
  it is possible import from excel to table with expected data of checkpoint database i sarcastic 0 2,253 08-05-2011, 04:42 PM
Last Post: sarcastic
Smile importing objects and properties to excel sheet sandy.jakhar 2 3,671 03-16-2011, 03:26 PM
Last Post: sandy.jakhar
  mapping database user to database kamalteja 4 4,663 11-03-2010, 11:58 AM
Last Post: kamalteja
  Data importing from excel using script vaigundamoorthy 3 3,666 05-19-2009, 04:53 PM
Last Post: lit_73

Forum Jump:


Users browsing this thread: 2 Guest(s)