github.com/hoffmann Peter Hoffmann on Stackoverflow @peterhoffmann on twitter Peter Hoffmann on Facebook Contact me per email Subscribe to Atom Feed

Peter Hoffmann

Software Engineer
prev page next page

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.

Posted on January 26, 2013
#php #exasol #nosql

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());

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 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);