Customizing SQL access types in Segments

You customize SQL access in the Segment -GG Lines, on G type lines. On the first line, you enter the access type that you want to customize. On the following lines, you specify the parts of the access you want to modify or add.

Procedure

  1. Enter the first line of the customized access type.

    Open the -GG Lines subtab of a Segment. Click Add and select G in the Type field

    Enter SQL at the beginning of the Description field to indicate that you want to customize an SQL access type. After a space, you enter the access type you want to customize. The access type is standard or nonstandard.
    • The standard access types are automatically implemented in the instances (Programs, Screens, or Servers) which call the Segment. The following access types exist:
      • R: Select
      • RU: Select for Update
      • W: Insert
      • RW: Update
      • D: Delete
      • P: Open Cursor
      • RA: Select (Pacbench C/S)
      • RN: Fetch
      • DC: Declare Cursor
      • CL: Close Cursor
      Note: The DC (Declare Cursor) standard access type is not managed for SQL access types where DC is generated in the WORKING-STORAGE SECTION.
      Figure 1. Example of the customization of the standard select access:
      Type   Description 
      G      SQL R
    • A nonstandard access type must be coded on 1 or 2 characters. It must be explicitly implemented in the instances that call this Segment. This access type can be used in two ways:
      • It can be combined with a standard access type to modify only part of the standard access type and let the generator manage the syntax. In this case, you must enter the code of the standard access type first and, after a blank, the code of the nonstandard access type.
      • It can be used alone to create an access type. In this case, you must describe this access type entirely and implement it in specific code. You must manage the syntax (such as the punctuation or keywords).
      Figure 2. Example of the customization of a nonstandard select access type:
      Type   Description 
      G      SQL R1 RA
      Figure 3. Example of the creation of a nonstandard access type that counts the number of records in the clients table:
      Type   Description 
      G      SQL CP
  2. Enter the following lines:

    The SQL access is described by clauses (such as ACCESS, WHERE, ORDER). Each clause is described by a keyword and data that is related to the keyword. From the second line onwards, you enter these keywords, which apply to the customization of both standard and nonstandard access types.

    The corresponding data will replace the data that would have been generated otherwise. If it must be added as a complement, enter ADD before or after each keyword.

    Select a G type line for the following lines. Enter the keyword and the parameters that required for its operation in the Description field.
    Notes:

    A keyword that is described on more than one line is not to be repeated on each line.

    The corresponding delimiter is automatically generated at the end of each line. If a column name and a host variable name exceed one line, add a V type line and continue on the next line (a G type line). In this case, and if you enter a column name and a host variable name (with or without :-), you must code the host variable on a new line.

    You can enter the following keywords with their associated parameters:
    • ACCESS, followed by the SQL access type:

      With this keyword, you can rewrite the clause of an access type (such as SELECT, UPDATE, DELETE).

    • COLUMN, followed by the list of columns:

      These columns can be coded either as SQL-Data Element (in this case they are replaced upon generation by their relational name) or directly under their relational name.

      Each column can be followed, after one blank at least, by the code of the host variable (and indicator). This code is either :-Data Element (upon generation, the Screen Segment that is defined on the -CS Lines will be added before :-Data Element), or the host variable name. If the host variable is not coded in an access type where it is required, it will be generated with the Screen Segment code and the Data Element indicated in the column (as SQL-Data Element, otherwise the code of the host variable must be indicated).

      The parameters that are associated with this keyword follow specific punctuation rules:
      • If the column and host variable names exceed one line, you can use one or more V lines but only for the host variable name. The column name is entered on one line only.
      • For the UPDATE statement (RW) of the COLUMN keyword, the host variable and its indicator must be coded on the same line whatever their input format.
    • FROM, followed by the list of tables:

      Upon generation, this keyword is either transformed into INTO or remains FROM according to the access type.

    • ORDER, followed by the list of columns:

      With this keyword, you can indicate the ORDER BY.

      The column names can be coded as SQL-Data Element and the host variables and indicators as :-Data Element.

    • PARAM, followed by additional parameters:

      With this keyword, you can code additional parameters such as COUNT or HAVING. The ADD keyword is not necessary.

      The column names can be coded as SQL-Data Element and the host variables and indicators as :-Data Element.

    • WHERE, followed by selection criteria:

      The column names can be coded as SQL-Data Element and the host variables and indicators as :-Data Element.

    Important:

    The generated format of the date and variable Data Elements (such as CURDAT (current date) as a parameter of the ORDER statement) varies according to the Block type.

    The option UPPER=NO, entered on an O type line in the -GO Lines of the instance that calls the Segment, inhibits the lowercase uppercase conversion of the values that are entered between two literal delimiters (the delimiter is defined in the Library).

    The syntax of SQL statements is not implemented on RDMS Blocks (R organization). The prefixing rule does not apply. The table name is kept as is and the period at the beginning is deleted if it exists.

Example


Feedback