OpenCobolIDE/OpenCobolIDE — pull request #91 by ColinDuquesnoy (2014-12-23 15:54:06+00:00)
Title: Feature: SQL Cobol support through dbpre

This PR implements feature request #82.

Now you can edit, compile and run sql cobol files (.scb)!

### How to upgrade OpenCobolIDE to test this feature?

1) upgrade pyqode.cobol from its ``sql`` branch:

```pip install git+https://github.com/pyQode/pyqode.cobol.git@sql --upgrade```

2) install OpenCobolIDE from the ``sql`` branch:

```pip install git+https://github.com/OpenCobolIDE/OpenCobolIDE@sql --upgrade```

Note that if you installed OpenCobolIDE in development mode, as suggested in the [wiki](https://github.com/OpenCobolIDE/OpenCobolIDE/wiki/Setting-up-a-development-environment), you just have to checkout and pull the ``sql`` branch).

### Installing dbpre

Follow the instruction in the [readme](http://sourceforge.net/p/dbpre/code/HEAD/tree/README.TXT). You don't need to install it system wide, just compile everything in place and use that directory to configure OpenCobolIDE as explained in the below section.

### Configuration:

1) You must setup OpenCobolIDE to work with dbpre: Edit->Preferences->SQL Cobol:

![sql-cobol-preferences](https://cloud.githubusercontent.com/assets/1681217/5539538/2a0ea426-8ac3-11e4-8806-7ae4ff8a6c7d.png)

2) You must configure the compiler to link with libmysqlclient (-l and -L option):

![compiler-config](https://cloud.githubusercontent.com/assets/1681217/5539540/317764aa-8ac3-11e4-8b08-942c9d712a05.png)

3) Now you can compile and run a .scb file

![example-running](https://cloud.githubusercontent.com/assets/1681217/5539542/383ee650-8ac3-11e4-8f0b-f91ebdc742f8.png)

you can see the compilers (dbpre and cobc) output in the application log (?->About OpenCobolIDE->Log)

![log_output](https://cloud.githubusercontent.com/assets/1681217/5539551/4cbb6b6c-8ac3-11e4-90cf-e221f556431d.png)

### Example

**this example come included with dbpre**

```cobol
----+-*--1----+----2----+----3----+----4----+----5----+----6----+----7-*--+----8
**************************************************************************
* I D E N T I F I C A T I O N D I V I S I O N *
**************************************************************************
IDENTIFICATION DIVISION.
PROGRAM-ID. PCTB003B.
AUTHOR. THE_PIPER.
DATE-WRITTEN. TODAY.
/
**************************************************************************
* D A T A D I V I S I O N *
**************************************************************************
DATA DIVISION.
/
**************************************************************************
* W O R K I N G S T O R A G E S E C T I O N *
**************************************************************************
WORKING-STORAGE SECTION.
*
* The needed working storage stuff for the framework
COPY PGCTBBATWS.
*
* This will be displayed in the logfile at runtime
01 PGCTB-VERSION PIC X(38)
VALUE '20120426 1.0 INITIAL RELEASE'.
*
01 FIELD1.
05 FIELD1-NUM PIC 9(08).
05 FILLER PIC X(12) VALUE SPACE.
01 FIELD2 PIC X(16).
01 FIELD3 PIC X(32).
*
01 T PIC S9(9) COMP.
*
* The communication area for the database
EXEC SQL
INCLUDE SQLCA.
END-EXEC.
/
**************************************************************************
* P R O C E D U R E D I V I S I O N *
**************************************************************************
PROCEDURE DIVISION.
* The framework itself, calling PGCTB-ACTION to run the users coding
EXEC SQL
INCLUDE PGCTBBAT REPLACING 'TTTTNNNB' BY 'PCTB003B'.
END-EXEC.

/
**************************************************************************
* P O C T B - A C T I O N S E C T I O N *
**************************************************************************
PGCTB-ACTION SECTION.
*
DISPLAY 'In PGCTB-ACTION.'
*
* EXEC SQL
* ROLLBACK
* please dont parse this...
* END-EXEC
*
*
PERFORM DISPLAY-ALL-RECORDS
*
DISPLAY 'Delete entire table'
*
EXEC SQL
DELETE
FROM example_table
END-EXEC.
EVALUATE TRUE
WHEN DB-OK
CONTINUE
WHEN OTHER
PERFORM DB-STATUS
END-EVALUATE
*
DISPLAY 'Insert new records'
*
PERFORM VARYING T FROM 1 BY 1 UNTIL T > 10
MOVE T TO FIELD1-NUM
EXEC SQL
INSERT
INTO example_table
(
FIELD1,
FIELD2,
FIELD3
)
VALUES
(
:FIELD1 ,
'Value2' ,
'Value3'

)
END-EXEC.
EVALUATE TRUE
WHEN DB-OK
CONTINUE
WHEN OTHER
PERFORM DB-STATUS
END-EVALUATE
END-PERFORM
*
PERFORM DISPLAY-ALL-RECORDS
*
DISPLAY 'Update the first record'
*

EXEC SQL
UPDATE example_table
SET FIELD1 = 'UpdatedValue1'
WHERE FIELD1 = '00000001'
END-EXEC.
*
DISPLAY 'Update the third record'
*

EXEC SQL
UPDATE example_table
SET FIELD1 = 'UpdatedValue3'
WHERE FIELD1 = '00000003'
END-EXEC.
* DISPLAY 'SQLCA-STATEMENT=' SQLCA-STATEMENT
* DISPLAY 'SQLCODE=' SQLCODE
* DISPLAY 'SQLCA-COUNT=' SQLCA-COUNT
EVALUATE TRUE
WHEN DB-OK
CONTINUE
WHEN OTHER
PERFORM DB-STATUS
END-EVALUATE
*
PERFORM DISPLAY-ALL-RECORDS
*
DISPLAY 'Ende PGCTB-ACTION.'
*
.
PGCTB-ACTION-EXIT.
EXIT.
/
**************************************************************************
DISPLAY-ALL-RECORDS SECTION.
*
DISPLAY '-------------------------------------------'
* Attention !! Table name is CaSe sensitive!!!!!!!!!!!!!
EXEC SQL
DECLARE ALLROWS CURSOR FOR
SELECT FIELD1, FIELD2, FIELD3
INTO :FIELD1 :FIELD2 :FIELD3
FROM example_table
END-EXEC.
EVALUATE TRUE
WHEN DB-OK
CONTINUE
WHEN OTHER
PERFORM DB-STATUS
END-EVALUATE
*
EXEC SQL
OPEN ALLROWS
END-EXEC.
EVALUATE TRUE
WHEN DB-OK
CONTINUE
WHEN DB-NOT-FOUND
CONTINUE
WHEN OTHER
PERFORM DB-STATUS
END-EVALUATE

DISPLAY 'FIELD1=' FIELD1 ' FIELD2=' FIELD2
' FIELD3=' FIELD3

PERFORM UNTIL NOT DB-OK
EXEC SQL
FETCH ALLROWS
INTO :FIELD1 :FIELD2 :FIELD3
END-EXEC.
EVALUATE TRUE
WHEN DB-OK
DISPLAY 'FIELD1=' FIELD1 ' FIELD2=' FIELD2
' FIELD3=' FIELD3
WHEN DB-NOT-FOUND
MOVE SPACE TO FIELD1
MOVE SPACE TO FIELD2
MOVE SPACE TO FIELD3
WHEN OTHER
PERFORM DB-STATUS
END-EVALUATE
END-PERFORM
SET DB-OK TO TRUE
DISPLAY '-------------------------------------------'
*
EXEC SQL
CLOSE ALLROWS
END-EXEC.
EVALUATE TRUE
WHEN DB-OK
CONTINUE
WHEN OTHER
PERFORM DB-STATUS
END-EVALUATE
.
DISPLAY-ALL-RECORDS-EXIT.
EXIT.
```

To use that program, you need to create a database called ``testdb`` and a table called ``example_table``:

``` sql
CREATE TABLE example_table (
field1 char(20),
field2 char(16),
field3 char(32));
```