Results object

, ,
1

I am executing a query in Oracle to return a sequence value. The service returns a results object. Anyone know how I can extract a string from this thing? Or have it return a string? byesToString doesn’t seem to cut it.

2

Hi, Barry.

When executing a SQL query, the result set is returned as a Record List, with each Record containing only the selected fields.

So, if your invocation of pub.db:execSQL includes the SQL statement “SELECT CUST_NO, CUST_FNAME, CUST_LNAME, CUST_PHONE1 FROM customer_info WHERE CUST_NO = ‘%input%’”, your result set Record List will contain one record which has the following structure:

[I]results[/i] 
  CUST_NO 
  CUST_FNAME 
  CUST_LNAME 
  CUST_PHONE1

To get these values from the result set to your pipeline variables, you have two options.

OPTION 1

  1. Create a new MAP step after the database query []Add a new String variable in the Pipeline Out pane []Highlight the new variable and then click “Set Value” [*]In the text input box, type "%results/CUST_FNAME%. Be sure to click the “Perform variable substitution” checkbox

OPTION 2

  1. Step through the entire Flow and stop on the step AFTER the database query []Select the results tab and look at the structure of the results record. Copy the String sub-nodes by highlighting them and pressing CTRL-C []Go to the Flow pane and create a new MAP step []In the Pipeline Out pane of the MAP step, create a new Record List. Name it anything you like. []Select the new Record List and press CTRL-V to paste the copied variables into the Record List. You may need to shift the variable to the right. []Map from the Pipeline In to the Pipeline Out, connecting results with your new Record List. []Continue your Flow as needed – the new Record List contains the complete result set. Loop over the Record List, if required.

These are two ways to extract values from a result set. If they don’t work for you, let me know.

Thanks.

3

Hi,
Adding some more info to Dan’s response:

If this is a query that you execute regularly and you would like to use the results of this query for other operations, you can create a record of your own with the same structure as the result object and then use the record reference in the pipeline out.
Then you can use this record to work with the results. Another advantage of this approach is in case you are using this database query in a loop, you can append the records to a recordList using the appendToRecordList service and then use the results.

Hope this helps.
Thanks

4

I have two adapter services, one is SELECT on condition and UPDATE on the ID which is obtained by the SELECT.
If i have only one result which we obtain from the SELECT, then iam able to UPDATE.
If there is more than one ID, i looped on the SELECT “results” and tried to update. But its not working.
Could someone help me wht the solution for this should be…
Thankyou