Peter Hoffmann Director Data Engineering at Blue Yonder. Python Developer, Conference Speaker, Mountaineer

Exasol In-Memory Database with Doctrine dbal in php

How to access the exasol in memory database with doctrine/dbal and build your queries with the QueryBuilder in php.

Exasol is an in memory database for business intelligence. It's sql syntax ist mostly oracle compatible. Doctrine has no built in support for the exasol database. But you can pass a pdo connection to doctine and tell the driver manager to use the OCI8 driver.

require_once __DIR__.'/../vendor/autoload.php';

$dsn = "odbc:EXA_DATABASE";
$user = "username";
$password = "password";
try {
    $dbh = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();

$config = new \Doctrine\DBAL\Configuration();
$connectionParams = array("pdo" => $dbh, 
                          "driverClass" => "Doctrine\DBAL\Driver\OCI8\Driver");
$conn = \Doctrine\DBAL\DriverManager::getConnection($connectionParams, $config);

$sql = "SELECT * from mytable;" 
$stmt = $conn->query($sql);
while ($row = $stmt->fetch()) {

This allows you to use the Doctrine Dbal QueryBuilder.

$qb = $conn->createQueryBuilder();
        ->where($query->expr()->eq('id', 2));

$result = $qb->execute();
As mentioned by Stefan in the comments the following is outdated. With EXASolution ODBC 4.1.1. you can now use prepared statements.

Due to the limits of the odbc driver you are not able to use prepared statements/setParameter().:

#does not work
    ->where("field1 = ?")
    ->setParameter(1, 2);