This chapter describes the interface between SQL (structured query language) and RuleWorks. The SQL interface allows you easily to read data from a database into RuleWorks working memory, and write values from working memory into a database.

• SQL Expression Syntax
• Mapping Data to Working Memory Objects
• Linking with the SQL Libraries
• Attaching to a Database
• Starting an SQL Transaction
• Reading from a Database
• Using Database Key Values
• Writing to a Database
• Error Handling
• Ending an SQL Transaction
• Detaching from a Database

1. SQL Expression Syntax SQL Expression Syntax

The SQL interface consists of a set of RHS actions that generate the appropriate dynamic SQL statements (see the following table, SQL Statements Generated by RuleWorks Actions). The arguments to the RHS actions are passed to the SQL statements unchanged. For example, the following RuleWorks action:

 

(sql-fetch-as-object select field1, field2 from table1 where field1 < field2)

generates the following dynamic SQL statement:

 

SELECT FIELD1, FIELD2 FROM TABLE1 WHERE FIELD1 < FIELD2

Note that the select expression must start with SELECT spelled out in full, not abbreviated.

Table -1. SQL Statements Generated by RuleWorks Actions

RHS Action
SQL Statement Description

SQL-ATTACH database-spec [dbkey-scope] Specifies the database that is to be DECLARE SCHEMA database-spec accessed by the other RuleWorks SQL DBKEY SCOPE IS dbkey-scope actions.

SQL-COMMIT Completes the current SQL transaction
COMMIT the current SQL and makes permanent any changes made during the
transaction.

SQL-DELETE table-name [where-clause] Deletes specified records from the
DELETE FROM table-name where-clause database.

SQL-DETACH Commits any outstanding transaction
FINISH and detaches from the database.

SQL-FETCH-EACH<var>...select-expr (rhs-action)... Binds field values to RuleWorks variables
select-expr and executes RuleWorks actions that can
use those variables.

SQL-FETCH-AS-OBJECT select-expr Makes WMOs from database records.
select-expr

SQL-INSERT table-name sql-expr Stores new records in the database.
INSERT INTO table-name sql-expr

SQL-INSERT-FROM-OBJECT <$id-var> Stores the contents of a WMO
SQL-INSERT-INTO table-name (field-names) in a new database record.
SQL-INSERT-INTO )VALUES (field-values)

SQL-ROLLBACK Completes the current SQL transaction
ROLLBACK and undoes any changes made during the
transaction.

SQL-START [txn-options] Starts an SQL transaction and sets
SQL-SET TRANSACTION txn-options transaction options.

SQL-UPDATE table-name set-clause [where-clause] Modifies existing database records.
SQL-UPDATE table-name set-clause where-clause

SQL-UPDATE-FROM-OBJECT <$id-var> [where-clause] Modifies existing database records,
SQL-UPDATE table-name set-clause where-clause using the contents of a WMO.

 

2. Using Vertical Bars ( | ) Using Vertical Bars ( | )

• When the information to be passed to SQL is case sensitive. RuleWorks automatically converts unquoted atoms to uppercase.
• When the information to be passed to SQL includes parentheses (). For example, this RHS action:

1. Using Variables Using Variables

Variables must be surrounded by white space to allow the RuleWorks parser to recognize them as variables. A variable name followed immediately by a comma looks to the parser like an atom instead of a variable to be evaluated. In the following example:

 

<var1>, <var2>

the RuleWorks parser treats <VAR1>, as a symbol and <VAR2> as a variable. The next example:

 

<var1> , <var2>

results in the expected behavior.

2. Using Single Quotes (') Using Single Quotes (')

Anything that SQL treats as a character string must be enclosed in single quotes ('). This applies to both atoms and variables. Quoted variables must have at least one white space character before the opening quote and after the closing quote.

Quoted variables expand to the variable value surrounded by single quotes. Quoted variables that are bound to compound values expand to the list of compound elements surrounded by a single set of single quotes. The elements of the compound are not quoted. For example, assuming <COMPOUND2> is bound to the value (COMPOUND SOME MORE TEXT), the following RHS argument:

 

'<compound2>'

 

is passed to SQL as the literal:

 

'SOME MORE TEXT'

3. SQL Data Types SQL Data Types

The following table, SQL Data Types Supported in RuleWorks, shows the mapping between SQL and RuleWorks data types.

Table -2. SQL Data Types Supported in RuleWorks*

SQL Data Type Converted to RuleWorks Data Type

CHAR SYMBOL

VARCHAR SYMBOL

SMALLINT INTEGER

INTEGER INTEGER

QUADWORD SYMBOL

FLOAT FLOAT

DOUBLE FLOAT

DATE SYMBOL

* RuleWorks has no way to represent double-precision floating-point numbers or integers larger than 32 bits.

4. Examples of SQL Expressions Examples of SQL Expressions

1. Mapping Data to Working Memory Objects Mapping Data to Working Memory Objects

The fetch, insert, and update actions come in two forms: simple and flexible. The simple forms require a one-to-one mapping between object class names and database table names, and between attribute names and database field names. The flexible forms have no mapping requirement.

1. One-to-One Mappings One-to-One Mappings

• SQL-FETCH-AS-OBJECT
• SQL-INSERT-FROM-OBJECT
• SQL-UPDATE-FROM-OBJECT

1. Flexible Mappings Flexible Mappings

• SQL-FETCH-EACH
• SQL-INSERT
• SQL-UPDATE

1. Linking with the SQL Libraries Linking with the SQL Libraries

You must link your RuleWorks application with the SQL library in order to use the SQL actions. The easiest way is to define a logical name to point to the SQL library. For example:

$ DEFINE LNK$LIBRARY SYS$LIBRARY:SQL$USER

$ LINK MY_FILE, RUL$LIBRARY:RUL_TRL/LIB

(See the DEC rdb Introduction to SQL for more information on the SQL library.)

2. Attaching to a Database Attaching to a Database

Before your first database transaction, you must specify which database you want to access. You do this with an SQL-ATTACH action, which executes a DECLARE SCHEMA statement.

The syntax of the SQL-ATTACH action is show below:

SQL-ATTACH database-spec [ DBKEY-scope ]

The database-spec argument identifies which database you want to access. This argument can be either the filename of the Rdb database file, or the pathname of the CDD schema source. If you specify a filename, you can use the optional keyword FILENAME. If you specify a pathname, you must use the PATHNAME keyword. You can use a logical name in either case. For example:

 

(sql-attach my_sql_db) ;logical name for DBDISK:[DATABASE]MY_DB.RDB

(sql-attach pathname rul_db) ;logical name for CDD$TOP.DEPT3.PERSONNEL

The optional DBKEY-scope argument can be either TRANSACTION or ATTACH.

Note: You can access only one database at a time. Simultaneous access to multiple databases is not supported in the RuleWorks <vnum> SQL interface.

You can sequentially access multiple databases in one RuleWorks program execution.

If SQL-ATTACH is executed while a database is already attached, the original attachment is terminated and the new database is attached (unless a transaction is active, in which case a warning WMO is made and the second attachment is not performed).

3. Starting an SQL Transaction Starting an SQL Transaction

In RuleWorks, explicitly starting an SQL transaction is optional if you are only going to read from the database; it is required if you are going to write to the database. The syntax for the SQL-START action is shown below:

SQL-START [ txn-options ]

The txn-options argument is optional: its default value is READ ONLY. This argument can include any transaction option that is valid in a SET TRANSACTION statement. For example:

 

(sql-start read write reserving table_1 for shared read)

If a transaction is not currently active when a fetch action is executed, SQL implicitly starts a READ ONLY transaction. The interface ends this transaction immediately after the fetch is completed.

4. Reading from a Database Reading from a Database

The RuleWorks SQL interface provides two ways to read (fetch) data from a database. The simple form, SQL-FETCH-AS-OBJECT, automatically makes new objects from selected database records.

The flexible form, SQL-FETCH-EACH, binds the values in selected database records to variables that can then be used in whatever RHS actions you specify.

Both forms of the fetch action use the SQL syntax for select expressions. The select expression specifies which database records are selected, and which database fields are fetched. The select expression must be a valid one that you could put in a SELECT statement. For example:

 

select fld1, fld2 from table_1 where fld3 > 10 and fld4 = 'abc'

select * from table_2 where fld4 = '<var>'

Note: You must put single-quote ( ') characters around nonnumeric constants and variables. You must put at least one white space character before the opening quote and after the closing quote. However, you must not put any white space between the single quotes and a variable.

1. Using the Simple Fetch Action Using the Simple Fetch Action

The SQL-FETCH-AS-OBJECT action automatically makes a new object out of each selected database record. If the select expression causes n records to be fetched from the database, then a single execution of that SQL-FETCH-AS-OBJECT action creates n new objects. The OBJECT-CLASS name of the new objects is the name of the database table.

The syntax of the SQL-FETCH-AS-OBJECT action is shown below:

SQL-FETCH-AS-OBJECT select-expr

The names of WMO attributes to be set by SQL-FETCH-AS-OBJECT must match the field names of the database table (unless a view is used to access the table, in which case it is the view's local field names that must match the OBJECT-CLASS attribute names). For example:

 

(sql-fetch-as-object select * from part)

The example above corresponds to the OBJECT-CLASS declaration and CREATE TABLE statement in the section of this chapter, One-to-One Mappings.

2. Using the Flexible Fetch Action Using the Flexible Fetch Action

The SQL-FETCH-EACH action binds data from selected database fields to RuleWorks variables. You can then use these variables in RHS actions inside the SQL-FETCH-EACH action to create or change instances of any declared OBJECT-CLASS. If the select expression causes n records to be fetched from the database, then the variables are bound and the RHS actions are executed n times for a single execution of the SQL-FETCH-EACH action.

The syntax of the SQL-FETCH-EACH action is shown below:

SQL-FETCH-EACH <variable> ... (select-expr)
(RHS-action)...

You can specify one or more variables as the first argument, but they must not be bound prior to the SQL-FETCH-EACH action. They can be used only in the RHS actions specified as the third argument. They cannot be used after the SQL-FETCH-EACH action.

If you use any variables in the select expression, they must be bound prior to the SQL-FETCH-EACH action. They can be bound on either the LHS or RHS of the rule.

You can specify one or more RHS-actions for the third argument. These actions can use the variables from the first argument as well as variables bound prior to the SQL-FETCH-EACH action. If you use a BIND action inside the SQL-FETCH-EACH action, that variable is still bound after the action executes.

SQL interface actions are not allowed inside the SQL-FETCH-EACH action.

Example -9. Fetching Fields from an SQL Database Fetching Fields from an SQL Database

(rule fetch-items-from-database:software-option

(active-context ^name fetch-items-from-database)

(software-option ^$ID <the-part> ^is-expanded NIL

^$INSTANCE-OF <part-type>)

-->

(sql-fetch-each <partnumber> <partname> <price> <media>

(select partnumber , name , price , media_type

from sw_part

where classname = '<part-type>' )

(modify <the-part>

^partnumber <partnumber>

^name <partname>

^price <price>

^media-type <media>

^is-expanded YES) ))

SQL-FETCH-EACH does tolerate a mismatch between the number of RuleWorks variables specified in the action and the number of database fields to be fetched for each database record. If more variables are specified than fields fetched, the excess variables are set to NIL; if more fields are fetched than variables specified, the excess values are just ignored. In either case, an SQL warning WMO is generated (see the section of this chapter, Error Handling).

Given the following OBJECT-CLASS declaration:

 

(object-class objclass ^fld1 ^fld2 ^fld3)

In terms of the WMOs created and the final binding of the variable <FETCHED-INSTANCES>, the following two sequences of BIND and fetch actions are equivalent:

Example -10. Bind-Fetch Sequence

(BIND <fetched-instances> (COMPOUND))

(SQL-FETCH-EACH <v1> <v2> <v3>

(|SELECT FLD1, FLD2, FLD3 FROM OBJCLASS WHERE FLD4 =10|)

(BIND <instance>

(MAKE objclass ^fld1 <v1> ^fld2 <v2> ^fld3 <v3>))

(BIND <fetched-instances>

(COMPOUND <fetched-instances> <instance>)))

 

If this is what is what you want to do, the second sequence is more efficient:

Example -11. Bind-Fetch Sequence - 2

(BIND <fetched-instances>

(SQL-FETCH-AS-OBJECT

|SELECT FLD1, FLD2, FLD3 FROM OBJCLASS WHERE FLD4 = 10|))

Note however, that the SQL-FETCH-EACH action gives you more flexibility by allowing arbitrary RHS actions (except other SQL actions) to be performed after each fetch. Also, you are not restricted to the one-WMO-to-one-record data model. Consider the following actions that fetch the same data but place it into one working memory object:

Example -12. Placing Fetch Data into One Working Memory Object (WMO)

(object-class objclass

^fld1 COMPOUND

^fld2 COMPOUND

^fld3 COMPOUND)

...

(BIND <fld1> (COMPOUND))

(BIND <fld2> (COMPOUND))

(BIND <fld3> (COMPOUND))

(SQL-FETCH-EACH <v1> <v2> <v3>

(|SELECT FLD1, FLD2, FLD3 FROM OBJCLASS WHERE FLD4 =10|)

(BIND <fld1> (COMPOUND <fld1> <v1>))

(BIND <fld2> (COMPOUND <fld2> <v2>))

(BIND <fld3> (COMPOUND <fld3> <v3>)))

(BIND <instance>

(MAKE objclass ^fld1 <fld1> ^fld2 <fld2> ^fld3 <fld3>))

 

3. Using Views to Fetch Data Using Views to Fetch Data

Using SQL views to fetch data from one or more tables into a specified class of object is perfectly acceptable and easily done. However, if you are using multiple tables, you can use views only to read database records, not to insert or update database records in multiple tables. (You cannot use views that contain aggregates to write data either; see the DEC Rdb Guide to SQL Programming section on CREATE VIEW for restrictions.)

You can define a view to achieve the one-to-one mapping of object class to database table names, or of object attributes to database field names, required by the simple forms of the fetch, insert, and update actions. For example:

Example -13. Using Views to Fetch Data

CREATE TABLE Y (X INTEGER,

Z CHAR(20), ...);

CREATE VIEW A (B, C)

AS SELECT X, Z FROM Y;

In this example, attribute ^B of object class A maps to field B in view A (satisfying the 1-to-1 name mapping requirement), while the view's field B in turn corresponds to field X in the underlying table Y to which the view provides access.

5. Using Database Key Values Using Database Key Values

You can fetch database key values and use them inside RuleWorks, but you cannot use them to insert or update records. The SQL interface translates database key values into character strings, such as 1:2:3, for the database area, page, and line numbers of the fetched records.

You can declare an attribute called ^DBKEY and test whether it is NIL to find out if the object was fetched from the database or not.

You can compare two fetched database key values for equality.

Note: Database key values are not persistent. They are valid for only the duration of a single attachment to the database (or less if you use the default transaction scope).

6. Writing to a Database Writing to a Database

• SQL-UPDATE
• SQL-UPDATE-FROM-OBJECT
• SQL-INSERT
• SQL-INSERT-FROM-OBJECT

1. Updating Existing Records Updating Existing Records

Updating existing records modifies data fields without creating any new database records. The SQL-UPDATE-FROM-OBJECT action uses the contents of an object to modify existing database records; the SQL-UPDATE action uses constants or bound variables.

Both update actions allow you to use the SQL syntax for WHERE clauses to specify which database records are modified. The syntax for the SQL-UPDATE-FROM-OBJECT action is shown below:

SQL-UPDATE-FROM-OBJECT <$id-variable> [ WHERE-clause ]

SQL-UPDATE-FROM-OBJECT modifies records in the database table whose name matches the OBJECT-CLASS of the WMO specified by the $id-variable argument. This argument also specifies which object provides the new data values for the update. If the object has attributes that do not correspond to fields in the target database table, those attributes are ignored. On the other hand, if the database table has fields that do not correspond to attributes in the object, those fields are not modified.

For example:

 

(sql-update-from-object <W> where fld1 = <var>)

Note that all of the fields in the database record(s) may be modified, not just fld1. SQL-UPDATE-FROM-OBJECT uses all the attribute values for the specified RuleWorks object to modify all the corresponding data fields in the selected database records. Depending on the WHERE clause, one or more records may be updated by a single SQL-UPDATE-FROM-OBJECT action.

The SQL-UPDATE action uses the SQL syntax for SET clauses to specify which database fields are modified. The syntax for the SQL-UPDATE action is shown below:

SQL-UPDATE table-name SET-clause [WHERE-clause]

The table-name argument can be a symbol or bound variable; the SET-clause argument can contain constants or bound variables. As with SQL-UPDATE-FROM-OBJECT, the WHERE-clause argument is optional. For example:

 

(sql-update tbl set fld1 = 0 where fld2 > 10)

Note that one or many database records may be updated by a single RuleWorks SQL-UPDATE execution. If you want to update only one record per firing of this rule, you must write a WHERE clause that restricts the selection to a single record.

2. Inserting New Records Inserting New Records

1. Error Handling Error Handling

The SQL interface signals error conditions by creating WMOs whose class name is SQL$MSG. In order to accept these objects, your RuleWorks program must include the following OBJECT-CLASS declaration:

Example -14. OBJECT-CLASS Declaration

(OBJECT-CLASS SQL$MSG

^SEV ; severity code

^COND ; condition code, or message name

^TEXT ; description of the error

^RULE) ; the name of the rule that executed the SQL action

The following example, SQL Error Objects, shows a few sample objects of class SQL$MSG.

Example -15. SQL Error Objects SQL Error Objects

RuleWorks>PPWM SQL$MSG

#54 60 [ATTACH-DATABASE:DO-IT] (SQL$MSG ^SEV W ^COND SQLATTFAI ^TEXT SQL attac

h to database failed ^RULE ATTACH-DATABASE:DO-IT)

#56 62 [FETCH-ITEMS-FROM-DATABASE:HARDWARE-OPTION] (SQL$MSG ^SEV W ^COND SQLFET

PRE ^TEXT Preparation of SQL fetch statement failed ^RULE FETCH-ITEMS-FROM-DATA

BASE:HARDWARE-OPTION)

#60 66 [FETCH-ITEMS-FROM-DATABASE:HARDWARE-OPTION] (SQL$MSG ^SEV W ^COND SQLFET

PRE ^TEXT Preparation of SQL fetch statement failed ^RULE FETCH-ITEMS-FROM-DATA

BASE:HARDWARE-OPTION)

You can write rules to process SQL$MSG objects as they are produced; you decide how, or whether, to proceed after an SQL warning. The following example, Handling an SQL Error, shows a rule that halts the program when the attachment to the database fails.

Example -16. Handling an SQL Error Handling an SQL Error

(rule abort-on-db-attach-failure

(sql$msg ^cond sqlattfai ^text <text> ^rule )

-->

(write (crlf) |Execution of| |caused the following error:|)

(write (crlf) <text>)

(halt))

 

2. Ending an SQL Transaction Ending an SQL Transaction

When you end an SQL transaction, you can either apply (commit) any changes to the database made during the transaction, or you can undo them (rollback). Use the SQL-COMMIT action to apply the changes, the SQL-ROLLBACK action to undo them. Both of these actions complete the current SQL transaction. Their syntax is shown below:

SQL-COMMIT
SQL-ROLLBACK

The SQL interface automatically performs an SQL-COMMIT action to terminate any transaction you started implicitly by a fetch operation.

3. Detaching from a Database Detaching from a Database