SQL Access objects

Defining this object configures the SQL statement that allows the CIC Studio to interact directly with a database.

You must have a Data Source object created to which you can direct the SQL Access object.

These steps take place in the SQL Access editor, which has these sections: Parameters and SQL Access Properties.

1. Create the object.
2. Under the Parameters section, click the Add button.
3. Define the parameters.
   The parameters are the placeholders for runtime values to be used when writing out the SQL Statement in Step 4. They will be visible in the Ruleset or Business Process calling the SQL Access object. For more information, see Assigning SQL Access Parameters.

   Note: A Business Process will display the parameter names as you've named them. A Ruleset will display In parameters as you've named them, but all output parameters will display as Output0, Output1, etc.
   1. Click the Add a new parameter button.
   2. From the Name column, click New_Parameter and type a name for this parameter.
   3. From the Type column, specify the type of parameter. Options are:
      • Boolean: true/false, or I/O
      • Number: numeric data
      • Object: generic data type
      • String: character data
   4. In the JDBC Type column, specify the JDBC parameter type by clicking VARCHAR to prompt a drop-down.
   5. In the Direction column, specify whether the parameter is for input or output. Options are:
      • In: Input. The value will be passed to the SQL Statement from the Ruleset or Business Process.
      • Out: Output. The value will be returned to the Ruleset or Business Process.
      • Both: Input and Output. Generally not used.
      Note: In most cases, you will create at least one input parameter and at least one output parameter for each SQL Action. The use of up to 99 parameters is supported.
   6. Make sure all In parameters are listed before the Out parameters. If you need to rearrange them, use the Move the selected parameters up and Move the selected parameters down buttons.
4. Under Sql Access Properties, specify the Data Source by clicking Click to select DataSource.
   For more information on Data Sources, see Data Sources.
5. In the SQL Statement field, type the SQL Statement.
   Note: When typing a parameter in the SQL Statement field, you must type it as ${parameter}.

   Note: The Studio does not perform SQL validation checks. "Valid" SQL Statement varies from database to database. Always refer to your database documentation for SQL statement validity.
6. The Timeout field specifies the maximum amount of time the Studio should try to communicate with the database. Either type or click the arrows to set the time. Click Seconds to select the units.
7. The Multi Record Action parameter specifies what to do if the SQL Statement returns more than one record. Options are:
   • Fail : the SQL Access fails
   • Use First: use the first record
   • Use Last : use the last record
8. The No Record Action parameter specifies what the Studio does if a SQL Statement returns nothing. Options are:
   • Fail - the SQL Access fails
   • ReturnNull - returns a null value (which is considered a success)
9. The Retry parameters specify what the Studio does when it cannot connect to the database.
   1. In the first parameter, specify the number of times to attempt a connection.
   2. In the second parameter, specify a wait-time interval number.
   3. Click the Seconds drop-down menu to set a unit for the wait-time interval.
10. (Optional) If you want to use cache values from a prior execution (within the same execution), select the Use Cache check box.
    If an SQL Access object is called twice (or more) in the same Ruleset with the same parameters, the Studio skips the Database connection and SQL execution, and returns the same information as before (instead of taking the extra steps to communicate with the database).
11. (Optional) If your SQL Statement is a callable Statement (Stored Procedure),select the Stored Procedure check box.
12. Click Save to complete this task.