Database Checkpoint and QTP Part 3 – Retrieving Data

by | Last Updated On Aug 7, 2014 | Database | 24 comments

In Part1 we saw the wizard method to use Database checkpoint.  In Part2 we learned how to connect to a database using scripts in QTP. In this part we will see how we can retrieve data from database using scripts.

Similar to connection object, you can use ADODB.Recordset to get the recordset object.

Set MyRecordSet = CreateObject("ADODB.Recordset")

Before starting let us first understand the properties and methods of recordset object which will help further.

RecordSet Properties Description
AbsolutePage Page of current position
AbsolutePosition Current position
ActiveConnection Active Connection Object
BOF Beginning of File
Bookmark Bookmark of current position
CacheSize Number of records cached
CursorLocation Server or Client
CursorType Forward, Static, Dynamic, Keyset
EOF End of File
EditMode Whether or not an edit is in progress
Filter What kind of records to hide
LockType Record lock type for edits or updates
MaxRecords Maximum number of records to retrieve
PageCount Total number of pages
PageSize Number of records per page
RecordCount Total number of records
Source Source Command Object
Status Status of last action

Recordset Methods Description
AddNew Add a new record to the RecordSet
CancelBatch Cancel pending batch updates
CancelUpdate Cancel pending update
Clone Copy the RecordSet
Close Close the RecordSet
Delete Delete current record
GetRows Retrieve multiple records
Move Move the record pointer to a specific record
MoveNext Move the record pointer to the next record
MovePrevious Move the record pointer to the previous record
MoveFirst Move the record pointer to the first record
MoveLast move the record pointer to the last record
NextRecordSet Load the next RecordSet in a multi-set query
Open Open the RecordSet (execute the query)
Requery Re-execute the last query executed
Resync Synchronize the data with the server
Supports Determine if a feature is supported by provider
Update Update the current record
UpdateBatch Update pending batched record updates

Don’t worry if you feel this listing is too big, you may never use many of these, all this is for your reference in case you need. The most commonly used property and functions are:

  • EOF – to identify no records returned. You run a query and need to determine the pointer is at last record or not or no records has been returned.
  • Open – To retrieve the RecordSet we use open method which requires two arguments – connection object and command object

Syntax for this would be

<RecordSetName>.Open source, connection, cursor, lock, type

 

  • Source – is actually the command object, it could be a sql statement
  • Connection – is the connection object connecting the database
  • Cursor – optional parameter to define recordset cursor type (default – Forward only)
  • Lock – optional parameter to set the lock type property(default – Read Only)
  • Type – optional parameter to define the command type (default unknown(8))

 

For example

Set oRecordSet = CreateObject(&quot;ADODB.Recordset&quot;)
oRecordSet.Open “Select * from Orders”,oConnection, adOpenStatic

here oConnection is connection session to the database , we have used the static cursor which allows us to open the record in readonly mode, and not used a lock type – so it will use the default lock.

a generic function for retrieving the recordset would be like

Function 2

Function getRecordset(strSQL)
Dim oConnection, oRecordSet
Set oConnection = CreateObject(&quot;ADODB.Connection&quot;)
Set oRecordSet = CreateObject(&quot;ADODB.Recordset&quot;)
oConnection = getConnection()
oRecordSet.Open strSQL,oConnection, adOpenStatic Set getRecordset = oRecordSet
End Function

Let us now try this to retrieve data from Orders table of Flight application database.

To get the customer name from table Orders with Order Number 1 will need to execute the query –
S

Select * from Orders where Order_Number = 1

Example 2

Set MyRecordset = getRecordset(&quot;Select * from Orders where Order_Number = 1&quot;)

If MyRecordset.EOF &lt;&gt; True Then
msgbox MyRecordset.Fields(&quot;Customer_Name&quot;).Value
End If

Result

getRecordset method in Database Checkpoint

In this example we are using the function 2 by passing the required query and we get the result recordset in the MyRecordset object. Then we are checking whether there is some records there or not using the EOF property.

In the same way we can use other methods as well for different operations

Move to next record – We have a method MoveNext which we can use in a loop until the max record in recordset. Below example illustrates the use of this method to get all the records from table

Example 3

Set MyRecordset = getRecordset(&quot;Select * from Orders&quot;)

Do while MyRecordset.EOF &lt;&gt; True
print MyRecordset.Fields(&quot;Customer_Name&quot;).Value
MyRecordset.MoveNext
Loop

Result

Move to next record - MoveNext Method

In the same way you can use other methods and properties of RecordSet object to achieve your task. I have listed her some example which you may encounter in your test.

Function 3 – Number of Records We can traverse through all the records in the recordset to get the

Function getRecordCount(ByRef RecordSet)
Dim Rows
Rows = 0
RecordSet.MoveFirst
Do Until RecordSet.EOF
Rows = Rows+1
RecordSet.MoveNext
Loop
getRecordCount = Rows
End Function

Function 4 – Execute Query

If you need to update, insert or delete records from database, you can use this function. But if you are working on the database for application under test, you should not perform such actions until unless it is required or a database used for QTP.

Function ExecuteQuery(strSQL)
On Error Resume Next
Set oConnection = CreateObject(&quot;ADODB.Connection&quot;)
oConnection = getConnection()
oConnection.Execute strSQL
If Err.Number &lt;&gt; 0 then
ExecuteQuery = False
Exit Function
End If
ExecuteQuery = True
End Function

Example 4 – Retrieve the column names from a table

Set MyRecordset = getRecordset(&quot;Select * from Orders&quot;)

nColumns = MyRecordset.Fields.Count
For n = 0 to nColumns - 1
Print MyRecordset.Fields(n).Name
Next

Retrieve the column names of a table
Example 5 – Import Database Table into Data table

‘ Get recordset
Set MyRecordset = getRecordset(&quot;Select * from Orders&quot;)
‘Get number of columns in table
nColumns = MyRecordset.Fields.Count
‘Add a sheet in your datatable
Datatable.AddSheet (&quot;DBImport&quot;)
For n = 0 to nColumns – 1
‘Get column name
ParamName = MyRecordset.Fields(n).Name
‘Add DTParameter
Datatable.GetSheet(&quot;DBImport&quot;).AddParameter ParamName,&quot;&quot;
nRow = 1
MyRecordset.MoveFirst
‘Retrieve and place data in data table
Do while MyRecordset.EOF &lt;&gt; True
Datatable.SetCurrentRow(nRow)
Datatable(ParamName,&quot;DBImport&quot;) = MyRecordset.Fields(ParamName)
nRow = nRow + 1
MyRecordset.MoveNext
Loop
Next

Database table into Data table

Apart from this, if you need to connect to a excel file using ADODB connection, you will have to specify a different connection string, which will be like

&quot; DRIVER={Microsoft Excel Driver (*.xls)};DBQ= FileName ;Readonly=True&quot;

With this Saket concludes the series on Database Checkpoint and QTP.

You Should Read These 3 Related Articles Too!

24 Comments

  1. Bond on Mar 27, 2018 at 01:05

    hi, I am trying to execute a union query which has a with statement using ADODB recordset. The recordset is fetching zero results and get a EOF file error. If I manually execute the with statement, it fetches results.

    My code looks as below

    CustomerInformation="WITH ALERT_ID AS "&_
  "(SELECT REVIEW_ID FROM Mantas.KDD_REVIEW WHERE REVIEW_ID IN (5753052)), "&_
  " CUST_LK AS "&_


Set myConn = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
myConn.Open CONN_STRING


objRecordSet.Open CustomerInformation, myConn

msgbox objRecordSet.Fields.Count
    Reply
  2. Navee on Dec 30, 2016 at 20:59

    Hi Ankur — I am working on simple automation script to compare two SQL output.

    the challenge I am facing is, below code works [returns value] for one sql but it does not return anything for other SQL. Not sure, why it is behaving like this? Can you please suggest please?
    Let me know if any additional details needed.

    ' Create an ADO Connection object, that will host out connection
	Set DBConnection = CreateObject("ADODB.Connection")
	Dim RecordSet 
	strConn = "Provider=OraOLEDB.Oracle;Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=HOST1)(PORT=2222))(CONNECT_DATA=(SID=MATASH)));User Id=904509;Password=TEST123?"
	DBConnection.Open strConn
	Set RecordSet = CreateObject("ADODB.recordset")
	RecordSet.CursorLocation = 3	
	sql1 = "SELECT MAX(IL.column1) FROM SCHEMA.TABLE1 IL where TRUNC(DATE)='20-DEC-2016' AND IL.COLUMN1 = 'XXX007709d8a1' GROUP BY IL.COLUMN1"
	'RecordSet.Open TRIM(UCASE(datatable("strSql","Action1"))), DBConnection
	RecordSet.Open sql1, DBConnection
	Value = RecordSet.fields.item(0)				
		msgbox Value
		Output: Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.
Line (12): "Value = RecordSet.fields.item(0)".
	
	If RecordSet.RecordCount = 0  then 
		msgbox "Zero count"  -- SQL reutnrs data however recordcount is returned as Zero or eof returend True
	End if 
	RecordSet.Close
	Set RecordSet = Nothing
    Reply
  3. Kevin Abel on Dec 10, 2015 at 04:26

    Is there a way to write to the DBTable properties? Change the Connection String and SQL on this object. Then tell UFT to either take a baseline db snapshot or do a compare?

    When using the DB Checkpoints where is the datatable being stored? Is it in the Object Repository?

    Reply
  4. vijaykanth on Jun 8, 2014 at 14:14

    Extremely helpful content. Its great idea to have such a blog and maintain it so effectively.

    Reply
  5. vinny on Dec 2, 2013 at 18:35

    Too good article and very helpful for Onjob learners like me. Thank you very much.

    Reply
  6. Rickson on May 8, 2013 at 15:58

    Thanks a lot ankur. I tried this out, this site, if you keep updating it will become a great repository for QTP for posterity ;-P

    Reply
  7. saju on Nov 16, 2012 at 16:31

    Thanks Ankur.I have one doubt.When I tried to connect Excel sheet using ” DRIVER={Microsoft Excel Driver (*.xls)};DBQ= FileName ;Readonly=True” ,getting error message ‘object cannot found’ .can you please look into .I have written below the whole code

    Dim objCon, objRecordSet, strExlFile, colCount,strSQLStatement

    Set objCon = CreateObject(“ADODB.Connection”)
    Set objRecordSet = CreateObject(“ADODB.Recordset”)

    strExlFile = “C:\Data\ExcelDB.xls”

    objCon.Open “DRIVER={Microsoft Excel Driver (*.xls)};DBQ=” &strExlFile & “;Readonly=True”

    strSQLStatement = “SELECT * FROM [Employee$]”

    objRecordSet.Open strSQLStatement, objCon

    colCount = objRecordSet.Fields.count

    print colCount

    Reply
  8. Saju on Nov 8, 2012 at 17:38

    am not sure who is owner of this site.In my earlier comment I mentioned Ankur.It’s written by mystake.
    Thanks a lot to the blog owner!

    Reply
    • Ankur on Nov 9, 2012 at 02:12

      Thanks Saju. The website is owned and managed by each single reader like you who provide their valuable encouraging comments and help us shaping things for the future.

      Reply
  9. Saju on Nov 8, 2012 at 17:34

    Wonderful Information.Thanks a lot Ankur

    Reply
  10. mamta on Nov 1, 2012 at 13:50

    Thanks a ton for a very nice article..

    Reply
  11. Venky on Apr 4, 2012 at 19:33

    This is really very helpful yaar!!!!!!!!!
    thanks a lot for ur post

    Reply
  12. Tejaswi on Mar 30, 2012 at 19:20

    Can someone help me, i’m getting the following error 800A0E7D.

    Reply
  13. raj on Nov 26, 2011 at 12:32

    am unable to pull the fields names in datatable “DBImport”
    plz help me out

    Reply
  14. raj on Nov 26, 2011 at 12:25

    am gettin general run error

    “datatable.getsheet(“DBImport”).addparameter ParamName,””

    Reply
  15. jo on Oct 31, 2011 at 05:02

    hi saket,

    i have found this very helpful. but i am stuck at function 2 getrecordset(strsql)

    the sql statement i am trying to pass to the funciton is
    “select * from orders where order_number = “& ordrno

    and i am getting error as wrong no.of variables.

    Reply
  16. Lora on Aug 31, 2011 at 06:25

    Hi Saket,
    Thank you for your posts
    I have a question
    I’m trying to retrieve data from record set. And it’s always failed 🙁
    It looks like QTP is not recognize a recordset properties (ex: EOF, Movenext, etc). But you use such properties in your examples! Could you tell me what i have to do to make it working? Maybe i need to make references to ado recordset library, but i didn’t find how to do it in QTP. Please give me advice. I’m working with MySQL and QTP 11 (trial version). Thanks, Lora

    Reply
  17. Honey on Feb 18, 2011 at 17:47

    Hello,

    Indeed this a very good knowledge platform for begineers like me..
    Thanks a lot.

    Thanks,
    Honey

    Reply
  18. Saket Kumar on Dec 10, 2010 at 17:26

    Thanks Archana, I am glad – this is helpful.

    Reply
  19. Archana on Dec 1, 2010 at 16:40

    Thanks for the valuable input. this is really helpful for people like me , who r getting to know more about database checkpoint.

    Reply
  20. Kunal on Sep 20, 2010 at 21:31

    Thanks for this.. this is really useful..

    However, I would like to add.. some examples of using Recordset Methos/Properties would be more helpful.. I tried to find them in QTP help.. could not find them there as well..

    Reply
  21. Saket on Jun 8, 2010 at 22:32

    Thanks sc0507, I am glad, you found this useful.

    Vijayendra – The Count property is the number of items in the property collection. it returns a long value. You can use this value to loop through the collection.
    The Item property is used to return a specific member of the Properties Collection.

    Reply
  22. Vijayendra on Jun 7, 2010 at 14:17

    Hi Saket,

    During run time I could see two properties named “Item” and “Count” of oConnection and oRecordSet objects. Can you please let me know what and how are they used for?

    Reply
  23. sc0507 on May 31, 2010 at 21:15

    what a great topic and lovely description. Thank you so much as I am trying to learn QTP descriptive programming on my own ..this article is like a step by step manual to get there
    I was struggling with DB connections before..looks like I am getting good at it :-))))

    Reply

Submit a Comment

Your email address will not be published. Required fields are marked *