How to setup GixSQL connections

Question

I am working on a prototype for a COBOL application in a container. Now I wanted to connect it to a database and I found ODBC. But it did not work our because I got the following error during docker build:

0.141 /function/YTR93.cob: 36: error: SQLCA: No such file or directory
0.141 /function/YTR93.cob: 36: error: syntax error, unexpected Identifier or Literal, expecting .
0.151 /function/YTR93.cob: 34: error: invalid level number 'EXEC'
0.152 cobc: call to cobc_plex_strdup with NULL pointer
0.152 cobc: aborting codegen for /function/YTR92.cob (unknown: unknown)

After that, someone recommended GixSQL for preprocessing the file. However, I was quite confused about the installation process. The instructions were not very clear, and I found myself struggling to understand how to connect it with ODBC. In addition, I was not sure where I needed to set the DATASRC connection string. Would you mind helping me with installing GixSQL the right way? Thank you so much in advance.

My DockerFile:

# Define app directory
ARG FUNCTION_DIR="/function"

FROM --platform=linux/amd64 python:3.10-buster as build-image

# Vermeiden von Interaktionen bei der Installation von Paketen
ENV DEBIAN_FRONTEND=noninteractive

# Install aws-lambda build dependencies
RUN apt-get update && \
  apt-get install -y \
  g++ \
  make \
  cmake \
  unzip \
  wget \
  gnucobol \
  gnucobol-cobsql \
  unixodbc \
  unixodbc-dev \
  wget \
  gnupg \
  default-libmysqlclient-dev \
  make \
  && rm -rf /var/lib/apt/lists/* \
  && wget https://dev.mysql.com/get/Downloads/Connector-ODBC/8.0/mysql-connector-odbc_8.0.29-1ubuntu20.04_amd64.deb \
  && dpkg -i mysql-connector-odbc_8.0.29-1ubuntu20.04_amd64.deb || apt-get -f install -y \
  && apt-get clean \
  && rm -rf /var/lib/apt/lists/* /tmp/* /var/tmp/* /mysql-connector-odbc_*.deb

# Include global arg in this stage of the build
ARG FUNCTION_DIR

# Create function directory
CMD mkdir -p ${FUNCTION_DIR}

# Copy code directory
COPY app/* ${FUNCTION_DIR}/

# Install the runtime interface client
RUN pip install \
  --target ${FUNCTION_DIR} \
  awslambdaric

# Install the AWS Software Development Kit for Python
RUN pip install \
  --target ${FUNCTION_DIR} \
  boto3

# Install requests for Python
RUN pip install \
  --target ${FUNCTION_DIR} \
  requests

# Set environment variables for MySQL Database configuration
ENV MYSQL_DATABASE=bank1 \
  MYSQL_USER=admin \
  MYSQL_PASSWORD=12345    \
  MYSQL_HOST=databasestring \
  MYSQL_PORT=3306

# Setup ODBC Driver in odbcinst.ini
RUN echo "[MySQL]\n\
Description=ODBC for MySQL\n\
Driver=/usr/lib/x86_64-linux-gnu/odbc/libmyodbc8a.so\n\
Setup=/usr/lib/x86_64-linux-gnu/odbc/libmyodbc8S.so\n\
FileUsage=1\n\
Server=$MYSQL_HOST\n\
User=$MYSQL_USER\n\
Password=$MYSQL_PASSWORD\n\
Database=$MYSQL_DATABASE\n\
Port=$MYSQL_PORT" > /etc/odbcinst.ini

# Interaktive Frontend Einstellungen zurücksetzen
ENV DEBIAN_FRONTEND=dialog

# Compile the cobol programs
#RUN cobc -x --free ${FUNCTION_DIR}/YTR93.cob ${FUNCTION_DIR}/YTR92.cob -o ${FUNCTION_DIR}/cobol-program
#RUN cobc -x -free -std=default -I/usr/include/mysql/ ${FUNCTION_DIR}/YTR93.cob -o YTR93 -L/usr/lib -lmysqlclient
RUN cobc -x -std=default -free -I/usr/include/mysql /function/YTR93.cob -o YTR93 -L/usr/lib -lmysqlclient

# Multi-stage build: grab a fresh copy of the base image
FROM python:3.10-buster

# Ensure that the runtime requirements for cobol are met
RUN apt update && apt install libcob4

# Include global arg in this stage of the build
ARG FUNCTION_DIR
# Set working directory to function root directory
WORKDIR ${FUNCTION_DIR}

# Copy in the build image dependencies
COPY --from=build-image ${FUNCTION_DIR} ${FUNCTION_DIR}

ENTRYPOINT [ "/usr/local/bin/python", "-m", "awslambdaric" ]
CMD [ "app.handler" ]

My Cobol:

PROCEDURE DIVISION.
MAIN-PROCEDURE.

   EXEC SQL
      CONNECT TO :DB-NAME AT :DB-SERVER AS :DB-USER IDENTIFIED BY :DB-PASSWORD
   END-EXEC.

   EVALUATE SQLCODE
      WHEN 0
            DISPLAY "Connected successfully to remote MySQL server."
      WHEN OTHER
            DISPLAY "Connection error: ", SQLERRMC
   END-EVALUATE.

I tried to integrate it into my application but I did not find a way to execute it via Dockerfile.

Answer 1

If you want to setup GixSQL then you'd need to install it. What is the gnucobol-cobsql package (ocesql, esqloc, gixsql?) and where is it from?

For GixSQL you could use the Debian packages, which should also auto-install necessary dependencies (so your list can get smaller - and you likely want to use the newer apt instead of apt-get) from its release page.
For ocesql and esqloc you'd likely need to install them from source.

In any case you'd want to RUN gixsql (or the other preprocessors) first to convert the source containing EXEC SQL, then RUN cobc to compile the result. The preprocessor will either include the SQLCA copybook directly or provide it for inclusion into your cobc command line with something like -I /path/to/preprocessor.

The documentation for GixSQL includes details how to use the ODBC backend - but for mysql you may want to use its mysql backend directly. These will be possible entries for your data source:

odbc://MySQL
mysql://databasestring:3306/bank1

I'm not sure if the separate AT :DB-SERVER is supported, if not and needed just create a feature request at their site, referencing the environment where it worked - for your ODBC setup that's not useful as you've setup the DB already there.