How to get multiple result set from Database Element in CVP VXML App.

[Amine-K]

Hello,
We are developing a CVP VXML Application and we are using a Database Element.
We use a Stored Procedure that return multiple rows as a result. We could use a SQL Query also that returns multiple rows.
The SP is working fine from Database Element.
As the SP returns multiple rows we selected 'Multiple' as a Type for this Database Element.
 - Type : Multiple
 - Key : Data.Session.MyVAR
Now we need to get the multiple rows, we tried using the Element Data (like the Elements with Single Type) but it did not work.
Any idea how to get this data?
Thank you so much

[Gerry O'Rourke]

Are you setting the key variable?

https://www.cisco.com/c/en/us/td/docs/voice_ip_comm/cust_contact/contact_center/customer_voice_portal/cvp11_6/programming/guide/ccvp_b_element-specifications-for-cisco-unified-cvp11-6/ccvp_b_element-specifications-for-cisco-unified-cvp11-6_chapter_01...

See above for full details.

In summary

Session data is created only when the type setting is set to multiple. In all other cases, no session data is created.

Name	Type	Notes
[value of setting "key"]	ResultSetList	The Java data structure that stores the returned values from a multiple type query. The name of the session data variable is specified by the developer in the key setting.

Gerry

[Amine-K]

Hi Gerry,
We have defined MyVAR as a session variable in the Key field.
Our question is how we can get the multiple rows from this Session Var? What is the syntax to use?
Amine

[ptindall]

I'm sure there are many other variants of this that others may have to offer but here's a simple custom element to access row data in the ResultSetList object that's populated in session data.

[Amine-K]

Hi,
Thank you for this answer. I still have some questions
Is this Java Class used by default to retrieve Multiple result set? Should I do something in order to be able to call it from the app?
Now suppose we are using this class, how can we retrieve data? Any sample please?
Thank you.
Amine

[ptindall · Accepted solution]

The database element which returns multiple rows puts the data in the session variable that you specify in the database element settings.  This is a custom element you can use after your database element to extract selected columns for a particular row.  This is essentially a helper element to get at the database results held in the session variable.  For example, you could use it in a Studio flow to iterate through the DB results and play something to the caller for each row -- maybe it's a list of transactions or items in an order.   

Once you've built and deployed the element, to use it you specify the session variable containing the DB results, the row you want data from, and the name(s) of one or more columns you want to retrieve for that row.  The data will be returned as element data with the column name.

If you're not clear on deploying custom elements, have a quick search on the community as it's been covered plenty of times before.