Connections and transactions

Connecting to a database

GixSQL has no “bind” step: a program must connect explicitly with a CONNECT statement before executing any other SQL statement. The following forms are accepted, where each operand is either a host variable (:VAR) or a string literal:

           EXEC SQL CONNECT TO :DATASRC END-EXEC.
           EXEC SQL CONNECT TO :DATASRC USER :USR-PWD END-EXEC.
           EXEC SQL CONNECT TO :DATASRC USER :USR USING :PWD END-EXEC.
           EXEC SQL CONNECT :USR IDENTIFIED BY :PWD USING :DATASRC END-EXEC.
           EXEC SQL CONNECT USING :DATASRC END-EXEC.
  • DATASRC contains a connection string (Connection strings).
  • The user name and the password are given either separately, or in a single item as user.password (a dot separates them).
  • With CONNECT USING, the credentials must be in the connection string.
  • The Pro*COBOL form CONNECT :USR-PWD, where a single item contains user/password, is not supported: use IDENTIFIED BY.

After the CONNECT, SQLCODE is 0 on success. On failure, it is negative and SQLERRMC contains the message of the database.

Connection strings

The general format of a connection string is:

<driver>://[<user>.<password>@]<host>[:<port>][/<database>][?<opt>=<val>[&<opt>=<val>]...]

where <driver> is one of pgsql, oracle, mysql, odbc, sqlite (see Overview of the backends). Examples:

pgsql://localhost:5432/demo
pgsql://demo.secret@dbserver/demo?default_schema=app
oracle://dbserver:1521/FREEPDB1
mysql://localhost:3306/demo
odbc://MYDSN
sqlite:///var/data/demo.db

Two other formats are accepted, without the driver name:

  • [<user>.<password>@]<host>[:<port>][/<database>]
  • <database>@<host>[:<port>] (the format of OCESQL)

The default driver

When the connection string does not name a driver, GixSQL uses the default driver, which is:

  1. the driver chosen when GixSQL was built (configure --with-default-driver=...), if any;
  2. otherwise, the contents of the environment variable GIXSQL_DEFAULT_DRIVER.

If there is no default driver, the CONNECT fails.

Connection options

Options follow a ? at the end of the connection string, separated by &. Boolean options accept on/off or 1/0.

Option Drivers Description
autocommit all on, off or native (Autocommit)
client_encoding pgsql, mysql encoding of the client, for example UTF8 (PostgreSQL) or utf8mb4 (MySQL)
fixup_params all convert the parameter placeholders of prepared statements (Parameter placeholders)
default_schema pgsql the search_path (one or more schemas, separated by commas)
native_cursors pgsql use native PostgreSQL cursors (default on)
decode_binary pgsql decode bytea values (default on)
updatable_cursors mysql, sqlite emulate updatable cursors
sslmode, sslcert, … pgsql passed to libpq (PostgreSQL)

Several options can also be set by environment variables (Runtime configuration); the option of the connection string has priority.

Multiple connections

A program can open several connections, each one with a name:

           EXEC SQL
               CONNECT TO :DATASRC-1 AS CONN1 USER :DBUSR-1
           END-EXEC.
           EXEC SQL
               CONNECT TO :DATASRC-2 AS CONN2 USER :DBUSR-2
           END-EXEC.

or, with the Pro*COBOL syntax:

           EXEC SQL
               CONNECT :USR IDENTIFIED BY :PWD AT CONN2 USING :DATASRC-2
           END-EXEC.

A statement is then executed on a given connection with the AT clause:

           EXEC SQL AT CONN1 DELETE FROM TAB1 END-EXEC.
           EXEC SQL AT CONN2 DELETE FROM TAB2 END-EXEC.

Statements without AT use the default connection, the one opened without a name.

NoteTo be verified

Which connection is the default one when all the connections are named?

Disconnecting

           EXEC SQL CONNECT RESET END-EXEC.
           EXEC SQL CONNECT RESET CONN1 END-EXEC.
           EXEC SQL DISCONNECT ALL END-EXEC.

Pro*COBOL programs disconnect with COMMIT WORK RELEASE or ROLLBACK WORK RELEASE: see Migrating from Pro*COBOL.

Transactions

A transaction is a sequence of SQL statements that are committed or rolled back together. It ends with:

           EXEC SQL COMMIT WORK END-EXEC.
           EXEC SQL ROLLBACK WORK END-EXEC.

SAVEPOINT name and ROLLBACK TO SAVEPOINT name are passed unchanged to the database.

Autocommit

The way transactions start depends on the autocommit mode, set by the autocommit option of the connection string or by the environment variable GIXSQL_AUTOCOMMIT:

native (default)
the connection uses the default behavior of the database: autocommit for PostgreSQL, MySQL and ODBC, explicit transactions for Oracle.
on
each successful statement is committed.
off
a transaction is started when connecting, and a new one after each COMMIT and ROLLBACK.
Important

On PostgreSQL, native cursors (the default) only exist inside a transaction: with autocommit, a DECLARE CURSOR fails. Programs that use cursors must either start transactions explicitly (EXEC SQL START TRANSACTION END-EXEC) or run with GIXSQL_AUTOCOMMIT=off, which is the behavior expected by Pro*COBOL programs.

NoteTo be verified

The old README states that the default is off, the code (GIXSQL_AUTOCOMMIT_DEFAULT in runtime/libgixsql/gixsql.h) says native. Check the actual behavior of each driver in native mode.