4.2.2 Execute an SQL statement by using an object of the FJDB-COMMAND class

DB Access Class Library for COBOL V1.0 API Reference - Microsoft(R) Windows(R) - - Microsoft(R) Windows NT(R) - - Microsoft(R) Windows(R) 2000 -

Contents Index

Chapter 4 Examples of Using the DB Access Class Library 4.2 Use an Object of the FJDB-COMMAND Class

4.2.2 Execute an SQL statement by using an object of the FJDB-COMMAND class

Update data by using an object of the FJDB-COMMAND class and a bind table.

An object of the FJDB-COMMAND class is created by using the CREATE-COMMAND method in the FJDB-DATABASE class.

This section introduces an example of using an object of the FJDB-COMMAND class representative of an SQL statement, as explained in Data Update Example, to add data to the database as used in Reference data using a recordset.

Define the class.
Define the class to use in the repository paragraph of the CONFIGURATION SECTION.
```
 CONFIGURATION SECTION
 REPOSITORY.
           :
   CLASS FJDB-COMMAND.
           :
```

Define variables.

Define the variables to use in the program.

 WORKING-STORAGE SECTION.
*Define object variables
           :
 01 COMMAND-A USAGE OBJECT REFERENCE FJDB-COMMAND.
           :
*Variables of the bind table for handling dynamic parameters
 01 BIND-NO      PIC S9(9) COMP-5.
 01 BIND-NAME    PIC X(50).
 01 BIND-ADDRESS PIC X(100).
*Define data setup variables
 01 XSQL         PIC X(255).
*Define the data return variable
 01 ROW-COUNT    PIC S9(9) COMP-5.
*Define the return value variable
 01 RET-CODE     PIC S9(9) COMP-5.
           :

Create an object of the FJDB-COMMAND class.
Create an object of the FJDB-COMMAND class to represent the SQL statement that is executed for the database.Use the CREATE-COMMAND method in the FJDB-DATABASE class.
Create "COMMAND-A " to designate the SQL statement explained in Data Update Example.
Each dynamic parameter appearing in the SQL statement specified with the CREATE-COMMAND method is designated by a question mark (?).

(1) Assign values to the variable (XSQL) of the arguments specified when a CREATE-COMMAND method is issued.
```
     MOVE "INSERT INTO EMPLOYEE(NO,NAME,ADDRESS) VALUES(?,?,?)" 
                                                 TO XSQL.
```
(2) Create an object of the FJDB-COMMAND class.
```
     INVOKE DATABASE-A "CREATE-COMMAND" USING XSQL COMMAND-A
                                          RETURNING RET-CODE.
```
Associate the bind table and the object of the FJDB-COMMAND class.
Using the BIND-PARAMETERS method in the FJDB-COMMAND class, associate the bind table and the object of the FJDB-COMMAND class.
```
     INVOKE COMMAND-A "BIND-PARAMETERS" USING BIND-A
                                          RETURNING RET-CODE.
```
Load the variables in the bind table with inserted data.
Load the variables registered in the bind table with the parameter values that are specified at SQL statement execution.
```
      MOVE 3000   TO BIND-NO.
      MOVE "Kato" TO BIND-NAME.
      MOVE "Kyoto" TO BIND-ADDRESS.
```
Execute the SQL statement
Execute the SQL statement by using the EXECUTE-COMMAND method in the FJDB-COMMAND class.
When an EXECUTE-COMMAND method is issued, the dynamic parameters are loaded with the values of the variables registered in the bind table.
```
     INVOKE COMMAND-A "EXECUTE-COMMAND" USING ROW-COUNT 
                                          RETURNING RET-CODE.
```

4.2.2.1 Data Update Example (Using an object of the FJDB-COMMAND class to execute an SQL statement)

Contents Index