Importing Data from a Database

Importing Data from a Database - Printable Version

+- Micro Focus QTP (UFT) Forums (https://www.learnqtp.com/forums)
+-- Forum: Micro Focus UFT (earlier known as QTP) (https://www.learnqtp.com/forums/Forum-Micro-Focus-UFT-earlier-known-as-QTP)
+--- Forum: VB Scripting/Descriptive Programming (https://www.learnqtp.com/forums/Forum-VB-Scripting-Descriptive-Programming)
+--- Thread: Importing Data from a Database (/Thread-Importing-Data-from-a-Database)

Importing Data from a Database - rajeshwar - 05-26-2010

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

RE: Importing Data from a Database - Saket - 05-26-2010

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

RE: Importing Data from a Database - rajeshwar - 05-26-2010

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.

RE: Importing Data from a Database - venkatbatchu - 07-14-2010

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

RE: Importing Data from a Database - venkatbatchu - 08-27-2010

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

RE: Importing Data from a Database - lotos - 09-14-2010

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.

RE: Importing Data from a Database - lotos - 11-23-2010

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!

RE: Importing Data from a Database - Seema Gupta - 04-12-2011

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

RE: Importing Data from a Database - qtp-qa - 04-03-2012

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?