Stored Procedure

Avaya Logo

Stored Procedure

Option six of the JDBC_SP sample application performs the Stored Procedure operation. The Inline Code node for the Stored Procedure operation uses the following VXML code:

<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 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 VXML code, 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 to and return 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. The 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 values that can be passed for <TYPE> are: STRING, NUMBER, DATE, TIME, or TIMESTAMP. When using a null STRING input parameter, the value of the parameter should be empty. For example, the PARAMS for a stored procedure with one STRING input parameter should be set to "STRING,,IN" when the value of the string input is null. Note: Applications can execute stored procedures created on a database and retrieve the resultset/values returned by the procedure. However, testing has revealed that a timestamp (date and time) parameter may fail to pass to the stored procedure.
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 interface 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 shown in the VXML code, 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 YES, the stored 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 operation.