Cisco Customer Response Applications Editor Step Reference Guide (3.1)
Database Step Descriptions
Download this chapter
Database Step DescriptionsDatabase Step Descriptions
 Feedback

Table of Contents

Database Step Descriptions
 DB Get
 DB Read
 DB Release
 DB Write

Database Step Descriptions

The steps in the Database palette of the Cisco Customer Response Applications (CRA) Editor provide script designers with a way to read and write data and/or documents to database tables.

This Database palette contains the following steps:

  • DB Get
  • DB Read
  • DB Release
  • DB Write
    • Note   Before you use the Database steps, refer to the Cisco Customer Response Applications Administrator Guide for information on how to set up a Data Source Name (DSN) and configure the Database subsystem.

Figure 12-1 shows the steps in the Database palette as they appear in the Palette pane of the CRA Editor.


Figure 12-1   Database Palette Steps


DB Get

Use the DB Get step to assign to specific variables the results of the Structured Query Language (SQL) query that you define in the DB Read step.

Note   Before you can use a DB Get step, you must use a DB Read step to define the SQL statements and identify the target database.

Each time the script executes the DB Get step, the script retrieves one row of the results returned by the DB Read step and places them in the variables you assign. To move to the next row in a table, you must execute the DB Get step again.

The DB Get step has three output branches:

  • Successful—The DB Get step successfully retrieved data.
  • No Data—The DB Get step did not retrieve data.
  • SQL Error—There was an error in the SQL command.
    • Note   You cannot create Join queries that retrieve columns with the same name from different tables. (For example, you cannot create "select a.x, b.x from a,b" and "select a.x as y from a".) Similarly, you cannot make aliases of column names. The SQL statements SELECT count(*) and SELECT min(*) are not supported.

The DB Get customizer window contains two tabs:

The following sections describe these two tabs.

General

Use the General tab of the DB Get step to specify the DB Resource Name and Data Source Name.

Figure 12-2 shows the General tab of the DB Get customizer window.


Figure 12-2   DB Get Customizer Window—General Tab


Table 12-1 describes the properties of the General tab of the DB Get customizer window.

Table 12-1   DB Get Properties—General Tab

Property Description

DB Resource Name

Query defined by a DB Read step.

You can choose a specific query when your script has multiple DB Read steps open at the same time.

Data Source Name

Name of the database selected on the CRA Administration Database web page.

For information about defining DSNs, refer to the Cisco Customer Response Applications Administrator Guide.

Refresh Database Schema

Button that allows you to update the CRA Editor with the fields defined in the selected database.

To use the General tab of the DB Get customizer window to specify the DB Resource Name and Data Source Name, perform the following procedure:

Procedure
Step 1   If necessary, click the General tab.

The General tab of the DB Get customizer window appears.

Step 2   From the DB Resource Name drop-down menu, choose the DB Resource Name you configured in the DB Read step.

Step 3   In the Data Source Name field, enter the name of the desired database.

Step 4   Click the Refresh Database Schema button to update the CRA Editor with the fields defined in the selected database.

Step 5   Click Apply.

You are now ready to configure the Field Selection tab of the DB Get customizer window.




Field Selection

Use the Field Selection tab of the DB Get customizer window to identify the fields within the selected query and to choose the variables in which you want to store the value for each field.

Figure 12-3 shows the Field Selection tab of the DB Get customizer window.


Figure 12-3   DB Get Customizer Window—Field Selection Tab


Table 12-2 describes the properties of the Field Selection tab of the DB Get customizer.

Table 12-2   DB Get Properties—Field Selection Tab

Property Description

Table Name

Name of the table from the selected database

Field Name

Name of the field in the selected database

Data Type

Data type of the variable

Local Variable

Variables that store the values of the associated fields

To use the Field Selection tab of the DB Get customizer window to identify fields and to choose the variables in which you want to store the value for each field, perform the following procedure:

Procedure
Step 1   If necessary, click the Field Selection tab.

The Field Selection tab of the DB Get customizer window appears.

Step 2   From the Table Name drop-down menu, choose an available table from the database specified in the General tab of this step.

Step 3   Click Add.

The Add Database Field dialog box appears. (See Figure 12-4 .)


Figure 12-4   Add Database Field Dialog Box


Step 4   From the Enter the Field (Column) Name drop-down menu, choose a field.

The data type of the selected field appears automatically in the Data Type text field.

Step 5   From the Enter the Variable Name to Store this Field drop-down menu, choose the variable you want to assign to the selected field, and then click OK.

The Add Database Field dialog box closes, and the field name, data type, and local variable appear in their respective columns in Field Selection tab of the DB Get customizer window.

(To modify already existing field and variable information, select the desired field or variable, and click Modify. The Modify Database Field dialog box appears, which contains the same fields as the Add Database Field dialog box and is configured in the same way.)

Step 6   Click OK.

The DB Get customizer window closes, and the resource name appears next to the DB Get step icon in the Design pane of the CRA Editor.




DB Read

Use the DB Read step to choose a database and enter SQL statements required to obtain the data you need for your script.

You can use a DB Get step after a DB Read step to assign the results of your query to specific variables.

Note   The DB Read step is not interruptible by external events.

The DB Read step has three output branches:

  • Successful—The DB Read step successfully executes.
  • Connection Not Available—The DB Read step can not make a connection to the specified database.
  • SQL Error—The DB Read step encounters a SQL command error.

The DB Read customizer window contains three tabs:

The following sections describe these tabs.

General

Use the General tab of the DB Read customizer window to specify a DB Resource Name and Data Source Name.

Figure 12-5 shows the General tab of the DB Read customizer window.


Figure 12-5   DB Read Customizer Window—General Tab


Table 12-3 describes the properties of the General tab of the DB Read customizer window.

Table 12-3   DB Read Properties—General Tab

Property Description

DB Resource Name

Name that identifies this database query.

The same DB Resource Name is used in the accompanying DB Get and DB Release steps.

Data Source Name

Database you want to access.

This list contains the DSNs you identified in the CRA Administration Database web page.

For information about defining DSNs, refer to the Cisco Customer Response Applications Administrator Guide.

Timeout (in sec)

Interval that prevents your script from waiting indefinitely when the database is unavailable. If the value for the timeout interval is 0, an indefinite wait occurs.

Note An indefinite wait may block the script from responding to events such as a remote disconnection or an agent becoming available.

Refresh Database Schema

Button that allows you to download the schema from the Cisco CRA engine.

To use the General tab of the DB Read customizer window to specify a DB Resource Name and Data Source Name, perform the following procedure:

Procedure
Step 1   If necessary, click the General tab.

The General tab of the DB Read customizer window appears.

Step 2   From the DB Resource Name drop-down menu, choose the name that identifies the database query.

Step 3   From the Data Source Name drop down menu, choose the name of the desired database.

If the Data Source Name drop-down list is empty, click the Refresh Database Schema button to download the schema from the Cisco CRA Engine.

Step 4   In the Timeout (In Sec) text field, enter a number to represent the timeout length.

Step 5   Click Apply.

You are now ready to configure the SQL tab of the DB Read customizer window.




SQL

Use the SQL tab of the DB Read customizer window to define the SQL SELECT statement required to retrieve the data that you need from the specified database.

Figure 12-6 shows the SQL tab of the DB Read customizer window.


Figure 12-6   DB Read Customizer Window—SQL Tab


Table 12-4 describes the properties of the SQL tab of the DB Read customizer window.

Table 12-4   DB Read Properties—SQL Tab

Property Description

Enter SQL command to be executed

SQL command that you want to be executed.

Number of rows returned

Rows returned when the Test button is clicked.

Show All Fields (Select Table)

Fields defined in a particular table in this database.

To use the SQL tab of the DB Read customizer window to define the SQL SELECT statement, perform the following procedure.

Procedure
Step 1   If necessary, click the SQL tab.

The SQL tab of the DB Read customizer window appears.

Step 2   In the Enter SQL Command to be Executed text field, enter the SQL command you want to be executed, using standard SQL syntax.

The SQL statements SELECT count(*) and SELECT min(*) are not supported.

Step 3   To test your query and connectivity to the chosen database, click Test.

Observe the number of rows returned to determine whether or not your query is working correctly.

To test the SQL statement, you must have a connection to an active Cisco CRA Engine.

Step 4   To display the fields defined in a particular table in this database, select the table from the Show all fields drop-down list.

Step 5   Click Apply.

You are now ready to configure the Comments tab of the DB Read customizer window.




Comments

Use the Comments tab of the DB Read customizer window to add a text description explaining your script.

Figure 12-7 shows the Comments tab of the DB Read customizer window.


Figure 12-7   DB Read Customizer Window—Comments Tab


To use the Comments tab of the DB Read customizer window to enter your comments, perform the following procedure:

Procedure
Step 1   If necessary, click the Comments tab.

The Comments tab of the DB Read customizer window appears.

Step 2   In the Enter Comment Text Here text field, enter your comments.

Step 3   Click OK.

The DB Read customizer window closes, and the resource name appears next to the DB Read step icon in the Design pane of the CRA Editor.




DB Release

Use the DB Release step after a DB Get or DB Write step to close a SQL query and release the allocated resources.

A released DB connection is returned to the connection pool and data is no longer associated with this connection.

Figure 12-8 shows the customizer window for the DB Release step.


Figure 12-8   DB Release Customizer Window


Table 12-5 describes the properties of the DB Release customizer window.

Table 12-5   DB Release Properties

Property Description

DB Resource Name

Name of the DB Resource for which you want to release resources.

Refresh Database Schema

Button that allows you to update the DB Resource Name drop-down list.

To use the DB Release customizer window to close a SQL query, perform the following procedure:

Procedure
Step 1   From the DB Resource Name drop-down menu, choose the DB Resource Name for which you want to release resources.

Click the Refresh Database Schema button to update the DB Resource Name drop-down list.

Step 2   Click OK.

The DB Release customizer window closes, and the resource name appears next to the DB Release step icon in the Design pane in the CRA Editor.




DB Write

Use the DB Write step to select a database and enter SQL UPDATE, SQL DELETE, or SQL INSERT statements.

You can use this step to update an enterprise database. When you are finished with a database write operation, use the DB Release step to free the connection to the database server.

The DB Write step has three output branches:

  • Successful—The DB Write step successfully enters the specified SQL statements.
  • Connection Not Available—The DB Write step can not find a connection to the specified database.
  • SQL Error—The DB Write step encounters a SQL command error.

The DB Write customizer window contains four tabs:

The following sections describe these tabs.

General

Use the General tab of the DB Write customizer window to identify the DB Resource Name variable and the database that you want to update.

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


Figure 12-9   DB Write Customizer Window—General Tab


Table 12-6 describes the properties of the General tab of the DB Write customizer window.

Table 12-6   DB Write Properties—General Tab

Property Description

DB Resource Name

Name you assign to identify this database query.

Data Source Name

Database you want to update when you are defining a new query.

Refresh Database Schema

Button that allows you to download the schema from the Cisco CRA Engine.

To use the General tab of the DB Write customizer window to identify the DB Resource Name variable and the database that you want to update, perform the following procedure:

Procedure
Step 1   If necessary, click the General tab.

The General tab of the DB Write customizer window appears.

Step 2   From the DB Resource Name drop-down menu, choose the name that identifies the database query.

Step 3   From the Data Source Name drop-down menu, choose the name of the desired database.

If the Data Source Name drop-down menu is empty, click the Refresh Database Schema button to download the schema from the Cisco CRA Engine.

Step 4   Click Apply.

You are now ready to configure the SQL tab of the DB Write customizer window.




SQL

Use the SQL tab of the DB Write customizer window to define SQL UPDATE, SQL DELETE, or SQL INSERT statements that will update the chosen database.

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


Figure 12-10   DB Write Customizer Window—SQL Tab


Table 12-7 describes the properties of the SQL tab of the DB Write customizer window.

Table 12-7   DB Write Properties—SQL Tab

Property Description

Enter SQL command to be executed

SQL command that you want to be executed

Show All Fields (Select Table)

Fields defined in a particular table in this database

To use the SQL tab of the DB Write customizer window to define SQL UPDATE, SQL DELETE, or SQL INSERT statements, perform the following procedure.

Procedure
Step 1   If necessary, click the SQL tab.

The SQL tab of the DB Write customizer window appears.

Step 2   In the Enter SQL Command to be Executed text field, enter the SQL UPDATE, SQL DELETE, or SQL INSERT statements, using standard SQL syntax.

Step 3   To display the fields defined in a particular table in this database, select the table from the Show all fields drop-down list.

Step 4   Click Apply.

You are now ready to test your query and connectivity with the Test tab of the DB Write customizer window.




Test

Use the Test tab of the DB Write customizer window to test your query and connectivity to the chosen database.

Figure 12-11 shows the Test tab of the DB Write customizer window.


Figure 12-11   DB Write Customizer Window—Test Tab


Table 12-8 describes the properties of the Test tab of the DB Write customizer window.

Table 12-8   DB Write Properties—Test Tab

Property Description

SQL command entered

SQL command you want to test

Number of rows altered

Number of rows altered by the test

To use the Test tab of the DB Write customizer window to test your SQL command string, perform the following procedure.

Procedure
Step 1   If necessary, click the Test tab.

The Test tab of the DB Write customizer window appears.

Step 2   In the SQL Command Entered text field, enter a SQL command.

Step 3   Click Execute.

The result appears in the Number of Rows Altered text field.

Step 4   Observe the figure in the Number of Rows Altered text field. This number indicates whether or not your SQL statements are producing the expected results.

Clicking the Execute button in this window does not update the database; this action does nothing but validate the SQL statement. You must be connected to an active CRA server to perform this operation.

Step 5   Click Apply.

You are now ready to enter comments in the Comments tab of the DB Write customizer window.




Comments

Use the Comments tab of the DB Write customizer window to add a text description that explains your script.

Figure 12-12 shows the Comments tab of the DB Write customizer window.


Figure 12-12   DB Write Customizer Window—Comments Tab


To use the Comments tab of the DB Write customizer window to enter your comments, perform the following procedure:

Procedure
Step 1   If necessary, click the Comments tab.

The Comments tab of the DB Write customizer window appears.

Step 2   In the Enter Comment Text Here text field, enter your comments.

Step 3   Click OK.

The DB Write customizer window closes, and the resource name appears next to the DB Write step icon in the Design pane of the CRA Editor.