Motivation

I have developed cbl2pg because at that moment I needed to access a PostgreSQL database from a COBOL program and the only 2 options I had where to go with a commercial and expensive solution accessing the database via ODBC or the ecpg precopiler.

The problem with the comercial version was the price and that you cannot execute arbitrary SQL commands. The problem with the ecpg was that it generated a COBOL code that was not compatible with the compilers that I was using and that I cannot define dynamic SQL.

All this reasons make me develop this solution.

How it works

cbl2pg works as a gateway between the C PostgreSQL library and your COBOL program. It adapts the data from COBOL to C and from C to COBOL.

The most dificult work is to adapt the data row from the C structure to the COBOL record, converting each SQL field to a field in a COBOL record.

Table 1 shows this conversions :

By now only this types are suported but if needed more can be added.

The COBOL program can generate a SQL statement in a PICTURE X field and then pass this field to a route tahat executes the query and returns a pointer that is used to access the data.

The data is returned as a COBOL record with the fields formates as shown in Table 1. Usually you know the format anb define a COBOL record to receive the data. If you don't know the format you can access the returned data field by field with the format.

Initializing

Befor using any function of cbl2pg you need to load the library. In AcuCOBOL you have to do:

CALL 'cbl2pg.dll'.

or:

CALL '\path\to\cbl2pg.dll'.

In TinyCOBOL:

CALL 'libcbl2pg.so'

Nou you can access all the cbl2pg routines.

Connect to the database

To connect to the database you have to call the sql_connect routine. This routine has 2 parameters, the connection handler and the connection strng. The connection handler is a pointer to the database connection, in COBOL it can be a USAGE POINTER field or a PIC X field with a size bigger than the size of a pointer. (A PIC X(10) should always work).

The connection string contains the variables to connect to the database. These variables are the same used to connect to the database from any other program.

This variables are defines like this:

01 CONNECT-SQL                   PIC X(200).
01 DBHANCLER-SQL                 USAGE POINTER.

You call the sql_connect like this

MOVE "user=john password=secret host=somehost dbname=foo "
            TO CONNECT-SQL.
CALL 'sql_connect' USING DBHANDLER-SQL CONNECT-SQL

If the returned dhhandler-sql is zero, the connection is not possible. Is is not zero the connection has been established and you can work with the database.

It is possible to connect from a program to more than one database or connect various times to the same database (for working inside and outside a transaction). To do this, you only need to work with diferent dbhandler-sql variables.

Execute a query

If you have a handler to a database you can make queries. To make a query to the database you need to put the query on a PIC X field (terminated by a null values x'00') and then call sql_query.

The parameters of sql_query are the dbhandler, the query handler, that is another USAGE POINTER variable thet is used as a pointer to the query resultset, a SQLCA structure to hold the response metadata and the query string terminated by a null value. The query string can be arbitrary but needs to be terminarted by a null value (the C string terminator). This can be done by using the STRING command to string a X'00' at the end of the string or you can define a structure like this:

01 QUERY-SQL.
   02 SQL-SQL         PIC X(1000).
   02 FILLER          PIC X VALUE X'00'

The SQLCA is a structe that holds the number of recors readed, amb the possible error status and error mesages. The SQLCA structure is explained below.

A call to sql_query i like this:

MOVE SPACES TO SQL-SQL.

STRING 'SELECT * FROM FOO' X'00'
             DELIMITED BY SIZE INTO SQL-SQL.

CALL 'sql_query' USING DBHANDLER-SQL,QHANDLER-SQL,
                       SQLCA,SQL-SQL

No you can use the QHANDER to retrieve the values from the resultset (if there is any).

Retrieve the result

You can know if the query has returned any values by examining SQLCODE in SQLCA. If the value of SQLCODE if zero, then there is a result of the query and this result can be obtained using one of the functions in Table 2

CALL 'sql_get_line' USING QHANDLER-SQL,LIN-SQL,RECORD-DATA.

CALL 'sql_get_item' USING QHANDLER-SQL,LIN-SQL,COL-SQL,FIELD-DATA.

CALL 'sql_get_item_by_name' USING QHANDLER-SQL,LIN-SQL,COLNAME-SQL,FIELD-DATA.

CALL 'sql_get_info' USING QHANDLER-SQL,LIN-SQL,COL-SQL,INFO-DATA.

Update and insert shortcuts

To make easier the operations of insert and update to the database from a COBOL record we created to simple functions sql_make_update and sql_make_insert

This fucntions get the database table structure from a table in memory, create the SQL INSERT or UPDATE statement with the data from a COBOL record and sent it to the database.

The memory table that defines the format of the database is used to make is possible to the routine to interpret the data in the record and to get the field names.

This table can be created from the metadata in the database with the sql_create_field_table function or can be written by hand. The format of this table is shown in Table Format

Table 1 shows the functions involved in this proces.

CALL 'sql_make_update' USING DBHANDLER-SQL,SQLCA,DATA-RECORD,TABLE-FORMAT .

CALL 'sql_make_insert' USING DBHANDLER-SQL,SQLCA,DATA-RECORD,TABLE-FORMAT .

CREATE TABLE product {
   id serial,
   created date,
   description char(128),
   price numeric(9,2),
}


01 llista-expedi.
   02 filler pic x(27) value 'product                    ' .
   02 filler pic x(27) value 'id                  01000kN'.
   02 filler pic x(27) value 'created             00800 D'.
   02 filler pic x(27) value 'description         12800 C'.
   02 filler pic x(27) value 'price               01002 N'.
   02 filler pic x(27) value x'00'.

CALL 'sql_create_field_table' USING DBHANDLER-SQL,TABLE-FORMAT .

Miscelaneous fucntions

The SQLCA

TODO

The sqlca structure

*
*  SQLCA
*
  01 sqlca.
     05 sqlcaid           pic x(8).
     05 sqlcabc           pic s9(9) comp-5.
     05 sqlcode           pic s9(9) comp-5.
     05 sqlerrm.
        10 sqlerrml       pic s9(4) comp-5.
        10 sqlerrmc       pic x(70).
     05 sqlerrp           pic x(8).
*
* 1. empty
* 2. empty
* 3. Num. rows processed (update, delete, inset)
* 4. Number of rows returned (select , fetch)
*
*
*
     05 sqlerrd           occurs 6
                          pic s9(9) comp-5.
     05 sqlwarn.
        10 sqlwarn0       pic x.
        10 sqlwarn1       pic x.
        10 sqlwarn2       pic x.
        10 sqlwarn3       pic x.
        10 sqlwarn4       pic x.
        10 sqlwarn5       pic x.
        10 sqlwarn6       pic x.
        10 sqlwarn7       pic x.
     05 sqlext.
        10 sqlwarn8       pic x.
        10 sqlwarn9       pic x.
        10 sqlwarnA       pic x.
        10 sqlstate       pic x(5).