|
|
|
|
|
|
Option six performs the Stored Procedure operation. The Inline Code node for the Stored Procedure operation uses the following code snippet:
<var name="CHANNEL" expr="Channel"/>
<subdialog name="MyServlet" src="http://localhost/servlet/JdbcServlet" method="get" namelist="CHANNEL CMD SPNAME PARAMS SPRETCODE DIPNAME">
<filled>
<if cond="MyServlet.retcode<='0'">
<prompt> Got a negative error code </prompt>
<goto expr="'#d3f8ult3x1tF0rm'"/>
</if>
<assign name="document.RetCode" expr="MyServlet.retcode"/>
<assign name="document.RetCode_SP" expr="MyServlet.spretcode"/>
<assign name="document.RetParam1" expr="MyServlet.param1"/>
</filled>
</subdialog>
<catch event="error.badfetch">
<goto expr="'#d3f8ult3x1tF0rm'"/>
</catch>
The namelist attribute again specifies the arguments to be passed to the servlet, which are in turn used to perform the database operation. The following table describes the fields:
Field |
Description |
CHANNEL |
Specifies the channel number of the call in progress. IVR Designer provides a system variable called 'Channel' in every application for this use. As can be seen from the first line in the code snippet, the system variable 'Channel' is copied into the local CHANNEL variable. |
CMD |
Specifies the database command to perform. In this application, CMD has been declared as a character variable in the application and has been initialized to the string 'STOREDPROC' in the 'SetSelectParams' node. |
SPNAME |
Specifies the Store procedure name to call on the database. In this application, SPNAME has been declared as a character variable in the application and has been initialized to the string 'SP1' in the 'SetSPParams' node. |
PARAMS |
Specifies the parameters to pass as well as return to/from the stored procedure. In this example, PARAMS is assigned the value "STRING,699999,IN,STRING,null,OUT" in the 'SetSPParams' node. This is because the stored procedure SP1 expects an input string parameter and returns an output string parameter. PARAMS field should only be assigned a comma-separated list of strings that are in the format: "<TYPE>,<VALUE>,<IN/OUT/INOUT>,<TYPE>,<VALUE>,<IN/OUT/INOUT>�.." The different values that can passed for <TYPE> are: STRING, NUMBER, |
SPRETCODE |
Specifies if the stored procedure returns a value other than the parameters specified as 'OUT'. Note that this feature is not supported by all databases. In this application, SPRETCODE has been declared as a character variable in the application and has been initialized to the string 'YES' in the Variables Manager. |
DIPNAME |
Specifies the DBDIP on the Avaya IR platform that should be used for accessing the database. One of the DIPs on the IR platform should be configured using the Web Administration to be able to access the required database. In this application, TNAME has been declared as a character variable in the application and has been initialized to the string 'DBDIP2' in the Variables Manager. |
As can be seen in the code snippet, a retcode value is always returned from the servlet indicating the success or failure of the servlet and database operation. The retcode field will contain a positive value if the operation was successful. In this example, an error prompt is being played if the retcode contains a value less than or equal to zero. Since the SPRETCODE field has been set to true, the Store procedure also returns a value that can be obtained by accessing MyServlet.spretcode. Other values returned by the stored procedure and originally specified as 'OUT' parameters, can be accessed by MyServlet.param1, MyServlet.param2 etc based on the number of parameters returned by the stored procedure. If the stored procedure returns a resultset or recordset, only one row is returned at a time to the application. In such a case, the individual fields returned can be obtained by accessing MyServlet.<DB_FLDNAME>. Successive rows can be accessed by using the 'Next' function.
