Customizing SQL access types in Segments, Logical Views, and Tables
You customize SQL access in the -GG Lines, on G type lines. On the first line, you enter the access type that you want to customize. On the next lines, you specify the parts of the access type that you want to modify or add.
Procedure
- Declare the customized access type on the first line.
In the -GG Lines subtab of a Segment, Logical View, or Table, 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, enter the access type you want to customize. The access type is standard or nonstandard. It can also correspond to an extraction method.- The standard access types are automatically implemented in the instances (Programs, Screens, or
Business Components) that call the Segment. The following access types exist:
- R for SELECT
- RU for SELECT FOR UPDATE
- W for INSERT
- RW for UPDATE
- D for DELETE
- P for OPEN CURSOR
- RA for SELECT (Pacbench C/S)
- RN for FETCH
- DC for DECLARE CURSOR
- CL for 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 accessType 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 the Segment. You can specify this access type in two ways:
- Combine it with a standard access type to modify only part of the standard access type. The
generator manages 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. Figure 2. Example of the customization of a nonstandard select access type
Type Description G SQL R1 RA
- Enter it 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 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
- Combine it with a standard access type to modify only part of the standard access type. The
generator manages 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.
- An extraction method is a select access type to a Business Component. It is constituted of four
access types (DECLARE CURSOR, OPEN CURSOR, FETCH,
and CLOSE CURSOR) that you can customize in two ways:
- You can partially describe only one customized access type, by entering EX
twice.Figure 4. Example of the declaration of the METHODNAME extraction method
Type Description G SQL EX EX METHODNAME
- You can describe the four access types entirely, by combining your customized accesses with each
of the four access types (DC, P, RN, and
CL).Figure 5. Example of the customization of the METHODNAME four access types
Type Description G SQL D1 DC METHODNAME G SQL O1 P METHODNAME G SQL F1 RN METHODNAME G SQL C1 CL METHODNAME
You must enter the method name in uppercase letters, from column 7, on 10 characters maximum.
The extraction is completed according to the criteria that are indicated in the WHERE parameter, and in the order that is specified in the ORDER parameter.
Note: You must also specify the selection parameters in the Logical View because they are included in the message that is sent to the Business Component. To do so, in the Logical View -CE Lines, select E in the Data type field of each Data Element that constitutes a selection parameter. - You can partially describe only one customized access type, by entering EX
twice.
- The standard access types are automatically implemented in the instances (Programs, Screens, or
Business Components) that call the Segment. The following access types exist:
- Describe the customized access type on the next lines.
The SQL access type 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. A keyword that is described on more than one line is not to be repeated on each line.
Select a G type line for the next lines. Enter the keyword and its parameters in the Description field in the following way:- 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 complementary parameters.
With this keyword, you can code complementary 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.
Note: 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. However, if you enter a host-variable and its indicator as :- (without Segment code), you must code them on the same line.Examples:- Customization of a nonstandard select access type to select a client number higher than or equal
to the requested number.
You modify the WHERE clause only but you do not want to modify the standard access directly because this selection applies to few Screens only.
Figure 6. Example of the customization of a nonstandard select access typeType Description G SQL RB RA G WHERE SQL-NOTJ >= :-NOTJ
- Example of the creation of a nonstandard access type to count the number of records in the
clients table.
In this case, it is a specific need. So you describe a new access type.
Figure 7. Example of the creation of a nonstandard access typeType Description G SQL CP G ACCESS SELECT COUNT (*) G INTO :WWCA-COMPTEUR G FROM PTB0.TRAJET
Generating keywords is possible only for standard or similar access types (CP, R). For a CP access type that is not followed by a standard access type, you must also write the keyword on the G line.
- Customization of a nonstandard select access type to select a client number higher than or equal
to the requested number.
If an extraction method is specified in the selection request, you can specify the selection criteria in two ways:- Describe only one EX customized access type with the required clauses.
Type Description G SQL EX EX METHODNAME G WHERE ... G ORDER ...
- Describe the four customized accesses comprehensively, and specify the clauses for each one.
Type Description G SQL D1 DC METHODNAME description... G SQL O1 P METHODNAME description... G SQL F1 RN METHODNAME description... G SQL C1 CL METHODNAME description...
Notes: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 Database Block type.
The option UPPER=NO inhibits the lowercase uppercase conversion of the values that are entered between two literal delimiters. The delimiter is defined in the Library. The option is entered on an O type line of the -GO Lines of the instance that calls the Segment.
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.
- ACCESS, followed by the SQL access type.
Feedback