InterSystems IRIS with Laravel (via ODBC)

It has been asked a few times recently, how one can make Laravel Framework work with InterSystems IRIS Data Platform. It's been a while since this post about Laravel and InterSystems Caché was published. To bring it up to date, the article gives a brief instruction set on how to setup and configure a Laravel project for use with InterSystems IRIS through ODBC.

What is Laravel?

Laravel is a PHP framework that is based on MVC architecture. Using Laravel simplifies and speeds up the backend development while building modern secure web applications. It is very beginner-friendly, widely used in PHP world and tends to be the most popular backend framework according to github.com star rating measured in this video. Combining all that with flexibility and performance capabilities delivered by InterSystems IRIS as a database is seen to be beneficial for both worlds.

This post is organised into 4 steps which represent a sequence of actions one needs to complete in order to make the connection work. Specific ways of completing each step may vary depending on the platform. Commands here are shown for Ubuntu 22.02 (x64).

Setup Driver Manager (unixODBC)

In order to make the connection work we need to install a Driver Manager. Most commonly used driver managers are 'unixODBC' and 'iODBC'. This guide uses 'unixODBC', which may be downloaded here http://www.unixodbc.org/ . Please refer to 'Download' section of the website to download and build. Alternatively, build instructions can also be found here. We'll use here packages from 'apt' package manager for Ubuntu.

Install packages

Install unixodbc package accompanied by libodbccr2 which provides unixODBC Cursor library.

sudo apt update
sudo apt -y install unixodbc libodbccr2 odbcinst

Create a link for Cursor Library
In certain cases there might be issues with Shared Object Dependencies after unixODBC installation. This is shown as 'Can't open cursor lib' error. There are few workarounds described in internet. In order to resolve this issue we make a symbolic link to a desired library. First, we locate the library:

sudo find / -type f -name "libodbccr*"

And then we create a link

sudo ln -s /usr/lib/x86_64-linux-gnu/libodbccr.so.2.0.0 /etc/libodbccr.so

Setup ODBC Driver for InterSystems IRIS

ODBC driver for InterSystems IRIS can be obtained in various ways. For example, ODBC Driver is included to all InterSystems IRIS kits. The other option would be Distributions Portal on wrc.intersystems.com.

Alternatively, drivers for all supported platforms can be found here: https://intersystems-community.github.io/iris-driver-distribution/

Download, unpack and install ODBC Driver:

sudo mkdir -p /usr/lib/intersystems/odbc
cd /usr/lib/intersystems/odbc/

sudo wget -q https://raw.githubusercontent.com/intersystems-community/iris-driver-distribution/main/ODBC/lnxubuntu2004/ODBC-2022.1.0.209.0-lnxubuntu2004x64.tar.gz
sudo tar -xzvf /usr/lib/intersystems/odbc/ODBC-2022.1.0.209.0-lnxubuntu2004x64.tar.gz
sudo ./ODBCinstall

sudo rm -f ODBC-2022.1.0.209.0-lnxubuntu2004x64.tar.gz

After that, the driver will be located in the following folder /usr/lib/intersystems/odbc/bin/. 

Additional information on drivers and their usage may be found in the docs. This guide uses libirisodbcur6435.so as a driver library.

Setup Laravel project

The traditional and convenient way to interact with a database from Laravel would be using its Eloquent package. Eloquent is an object relational mapper (ORM) that is included by default within the Laravel framework. Only few DBMS vendors are supported out-of-the-box. So in order to implement connection and SQL query builder specifics for InterSystems IRIS (via ODBC) some additional PHP code needs to be written. Thanks to @Jean Dormehl this gap was covered for InterSystems Caché . The same one could be used for InterSystems IRIS.
So in this article we describe steps to setup connection for existing Laravel project using jeandormehl/laracache package, assuming that installation and configuration of php, composer and Laravel is done prior to that.

Install php-odbc
Make sure that php-odbc module is installed. You can check the list of modules installed with the following command:

php -m | grep odbc

To install php-odbc extension use the following command using a proper version of php installed on your environment

sudo apt -y install php8.1-odbc

Setup 'jeandormehl/laracache' package
Go to your Laravel project directory, install package and publish its config file.

composer require jeandormehl/laracache
php artisan vendor:publish --tag=isc

Configure IRIS connection

Edit your .env file to contain settings needed to connect to a database. For Unix users it should look similar to this:

DB_CONNECTION=isc
DB_WIN_DSN=
DB_UNIX_DRIVER=/usr/lib/intersystems/odbc/bin/libirisodbcur6435.so
DB_HOST=127.0.0.1
DB_PORT=1972
DB_DATABASE=USER
DB_USERNAME=_SYSTEM
DB_PASSWORD=sys

After editing .env file you may find useful to clear application config cache:

php artisan config:cache

Usage

Let's try to retrieve some data using our new package. As an example we'll create a Model inherited from Laracache\Cache\Eloquent\Model . Just for testing purposes we will count the number of sent messages in Interoperability enabled namespace.

nano app/Models/EnsMessageHeader.php

<?php
namespace App\Models;
use Laracache\Cache\Eloquent\Model;
class EnsMessageHeader extends Model
{
    protected $table = 'Ens.MessageHeader';
    protected $fillable = [
        'MessageBodyClassName'
    ];
}

To execute a query we may create an artisan console command like this:

nano routes/console.php

<?php
use Illuminate\Foundation\Inspiring;
use Illuminate\Support\Facades\Artisan;
use App\Models\EnsMessageHeader;
Artisan::command('iris:test', function () {
    echo EnsMessageHeader::count() . PHP_EOL;
});

Then executing the following command should retrieve the number of records 

php artisan iris:test

This scenario should work to a wide range of InterSystems IRIS based products.