Further runtime datatable questions

[Jimmie Parson]

To all,

A few days ago I posted about some issues I had with returning the
values from the runtime datatable. With some assistance I was able to
resolve one of my issues I am hoping with your help I can resolve the
last issue I have right now. I will setup what I have tried so far:

1. I am connecting to an Oracle DB with an ADOB connection and running
a SQL query to return the results back to the runtime datatable. This
was the first issue I had trouble with that is now working.

2. As long as I only need one query ran and a single output sheet
added to the datatable everything is fine. The problem arises when
running 2 or more queries that add more sheets to the datatable. When
I try to return the value of a cell in any of those sheets with this
code:

OracleFormWindow("Advanced Pricing").OracleTabbedRegion("List
Lines").OracleTable("LIST_LINES").EnterField 1, "Product Value",
DataTable.GetSheet("output1").GetParameter("Segment1").Value


I get the following error:

The Sheet.GetParameter operation failed. The <Segment1> column does
not exist.

I have ran some scripts and figured out that the sheets have been
created and they have the correct number of parameters. I added my
variables to the debug "add to watch" and I get the same error.

If I run the following code:

Sheetname = DataTable.LocalSheet.Name
'
'Reporter.ReportEvent 1, "The Active Sheet is", Sheetname

I get a return of the active sheet is "Action1"

My question is:

1. Is there a way to change the active runtime datatable sheet to the
sheet I need to return the values from?

2. If not does anyone have any advice as to how to proceed.


I will include the code for the script below:


'Connect to Oracle DB and execute a SQL Query, then write the data to
'the QTP runtime datatable.

'Variables for the database connection
Dim adocon,adorecordset
'Create the Connection object
Set adocon=CreateObject("ADODB.Connection")
constr="Provider=OraOLEDB.Oracle.1;Password=xxxx;Persist Security
Info=True;User ID=xxxx;Data Source=Dev"
adocon.Open constr
'Execute the SQL query
Set adorecordset=CreateObject("ADODB.Recordset")
Set adorecordset.ActiveConnection=adocon
Strsql="Select 'TEST PRICE LIST:' || to_char(sysdate,' MM-DD-YY
HH:MM:SS') as PRICE_LST_NAME from Dual"
adorecordset.Source=Strsql
adorecordset.Open
'Create the datatable to export the data
'Create an output sheet
Set outsheet=Datatable.Addsheet("output")
'Loop through each field to get the name to
'create the datable parameters
For each fld in adoRecordset.Fields
outsheet.AddParameter fld.name,""
Next
i=1
Do while Not adorecordset.EOF
DataTable.GetSheet(outsheet.name).SetCurrentRow(i)
For each fld in adorecordset.Fields
outsheet.GetParameter(fld.name).value=fld.value
Next
adorecordset.MoveNext
i=i+1
Loop


'close the recordset object
adorecordset.close
'Destroy the recordset object
Set adorecordset = nothing


'close the database connection
adoCon.close
'Destroy the connection object
Set adoCon = nothing

'Connect to Oracle DB and execute a SQL Query, then write the data to
'the QTP runtime datatable.

'Create the Connection object
Set adocon=CreateObject("ADODB.Connection")
constr="Provider=OraOLEDB.Oracle.1;Password=xxxx;Persist Security
Info=True;User ID=xxxx;Data Source=Dev"
adocon.Open constr
'Execute the SQL query
Set adorecordset=CreateObject("ADODB.Recordset")
Set adorecordset.ActiveConnection=adocon
Strsql="SELECT Segment1, Quantity FROM (Select Description,
Enabled_Flag, A.Inventory_Item_Id, B.Inventory_Item_Id,
Inventory_Item_Status_Code, Organization_Id, Segment1,
Primary_Uom_Code, Primary_Unit_Of_Measure,(Case When B.Conversion_Rate
Is Null Then 1 Else B.Conversion_Rate End) Quantity, B.Uom_Code,
B.Unit_Of_Measure From Mtl_System_Items_B A Left Outer Join
Mtl_Uom_Conversions B On (A.Inventory_Item_Id = B.Inventory_Item_Id)
Where -1= -1 And (Organization_Id = 356 Or Organization_Id = 359
Or Organization_Id = 591) AND (ITEM_TYPE =('LNV_FGM') OR ITEM_TYPE
=('LNV_FGB'))And Enabled_Flag = 'Y' And Inventory_Item_Status_Code =
'Active' ORDER BY dbms_random.value ) WHERE rownum = 1"
adorecordset.Source=Strsql
adorecordset.Open
'Create the datatable to export the data
'Create an output sheet
Set outsheet=Datatable.Addsheet("output1")
'Loop through each field to get the name to
'create the datable parameters
For each fld in adoRecordset.Fields
outsheet.AddParameter fld.name,""
Next
i=1
Do while Not adorecordset.EOF
DataTable.GetSheet(outsheet.name).SetCurrentRow(i)
For each fld in adorecordset.Fields
outsheet.GetParameter(fld.name).value=fld.value
Next
adorecordset.MoveNext
i=i+1
Loop


'close the recordset object
adorecordset.close
'Destroy the recordset object
Set adorecordset = nothing

'close the database connection
adoCon.close
'Destroy the connection object
Set adoCon = nothing

Dim objIE
Set objIE = CreateObject("InternetExplorer.Application")
objIE.visible = True
objIE.navigate "http://devap1.linvatec.com:8030/OA_HTML/
AppsLocalLogin.jsp?requestUrl=APPSHOMEPAGE&cancelUrl=http%3A%2F
%2Fdevap1.linvatec.com%3A8030%2Foa_servlets
%2Foracle.apps.fnd.sso.AppsLogin&s2=CCC48C9F7A462B6500680579CCCFEB46A1937E84A75CCEAC13471DA4D4E81AB9"
objIE.statusbar = 1
Wait 3
Browser("Oracle Applications
Home").Page("Login").WebEdit("username").Set "xxxx"
Browser("Oracle Applications
Home").Page("Login").WebEdit("password").SetSecure "xxxxx"
Browser("Oracle Applications Home").Page("Login").Image("Login").Click
Browser("Oracle Applications Home").Page("Oracle Applications
Home_4").Link("LNV Pricing Super User").Click
Browser("Oracle Applications Home").Page("Oracle Applications
Home_5").Link("Price List Setup").Click
Browser("Oracle Applications 11i").Page("Oracle Applications
11i_2").Sync
Browser("Oracle Applications Home").Page("Oracle Applications
Home_5").Sync
OracleFormWindow("Advanced
Pricing").OracleTabbedRegion("Main").OracleTextField("Name").Enter
"ADV ALL BLADES & BURS"
OracleNotification("Note").Approve
OracleFormWindow("Advanced Pricing").PressToolbarButton "Clear Record"
DataTable.GetSheet("output").SetCurrentRow(1)
OracleFormWindow("Advanced
Pricing").OracleTabbedRegion("Main").OracleTextField("Name").Enter
DataTable.GetSheet("output").GetParameter("PRICE_LST_NAME").Value
OracleFormWindow("Advanced
Pricing").OracleTabbedRegion("Main").OracleTextField("Description").Enter
"QTP Test"
OracleFormWindow("Advanced
Pricing").OracleTabbedRegion("Main").OracleTextField("Effective
Dates").OpenDialog
OracleCalendar("Calendar").Enter "15-NOV-2010"
OracleFormWindow("Advanced
Pricing").OracleTabbedRegion("Main").OracleTextField("-").OpenDialog
OracleCalendar("Calendar").Enter "15-APR-2011"
OracleFormWindow("Advanced
Pricing").OracleTabbedRegion("Main").OracleTextField("Multi-Currency
Conversion").OpenDialog
OracleListOfValues("Multi Currency Conversion").Select "Generated
Currency Conversion For USD 2002"
OracleFormWindow("Advanced Pricing").OracleTabbedRegion("List
Lines").OracleTable("LIST_LINES").OpenDialog 1,"Product Context"
OracleFormWindow("Advanced Pricing").OracleTabbedRegion("List
Lines").OracleTable("LIST_LINES").OpenDialog 1,"Product Attribute"
OracleListOfValues("Product Attributes").Select "Item Number"
DataTable.GetSheet("output1").SetCurrentRow(1)
OracleFormWindow("Advanced Pricing").OracleTabbedRegion("List
Lines").OracleTable("LIST_LINES").EnterField 1, "Product Value",
DataTable.GetSheet("output1").GetParameter("Segment1").Value
OracleFormWindow("Advanced Pricing").OracleTabbedRegion("List
Lines").OracleTable("LIST_LINES").EnterField 1, "Value", "35.00"
OracleFormWindow("Advanced Pricing").OracleTabbedRegion("List
Lines").OracleTable("LIST_LINES").OpenDialog 1,"Start Date"
OracleCalendar("Calendar").Enter "15-NOV-2010"
OracleFormWindow("Advanced Pricing").OracleTabbedRegion("List
Lines").OracleTable("LIST_LINES").OpenDialog 1,"End Date"
OracleCalendar("Calendar").Enter "15-APR-2011"
OracleFormWindow("Advanced Pricing").OracleTabbedRegion("List
Lines").OracleTable("LIST_LINES").SetFocus 1,"[ ]"
OracleFlexWindow("Additional Info for List").OracleTextField("DM
FLOOR").Enter "1"
OracleFlexWindow("Additional Info for List").Approve
OracleFormWindow("Advanced Pricing").SelectMenu "File->Save"
OracleFormWindow("Advanced Pricing").SelectMenu "File->Exit Oracle
Applications"
OracleNotification("Caution").Approve
Browser("Oracle Applications 11i").Page("Oracle Applications
11i_2").Sync
Browser("Oracle Applications 11i").CloseAllTabs
Browser("Oracle Applications Home").Page("Oracle Applications
Home_5").Link("Logout").Click
Browser("Oracle Applications Home").Page("Login_2").Sync
Browser("Oracle Applications Home").CloseAllTabs




Any help is appreciated

[Roman Zilber]

I don't work right now with QTP, but when I did, I defined to myself
number of rules, here are three of them:

1. if you can, don't use build in QTP Excel support, use COM Excel
Automation instead
http://www.google.ca/search?q=vbscript+Excel+Automation
2. if you can, don't use actions, use functions libraries
3. don't use QTP report

There are many arguments why to do that way :) I only don't want to
start holy wars.
Now I wouldn't use Excel at all, some simple DB, as SQLite, can serve
the purpose better.

> --
> You received this message because you are subscribed to the Google
> "QTP - HP Quick Test Professional - Automated Software Testing"
> group.
> To post to this group, send email to Mercu...@googlegroups.com
> To unsubscribe from this group, send email to
> MercuryQTP+...@googlegroups.com
> For more options, visit this group at
> http://groups.google.com/group/MercuryQTP?hl=en

[Jimmie Parson]

Roman,

Thanks for the input and I appreciate the guidance. I am new to QTP
myself "<6months" and obviously still learning many of the features
and quirks of the software. The problem lies in that I am trying to
get something to work and then refine the process as I go. As I gather
more experience I am sure I will look back at these beginning efforts
and cringe! I have a system that works that in addition to the code
above creates an excel sheet and exports the datatable data and then
reads the data from that sheet. I was hoping to be able to eliminate
those steps and just use the runtime datatable data. As such I got
some help from a previous post and am so close to making it work that
I can taste it.

The first SQL query returns data that I can read with QTP it is just
the second query that throws an error. I know the sheets exist, I know
the parameters exist I am just to dumb to know what I am doing wrong.

Anyway thanks again for your input if you don't mind I will take your
rules and use them as the basis of my own list of rules. Ala Gibbs
rules on NCIS!! LOL


Jim

On Jan 28, 9:45 pm, Roman Zilber <rzil...@gmail.com> wrote:
> I don't work right now with QTP, but when I did, I defined to myself
> number of rules, here are three of them:
>
> 1. if you can, don't use build in QTP Excel support, use COM Excel

> Automation insteadhttp://www.google.ca/search?q=vbscript+Excel+Automation

> > objIE.navigate "/

> ...
>
> read more »

[ARIE...@aol.com]

What are you now using instead of QTP?  Selinium, Silk Test....? Hmmmm?

 

In a message dated 1/28/11 9:46:06 P.M. Eastern Standard Time, rzi...@gmail.com writes:

I don't work right now with QTP, but when I did, I defined to myself
number of rules, here are three of them:

1. if you can, don't use build in QTP Excel support, use COM Excel
Automation instead
http://www.google.ca/search?q=vbscript+Excel+Automation
2. if you can, don't use actions, use functions  libraries
3. don't use QTP report

There are many arguments why to do that way :) I only don't want to
start holy wars.
Now I wouldn't use Excel at all, some simple DB, as SQLite, can serve
the purpose better.

> objIE.navigate "http://devap1.linvatec.com:8030/OA_HTML/

> To post to this group, send email to Mercu...@googlegroups.com
> To unsubscribe from this group, send email to
> MercuryQTP+...@googlegroups.com
> For more options, visit this group at
> http://groups.google.com/group/MercuryQTP?hl=en

--
You received this message because you are subscribed to the Google
"QTP - HP Quick Test Professional - Automated Software Testing"
group.

[Rajiv Nanduani]

Hi Jim,

make two/three actions it will create two/three local sheets.
First action for Query data and fill the data values in thats action runtime sheets.
second action your application script for entering the value from the sheet of first action sheet.
For getting the data from first action sheet use data table sheet name  ("first sheet name") parameter name .

--
You received this message because you are subscribed to the Google
"QTP - HP Quick Test Professional - Automated Software Testing"
group.
To post to this group, send email to Mercu...@googlegroups.com
To unsubscribe from this group, send email to
MercuryQTP+...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/MercuryQTP?hl=en

--
RAJIV KUMAR

<http://rajivkumarnandvani.wordpress.com/>
<http://learnqtphelp.blogspot.com/>

[Roman Zilber]

I woked on a test tool with AutoIt (automation of terminal emulator
and some basic IE automation), friendly interface for the test in
current project more important. I was able to develop tools which can
be installed on any computer, need no license. But the ability of
Autoit limited, now I need extend the test to testing more data and
functionality (mostly not GUI related), I moved the datafrom Excel to
SQLite (it reduced half of the code), and I am changing the test to
run it remotely by Apache web server, so it is mix of Python, Autoit
(and HTML, JavaScript /Ajax for GUI).

[Ganesh muralidharan]

Roman,

Nice combination of Python, Autoit (and HTML, JavaScript /Ajax for GUI). I have also been tweaking around with Autoit. It is a powerful tool.

Regards,


Ganesh Muralidharan
'God Never Forgets'

[Jimmie Parson]

To all

With some of your advice in mind and some further testing to see what
I could do I found a workable solution. Very simple like I figured I
was just to blind to see it. If I declare a variable like this

paramvalue = DataTable.GetSheet("output1").GetParameter("SEGMENT1")


and then just call the vaiable in my script in place of this

OracleFormWindow("Advanced
Pricing").OracleTabbedRegion("ListLines").OracleTable("LIST_LINES").EnterField

1, "Product
Value",DataTable.GetSheet("output1").GetParameter("Segment1").Value

everything works.


My main reason for this was my other working method involved creating
an excel sheet for each test so I could export the data then return
the data by calling the created excel workbook.


This eliminates all that work and should work for all 1200+ scripts I
am working on.


Once again thanks for the input I do appreciate it.


Jim

On Jan 31, 9:07 pm, Ganesh muralidharan <mgkgan...@gmail.com> wrote:
> Roman,
>
> Nice combination of Python, Autoit (and HTML, JavaScript /Ajax for GUI). I
> have also been tweaking around with Autoit. It is a powerful tool.
>
>
>
>
>
>
>
> On Mon, Jan 31, 2011 at 8:04 PM, Roman Zilber <rzil...@gmail.com> wrote:
> > I woked on a test tool with AutoIt (automation of terminal emulator
> > and some basic IE automation), friendly interface for the test in
> > current project more important. I was able to develop tools which can
> > be installed on any computer, need no license. But the ability of
> > Autoit limited, now I need extend the test to testing more data and
> > functionality (mostly not GUI related), I moved the datafrom Excel to
> > SQLite (it reduced half of the code), and I am changing the test to
> > run it remotely by Apache web server, so it is mix of Python, Autoit
> > (and HTML, JavaScript /Ajax for GUI).
>

> > On Sun, Jan 30, 2011 at 10:44 PM,  <ARIESe...@aol.com> wrote:
> > > What are you now using instead of QTP?  Selinium, Silk Test....? Hmmmm?
>
> > > In a message dated 1/28/11 9:46:06 P.M. Eastern Standard Time,

> > > rzil...@gmail.com writes:
>
> > > I don't work right now with QTP, but when I did, I defined to myself
> > > number of rules, here are three of them:
>
> > > 1. if you can, don't use build in QTP Excel support, use COM Excel
> > > Automation instead
> > >http://www.google.ca/search?q=vbscript+Excel+Automation
> > > 2. if you can, don't use actions, use functions  libraries
> > > 3. don't use QTP report
>
> > > There are many arguments why to do that way :) I only don't want to
> > > start holy wars.
> > > Now I wouldn't use Excel at all, some simple DB, as SQLite, can serve
> > > the purpose better.
>

> > > On Fri, Jan 28, 2011 at 1:41 PM, Jimmie Parson <jparso...@gmail.com>

> ...
>
> read more »