Cannot figure out how to use Gixsql and MySQL

[octanervoc]

Hello. I've looked up and down for solutions, but I really couldn't do it. Help is very much appreciated.

So, I have a database employees which has a table departments as follows:

All I want to do for now is a simple SELECT into a local COBOL variable and then display it to the screen. What am I doing wrong at the moment?

       IDENTIFICATION DIVISION.
       PROGRAM-ID. PGMAIN.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.
        01 TABLE-VARS.
           05 VDEPT-NO PIC X(4).
           05 VDEPT_NAME PIC X(40).
        01 DB-PRE-VARS.
           05 DATASRC PIC X(100) VALUE 
               "mysql://localhost:3306/employees".
           05 DBUSR pic x(4) value "root".
           05 DBPSW pic x(4) value "sqlp".
       
       EXEC SQL 
            INCLUDE SQLCA 
       END-EXEC.

       PROCEDURE DIVISION.
       100000-MAIN.

           EXEC SQL
               CONNECT TO :DATASRC USER :DBUSR USING :DBPSW
           END-EXEC

           EXEC SQL 
              SELECT DEPT_NAME INTO :VDEPT_NAME
              FROM DEPARTMENTS
              WHERE DEPT_NO="d009" 
           END-EXEC
           
           DISPLAY VDEPT_NAME

           STOP RUN.

This should return "Customer Service", but instead it shows nothing. I'm using a local machine, so the database password is simply "sqlp", the user being "root" and the MySQL URL given by DBeaver is jdbc:mysql://localhost:3306/, so I just edited it into the format explained in the readme: mysql://localhost:3306/employees.

I have installed gixsql using the Ubuntu 20.04 .deb, as I'm in Linux Mint 20.3. cobc: 3.2.0, gixsql: 1.0.20b.

Thanks to this issue, I could compile the program using:

gixpp -e -S -I/usr/share/gixsql/copy -i pgmain.cob -o pge.cbsql
gixsql pgmain.cob pge.cbsql -S -I.
cobc -x -I/usr/share/gixsql/copy pge.cbsql -L /usr/lib -lgixsql
./pg

Thank you either way.

[octanervoc]

Solved.

First, all picture clauses must be uppercase. The database was not connecting purely because I used 05 DBUSR pic x(4) value "root" instead of 05 DBUSR PIC X(4) VALUE "root". Keep this always in mind.

Second, make sure that the table and column names are lowercase. From my tests, column names aren't as sensitive to this, but table names most definitely are. employees works, but emploYees does not.

Working code:

       IDENTIFICATION DIVISION.
       PROGRAM-ID. PGMAIN.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.
        01 TABLE-VARS.
           05 VDEPT-NO PIC X(4).
           05 VDEPT_NAME PIC X(40).
           05 T1 PIC 999 VALUE 0.
        01 DB-PRE-VARS.
           05 DATASRC PIC X(100) VALUE 
               "mysql://localhost:3306/employees".
           05 DBUSR PIC X(4) value "root".
           05 DBPSW PIC X(5) value "mysql".
       
       EXEC SQL 
            INCLUDE SQLCA 
       END-EXEC.

       PROCEDURE DIVISION.
       100000-MAIN.

           EXEC SQL
               CONNECT TO :DATASRC USER :DBUSR USING :DBPSW
           END-EXEC
           
           DISPLAY "Code: " SQLCODE

           EXEC SQL 
              SELECT dept_naMe INTO :VDEPT_NAME
               FROM departments
               where dept_no='d009'
           END-EXEC.
           
           DISPLAY "Code: " SQLCODE
           DISPLAY VDEPT_NAME

           STOP RUN.

Terminal output:

Code: +0000000000
Code: +0000000000
Customer Service  

For the curious, I'm using the .deb version of gixsql, so these are the commands I used to compile the file:

clear
gixpp -e -S -I /usr/share/gixsql/copy -I ./ -i PG000002.cob -o pg-sql.cbsql
cobc -x pg-sql.cbsql -I /usr/share/gixsql/copy -I ./ -L /usr/lib -l gixsql -o exec
./exec

For reference, please see this repository.

[GitMensch]

The PICTURE part had a PR for a partial fix since 2023 and now a "complete" one with #189.
Until that is in you need uppercase letters XNSV.

@mridoni could you please try to inspect the open PRs and release 0.21?

The case-sensitivity of table names seem to depend on Windows (case-insensitive) vs. everything else.
I'd suggest to use all lower-case for mysql.