Embedded SQL for GnuCOBOL using ocesql

After 6 months of messing around with gnuCOBOL, I am finally to the point of trying to implement Embedded SQL.

What is Embedded SQL?

In all of the programming I’ve done / seen, SQL is accessed via some type of procedure calls. A query is setup, executed, then the results are extracted. There is a fair amount of extra code besides the actual SQL statement.

For example, in one of my Pascal programs, I setup an SQL command I want to execute:

query.SQL.Text := 'select * from devices where ownerId  = :ownerId and deviceId = :deviceId';

The variables in the where clause of the query, :ownerId and :deviceId, are then assigned values from program variables:

qpbn(query, deviceR.ownerId,  'ownerId');
qpbn(query, deviceR.deviceId, 'deviceId');

Then the query is executed on the server:

query.Open;

and finally I extract from the query result the values my program needs:

qfbn(query, macAddr,        'macAddr');

It works, but wouldn’t it by much nicer if I could just embed the query straight into the Pascal program something like

SQL('select :macdaddr from devices where ownderId = :ownerId and deviceId = :deviceId');

That’s exactly how embedded SQL in COBOL works. You just delimit the SQL command with EXEC SQL … END-EXEC. For example, the above in COBOL would be:

EXEC SQL
    SELECT :MACADDR FROM DEVICES WHERE OWNERID = :OWNERID AND DEVICEID = :DEVICEID
END-EXEC.

I won’t spend time explaining the operation of embedded SQL as mainframe examples can be easily found elsewhere. Instead, I’m going to cover how to implement it for gnuCOBOL.

Caveats

My initial plan was to do this running gnuCOBOL in Windows. There was a lot of wasted time and head banging trying to make whatever test I was trying to do in gnuCOBOL work in Windows. Eventually I would get the problem resolved.

This was not the case for embedded SQL. I am using OCESQL and I could not get it to compile properly in minGW. After wasting several hours on that, I gave up and implemented it in Linux. Honestly, I can’t imagine any scenario I would prefer using COBOL in Windows more than Linux.

I will give a quick summary regarding the Linux installation below.

Early on I installed PostgreSQL onto a Windows system. That is working fine and for this test, I will be using PostgreSQL for Windows.

Installing gnuCOBOL on Linux

I created a Virtualbox VM and installed Linux Mint 19.3 to do this testing.

I wanted to use the COBOLWorx distribution of gnuCOBOL so I can use their debugger if necessary.

The compiler and debugger can be found at:

Packages for the Debian Package Manager (DPKG)

Download both COBOLworx GnuCOBOL 3.1 and COBOLworx GnuCOBOL CBL-GDB Debugging Extensions.

Installing is very easy, just use apt install for each file:

sudo apt install <gnuCOBOL filename>
sudo apt install <debugger filename>

To verify the install, do the following:

cobc -v
cobc (GnuCOBOL) 3.1-dev.0
Built     Oct 09 2020 13:47:45  Packaged  Oct 26 2020 15:31:29 UTC
C version "7.5.0"
loading standard configuration file 'default.conf'
cobc: error: no input files

cobcd
This is the cobc debugging wrapper [Version 4.2]
Use it as you would 'cobc'

no-pie-link.specs Error

This probably won’t be an issue for you, but it was for me so I will document it. After installing gnuCOBOL I did a test compile and got the error

gcc: error: /usr/share/dpkg/no-pie-link.specs

For some reason my PC was missing the file specified above. I found it on another system and created it in the requested directory. The contents of the file are:

+ %{!shared:%{!r:%{!fPIE:%{!pie:-fno-PIE -no-pie}}}}

Install PostgreSQL Client

If you aren’t using PostgreSQL server on the same system as the compiler, you are going to want to install the client:

sudo apt install postgresql-client

Now verify you can get access to the PostgreSQL server using psql:

psql -h <yourhost> -U postgres
Password for user postgres: 
psql (10.14 (Ubuntu 10.14-0ubuntu0.18.04.1), server 12.3)
WARNING: psql major version 10, server major version 12.
Some psql features might not work.
Type "help" for help.

postgres=#

Fix Windows 10 Firewall

When I attempted to access PostgreSQL on my Windows 10 system, the firewall blocked access. I had to go into the firewall, add the PostgreSQL server, and allow access to it:

Create the testdb Database

The sample ocesql programs will make use of a database called testdb. Use psql to set that up now.

To list the existing databases use the psql command \l (that’s a lowercase L):

and then create the database:

Using the \l command again you will see the database:

Use \q to exit psql.

Installing Ocesql

To install ocesql, you will need C++. You can use g++ to verify it is there:

g++ -v
Using built-in specs.
COLLECT_GCC=g++
COLLECT_LTO_WRAPPER=/usr/lib/gcc/x86_64-linux-gnu/7/lto-wrapper
OFFLOAD_TARGET_NAMES=nvptx-none
OFFLOAD_TARGET_DEFAULT=1
Target: x86_64-linux-gnu
Configured with: ../src/configure -v --with-pkgversion='Ubuntu 7.5.0-3ubuntu1~18.04' --with-bugurl=file:///usr/share/doc/gcc-7/README.Bugs --enable-languages=c,ada,c++,go,brig,d,fortran,objc,obj-c++ --prefix=/usr --with-gcc-major-version-only --program-suffix=-7 --program-prefix=x86_64-linux-gnu- --enable-shared --enable-linker-build-id --libexecdir=/usr/lib --without-included-gettext --enable-threads=posix --libdir=/usr/lib --enable-nls --enable-bootstrap --enable-clocale=gnu --enable-libstdcxx-debug --enable-libstdcxx-time=yes --with-default-libstdcxx-abi=new --enable-gnu-unique-object --disable-vtable-verify --enable-libmpx --enable-plugin --enable-default-pie --with-system-zlib --with-target-system-zlib --enable-objc-gc=auto --enable-multiarch --disable-werror --with-arch-32=i686 --with-abi=m64 --with-multilib-list=m32,m64,mx32 --enable-multilib --with-tune=generic --enable-offload-targets=nvptx-none --without-cuda-driver --enable-checking=release --build=x86_64-linux-gnu --host=x86_64-linux-gnu --target=x86_64-linux-gnu
Thread model: posix
gcc version 7.5.0 (Ubuntu 7.5.0-3ubuntu1~18.04) 

If it is not, install it with:

sudo apt install build-essential g++

You are also going to need some postgresql libs:

sudo apt install libpq5 libpq-dev

Source for ocesql can be found at:

https://github.com/opensourcecobol/Open-COBOL-ESQL/releases/tag/v1.2.

Unzip the file into your home directory (~). You should now have the directory ~/Open-COBOL-ESQL-1.2.

To compile ocesql:

cd ~/Open-COBOL-ESQL-1.2
export CPATH=/usr/include/postgresql/
./configure
make

you will now have the file ./ocesql/ocesql. To install into /usr,

sudo make install

and test:

ocesql
Open Cobol ESQL (Ocesql)
Version 1.2.0

April 19, 2019

Tokyo System House Co., Ltd. <opencobol@tsh-world.co.jp>

Usage: ocesql [options] SOURCE [DESTFILE] [LOGFILE]

options
      --inc=include_dir      set INCLUDE FILE directory path.

usage
  -v, --version              show version.
  -h, --help                 show this usage.

Compiling and Running the Sample Programs

ocesql comes with two sample programs, INSERTTBL and FETCHTBL. I wanted to make sure I could get these to work before I tried my own code.

Fixing the Code

The program INSERTTBL is set to populate the database with data containing Japanese characters. Fortunately, the writer includes both versions of text, you just need to uncomment the English code and delete the Japanese code.

becomes

Go thru the rest of this program and you will find various literals that are in both Japanese and English. Convert them to use the English version.

The last thing, in both programs is to fix the database credentials to the appropriate values:

Preparing to Compile INSERTTBL

You need to make the ocesql copy book available to the COBOL compiler:

export COBCPY=/home/<yourName>/Open-COBOL-ESQL-1.2/copy

The following fixes the runtime error

libcob: error: module 'OCESQLConnect' not found'

(see https://stackoverflow.com/questions/26227458/gnucobol-failing-to-find-dynamic-symbols-only-on-recent-ubuntu):

export COB_LDFLAGS=-Wl,--no-as-needed

Precompiling  INSERTTBL

The first step is to run the precompiler ocesql against the source code. This will convert the EXEC SQL … END-EXEC code into actual COBOL code:

ocesql INSERTTBL.cbl INSERTTBL.cob
precompile start: INSERTTBL.cbl
=======================================================
LIST OF CALLED DB Library API
=======================================================
Generate:OCESQLConnect
Generate:OCESQLExec
Generate:OCESQLExec
Generate:OCESQLExec
Generate:OCESQLExec
Generate:OCESQLExecParams
Generate:COMMIT
Generate:OCESQLDisconnect
Generate:ROLLBACK
=======================================================

Here is an example of ocesql converting an INSERT statement into COBOL code:

     *    INSERT ROWS USING LITERAL
           EXEC SQL
               INSERT INTO EMP VALUES (46, 'KAGOSHIMA ROKURO', -320)
           END-EXEC.
           IF  SQLCODE NOT = ZERO PERFORM ERROR-RTN.

becomes

      *    INSERT ROWS USING LITERAL
OCESQL*    EXEC SQL
OCESQL*        INSERT INTO EMP VALUES (46, 'KAGOSHIMA ROKURO', -320)
OCESQL*    END-EXEC.
OCESQL     CALL "OCESQLExec" USING
OCESQL          BY REFERENCE SQLCA
OCESQL          BY REFERENCE SQ0003
OCESQL     END-CALL.
           IF  SQLCODE NOT = ZERO PERFORM ERROR-RTN.

Compiling and Running INSERTTBL

cobc -x -locesql INSERTTBL.cob
./INSERTTBL
*** INSERTTBL STARTED ***
NOTICE: table "emp" does not exist, skipping
*** INSERTTBL FINISHED ***

Reviewing the Results

Using psql, review the new EMP table:

psql -h chiefdude10 -U postgres 
Password for user postgres: 
psql (10.14 (Ubuntu 10.14-0ubuntu0.18.04.1), server 12.3)
WARNING: psql major version 10, server major version 12.
         Some psql features might not work.
Type "help" for help.

postgres=# \c testdb
psql (10.14 (Ubuntu 10.14-0ubuntu0.18.04.1), server 12.3)
WARNING: psql major version 10, server major version 12.
         Some psql features might not work.
You are now connected to database "testdb" as user "postgres".
testdb=# select * from emp;
 emp_no |       emp_name       | emp_salary 
--------+----------------------+------------
     46 | KAGOSHIMA ROKURO     |       -320
     47 | OKINAWA SHICHIRO     |        480
      1 | HOKKAI TARO          |        400
      2 | AOMORI JIRO          |        350
      3 | AKITA SABURO         |        300
      4 | IWATE SHIRO          |       -250
      5 | MIYAGI GORO          |       -200
      6 | FUKUSHIMA RIKURO     |        150
      7 | TOCHIGI SHICHIRO     |       -100
      8 | IBARAKI HACHIRO      |         50
      9 | GUMMA KURO           |       -200
     10 | SAITAMA JURO         |        350
(12 rows)

FETCHTBL

If you have been successful at running INSERTTBL, FETCHTBL is more of the same:

ocesql FETCHTBL.cbl FETCHTBL.cob
precompile start: FETCHTBL.cbl
=======================================================
              LIST OF CALLED DB Library API            
=======================================================
Generate:OCESQLConnect
Generate:OCESQLExecSelectIntoOne
Generate:OCESQLCursorDeclare
Generate:OCESQLCursorOpen
Generate:OCESQLCursorFetchOne
Generate:OCESQLCursorFetchOne
Generate:OCESQLCursorClose
Generate:COMMIT
Generate:OCESQLDisconnect
Generate:ROLLBACK
=======================================================

cobc -x -locesql FETCHTBL.cob

./FETCHTBL
*** FETCHTBL STARTED ***
TOTAL RECORD: 0012
---- -------------------- ------
NO   NAME                 SALARY
---- -------------------- ------
0001 HOKKAI TARO             400
0002 AOMORI JIRO             350
0003 AKITA SABURO            300
0004 IWATE SHIRO            -250
0005 MIYAGI GORO            -200
0006 FUKUSHIMA RIKURO        150
0007 TOCHIGI SHICHIRO       -100
0008 IBARAKI HACHIRO          50
0009 GUMMA KURO             -200
0010 SAITAMA JURO            350
0046 KAGOSHIMA ROKURO       -320
0047 OKINAWA SHICHIRO        480
*** FETCHTBL FINISHED ***

Now that I am able to use ocesql on the sample programs, next I will use it to access the dvdRental database that I installed in Installing PostgreSQL (for GnuCOBOL).

This entry was posted in c-gnuCOBOL and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.