DB Query

This Database tool retrieves a result set from a table based on a Where clause. The result set retrieved matches the conditions you specify in your Where clause. You can then specify a variable (bind) for each row of data returned.

The following example shows you one example of a where clause you might want to construct, and then shows you how to configure DB Query to execute that where clause.

Sample Where Clause

You have a database containing transaction IDs, the names of the Agent's who processed the transactions, the Agent's phone number, and the cost of the transaction. You want to retrieve all purchase records for a single agent. In this example, the agent's name is specified in a variable called strAgent1. The Clothing database to be queried is shown below.

The Where clause you should use to retrieve the value is shown below:

SELECT ID, Cost FROM Clothing WHERE Name=’strAgent1’

Creating the Where Clause with the DB Query Tool

Now that you know the Where clause you want to use, follow these steps to configure the DB Query tool to execute the Where clause.

1. Specify the Connection Variable and Data Source.
   The Connection Variable is generated by the DB Connect step elsewhere in the handler. The Data Source list is populated with ODBC data sources you have configured. For more information on these two parameters, see the field documentation further down in this help topic.

2. Select a Table Name.
   Once you have selected a data source, the Table Columns for that data source are listed as choices for this parameters. Using the our sample database as an example, Clothing is the table listed. The Table Name forms the FROM portion of your Where Clause, so you've now configured FROM Clothing.
   Once you have selected a Table Name, the columns within that table appear in the Table Columns List.

3. In the Table Columns list, select the columns you want to query.
   When you pick Clothing as your Table Name, ID, Name, Phone, and Cost all appear in the Table Column list. You would highlight ID, then click the Bind... button. In the Column Binding Dialog, you would select the variable to which will receive the value retrieved from the ID column. Next you would select the Cost table column and bind the variable that will receive the value of from the Cost Column. (The variable to which the Table Columns are bound are created with Assignment steps that precede this DB Query step.)
   
   Binding variables to the ID and Cost columns completes the SELECT portion of your Where clause. Now you have configured DB Query to execute the following statement:
   
   SELECT ID, Cost FROM Clothing
   
   The next step will be to add the WHERE portion of the Where clause.

Note: Interaction Center 4.0 supports SQLServer 2008, which supports a new datetime2 data type. To bind this type to a datetime variable in this and other toolsteps, use the latest driver "SQL Server Native Client 10.0" in the DSN. Otherwise, Interaction Designer won't recognize the datetime2 data type and will treat it as a string.

4. Specify the Where Clause Variable.
   In the Where Clause Variable parameter, you will specify the variable that contains your Where clause. You must create your Where clause in an Assignment step preceding this DB Query step.
   
   The DB Query step will insert the WHERE portion for you, so your Assignment step only needs to assign:
   Name = 'strAgent1'
   
   To type this value in Expression Editor Assistant, you would type the following:
   
   "Name=" & SQLStr(strAgent1)
   
   When the handler executes, this expression results in Name = 'strAgent1'. Once you have assigned this value to a string variable, you can select that variable from the DB Query's Where Clause Variable drop-down list.
   
   Once you have selected your Where Clause Variable, the WHERE portion of your Where clause is complete. When you run the handler the following Where clause is executed:
   
   SELECT ID, Cost FROM Clothing WHERE Name=’strAgent1’

5. After this step executes, you can use a DB Fetch step to retrieve, one record at a time, the records stored in the result set. This example is continued in the documentation for that tool.

Settings Page

Connection Variable

Specifies what connection on which the query is performed. Use a connection variable that you specified in a DB Get Connection step. Each connection can contain only one result set, so if you need more than one result set, you should open more than one connection.

Data Source

Select a data source to query. The selected data source’s table columns appear in the Table Name drop-down list box.

Table Name

This is a list of tables available for the data source you specified in the previous Data Source parameter. When you select a Table Name, the columns defined in that table appear in the TableColumns/VariableList.

Runtime Table Name

Specify a string expression that, at handler runtime, is used as the table name for the select statement. This field is optional. If no value is specified, the table selected in the Table Name field will be used.

Timeout (seconds)

The number of seconds to wait for this operation to complete successfully before taking the failure path. Any value less than or equal to zero results in the default value of 60 seconds being used. You may specify a decimal value, such as 5.5.

Table Columns/Variable List

This is a list of Columns for the table specified in the previous Table Name parameter. You can bind variables created previously in Assignment steps earlier in the handler.

Bind Button

A binding specifies which variable receives the value of the selected column and row. Use this button to bind a column to a variable. When you execute this query and do a fetch, that variable receives the value of that column for the current row. A second fetch overwrites the value of this variable.

Note: The bindings dialog box allows bindings to "convertible" types. This is useful if you want to read a value from a column and have it be converted into another type automatically. For instance, SQL integers into numeric type variables.

Users wanting to bind a variable to a column are presented with the list of variables that are eligible for binding to the SQL type for that column. In past releases, these variables would have been all the same type (i.e. there was only one handler variable type allowed for a given SQL type). Now the list of variables will contain the variables of all the types that can be converted from the SQL Type.

Unbind Button

Use this button to unbind a column from a variable. See Bind Button above.

Where Clause Variable

Specify the variable that contains a Where clause. You will need to construct the Where clause in an Assignment step preceding this step. For more information on building Where clauses, see Sample Where Clauses.

Exit Paths

Success

If this step executes successfully, this step takes the Success exit path.

Failure

If this step does not execute successfully, this step takes the Failure exit path. This can occur when information specified about the table or view is incorrect, the DB connection variable is not valid, or the Where clause was not correct (i.e. it did not contain valid SQL statement).