ODBC driver for Laravel 6 and 7

Package inspired by tck/odbc but simplified, modernized and made compatible with Laravel 6 and 7.

Installation

composer require kkszymanowski/laravel-6-odbc

Add you database configuration in config/database.php, for example:

'connections' => [
    'myOdbcConnection' => [
        'driver'   => 'odbc',
        'dsn'      => env('DB_ODBC_CONNECTION_STRING'),
        'host'     => env('DB_ODBC_HOST'),
        'database' => env('DB_ODBC_DATABASE'),
        'username' => env('DB_ODBC_USERNAME'),
        'password' => env('DB_ODBC_PASSWORD'),
    ],

    // ...
],

Add the environment variables referenced in the configuration to your .env file, for example:

DB_ODBC_CONNECTION_STRING="odbc:DRIVER=Pervasive;ServerName=192.168.0.1;DBQ=DATABASE;UID=User;PWD=Password"
DB_ODBC_HOST=192.168.0.1
DB_ODBC_DATABASE=DATABASE
DB_ODBC_USERNAME=User
DB_ODBC_PASSWORD=Password

If you would like to customize the Schema and Query grammars used in the ODBC connection you can do that by extending \Odbc\OdbcSchemaGrammar and \Odbc\OdbcQueryGrammar. Then add the following configuration entries:

'database.connections.odbc.grammar.query'
'database.connections.odbc.grammar.schema'

For example:

'connections' => [
    // ...

    'odbc' => [
        'grammar' => [
            'query' => \App\Grammars\CustomQueryGrammar::class,
            'schema' => \App\Grammars\CustomSchemaGrammar::class,
        ],
    ],

    // ...

One of the more common cases would be to customize the compileLimit() method used in pagination and in the skip() method. You can do this in the following way

use Illuminate\Database\Query\Builder;
use Odbc\OdbcQueryGrammar;

class CustomQueryGrammar extends OdbcQueryGrammar
{
    /**
     * Compile the "limit" portions of the query.
     *
     * @param Builder $query
     * @param int     $limit
     *
     * @return string
     */
    protected function compileLimit(Builder $query, $limit)
    {
        return 'select top ' . (int) $limit;
    }
}

Usage

With Eloquent

To override your default database connection define $connection name in your Eloquent Model

/**
 * The connection name for the model.
 *
 * @var string
 */
protected $connection = 'myOdbcConnection';

After defining the connection name you perform all the standard Eloquent operations:

$user = User::where('id', 1)->get();
$users = User::all();

Without Eloquent

You can also perform queries without Eloquent models. Make sure you specify the connection name if it isn't your default one, for example:

$user = DB::connection('myOdbcConnection')->select('SELECT * FROM USERS WHERE id = :id', ['id' => 1]);
$users = DB::connection('myOdbcConnection')->table('USERS')->select('*')->get();

If you're running raw queries make sure to use parameter bindings wherever possible to avoid SQL Injection vulnerabilities.