Coding Considerations › Using SQL Statements

Using SQL Statements

You can submit SQL statements to CA IDMS by:

• Using the CA IDMS online command facility (that is, interactively)
• Using the CA IDMS batch command facility
• Embedding the statements in an application program (that is, 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. Chapter 9, "Statements" indicates those statements that you submit only in SQL that is embedded in a program.

Statement Components

Keywords, Values, and Separators

SQL statements consist of:

• Keywords that:
  • Identify the action requested by the statement (for example, CREATE or SELECT)
  • Specify the type of entity (for example, TABLE or INDEX) that is the object of the requested action
  • Place qualifications on the requested action, either by themselves (for example, NOT NULL or DISTINCT) or in conjunction with user-supplied values (for example, ORDER BY EMPLOYEE_LNAME)
• User-supplied values that:
  • Identify specific occurrences of entities (for example, the EMPLOYEE table or user EKJ)
  • Specify data values (for example, 983 or 'Boston')
• Separators that 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

Separators are not required before or after a character string literal or any of the following symbols:

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
;

Continuing Statements

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.

Note: For more information about delimiting embedded SQL statements, see the CA IDMS SQL Programming Guide.