Exasol In-Memory Database with Doctrine DBAL in PHP

Posted on January 26, 2013 in php exasol nosql

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. Its SQL syntax is mostly Oracle-compatible. Doctrine has no built-in support for the Exasol database, but you can pass a pdo connection to Doctrine 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()) {
  print_r($row);
}

This allows you to use the Doctrine DBAL QueryBuilder.

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

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

Update
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 limits of older ODBC drivers, you could not use prepared statements or setParameter():

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