[Migrated from old forumParticipant April 10, 2015 at 3:06 pm Post count: 327]

#5791 |

Hi,

I am just wondering if someone can give me some idea on how to index to the next result in the MySQL fetch command in Visual DialPlan and save it to a unique variable name.

I have a result set being returned from a query that has from 1 to 5 rows. I want to get the “first_name” field of each row and store it in a separete variable (name1, name2, name3 , etc….).

The only examples I can find that use the fetch and return multiple rows is adding up a total result.

Any help on how to do this I would be much appreciated.

Ken

[Migrated from old forumParticipant April 10, 2015 at 3:07 pm Post count: 327]

#5792 |

Ken,

You need to create dynamic variables. For example create variable row${counter}.
If there are 3 records in result set, first variable that stores first record from database would be row1, second would be row2, and third would be row3. After each fetched row you need to increase counter for 1, counter = counter+1.

I often use this approach with Integration Server, you may try IS it’s so much easier to retrieve and manipulate data from a database.

Raman

[Migrated from old forumParticipant April 10, 2015 at 3:07 pm Post count: 327]

#5793 |

Thanks for the reply Raman.

I have spent the last 6 hours trying to make and use a dynamic variable but have had no luck.
I am from a programming background and I get exactly what you are saying but I can not work it out in Visual DialPlan.

If you could spare some time to give some more in depth instructions I would much appreciate it.

Thanks in advance.
Ken

[Migrated from old forumParticipant April 10, 2015 at 3:07 pm Post count: 327]

#5794 |

Ok, I will try to explain it in more details.

1)Create the following variables: “row${counter}”, “currentVar” (short from current variable) and “counter”.
2)At the beginning of the call flow set counter variable to 1 (counter=1).
3)In MySQL Fetch block under the Variables section select “currentVar”.
4)Connect green output port from MySQL Fetch block with this Set block Set(row${counter}=${currentVar}).
Now the counter needs to be increased, put another Set block Set(counter=${MATH(${counter}+1,i)}) and connect this block with MySQL Fetch block. This way you will create loop that creates new variable for every record from the database.

Here is the call flow image http://imageshack.us/photo/my-images/521/pic1h.png/.(external link)

I hope this helps.

Raman

[Migrated from old forumParticipant April 10, 2015 at 3:07 pm Post count: 327]

#5795 |

Thanks Raman.

Managed to get it working perfectly with your instructions.
Much appreciated.

Ken