Sign in
< Back to User Guide Top

Creating and Defining SQL Access Objects

Creating

Create an SQL Access Object to execute ad-hoc queries against a database.

  1. Select File | New | SQL Access from the main menu. The SQL Access editor appears.
    Note: If you select the Package in which the SQL Access object should reside before creating it, some parameters are automatically populated in the New SQL Access window.
  • Provide the Source folder, Package, and Name.
  • Click Finish. An editor appears.
    Note: You can also use the Wizard to create this object. Click New to select the object.

     

     

Defining

Defining this object configures the SQL statement that allow Clarify to interact directly with a database.

You must have a Data Source object created to which you can direct the SQL Access object. See Creating and Defining Data Sources for more information.

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

  1. Under the Parameters section, click the Add button.
  2. 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. Clarify currently supports the use of up to 99 parameters.
  • 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.
  • Under Sql Access Properties, specify the Data Source by clicking Click to select DataSource.
  • 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: Clarify does not perform SQL validation checks. "Valid" SQL Statement varies from database to database. Always refer to your database documentation for SQL statement validity.
  • The Timeout field specifies the maximum amount of time Clarify should try to communicate with the database. Either type or click the arrows to set the time. Click Seconds to select the units.
  • 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
  • The No Record Action parameter specifies what Clarify does if a SQL Statement returns nothing. Options are:
    • Fail - the SQL Access fails
    • ReturnNull - returns a null value (which is considered a success)
  • The Retry parameters specify what Clarify 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.
  • (Optional) If you want to use cache values from a prior execution (within the same execution), select the Use Cache checkbox.
    If an SQL Access object is called twice (or more) in the same Ruleset with the same parameters, Clarify 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).
  • (Optional) If your SQL Statement is a callable Statement (Stored Procedure), select the Stored Procedure checkbox.
  • Click Save to complete this task.
Next: Next: You must add the SQL Access action to either a Ruleset or Business Process.

Related articles

Comments

0 comments

Please sign in to leave a comment.