Convert a Generated Table Procedure to Use SQL

SQL Quick Bridge (Quick Bridge) generates table procedures using network DML syntax. Network DML provides the most flexibility and functionality for accessing a network database, and can more closely mimic application program logic. 
idms19
SQL Quick Bridge (Quick Bridge) generates table procedures using network DML syntax. Network DML provides the most flexibility and functionality for accessing a network database, and can more closely mimic application program logic. 
System virtual key support makes it possible to use SQL in place of network DML and create a simpler table procedure. Work is shifted from the table procedure to SQL. Entire sections of generated code can be replaced with a single SQL statement.  
SQL may not work in all cases, but it is still recommended to generate the initial program with Quick Bridge. Quick Bridge generates many field and parameter definitions for you. The following sample programs are programs generated by Quick Bridge, and the same programs are converted to use SQL:
  • AGJCOVEN - 2 level, network. Generated by QB
  • AGJCOVEQ - 2 level, converted to SQL
  • AGJHOSPN - 3 level, network. Generated by QB
  • AGJHOSPQ - 3 level, converted to SQL
These sample programs can be found in the CAGJSAMP library with
PTF RO97698
 applied.
The Conversion Process
The following topics describe the process for converting a generated table procedure to use SQL:
Code a DECLARE CURSOR in Working Storage
Use the following guidelines when coding a DECLARE CURSOR in working storage:
  • When targeting a VIA record that is owned by a CALC record, you code a SELECT that joins the owner with the member, where the FKEY field of the member matches the ROWID of the owner.
  • When targeting a VIA record, that is owned by a VIA record that is owned by a CALC record, you code a SELECT that joins the three tables, where the FKEY of a target VIA matches the ROWID of its owner, and the FKEY of the owning VIA matches the ROWID of its owner. This defines a path from the entry record to the target record. Select all columns from the target record and key columns from the entry and path records. Generally you want to return all data in the path, and let the program calling the table procedure limit what it wants with a where clause.
Rename SQLSTATE to SQLSTATE2
The generated code contains an SQLSTATE field in the Procedure division parameters and in the linkage section. You rename this field and the references to SQLSTATE2 to avoid a conflict with the SQLSTATE field that is expanded in SQLCA.
The SQLSTATE2 value is used to return a state value to its caller. After an SQL call, SQLSTATE contains the result of that call. You code a MOVE SQLSTATE TO SQLSTATE2 after each SQL call to pass the value to the caller of the procedure.
Declare LINKAGE SECTION Variables to SQL
Any variable in the LINKAGE SECTION or WORKING-STORAGE that is referenced in an SQL command needs to be declared to SQL. You do this by coding a BEGIN DECLARE SECTION before the first variable and coding an END DECLARE SECTION after the last variable. Multiple declare sections may be coded.
Quick Bridge generates two field names for each table procedure parameter—one set with a -D suffix and another with a -I suffix. Values are passed and returned in the -D suffix fields and the -I fields indicate if they are null or not. These fields usually should be declared. 
Saved ROWID values are usually kept in WORKING-STORAGE and should be declared.