A sample program (sample 12) provided with this product is explained below.
Sample 12 shows an example where two or more lines in a database are operated with one SQL statement as an example of advanced usage of the database (SQL) function.
A database is present in a server and is accessed from a client.
A database is accessed via the ODBC driver. For details of database access using the ODBC driver, refer to Chapter 19 in the “NetCOBOL User’s Guide”.
To use this program, the following products are necessary.
Client:
ODBC driver manager
ODBC driver
Products necessary for ODBC driver
Server:
Database
NOTE:
The function to use with this sample depends on your database. With Microsoft® Access, we could not execute this sample correctly.
Products necessary for database access using ODBC
Overview
Sample 12 uses the STOCK table of the sample database. Refer to “Sample Database” in the “NetCOBOL User’s Guide” for details of the sample database.
Sample 12 accesses and disconnects it after the following operation:
Display of all data items in the database
Fetch of the GNO value on a row with GOODS value "TELEVISION"
QOH update on a row with the fetched GNO
Deletion of lines with GOODS values "RADIO", "SHAVER", and "DRIER"
Redisplay of all data items in the database
Part of the output result is output to a file by using compiler option SSOUT.
Programs and files in Sample 12
SAMPLE12.COB (COBOL source program)
SAMPLE12.PRJ (Project file)
SAMPLE12.CBI (COBOL compiler option file)
COBOL85.CBR (COBOL run-time initialization file)
SAMPLE12.INF (ODBC information file)
COBOL functions used in sample 12
Remote database access
Project management function
COBOL statements used
The CALL, DISPLAY, IF, and PERFORM statements are used.
Embedded SQL statements (host variable with multiple rows specified, host variable with a table specified, embedded exception declaration, CONNECT statement, cursor declaration, OPEN statement, FETCH statement, SELECT statement, DELETE statement, UPDATE statement, CLOSE statement, COMMIT statement, ROLLBACK statement, and DISCONNECT statement) are used.
Prerequisite to Executing the Program
In order to execute this sample, the DBMS product which can be connected via ODBC is installed in server side and make the table named STOCK for the database connected by default.
Make the STOCK table in the format as following.
GNO | Goods | QOH | WHNO |
|---|---|---|---|
Binary integer 4 digits | Fixed-length character 10 bytes | Binary integer 9 digits | Binary integer 4 digits |
Store the data items shown following in the STOCK table.
GNO | Goods | QOH | WHNO |
|---|---|---|---|
110 111 123 124 137 138 140 141 200 201 212 215 226 227 240 243 351 380 390 | TELEVISION TELEVISION REFRIGERATOR REFRIGERATOR RADIO RADIO CASSETTE DECK CASSETTE DECK AIR CONDITIONER AIR CONDITIONER TELEVISION VIDEO REFRIGERATOR REFRIGERATOR CASSETTE DECK CASSETTE DECK CASSETTE TAPE SHAVER DRIER | 85 90 60 75 150 200 120 80 4 15 0 5 8 15 25 14 2500 870 540 | 2 2 1 1 2 2 2 2 1 1 2 2 1 1 2 2 2 3 3 |
Building and rebuilding the program
The project manager's build function is used to compile and link this program.
Folder C:\NetCOBOL is assumed below as the location where NetCOBOL is installed.
Change the folder name C:\NetCOBOL to the name of the folder where NetCOBOL is installed.
Start the Project Manager, and project file SAMPLE12.PRJ is open.
Select Build from the Project menu.
After build termination, check that SAMPLE12.EXE is created.
Setting ODBC information
The ODBC information file is necessary for the execution of this sample program. When connecting it with the database by using the ODBC driver, necessary information is included as for the ODBC information file.
The ODBC information file is made by using ODBC information Setup Tool (SQLODBCS.EXE). Refer to the “Database (SQL)” chapter in the “NetCOBOL User’s Guide” for more detailed information.
Setting Run-time Environment Information
Select “Run-time Environment Setup Tool” from the Tools menu.
The Run-time Environment Setup Tool is displayed.
Select "Open" on the File menu and create an object initialization file (COBOL85.CBR) in the folder that contains the executable program (SAMPLE12.EXE).
Select a common tab and set data as shown below:
Specify an ODBC information file name in environment variable information @ODBC_Inf (ODBC information file specification).
Specify a file to save the DISPLAY statement output result in environment variable RESULT.
Click the Apply button.
The set data is saved in the object initialization file.
Select "Exit" on the File menu to terminate the Run-time environment setup tool.
Debugging the Program
To run the program under the control of the Debugger, select Debug from the Project menu. The Debugger starts and displays the Start Debugging dialog box.
Press the ENTER key.
Executing the program
Select "Run" on the Project Manager "Project" menu.
The following is displayed in the COBOL console window.
SUCCESSFUL CONNECTION WITH DATABASE. RECEIVE THE PRODUCT NUMBER WHOSE PRODUCT NAME IS ‘TELEVISION’ SET STOCKS OF THE FOLLOWING PRODUCTS DECREASING 10 TELEVISION -> + 0110 TELEVISION -> + 0111 TELEVISION -> + 0212 DELETE THE ROW WHICH HAS PRODUCT NAME IS ‘RADIO’, ‘SHAVER’ OR ‘DRIER’. PROGRAM END
To the file assigned to environment variable RESULT, the contents of the STOCK table before and after the operation are output in the format shown below.
Contents before processing
No.01:
Product number = +0110
Product name = TELEVISION
Stock quantity = +000000085
Warehouse number = +0002
:
No.19:
Product number = +0390
Product name = DRIVER
Stock quantity = +000000540
Warehouse number = +0003
There are 19 data in total.
Contents after processing
No.01:
Product number = +0110
Product name = TELEVISION
Stock quantity = +000000075
Warehouse number = +0002
:
No.15:
Product number = +0351
Product name = CASSETTE TAPE
Stock quantity = +000002500
Warehouse number = +0002
There are 15 data items in total.