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.
DATASRCcontains 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 containsuser/password, is not supported: useIDENTIFIED 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:
- the driver chosen when GixSQL was built (
configure --with-default-driver=...), if any; - 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.
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
COMMITandROLLBACK.
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.
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.