Developing Database Scripts
Download this chapter
Developing Database ScriptsDeveloping Database Scripts
 Feedback

Table of Contents

Developing a Database Script
 Database Script Variables
 The Start Step
 The Accept Step
 The Play Prompt Step
 The DB Read Step
 The Label Step (Physician Loop)
 The DB Get Step
 The End Step

Developing a Database Script

You can use the Cisco Customer Response Applications (CRA) Editor to design scripts that can access information from a specified database.

One basic script can access information stored in a database. This chapter describes the design of such a script. This simple script, database.aef, uses steps from the Database palette to automatically provide callers with contact information for local physicians.

In this sample script, the script reads all the data from a database table named physician_locator and plays back output one row at a time, looping back and repeating this process until there is no more data.

Figure 8-1 shows the database.aef script as it appears in the Design pane of the CRA Editor window.


Figure 8-1   database.aef Script


Table 8-1 shows the physician_locator database table.

Table 8-1   Physician_Locator Database Table

ZIP_CODE CATEGORY NAME SPOKEN_NAME ADDRESS PHONE

11111

chiropractor

john doe

(audio document)

222 main st. bedrock ca

5551112222

22222

podiatrist

jane wong

(audio document)

333 oak st. bubble city ny

5552344343

33333

dentist

jim smith

(audio document)

435 state st. oakwood tn

5556458978

99999

general

tia gomez

(audio document)

382 first st. river city ia

5557674444

This chapter contains the following sections:

Database Script Variables

The designer begins the database.aef script design process by using the Variable pane of the CRA Editor to define script variables.

Figure 8-2 shows the variables of the database.aef script as they appear in the Variable pane of the CRA Editor.


Figure 8-2   Variable Pane of the database.aef Script


Table 8-2 describes the variables used in the database.aef script.

Table 8-2   Variables in the database.aef Script

Variable Name Variable Type Value Function

Spoken_Name

Document

null

Stores the audio document of the spoken name of the physician.

(See The DB Get Step.)

Phone

String

—

Stores the phone number of the physician.

(See The DB Get Step.)

Address

String

—

Stores the address of the physician.

(See The DB Get Step.)

Name

String

—

Stores the written name of the physician.

(See The DB Get Step.)

Category

String

—

Stores the category of the physician.

(See The DB Get Step.)

Zip_Code

String

—

Stores the Zip code of the physician.

(See The DB Get Step)

The Start Step

Every script built in the Design pane of the CRA Editor window begins with a Start step.

The designer begins to build the database.aef script by choosing the File > New menu option from the CRA Editor menu bar. The CRA Editor places a Start step in the Design pane of the CRA Editor window.

The Start Step needs no configuration and has no customizer window.

The Accept Step

The designer continues the database.aef script by dragging an Accept step from the Contact palette (in the Palette pane of the CRA Editor window) to the Design pane, and dropping it over the Start step, as shown in Figure 8-1.

Next, the designer configures this Accept step to accept an incoming call.

-or-

Because the designer intends to accept the default contact, no configuration is necessary for this step.

The Play Prompt Step

The designer continues the database.aef script by dragging a Play Prompt step from the Media palette to the Design pane, and dropping it over the Play Prompt step, as shown in Figure 8-1.

The designer then configures the Play Prompt step to play a welcome message to the caller, announcing that the script will play back a list of physicians and their addresses.

The designer then configures this Play Prompt step to play a welcoming prompt to the caller.

Figure 8-3 shows the configured Prompt tab of the Play Prompt customizer window.


Figure 8-3   Play Prompt Customizer Window—Configured Prompt Tab


The designer configures the Play Prompt step as follows:

  • General tab
    • Contact—Triggering Contact

The step operates on the contact that triggers the execution of the script.

  • Interruptible—Yes

External events can interrupt the playing of the prompt. (At this point the script has not yet queued the call, so this configuration has no effect.)

  • Prompt tab
    • Prompt—WelcomePrompt

WelcomePrompt is the prompt that the Play Prompt step plays back to welcome the caller.

  • Barge in—Yes

The caller can interrupt the prompt playback.

  • Continue on Prompt Errors—Yes

In the event of a prompt error, the script continues to play back the next prompt in the sequence or waits for caller input.

  • Input tab
    • Flush Input Buffer—No

The system does not erase previously entered input before capturing new caller input.

The DB Read Step

The designer continues the database.aef script by dragging a DB Read step from the Database palette to the Design pane.

Next, the designer configures the DB Read step to use SQL (Structured Query Language) commands to read the physician_locator table in the specified database.

Figure 8-4 shows the configured General tab of the DB Read customizer window.


Figure 8-4   DB Read Customizer Window—Configured General Tab


The designer configures the General tab of the DB Read customizer window as follows:

  • DB Resource Name—getPhysicians

This choice names the query that is used by the subsequent DB Get step to read data from the database.

  • Data Source Name—ALPINE00

This choice specifies the database that contains the desired information.

Figure 8-5 shows the configured SQL tab of the DB Read customizer window,


Figure 8-5   DB Read Customizer Window—Configured SQL Tab


The designer configures the SQL tab as follows:

  • Enter SQL comments—the designer enters the SQL command "select * from physician_locator", which tells the DB Read step to read all the data that exists in the physician_locator table.

The DB Read step has three output branches, Successful, Connection Not Available, and SQL Error, as shown in Figure 8-1.)

These following sections describe these output branches:

The Successful Output Branch

If the DB Read step successfully reads the physician_locator table in the specified database, the script executes the Successful output branch.

The designer configures the Successful output branch of the DB Read step to concatenate all the information extracted from the database and play it back to the caller.

The Successful output branch is discussed in the following sections, beginning with The Label Step (Physician Loop).

The Connection Not Available Output Branch

If the DB Read step does not successfully read the physician_locator table in the specified database, the script executes the Connection Not Available output branch, and the script falls through to the End step.

The SQL Error Output Branch

If the DB Read step cannot execute because of an error in the SQL command, the script executes the SQL Error output branch, and the script falls through to the End step.

The Label Step (Physician Loop)

The designer begins the Successful output branch of the DB Read step by dragging a Label step from the General palette to the Design pane, and dropping it over the Successful icon under the DB Read step icon.

The designer then configures the Label step to provide a target for the beginning of a loop that will repeat until all the names in the database have been read back to the caller.

After the DB Read step, the designer uses a DB Get step within a Label step defined as Physician Loop. (See Figure 8-1.)

The DB Get Step

The designer continues the Successful output branch of the DB Read step by dragging a DB Get step from the Database palette to the Design pane, and dropping it over the Label step icon under the Successful icon under the DB Read step icon.

The designer then configures the DB Get step to retrieve the information in the physicians_locator database.

Figure 8-6 shows the configured General tab of the DB Get customizer window.


Figure 8-6   DB Get Customizer Window—Configured General Tab


In the DB Resource Name text field, the designer specifies "getPhysicians", which is the query that retrieves one row of data at a time from the physician_locator table.

Figure 8-7 shows the configured Field Selection tab of the DB Get customizer window.


Figure 8-7   DB Get Customizer Window—Configured Field Selection Tab


The designer uses the Field Selection tab to associate each field in the database table with a local variable.

The DB Get step has three output branches, Successful, No Data, and SQL Error. (See Figure 8-1.)

These following sections describe these output branches:

The Successful Output Branch

If the DB Get step successfully obtains data from the database table and stores the data in the defined variables, the script executes the Successful output branch.

The designer configures the Successful output branch of the DB Get step to concatenate all the information extracted from the database, play it back to the caller.

The Successful output branch contains two steps, discussed in the following sections:

The Play Prompt Step

The designer begins the Successful output branch of the DB Get step by dragging a Play Prompt step from the General palette to the Design pane, and dropping it over the Successful icon under the DB Get step icon.

The designer then configures the Play Prompt step to play back to the caller the information retrieved from the database table.

Figure 8-8 . shows the configured Prompt tab of the Play Prompt customizer window.


Figure 8-8   Play Prompt Customizer Window—Configured Prompt Tab


The designer sets the Prompt variable to the following expression:

"Spoken_Name + DP[250] + S[Phone] + DP[250] + S[Address] + DP[250] + S[Name] + DP[250] + S[Category] + DP[250] + S[Zip_Code]"

This expression represents a prompt concatenation where the Play Prompt step plays back the Document variable Spoken_Name as a prompt.

DP[250] stands for 250 milliseconds of silence. All the S[xx] elements represent the xx String variables that the Play Prompt step converts to a prompt that spell out the contents of the variables.

The Goto Step (Physician Loop)

The designer ends the Successful output branch of the DB Get step by dragging a Goto step from the General palette to the Design pane, and dropping it over the Play Prompt step icon under the Successful icon under the DB Get step icon, as shown in Figure 8-1.

The designer then configures the Goto step to instruct the script to loop back to the DB Get step and continue to retrieve a row of data from the table each time this step executes.

When the script reads every row of data and no data is found, the script automatically drops down to the DB Get step No Data output branch. (See The No Data Output Branch.)

The No Data Output Branch

If the DB Get step does not find any data in the database table, or reaches the end of the table, the script executes the No Data output branch.

The No Data output branch contains four steps, discussed in the following sections:

The DB Write Step

The designer begins the No Data output branch of the DB Get step by dragging a DB Write step from the Database palette to the Design pane, and dropping it over the No Data icon under the DB Get step icon, as shown in Figure 8-1.

The designer then configures the DB Write step to search the database table for entries for which the zip code is 99999 and delete them from the table.

Figure 8-9 shows the configured General tab of the DB Write customizer window.


Figure 8-9   DB Write Customizer Window—Configured General Tab


The designer configures the General tab of the DB Write customizer window as follows:

  • DB Resource Name—Next

You assign this name to identify this database query.

  • Data Source Name—ALPINE00.

This variable specifies the database that contains the desired information.

Figure 8-10 shows the configured SQL tab of the DB Write customizer window.


Figure 8-10   DB Write Customizer Window—Configured SQL Tab


The designer configures the SQL tab as follows:

  • Enter SQL Comments—delete from physician_locator where zip_code = 99999

This SQL command tells the step what to write to the database table.

Using the data in Table 8-1 , the DB Write step deleted the last row of the table.

The DB Write step has the following three output branches, (each of which fall through to the DB Release step):

  • Successful—The DB Write step successfully deleted the specified information.
  • Connection Not Available—The DB Write step was not successful because a connection was not found.
  • SQL Error—The DB Write step was not successful because of a SQL command error.

The DB Release Step

The designer continues the No Data output branch of the DB Get step by dragging a DB Release step from the Database palette to the Design pane, and dropping it over the DB Write step icon under the No Data output branch of the DB Get step icon, as shown in Figure 8-1.

The designer then configures the DB Release step to close the SQL query and release the allocated resources.

The system returns the released DB connection to the connection pool, and no longer associates data with this connection.

Figure 8-11 shows the configured DB Release customizer window.


Figure 8-11   Configured DB Release Customizer Window


In the DB Resource Name text field, the designer specifies getPhysicians as the DB Resource to be released.

The Terminate Step

The designer continues the No Data output branch of the DB Get step by dragging a Terminate step from the Contact palette to the Design pane, and dropping it over the DB Release step icon under the No Data output branch of the DB Get step icon, as shown in Figure 8-1.

The designer then configures the Terminate step to terminate the outgoing call.

The End Step

The designer end the No Data output branch of the DB Get step by dragging an End step from the General palette to the Design pane, and dropping it over the DB Terminate step icon under the No Data output branch of the DB Get step icon, as shown in Figure 8-1.

The End step ends the script and releases all system resources. The End step requires no configuration and has no customizer.

The SQL Error Output Branch

If the DB Get step does not execute because of a SQL command error, the script executes the SQL Error output branch, and the script falls through to the End step to end the script.

The End Step

The designer concludes the database.aef script by dragging an End step from the General palette to the Design pane, and dropping it over the DB Read step, as shown in Figure 8-1.

The End step ends the script and releases all system resources. The End step requires no configuration and has no customizer.