jeandormehl / laracache

Laravel package providing InterSystems Cache as a data source using ODBC

InterSystems Caché provider for Laravel (ODBC)

Laracaché

Laracaché is an InterSystems Caché database driver package for Laravel. Laracaché is an extension of Illuminate/Database that uses the php-odbc extension to communicate with Caché. This package plays well with Eloquent.

Quick Installation

PHP >= 8 and Laravel >= 8

composer require jeandormehl/laracache

Environment variables

Modify your .env file to look similar to this. Windows users only need set DB_CONNECTION and DB_WIN_DSN variables. Unix users should set all other variables as seen below:

DB_CONNECTION=isc
DB_WIN_DSN=
DB_UNIX_DRIVER=/usr/lib/intersystems/odbc/bin/libcacheodbcur6435.so
DB_HOST=127.0.0.1
DB_PORT=1972
DB_DATABASE=LARAVEL
DB_USERNAME=_SYSTEM
DB_PASSWORD=SYS

Configuration

Publish a configuration file by running the following Artisan command.

php artisan vendor:publish --tag=isc

This will copy the configuration file to config/isc.php.

'isc' => [
    'driver'      => 'odbc',
    'win_dsn'     => env('DB_WIN_DSN', ''),         // windows users only
    'unix_driver' => env('DB_UNIX_DRIVER', ''),     // unix users only
    'host'        => env('DB_HOST', ''),
    'port'        => env('DB_PORT', 1972),
    'database'    => env('DB_DATABASE', ''),        // namespace
    'username'    => env('DB_USERNAME', '_SYSTEM'),
    'password'    => env('DB_PASSWORD', 'SYS'),
    'schema'      => env('DB_SCHEMA', 'SQLUser'),   // SQLUser is default, avoid changing if possible
    'options'     => [
        \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_OBJ,
        'processor'                   => \Laracache\Cache\Query\Processors\Processor::class,
        'grammar'                     => [
            'query'  => \Laracache\Cache\Query\Grammars\Grammar::class,
            'schema' => \Laracache\Cache\Schema\Grammars\Grammar::class,
        ],
    ],
],

ODBC Drivers for Caché

You will need to ensure that you have the relevant ODBC drivers installed. For Linux distributions I recommended using the unixODBC driver, in specific, libcacheodbcur6435.so for 64-bit. If you have any trouble try to switch to libcacheodbcur64.so.

unixODBC

Download, untar and build the unixODBC package. This example uses version 2.3.7.

# get unixODBC
wget -q ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.7.tar.gz

# untar the package
sudo tar -xzvf unixODBC-2.3.7.tar.gz

# navigate to build file path, in this case, ~/unixODBC-2.3.7
cd ~/unixODBC-2.3.7

# build the package - modify prefix, sysconfdir and doc location as needed
sudo -s <<EOF
./configure --prefix=/usr --sysconfdir=/etc \
  && make \
  && make install \
  && find doc -name "Makefile*" -delete \
  && chmod 644 doc/{lst,ProgrammerManual/Tutorial}/* \
  && install -v -m755 -d /usr/share/doc/unixODBC-2.3.7 \
  && cp -v -R doc/* /usr/share/doc/unixODBC-2.3.7
EOF

php-odbc

Ensure php-odbc extension is installed. This example uses Apache & PHP 7.2.

sudo apt-get -y update
sudo apt-get -y install php-odbc

# restart services
sudo service apache2 restart
sudo service php7.2-fpm restart

InterSystems ODBC Drivers

Download, untar and install. This example uses the 2018.1.0.184.0 build for Ubuntu 64bit. Find available drivers at this link: InterSystems ODBC Drivers

# download drivers
wget -q ftp://ftp.intersys.com/pub/cache/odbc/2018/ODBC-2018.1.0.184.0-lnxubuntux64.tar.gz

# create a directory to hold drivers and copy tar file to it
sudo mkdir -p /usr/lib/intersystems/odbc
sudo cp ODBC-2018.1.0.184.0-lnxubuntux64.tar.gz /usr/lib/intersystems/odbc

# untar the file and run installer
sudo tar -xzvf /usr/lib/intersystems/odbc/ODBC-2018.1.0.184.0-lnxubuntux64.tar.gz
sudo /usr/lib/intersystems/odbc/ODBCinstall

/etc/odbc.ini

After completeing the above steps, you should have a file located in /etc called odbc.ini. Edit this file using vi or nano. It should look something like this:

[ODBC Data Sources]
cache=cache

[cache]
Driver                = /usr/lib/intersystems/odbc/bin/libcacheodbcur6435.so
Description           = InterSystems Cache ODBC Connection
Protocol              = TCP
Query Timeout         = 1
Static Cursors        = 0
Authentication Method = 0

Register and create symlink to the cursor.

# register
sudo odbcinst -i -s -f /etc/odbc.ini

# create the symlink
sudo ln -s /usr/lib/x86_64-linux-gnu/libodbccr.so.2.0.0 /usr/lib/x86_64-linux-gnu/odbc/libodbccr.so

ODBC Installation and Validation on UNIX® Systems

For Windows, setup the ODBC data source in Administrative Tools and set the win_dns setting in the config file, isc.php to the name of your ODBC Data Source.

Contribute

You can run this project on VSCODE with Remote Container. Make sure you will use internal VSCODE terminal (inside running container).

composer install
composer test
composer test:coverage

License

The MIT License (MIT). Please see License File for more information.

laracache's Issues

DSN string not built properly

Here is my .ENV

DB_CONNECTION=isc
DB_HOST=10.0.1.2
DB_PORT=1972
DB_DATABASE=MYNAMESPACE
DB_USERNAME=user1
DB_PASSWORD=pass1

Just noticed $dsn value is onlyodbc:cache . Is it supposed to be a full DSN string?

class Cache extends PDO
{
public function __construct($dsn, $username, $password, array $options = [])
    {
        // must call pdo constructor
        parent::__construct($dsn, $username, $password, $options);

Am i missing something? Can you, please, provide a real configuration sample?

Issue with data within max column size

I am experiencing a issue when retrieving fields within max column size defined on a table.

How to reproduce:

• Considering you have a column "name" (VARCHAR 10) and it has exactly 10 chars
• When you try to select it from a eloquent query an error will be throw.
• If you delete 1 char, from this field on database and select again, no errors will be throw.

Intersystem Caché ODBC driver download

Hi, guys!

It seems FTP for downloading Caché ODBC drivers is not working anymore.

ftp://ftp.intersys.com/pub/cache/odbc/2018/ODBC-2018.1.0.184.0-lnxubuntux64.tar.gz

Could you please share ODBC drivers?

Cc @liamjcooper @jeandormehl

[PHP 8] PDO fetchAll() incompatible signature

Hi,

Have you managed to upgrade sucessfully to PHP 8.0?

cc @robsontenorio @liamjcooper

Add code coverage

Hi, @jeandormehl

When you have a chance could you please authorize this repository on https://about.codecov.io/ ? Use "login with github"

It is necessary to generate code coverage badges and coverage reports.

Thank you!

Random "Connection reset by peer"

@jeandormehl Do you see sometimes that problem? A few per month and it is momentaneous.

SQLSTATE[HY000] SQLDriverConnect: 12104 [Cache ODBC][State : HY000][Native Code 12104]
[libcacheodbcur6435.so]
Socket: Connection reset by peer (due to timeout or reboot)

Maybe an infraestructure problem in Caché DB / Network ? Or something we can do better on handling ODBC connections?

Could you share your full odbc.ini file?

Laravel 10 Support

Support for Laravel 10 would be appreciated :)

Handle special chars

Is there any alternative through configuration to ignore special chars like:

á -> a
õ -> o
ç -> c
...

By default Caché does not ignore when making a simple "where" with sql. Otherwise i must implement a translator helper at app level.

It tries to connect to database after composer install

After "composer install" it tries to connect.

Considering we have CI pipelines, it is usual to have a "test" step where no connection is needed. It causes a failure because package always tries to connect to database.

The same does not occurs when using Mysql or Postgres.

Am i missing some config?

Create version (tags)

I am considering using laracache on production. Can you tag first the version?
Currently it is on dev-master

Linux Configuration

Nice package!

Can you, please, expand the "ODBC Drivers for caché" section from README? How to install and setup the caché odbc driver? The official docs from Intersystem is very tricky about how to install!

https://github.com/jeandormehl/laracache#odbc-drivers-for-cach%C3%A9

I am getting SQLSTATE[IM002] SQLConnect: 0 [unixODBC][Driver Manager]Data source name not found, and no default driver specified , probably because "libcacheodbcur6435" is not properly installed

Pagination (%vid)

Although it is a valid test, the resulting query does not make sense.

laracache/tests/CacheQueryBuilderTest.php

Lines 505 to 510 in 7214330

	$builder->select('*')->from('users')->offset(5)->limit(10);
	$this->assertEquals(
	'select *, %vid from (select top 10 * from users order by 1) where %vid between 6 and 15',
	$builder->toSql()
	);

Just executed in Caché (2016) and it does not return the right result, because "top 10" (limit 10) is fixed and it will always get same first top 10 results. So, pagination never happens. Any tricks?

Unable to acquire lock for INSERT

Do you gut have any issues with INSERT/UPDATE/DELETE? Could you please share your current config?

For this whole time I have been using LARACACHE for SELECT only.

Now I am trying to make a simple INSERT.

public function test(){
     // Laravel begin transaction here
     MyModel::create([
            'name' => 'Joe'
    ]);
    // Laravel commit transaction here 
}

The first time the insert works. But if I try todo a second insert with a different payload I'm getting.

[Cache ODBC][State : S1000][Native Code 110] - [libcacheodbcur64.so] - 
[SQLCODE: <-110>:<Locking conflict in filing>]
[Location: <ServerLoop>] [%msg: <Unable to acquire lock for INSERT of table .....

Here is my odbc.ini

[ODBC Data Sources]
cache=cache

[cache]
Driver                = /usr/local/cache/2018/bin/libcacheodbcur64.so
Description           = InterSystems Cache ODBC Connection
Protocol              = TCP
Query Timeout         = 1
Static Cursors        = 0
Authentication Method = 0

Here is isc.config

'driver' => 'odbc',
        'win_dsn' => env('DB_WIN_DSN', ''),         // windows users only
        'unix_driver' => env('DB_UNIX_DRIVER', ''),     // unix users only
        'host' => env('DB_HOST', ''),
        'port' => env('DB_PORT', 1972),
        'database' => env('DB_DATABASE', ''),        // namespace
        'username' => env('DB_USERNAME', '_SYSTEM'),
        'password' => env('DB_PASSWORD', 'SYS'),
        'schema' => env('DB_SCHEMA', 'SQLUser'),   // SQLUser is default, avoid changing if possible
        'options' => [
            \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_OBJ,
            \PDO::ATTR_CASE => \PDO::CASE_LOWER,
            'processor' => \Laracache\Cache\Query\Processors\Processor::class,
            'grammar' => [
                'query' => \Laracache\Cache\Query\Grammars\Grammar::class,
                'schema' => \Laracache\Cache\Schema\Grammars\Grammar::class,
            ],
        ],

Add support to PHP8

PDO attributes are not parsed

return [
    'isc' => [
        ....
        'options' => [
            \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_OBJ,
            \PDO::ATTR_CASE => \PDO::CASE_LOWER,  // <---------------------- not parsed
            'processor' => \Laracache\Cache\Query\Processors\Processor::class,
            'grammar' => [
                'query' => \Laracache\Cache\Query\Grammars\Grammar::class,
                'schema' => \Laracache\Cache\Schema\Grammars\Grammar::class,
            ],
        ],
    ],

Not sure, but probably $this->getAttribute should be $this->connection->getAttribute.

laracache/src/Pdo/Cache/Statement.php

Lines 85 to 93 in 7214330

	public function fetch($fetchMode = null, $cursorOrientation = PDO::FETCH_ORI_NEXT, $cursorOffset = 0)
	{
	if ($fetchMode === null) {
	$fetchMode = $this->fetchMode;
	}
	$toLowercase = ($this->getAttribute(PDO::ATTR_CASE) === PDO::CASE_LOWER);
	$nullToString = ($this->getAttribute(PDO::ATTR_ORACLE_NULLS) === PDO::NULL_TO_STRING);
	$nullEmptyString = ($this->getAttribute(PDO::ATTR_ORACLE_NULLS) === PDO::NULL_EMPTY_STRING);

Make it compatible with InterSystems IRIS

Support for Laravel 6

Hi,
Could you please add support for Laravel 6?

First query/connection issue

Hi,

I am facing a issue where the first run/query/connection it a bit slow, but subsequents one get faster.

Is there any configuration am i missing? Just used default odbc.ini on README.