Most of the definitions and entities that are used in this documentation are intuitively understood and generally used.
This topic contains information about coding consideration for CA IDMS SQL:
Most of the definitions and entities that are used in this documentation are intuitively understood and generally used. With the introduction of SQL procedural language support it became necessary to more formally define several SQL routine-like objects. The following definitions are based on the definitions that are used in the SQL standard.
- SQL-invoked routineSpecifies a routine that is allowed to be invoked only from within SQL. An SQL-invoked routine can be defined in the SQL catalog as a procedure, function, or table procedure.
- SQL-invoked procedureSpecifies an SQL-invoked routine that is defined as a procedure in the SQL catalog.
- SQL-invoked functionSpecifies an SQL-invoked routine that is defined as a function in the SQL catalog.
- SQL routineSpecifies an SQL-invoked routine whose language attribute is SQL. Because table procedures cannot be written in the SQL language, an SQL routine is necessarily defined as a procedure or a function.
- SQL procedureSpecifies an SQL routine that is defined in the SQL catalog as a procedure with language attribute SQL.
- SQL functionSpecifies an SQL routine that is defined in the SQL catalog as a function with language attribute SQL.
Using SQL Statements
You can submit SQL statements to CA IDMS by:
- Using the CA IDMS online command facility (interactively)
- Using the CA IDMS batch command facility
- Embedding the statements in an application program (programmatically)
- Using tools and facilities (such as, CA IDMS Visual DBA, CA Visual Express) that submit SQL statements through CA IDMS Server
The same syntax applies no matter how you submit the statements. However, there are some statements that are only programmatic. See Statements for information about statements that you submit only in SQL that is embedded in a program.
SQL statements consist of keywords, user-supplied values, and separators, as follows:
Separate keywords and user-supplied values from one another. A separator can be a space, a comment, a new-line character, or the end of the line.
Where Separators Are Not Required
notrequired before or after a character string literal or any of the following symbols:
Greater than or equal to sign
Greater than sign
( and )
Left and right parentheses
Less than or equal to sign
Less than sign
Not equal sign
The logical not operator can be specified using the code point that is represented by X'5F' that can vary with the code page used. For example, the forms ¬=, ¬<, and ¬> are supported in code pages where the logical not sign is X'5F'. The forms ^=, ^<, and ^> are supported in code pages where the circumflex accent is represented by X’5F’. These forms of the operators are intended only to support existing SQL statements and are not recommended for use when writing new SQL statements. The equivalent operator should be substituted for any operator which includes a "not" sign. For example, substitute '<>' for '¬=', '<=' for '¬>', and '>=' for '¬<'.
Uppercase and Lowercase
You can use both uppercase and lowercase to enter keywords and user-supplied values in SQL statements. CA IDMS converts lowercase letters to uppercase in keywords and in user-supplied values that are not enclosed in quotation marks.
Delimiting and Continuing Statements
Statement Delimiter for the Command Facility
When you use the command facility to submit SQL statements, you must terminate each statement with a command delimiter, which is by default a semicolon (;). You can enter the command delimiter either on the same line as the rest of the statement or on a separate line. For example, the following two statements are equivalent:
select * from employee; select * from employee ;
You can code SQL statements on one or more lines. No special character is required to indicate that a statement continues on the next line.
Embedded SQL Delimiters
When you embed SQL statements in an application program or a CA ADS process module, you must delimit each statement both at the beginning and at the end. The requirements for delimiting embedded SQL statements vary according to the program language. For more information about delimiting embedded SQL statements, see Programming IDMS SQL.
You can embed an SQL comment within an SQL statement. SQL comments may be used in both interactive and embedded SQL statements.
An SQL comment:
- Begins with two consecutive hyphens (--)
- Consists of any combination of numbers, letters, spaces, and other characters
- Ends at the end of the line
- A bracketed comment starts with the bracket introducer string '/*' and ends with the bracket terminator string '*/'.
- Bracketed comments can span multiple lines.
- The bracket introducer and terminator strings cannot split over two lines.
- Can be used whenever a separator or space is allowed.
- Bracketed comments are only allowed in the routine body of an SQL-routine. They are not recognized by the command facility outside this context.
When defining an SQL routine using the command facility tools OCF, IDMSBCF, or using an OCF console in CA IDMS Visual DBA, the comment introducer '/*' must not be placed in column 1, because '/*' is interpreted as an end of file on input by the command facility.
Sample SQL Comments
The following example shows SQL comments with an embedded SQL statement:
select emp_id, emp_lname, dept_id -- Columns to be selected from employee -- Tables containing the data where dept_id = 1234; -- Selection criterion