Database Connections

Database Connections

OpenRPG uses ODBC for database access, making it portable across databases. You connect using a connection string with EXEC SQL CONNECT USING.

Prerequisites

macOS / Linux:

1. Install unixODBC (the driver manager)
2. Install a database-specific ODBC driver
3. Register the driver in /etc/odbcinst.ini or (on macOS with Homebrew) /opt/homebrew/etc/odbcinst.ini

Windows:

1. ODBC driver manager is built in — no additional install needed
2. Install a database-specific ODBC driver (typically an MSI installer)
3. Drivers are registered automatically; manage them via the ODBC Data Source Administrator (search "ODBC" in the Start menu)

SQLite

SQLite is ideal for local development and testing — no server required.

Install:

# macOS
brew install sqliteodbc

# Linux (Debian/Ubuntu)
sudo apt install libsqliteodbc

Register driver (macOS — add to /opt/homebrew/etc/odbcinst.ini):

[SQLite3]
Description=SQLite3 ODBC driver
Driver=/opt/homebrew/lib/libsqlite3odbc.dylib
Threading=2

Register driver (Linux — add to /etc/odbcinst.ini):

[SQLite3]
Description=SQLite3 ODBC driver
Driver=/usr/lib/x86_64-linux-gnu/odbc/libsqlite3odbc.so
Threading=2

Connect:

DCL-S connStr VARCHAR(200);
connStr = 'Driver={SQLite3};Database=/path/to/mydb.sqlite;';
EXEC SQL CONNECT USING :connStr;

Windows:

Download the SQLite ODBC driver from http://www.ch-werner.de/sqliteodbc/ and run the MSI installer. The driver is registered automatically. Use the same connection string as above.

The database file is created automatically if it doesn't exist.

PostgreSQL

Install:

# macOS
brew install psqlodbc

# Linux (Debian/Ubuntu)
sudo apt install odbc-postgresql

Register driver (macOS — add to /opt/homebrew/etc/odbcinst.ini):

[PostgreSQL]
Description=PostgreSQL ODBC driver (ANSI)
Driver=/opt/homebrew/lib/psqlodbca.so
Threading=0

[PostgreSQL Unicode]
Description=PostgreSQL ODBC driver (Unicode)
Driver=/opt/homebrew/lib/psqlodbcw.so
Threading=0

Register driver (Linux — add to /etc/odbcinst.ini):

[PostgreSQL]
Description=PostgreSQL ODBC driver
Driver=/usr/lib/x86_64-linux-gnu/odbc/psqlodbca.so
Threading=0

[PostgreSQL Unicode]
Description=PostgreSQL ODBC driver (Unicode)
Driver=/usr/lib/x86_64-linux-gnu/odbc/psqlodbcw.so
Threading=0

Windows:

Download the PostgreSQL ODBC driver from https://www.postgresql.org/ftp/odbc/versions/msi/ and run the MSI installer. The driver name is typically PostgreSQL Unicode(x64).

Connect:

DCL-S connStr VARCHAR(300);

// Basic connection
connStr = 'Driver={PostgreSQL};Server=localhost;Port=5432;'
        + 'Database=mydb;Uid=myuser;Pwd=mypassword;';
EXEC SQL CONNECT USING :connStr;

// Windows (use the registered driver name)
connStr = 'Driver={PostgreSQL Unicode(x64)};Server=localhost;Port=5432;'
        + 'Database=mydb;Uid=myuser;Pwd=mypassword;';
EXEC SQL CONNECT USING :connStr;

MySQL / MariaDB

Install:

# macOS
brew install mysql-connector-odbc

# Linux (Debian/Ubuntu)
sudo apt install libmyodbc

Register driver (macOS — find the .so path with find /opt/homebrew -name "libmyodbc*"):

[MySQL]
Description=MySQL ODBC driver
Driver=/opt/homebrew/lib/libmyodbc8a.so
Threading=0

Windows:

Download MySQL Connector/ODBC from https://dev.mysql.com/downloads/connector/odbc/ and run the MSI installer. The driver name is typically MySQL ODBC 8.0 Unicode Driver.

Connect:

DCL-S connStr VARCHAR(300);

// macOS / Linux
connStr = 'Driver={MySQL};Server=localhost;Port=3306;'
        + 'Database=mydb;User=myuser;Password=mypassword;';
EXEC SQL CONNECT USING :connStr;

// Windows
connStr = 'Driver={MySQL ODBC 8.0 Unicode Driver};Server=localhost;Port=3306;'
        + 'Database=mydb;User=myuser;Password=mypassword;';
EXEC SQL CONNECT USING :connStr;

Microsoft SQL Server

Install:

# macOS
brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
brew install msodbcsql18

# Linux (Debian/Ubuntu) — see Microsoft's docs for repo setup
sudo apt install msodbcsql18

Windows:

Download "ODBC Driver 18 for SQL Server" from Microsoft's download page and run the MSI installer. SQL Server Express also installs the driver automatically.

Connect:

DCL-S connStr VARCHAR(300);
connStr = 'Driver={ODBC Driver 18 for SQL Server};'
        + 'Server=localhost,1433;Database=mydb;'
        + 'Uid=sa;Pwd=mypassword;TrustServerCertificate=yes;';
EXEC SQL CONNECT USING :connStr;

The connection string is the same on all platforms.

IBM Db2

Install (macOS / Linux):

# Download IBM Data Server Driver from IBM's website
# Or use the IBM i Access ODBC driver for IBM i connections

Install (Windows):

Download the IBM Data Server Driver Package from IBM Fix Central or install IBM i Access Client Solutions which includes the IBM i Access ODBC Driver. Run the installer and the driver is registered automatically.

Connect:

DCL-S connStr VARCHAR(300);

// Db2 on Linux/Windows
connStr = 'Driver={IBM DB2 ODBC DRIVER};'
        + 'Database=mydb;Hostname=localhost;Port=50000;'
        + 'Uid=db2admin;Pwd=mypassword;Protocol=TCPIP;';
EXEC SQL CONNECT USING :connStr;

// IBM i (via IBM i Access ODBC Driver)
connStr = 'Driver={IBM i Access ODBC Driver};'
        + 'System=myibmi.example.com;'
        + 'Uid=myuser;Pwd=mypassword;';
EXEC SQL CONNECT USING :connStr;

Using Environment Variables for Connection Strings

Avoid hardcoding credentials. Use %GETENV to read connection details from the environment:

DCL-S connStr VARCHAR(300);

connStr = %GETENV('DATABASE_URL');
IF connStr = '';
  DSPLY 'DATABASE_URL not set!';
  RETURN;
ENDIF;

EXEC SQL CONNECT USING :connStr;

export DATABASE_URL="Driver={PostgreSQL};Server=localhost;Port=5432;Database=mydb;Uid=user;Pwd=pass;"
./myprogram

Verifying Your ODBC Setup

macOS / Linux — list registered drivers:

odbcinst -q -d

Test a connection with isql (comes with unixODBC):

isql MyDSN myuser mypassword

Windows — open the ODBC Data Source Administrator (search "ODBC" in the Start menu). The Drivers tab lists all registered drivers. Use the Test Connection button when configuring a data source.

Connection Lifecycle

// Connect
EXEC SQL CONNECT USING :connStr;

// ... do work ...

// Disconnect when done
EXEC SQL DISCONNECT;

// Or use CONNECT RESET (equivalent to DISCONNECT)
EXEC SQL CONNECT RESET;

Note: OpenRPG supports one database connection per program. You must disconnect before connecting to a different database.