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);
$stmt->execute();
while ($row = $stmt->fetch()) {
  print_r($row);
}

This allows you to use the Doctrine Dbal QueryBuilder.

$qb = $conn->createQueryBuilder();
$qb->select("field1","filed2")
        ->from("mytable")
        ->where($query->expr()->eq('id', 2));

var_dump($qb->getSQL());
$result = $qb->execute();
var_dump($result->fetchAll());

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

#does not work
$qb->select("field1","filed2")
    ->from("mytable")
    ->where("field1 = ?")
    ->setParameter(1, 2);