+- 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: SQL quering - how to use results of one query in the following one (/Thread-SQL-quering-how-to-use-results-of-one-query-in-the-following-one)
Pages:
1
2
SQL quering - how to use results of one query in the following one - unbeliever - 12-29-2009
Dear All,
Looking forward to your help regarding issue described below.
I need to:
- query database for specific results
- select value of one of the columns for one particular record
- use this value in the next query.
I am connecting to Oracle 10 DB and example of queries I need to run are below:
=====
1. Get the order ID by execute:
select * from v_order where USERID_REQUESTOR = ‘TESTXXX’
Note the ordered in ‘WKF_ORDER_ID’.
Expected outcome:
The Order ID ‘WKF_ORDER_ID’ is displayed for the order where COMPLETION_TIME is today.
2. With the noted OrderID (‘WKF_ORDER_ID’), execute following statement:
select * from taudit where USERID_USR = ‘TESTYYY’ and AUDITSINGLEID = ‘< orderid>’
Make sure there is a ‘IPS’ entry in the request component.
Expected outcome:
In the audit table is a ‘IPS’ entry in the request component.
=====
Can you please provide me with methods and sample code for such operation?
Thanks in advance
RE: SQL quering - how to use results of one query in the following one - Saket - 12-29-2009
what is issue you are facing with this?
have you tried anything so far? paste your code here
RE: SQL quering - how to use results of one query in the following one - unbeliever - 12-29-2009
I can run only simple queries using objDB.Execute() procedure.
One of the solutions I am considering is building complex sql query, but that is not appreciated.
I am interested how to input results of one execute procedure into succeeding procedure and to execute it with this input. In other words I want to build a connector and I have no idea so far how to do it.
If you can point me to some specific manual dedicated to this topic that would be greatly appreciated.
RE: SQL quering - how to use results of one query in the following one - Saket - 12-29-2009
you can explore more on using adodb objects and get the query results in recordset, that will definitely help you.
I have listed a peice of code here,
Code:
Set ConObj = CreateObject("ADODB.Connection")
ConObj.Provider = "MSDAORA.1"
ConObj.Properties("Data Source").Value = Service name
ConObj.Properties("User ID").Value = UserName
ConObj.Properties("Password").Value = Password
ConObj.Open
Set RecObj = CreateObject("ADODB.Recordset")
RecObj.Open <<Your SQL query>>ConObj,adLockPessimisticHopefully we will have a series of tutorial Posts on the same topic, soon at http://www.learnqtp.com
register you mail there to receive the notification
RE: SQL quering - how to use results of one query in the following one - unbeliever - 12-29-2009
Thanks for advice
I will do further research on ADODB
RE: SQL quering - how to use results of one query in the following one - unbeliever - 01-08-2010
I have done some further reading and the result is as follows.
Code:
Dim oConn
Dim oRecSet
Dim strQuery
Set oConn = CreateObject("ADODB.Connection")
Set oRecSet = CreateObject("ADODB.Recordset")
strQuery = "Select * from somewhere where location = 'Moon'"
oConn.Open "Driver={Microsoft ODBC for Oracle};Server=myserver.com;Uid=user;Pwd=pwd;"
oRecSet.Open strQuery, objConnI need to select a value for a column 'Mountain' for the one record that is generated for this query and then move it to QTP Data Table. Let's call this value BigX
Afterwards I need to get results of query "Select * from elsewhere where population = 'BigX'". BigX here is the value from QTP Data Table, not DB, in other words variable dependent on previous query.
Can you please provide me with some sample code for this case?
RE: SQL quering - how to use results of one query in the following one - unbeliever - 01-12-2010
Hello,
Does silence mean that I asked something stupid or something impossible?
I beg for your answer.
RE: SQL quering - how to use results of one query in the following one - unbeliever - 01-12-2010
I have unworking solution, perhaps you can advise me how to correct it? I am getting Type Mismatch run error in red row. Perhaps you can help me to indicate exact cell from which I need a value?
Code:
Dim Conn
Dim Rs
Dim strQuery
Set Conn = CreateObject("ADODB.Connection")
Set Rs = CreateObject("ADODB.Recordset")
strQuery = "Select column from table where USERID = 'testuser"
Rs.CursorType = 1
Conn.Open "Driver={Microsoft ODBC for Oracle};Server=myserver.com;Uid=user;Pwd=pwd;"
Rs.Open strQuery, Conn
Recordcount = Rs.RecordCount
DataTable.GlobalSheet.AddParameter "column",""
While (NOT Rs.EOF)
For i=1 to Recordcount
DataTable.SetCurrentRow(i)
DataTable("WKF_ORDER_ID",DtGlobalSheet) = Rs.Fields("WKF_ORDER_ID")
Rs.MoveNext
Next
Wend
Conn.close
Set Conn = Nothing
Set Rs = Nothing
Dim Conn2
Dim Rs2
Dim strQuery2
Set Conn2 = CreateObject("ADODB.Connection")
Set Rs2 = CreateObject("ADODB.Recordset")
[color=#FF0000]strcolumn DataTable.Value("column")[/color]
strQuery2 = "select * from table2 where USERID_R = 'test2' and AUDITCOLUMN = '" & strcolumn & "'"
Rs2.CursorType = 1
Conn2.Open "Driver={Microsoft ODBC for Oracle};Server=myserver.com;Uid=user;Pwd=pwd;"
Rs2.Open strQuery2, Conn2
Recordcount2 = Rs2.RecordCount
DataTable.GlobalSheet.AddParameter "REQUEST_COMPONENT", ""
While (NOT Rs.EOF)
For i=1 to Recordcount2
DataTable.SetCurrentRow(i)
DataTable("REQUEST_COMPONENT",DtGlobalSheet) = Rs.Fields("REQUEST_COMPONENT")
Rs.MoveNext
Next
Wend
Conn.close
Set Conn = Nothing
Set Rs = NothingRE: SQL quering - how to use results of one query in the following one - Saket - 01-12-2010
i think, you are missing a "=" there in your statement in red.
btw where to set values to 'column' parameter? i can see you are settign it in WKF_ORDER_ID with first query result.
RE: SQL quering - how to use results of one query in the following one - unbeliever - 01-12-2010
Sorry I have pasted here a mixed version - changed some authorization details, I have tried "=" and the subsequent Run Error was generated. Please see row in red. Now everything will look consistent. As for Run Error it states that "object is required", sql is parametrized correctly, but cannot pass data to Data Table.
Code:
Dim Conn
Dim Rs
Dim strQuery
Set Conn = CreateObject("ADODB.Connection")
Set Rs = CreateObject("ADODB.Recordset")
strQuery = "Select wkf_order_id, completion_time from v_order where USERID_REQUESTOR = 'ILMT12' and SERVICE_NAME = 'Mailbox'"
Rs.CursorType = 1
Conn.Open "Driver={Microsoft ODBC for Oracle};Server=myserver.com;Uid=user;Pwd=pwd;"
Rs.Open strQuery, Conn
Recordcount = Rs.RecordCount
DataTable.GlobalSheet.AddParameter "WKF_ORDER_ID",""
While (NOT Rs.EOF)
For i=1 to Recordcount
DataTable.SetCurrentRow(i)
DataTable("WKF_ORDER_ID", DtGlobalSheet) = Rs.Fields("WKF_ORDER_ID")
Rs.MoveNext
Next
Wend
'DataTable.Export "C:\results.xls"
Conn.close
Set Conn = Nothing
Set Rs = Nothing
Dim Conn2
Dim Rs2
Dim strQuery2
Set Conn2 = CreateObject("ADODB.Connection")
Set Rs2 = CreateObject("ADODB.Recordset")
WKF_ORDER_ID = DataTable.GlobalSheet.GetParameter("WKF_ORDER_ID").ValueByRow(1)
'strWKF_ORDER_ID DataTable.Value("WKF_ORDER_ID")
strQuery2 = "select REQUEST_COMPONENT from taudit where USERID_USR = 'ILMT21' and AUDITSINGLEID = '" & WKF_ORDER_ID & "' and userid_actor = 'ILMT12'"
Rs2.CursorType = 1
Conn2.Open "Driver={Microsoft ODBC for Oracle};Server=myserver.com;Uid=user;Pwd=pwd;"
Rs2.Open strQuery2, Conn2
'strQuery2 "select * from taudit where USERID_USR = 'ILMT21' and AUDITSINGLEID = '" & WKF_ORDER_ID & "'"
Recordcount2 = Rs2.RecordCount
DataTable.GlobalSheet.AddParameter "REQUEST_COMPONENT", ""
[color=#FF0000]While (NOT Rs.EOF)[/color]
For i=1 to Recordcount2
DataTable.SetCurrentRow(i)
DataTable("REQUEST_COMPONENT",DtGlobalSheet) = Rs.Fields("REQUEST_COMPONENT")
Rs.MoveNext
Next
Wend
Conn.close
Set Conn = Nothing
Set Rs = NothingPlease don't bother problem resolved.
Why can't I edit or delete my posts, by the way?