You will need to use the database element and select the type as multiple rows. You will then need to use custom java to select each row in sequence.
Janine Graves created an element that does this, and she gives that to you in her class. I have modified the element slightly, so that it is a self supporting element and does not require any other counter elements.
You give the element the session data name the the multi-row result has been stored in, and it will exit out the data path with the first row (0th row). You can then do what you need to do to play the info to the caller, and then point back to the getNextRow element. It will return the next row, and so on. When it has no more rows, the element will exit out the noData path.
Here is the java for the element
1
2/*
3 Copyright (c) 1999-2005 Audium Corporation
4 All rights reserved
5*/
6
7
8import com.audium.server.AudiumException;
9import com.audium.server.voiceElement.DecisionElementBase;
10import com.audium.server.voiceElement.ElementInterface;
11import com.audium.server.voiceElement.Setting;
12import com.audium.server.voiceElement.ElementData;
13import com.audium.server.voiceElement.ExitState;
14import com.audium.server.voiceElement.ElementException;
15import com.audium.server.action.database.ResultSetList;
16import com.audium.server.session.DecisionElementData;
17import com.audium.server.xml.DecisionElementConfig;
18
19/**
20 * This decision element compares 2 numbers and returns 'true' (or 'false') if the comparison is true (or false).
21 * It returns 'error' if one of the input parameters can't be converted to a number.
22 **/
23public class AshersGetNextRow extends DecisionElementBase implements ElementInterface
24{
25 /**
26 * This is the name of the decision element which appears in Studio.
27 */
28 public String getElementName()
29 {
30 return "DB Get Next Row";
31 }
32 /** Folder name containing the decision element in Studio **/
33 public String getDisplayFolderName()
34 {
35 return "Integration";
36 }
37 /** Description of the decision element in Studio **/
38
39 public String getDescription()
40 {
41 return "Get the next row of data from a Session variable created by a DataBase element using 'Multiple'. " +
42 "Invalid column names --> return down the 'error' path.\n" +
43 "No rows left --> return down the 'noData' path.\n" +
44 "This is to be used with the database element that gets Multiple rows."+
45 "Created by Janine Graves"+
46 "Modified by Asher Max Schweigart";
47 }
48 /** The Settings for the decision element in Studio **/
49 public Setting[] getSettings() throws ElementException
50 {
51 Setting[] settingArray = new Setting[3];
52
53 /**Each setting array element must specify:
54 * real name, display name, description,
55 * required?, occurs only once?, substitution allowed?, setting type
56 **/
57 settingArray[0] = new Setting("dataName", "Data Name",
58 "Name of the Session data holding the ResultSetList object returned\n" +
59 "by a Database element that selects 'Multiple'",
60 true, // It is required
61 true, // It appears only once
62 true, // It does allow substitution
63 Setting.STRING);
64 settingArray[1] = new Setting("colName", "Col Name",
65 "Column Name to retrieve\n" +
66 "This setting is repeatable\n" +
67 "If Column Name is not valid, call flow exits down the 'error' path",
68 true, // It is required
69 false, // It is repeatable
70 true, // It does allow substitution
71 Setting.STRING);
72 settingArray[2] = new Setting("resultType", "Store Into",
73 "Where to store the result, Element or Session data\n" +
74 "Data is named with the Column Name(s) listed above.",
75 true, // It is required
76 true, // It appears only once
77 false, // It does not allow substitution
78 new String[] {"Element","Session"});
79 settingArray[2].setDefaultValue("Element");
80
81 return settingArray;
82 }
83
84 /**
85 * This method returns an array of ExitState objects representing all the
86 * possible exit states the decision element can return. Here, the exit states
87 * reflect the three possible states of the day, morning, afternoon, and evening.
88 */
89 public ExitState[] getExitStates() throws ElementException
90 {
91 ExitState[] exitStateArray = new ExitState[3];
92
93 exitStateArray[0] = new ExitState("data", "data",
94 "");
95 exitStateArray[1] = new ExitState("noData", "noData",
96 "");
97 exitStateArray[2] = new ExitState("error", "error",
98 "");
99
100 return exitStateArray;
101 }
102
103 /**
104 * This method returns an array of ElementData objects representing the
105 * element data that this decision element creates.
106 * If no element data created, return null
107 */
108 public ElementData[] getElementData() throws ElementException
109 {
110 ElementData[] elementDataArray = new ElementData[2];
111 elementDataArray[0] = new ElementData("result", "The result of the decision");
112 elementDataArray[1] = new ElementData("rowNum", "Current row pointer");
113 return elementDataArray;
114 }
115 /** This is the runtime code executed by VXML Server **/
116 public String doDecision(String name, DecisionElementData decisionData) throws ElementException
117 {
118 // Get the configuration
119 DecisionElementConfig config = decisionData.getDecisionElementConfig();
120
121 try {
122 //Get each setting from the config, using the setting's 'real name'
123 //as defined in the getSettings method above
124 String dataName = config.getSettingValue("dataName", decisionData);
125 System.out.println("dataName="+dataName);
126
127 int rowNum = 0;
128
129 if(decisionData.getElementData(name, "rowNum") == null ||
130 decisionData.getElementData(name, "rowNum").length() < 0)
131 {
132 rowNum = 0;
133 }else
134 {
135 rowNum = Integer.parseInt(decisionData.getElementData(name, "rowNum"));
136 }
137 System.out.println("rowNum="+rowNum);
138
139 String resultType = config.getSettingValue("resultType", decisionData);
140 System.out.println("resultType="+resultType);
141
142 //retrieve the Column Name settings which are repeatable
143 String[] colNames = config.getSettingValues("colName", decisionData);
144
145 if (((ResultSetList) decisionData.getSessionData(dataName)).getNumRows() <= rowNum){
146 return "noData";
147 }
148 //make sure there's at least one colName specified
149 if (colNames != null && colNames.length > 0)
150 {
151 System.out.println("the number of columns requested was "+colNames.length);
152 for (int i = 0; i < colNames.length; i++)
153 {
154 String colValue = ((ResultSetList) decisionData.getSessionData(dataName)).getValue(colNames[i],rowNum);
155 System.out.print("colName[" +i+ "]=" + colNames[i]);
156 System.out.println("; colValue[" +i+ "]=" + colValue);
157 if (resultType.equals("Element"))
158 {
159 decisionData.setElementData(colNames[i],colValue);
160 } else
161 {
162 decisionData.setSessionData(colNames[i], colValue);
163 }//end else
164 }//end for
165 decisionData.setElementData("rowNum", Integer.toString(rowNum+1));
166 return "data";
167 } else
168 decisionData.setElementData("result","colName == NULL or length is 0");
169 return "error";
170 } catch (RuntimeException e) {
171 //If we couldn't retrieve the settings and turn the numbers into float's then
172 //print a stack trace in the error log and return down the 'error' exit state
173 e.printStackTrace();
174 decisionData.setElementData("result","RuntimeException");
175 return "error";
176 } catch (AudiumException e) {
177 // TODO Auto-generated catch block
178 e.printStackTrace();
179 decisionData.setElementData("result","AudiumException");
180 return "error";
181 }
182 }
183}
Hope this helps!
CVP Forum 
