SQL Application Development in CA IDMS › Accessing Non-SQL Defined Databases

Accessing Non-SQL Defined Databases

What You Can Do

CA IDMS provides the ability to use SQL to access a non-SQL defined database. The SQL statements used to access such a database are the same as those used to access a database that is defined with SQL DDL. Programming considerations such as session management and concurrency control are also the same.

Note: For more information about accessing a non-SQL defined database using SQL, see the CA IDMS SQL Reference Guide.

You can use a table procedure, a procedure, or a user-defined function to process non-SQL defined data in a relational way even though the data does not conform to the rules established for such access.

A table procedure is a user-written program which allows any data accessible through CA IDMS to be viewed and processed as a table. The parameters passed to and from the program are treated as the columns of a table which can be manipulated using SQL DML commands. The specifics of how the database is accessed in servicing these requests is hidden within the table procedure. A table procedure can:

• Provide full update capability on member records that do not contain foreign keys
• Access data with multiple definitions
• Access data that does not conform to the data type defined in the non-SQL schema
• Translate special data values into null values

A procedure is a user-written program and can be used to process and access a non-SQL-defined database. Procedures provide a method for implementing the remote call procedure paradigm.

When a procedure is invoked, it is called only once for each set of input values regardless of the type of statement containing the procedure reference. Within the single call, the procedure must use the input values, perform the expected action, and return the appropriate output values. This differs from a table procedure that can be called multiple times for a given set of input values depending upon the type of statement containing the procedure reference. Procedures are much easier to write and to interface with than table procedures.

A user-defined-function is invoked through a qualified or unqualified function identifier together with an optional set of parameter values and returns a single value. An external user-defined function has an associated user-written program that can be used to process and access a non-SQL-defined database.

Note: For more information about using table procedures, procedures, and user-defined functions to access non-SQL databases, see the CA IDMS SQL Reference Guide.

Requirements

Before you can access a non-SQL defined database through SQL, you must define a schema with the SQL statement CREATE SCHEMA that references the non-SQL defined schema. Then you can reference the records defined in the non-SQL defined schema as tables in SQL DML statements.

Tables and Columns

Once an SQL schema has been defined that references a non-SQL defined schema, each record in the non-SQL defined schema is represented as a table and each record element is represented as a column. Some elements, such as group elements, do not appear as columns in tables representing non-SQL defined records.

These transformations are applied to the names of record elements:

• All hyphens ('-') are translated to underscores ('_')
• Elements occurring a fixed number of times are suffixed with an occurrence count to distinguish individual items

No transformations are applied to the names of records. If the name does not comply to the rules for non-delimited SQL identifiers (for example, because it contains a hyphen), the name has to be delimited in double quotation marks.

Conditions Imposed by Database Design

The design of your non-SQL defined database may impose conditions on the use of some SQL DML statements:

• INSERT, UPDATE and DELETE statements are governed by the rules of referential integrity if the table being operated on represents a record that participates in a set defined with primary and foreign keys in the non-SQL defined schema
• When joining two tables representing records linked through a set in which the member record does not physically contain the owner's key value (that is, there are no embedded foreign keys), you must specify the set name in the join criteria

Limitations Imposed by Database Design

The design of your non-SQL defined database may impose limitations on the use of some SQL DML statements:

• DELETE of a table row representing a record occurrence is disallowed if that record occurrence is the owner of any non-empty set
• INSERT is disallowed on a table representing a record if that record participates in an automatic set for which foreign keys have not been defined in the non-SQL defined schema