An SQL Access object is a Structured Query Language (SQL) statement that allows the CIC Studio to interact directly with a database. The object must reference a Data Source.
SQL Access can be used with any JDBC-compliant database, and can execute Select, Insert, Delete, and Update statements. They can call Stored Procedures or TSQL Statements, and can return multiple columns of data from a single record, but cannot return multiple records. To select multiple records from a database, use multiple SQL Access actions, or a Database Adapter with Select Interactions.
These objects are meant for a one-time operation, such as a one-time lookup, a one-time update, etc.
- Looking up specific/specialized data in a table
- Updating a record status
- Calling a Stored Procedure
- Invoking an external database table lookup to reference the customer-specific Unit of Measure for items used during your invoicing process.
- Invoking the same external database table lookup, but referencing a customer's specific shipping methods.
- Checking an item on an inbound order against an external program that tracks discontinued inventory.
How the Object Works
SQL Access objects are referenced from Business Processes and Rulesets. When referenced by a Ruleset, it can return multiple values. When referenced from a Business Process task however, it can only return a single value.
- Create the object.
- Add the parameters to use when writing your SQL Statement in its editor.
- Write your SQL Statement.
- Define the other parameters needed to interact with the database.
- Reference the object from a Business Process or Ruleset.