Retrive information from Database element

[cafej]

First, let's start by stating that we (my team) are kinda new to CVP, so we don't know a lot, and the first thing that we notice is that the official CVP documentation is not that good.

We're trying to make use of the database element with a multiple query, but we've only manage to get the information by using a Audio element with say it smart using the data from the database element xml_resultset, but the application says it like a reading a xml. How can we retrieve the information in a more user friendly way?

Thank you to anyone that can help us. 

[Quigath]

I'd usually use javascript in an Action element for something like this. Can you give an example though of the data and your query to get it?

[janinegraves · Accepted solution]

Using the SetValue element and javascript, you can parse the Element Data named: xml_resultset

It's returned in this format

<Results>

<Row>    <col1name> value1 </col1name>    <col2name> value2 </col2name> <col3name> value3 </col3name> </Row>

<Row>    <col1name> value1 </col1name>    <col2name> value2 </col2name> <col3name> value3 </col3name> </Row>

...

</Results>

To loop through each row, use a Studio Counter_01 element,

Then a Decision Element to determine if the Counter_01 count < Database01 num_rows_processed. 
If so, then use a SetValue element to retrieve each value for each column from that row into a Local Variable:

   importPackage(com.audium.server.cvpUtil)

   var ctr = {Data.Element.Counter_01.count}

   var xml = {Data.Element.Database01.xml_resultset}

   var path = "/Results/Row[" + ctr + "]/col1name"  //replace with your column name

   XpathUtil.eval(xml, path) //this parses and returns value1 into your local variable

Wow - this isn't the simplest for someone who hasn't used Studio before!

Maybe you want to attend some fabulous CVPD Scripting training through TrainingTheExperts.com?

:-)

[cafej]

Thank you very much for your answer Janine. Indeed it's not the simplest way of working this out, but a very cleaver one. Will put it to work immediately.

[Ahmed Adeyemi]

Hello Janine,

     By anychance do you have an example of how to use the XPATH or extract the result of the Database xml_resultset?