The shared cobdbi library is wrapper around the libdbi for opencobol. Thus giving opencobol the capability to interact with, Mysql, Postgres, sqllite/sqllit3, Firebird and the other database that libdbi can work with. Libdbi is a robust library that already comes with a lot of Linux distribution. It works by incorporating drivers, that are written for each type of database. So besides having libdbi on your system you need to have the database driver for the particular database type you want to work with.

Here is a link to libdbi, please refer to that for downloading the drivers you need. Also libdbi is well documented . For now cobdbi is very much in alpha because it has only been tested with Mysql and not all libdbi calls has been wrapped and put into the cobdbi copybooks.

download

Installation Instructions

This library has only been tested with mysql but the cobdbi should work for any libdbi drivers installed on your system. All of examples in these articles use mysql. At this time the utility program taccess only works with Mysql but of cobdbi is only an alpha release and more databases will be added soon.

compiling for cobdbi

From the compile script for the utility program taccess. Notice the -I to the copybook directory along with -ldbi the libdbi library and -lcobdbi the cobol wrapper for the libdbi.

using the cobdbi copybooks

You will probably only use the cobdbi.cbl, cobdbiws.cbl and sometimes cobdbilink. cobdbiws.cbl holds the working storage layouts for the database calls, while cobdbi.cbl holds most of the database calls. If your passing database information to a subprogram you can use the copybook cobdbilink.cblin the linkage section. All of the copybooks are used fixed source cobol format but you can still use them in free source formated cobol programs by setting the source back to free following the copy statement.


Above is an example of showing how to use cobdbi.cbl inside a free formated cobol source. You need to define the 01 dbParms. level then copy in its lower level items.

These are the main fields you need to be familiar with when working with cobdbi.

*>01  dbParms.
    	     05  dbConn 	usage pointer sync.
    	     05  dbResult	usage pointer sync.
    	     05  dbNumRows	binary-double.
    	     05  dbNumFields    pic 9(4) binary.
             05  dbOpenParms.
             	  10  dbType	pic x(12) value spaces.
	     	  10  dbHost	pic x(18) value spaces.
       	     	  10  dbUser	pic x(18) value spaces.
    	     	  10  dbPass	pic x(18) value spaces.
    	          10  dbName	pic x(66) value spaces.
    	          10  dbEncode	pic x(10) value spaces.
    	    05  dbCode		pic s9(4) binary value zero.
    	    05  dbQuery	pic x(500) value spaces.

Examples

First example using the cobdbi wrapper in a very simple program that reads and displays one record at a time from a mysql table. To keep it simple all data will be display and interaction will be done using simple display and accepts.

Initialize dbParms and Opening a Connection to Database

First you need to move the database type, hostname, user, encoding and user password into the db parms. Then you call the cobdbi function to open the database. You can always check dbCode(also defined in cobdbiws.cbl) to check if any errors occurred.

0010-initialize.
    move "mysql"		to dbType.
    move "localhost"            to  dbHost.
    move "ocuser"               to  dbUser.
    move "pfdb"                 to  dbName.
    move "UTF-8"                to  dbEncode.
    move "password "            to  dbPass.
 
    perform 1001-dbOpenB.
    if dbCode  0
       display "db connection error " dbCode
       perform 0090-endProgram.

Execute SQL Queries.

Earlier you were shown the copylibws copybook with the working storage for the dbParms. One of the fields in that copybook is dbQuery. Any type of sql command can be placed into dbQuery and then executed(select, include, update, deletes, etc...). So the steps are place the query string into dbQuery, perform 1010-dbExec and then if you working retrieve any table rows you perform 1015-dbGetRows.

01  selectTable		pic x(24) value "select * from chapters;" & X"00".

The selection shown above is moved into dbQuery. You can also move a string literal or for complicated queries you might use the string command. Notice that we appended X"00" to the end of the value statement. This character is used by C programs as an indication of end of the field. low-values could be used to accomplish the same thing.

When the code shown above executes and no errors occur the working storage field dbNumRows gets updated. Then we can use dbNumRows to control the next set of code to execute.

if dbNumRows > 0
       perform 0110-showChapters
	       varying rows from 1 by 1
               until rows > dbNumRows.

Bellow we are showing you the actual Mysql description of the selected chapters table, along with its corresponding working storage layout.

You call the cobdbi sub routine dbNextRow to move the results 1 row at a time into the buffer. Notice the second and third parameters are the working storage buffer and description of the buffer. Be sure to free the results when finished by performing 1020-dbfreeResults from the cobdbi copybook.

*>   
 0110-showChapters.
    call "dbNextRow"		using by value dbResult,
                                      by reference t004-chapters,
                                      by reference t004-chaptersDesc,
				returning dbResult.
    perform 0120-move-to-line.
    display disp-prompt with no advancing.
    accept action.
    if action = "q" or action = "Q"
       compute rows		= dbNumRows + 1.

In the next article we will add other tables along with inserts, updates and deletes. Since this is about as complicated as I want to code while using displays and accepts I will use either the OC screen section, cobcurses or tk gui. To view the whole sample click Sample