Overview of PDO

GOLDILOCKS PDO (PDO_GOLDILOCKS) driver provides PDO interface to access GOLDILOCKS database in PDO, and it is provides in a package.

PDO_GOLDILOCKS is built based on GOLDILOCKS CLI driver.

Installation /Configuration

Requirement

The client package of GOLDILOCKS should be installed in the system which is as same as that of PHP, and environment variables of $GOLDILOCKS_HOME should be set.

Installation

Installation by Using PECL

Create the package file if PDO_GOLDILOCKS package file does not exist.

% pecl pickle
Attempting to process the second package file
Package PDO_GOLDILOCKS-3.2.0.tgz done

Install the package by using PECL if PDO_GOLDILOCKS package file exists.

% sudo -E pecl install PDO_GOLDILOCKS-3.2.0.tgz

Installation by Building the Source

1. Decompress PDO_GOLDILOCKS.

% tar xvzf PDO_GOLDILOCKS-3.2.0.tgz
% cd PDO_GOLDILOCKS-3.2.0

2. Create the environment for the build.

Create the build environment for PDO_GOLDILOCKS expanded module by using phpize command.
If phpize command can not be found, PHP devel package in an appropriate version should be installed.

% whereis phpize
phpize: /usr/bin/phpize /usr/share/man/man1/phpize.1.gz

% /usr/bin/phpize
Configuring for:
PHP Api Version:         20121113
Zend Module Api No:      20121212
Zend Extension Api No:   220121212

3. Build it.

Execute configure by adding the path of $GOLDILOCKS_HOME to --with-pdo-goldilocks option and adding the path of php-config to --with-php-config option, then build it.

% whereis php-config
php-config: /usr/bin/php-config /usr/share/man/man1/php-config.1.gz

% ./configure --with-php-config=/usr/bin/php-config --with-pdo-goldilocks=/home/goldilocks/goldilocks_home/

% make

4. Install it.

% sudo -E make install

Altering PHP Configuration File

When using CentOS 6.0 or higher or Fedora 15 or higher, the pdo_goldilocks.ini file should be created in /etc/php.d directory and extension property should be added as follows.

extension=pdo_goldilocks.so

When using other OS extension_dir should be appropriately set in php.ini file and add extension property should be added as follows.

extension=pdo_goldilocks.so

Restarting Web Server

Restart the web server.

Checking Installation of PDO_GOLDILOCKS

Check whether PDO_GOLDILOCKS is activated by using phpinfo().

% php -i | grep PDO
PDO
PDO support => enabled
PDO drivers => goldilocks, sqlite
PDO Driver for GOLDILOCKS => enabled
PDO Driver for SQLite 3.x => enabled

Usage

Data Source Name (DSN)

Data Source Name (DSN) of PDO_GOLDILOCKS is configured as follows.

Examples

• Connecting to GOLDILOCKS

print "\n[Connecting to GOLDILOCKS]\n";
$db = new PDO('goldilocks:HOST=192.168.0.16;PORT=22581', 'test', 'test');
//$db = new PDO('goldilocks:DSN=GOLDILOCKS;UID=test;PWD=test');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

• Set test data

$result = $db->exec('DROP TABLE IF EXISTS t1');
$result = $db->exec('CREATE TABLE t1 ( id INTEGER GENERATED BY DEFAULT AS IDENTITY, name VARCHAR(32) )');
$result = $db->exec("INSERT INTO t1(name) VALUES ('Carol')");
$result = $db->exec("INSERT INTO t1(name) VALUES ('Ted')");
$result = $db->exec("INSERT INTO t1(name) VALUES (null)");
$result = $db->exec("INSERT INTO t1(name) VALUES ('William')");
$result = $db->exec("INSERT INTO t1(name) VALUES ('Chelsea')");
$result = $db->exec("INSERT INTO t1(name) VALUES ('Colin')");

$result = $db->exec('DROP TABLE IF EXISTS t2');
$result = $db->exec('CREATE TABLE t2 ( id INTEGER, name VARCHAR(32) )');
$result = $db->exec("INSERT INTO t2 VALUES (1, 'John')");
$result = $db->exec("INSERT INTO t2 VALUES (1, 'John')");
$result = $db->exec("INSERT INTO t2 VALUES (1, 'John')");
$result = $db->exec("INSERT INTO t2 VALUES (2, 'Smith')");
$result = $db->exec("INSERT INTO t2 VALUES (2, 'Smith')");

$result = $db->exec('DROP TABLE IF EXISTS images');
$result = $db->exec('CREATE TABLE images ( id INTEGER, contenttype VARCHAR(256), imagedata LONG VARBINARY )');

$result = $db->exec('DROP PROCEDURE IF EXISTS sp_out_string');
$result = $db->exec("CREATE OR REPLACE PROCEDURE sp_out_string( v1 OUT VARCHAR(32) ) ".
                    "IS ".
                    "BEGIN ".
                    "  v1 := 'WORLD'; ".
                    "END; ");

$result = $db->exec('DROP PROCEDURE IF EXISTS sp_inout_string');
$result = $db->exec("CREATE OR REPLACE PROCEDURE sp_inout_string( v1 INOUT VARCHAR(32) ) ".
                    "IS ".
                    "BEGIN ".
                    "  v1 := V1 || ' WORLD'; ".
                    "END; ");

• Quoting a normal string

print "\n[Quoting a normal string]\n";
$string = 'Nice';

print "Unquoted string: $string\n";
print "Quoted string  : " . $db->quote($string) . "\n";

• Quoting a dangerous string

print "\n[Quoting a dangerous string]\n";
$string = 'Naughty \' string';

print "Unquoted string: $string\n";
print "Quoted string  :" . $db->quote($string) . "\n";

• Quoting a complex string

print "\n[Quoting a complex string]\n";
$string = "Co'mpl''ex \"st'\"ring";

print "Unquoted string: $string\n";
print "Quoted string  : " . $db->quote($string) . "\n";

• Handling an error

print "\n[Error Handling]\n";
try
{
    //connect as appropriate as above
    $db->query('invalid query'); //invalid query!
}
catch(PDOException $ex)
{
    print "ERROR:".$ex->getMessage()."\n";
}

• Retrieving column metadata

print "\n[Retrieving column metadata]\n";
$stmt = $db->query('SELECT * FROM t1');
foreach(range(0, $stmt->columnCount() - 1) as $column_index)
{
    $meta = $stmt->getColumnMeta($column_index);
    var_dump($meta);
}

• Running simple select statements

print "\n[Running Simple Select Statements]\n";

print "\n#1\n";
foreach($db->query('SELECT * FROM t1') as $row)
{
    print $row['ID'].' '.$row['NAME']."\n";
}

print "\n#2\n";
$stmt = $db->query('SELECT * FROM t1');
 
while($row = $stmt->fetch(PDO::FETCH_ASSOC))
{
    print $row['ID'].' '.$row['NAME']."\n";
}

• Fetching rows using different fetch styles

print "\n[Fetching rows using different fetch styles]\n";
$stmt = $db->prepare("SELECT id, name FROM t1");
$stmt->execute();

print("PDO::FETCH_ASSOC: ");
print("Return next row as an array indexed by column name\n");
$result = $stmt->fetch(PDO::FETCH_ASSOC);
print_r($result);
print("\n");

print("PDO::FETCH_BOTH: ");
print("Return next row as an array indexed by both column name and number\n");
$result = $stmt->fetch(PDO::FETCH_BOTH);
print_r($result);
print("\n");

print("PDO::FETCH_LAZY: ");
print("Return next row as an anonymous object with column names as properties\n");
$result = $stmt->fetch(PDO::FETCH_LAZY);
print_r($result);
print("\n");

print("PDO::FETCH_OBJ: ");
print("Return next row as an anonymous object with column names as properties\n");
$result = $stmt->fetch(PDO::FETCH_OBJ);
print $result->NAME;
print("\n");

• Fetching rows with a scrollable cursor

print "\n[Fetching rows using different fetch styles]\n";
print "\n[Fetching rows with a scrollable cursor]\n";
$sql = 'SELECT id, name FROM t1 ORDER BY id';

print "Reading forwards:\n";
try
{
    $stmt = $db->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
    $stmt->execute();
    while( $row = $stmt->fetch(PDO::FETCH_NUM, PDO::FETCH_ORI_NEXT) )
    {
        $data = $row[0] . "\t" . $row[1] . "\n";
        print $data;
    }
    $stmt = null;
}
catch (PDOException $e)
{
    print $e->getMessage();
}

print "Reading backwards:\n";
try
{
    $stmt = $db->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
    $stmt->execute();
    $row = $stmt->fetch(PDO::FETCH_NUM, PDO::FETCH_ORI_LAST);
    do
    {
        $data = $row[0] . "\t" . $row[1] . "\n";
        print $data;
    } while( $row = $stmt->fetch(PDO::FETCH_NUM, PDO::FETCH_ORI_PRIOR) );

    $stmt = null;
}
catch (PDOException $e)
{
    print $e->getMessage();
}

• Constructing an order

print "\n[Construction order]\n";
class Person
{
    private $NAME;

    public function __construct()
    {
        $this->tell();
    }

    public function tell()
    {
        if (isset($this->NAME))
        {
            print "I am {$this->NAME}.\n";
        }
        else
        {
            print "I don't have a name yet.\n";
        }
    }
}
$stmt = $db->query("SELECT name FROM t1");
$stmt->setFetchMode(PDO::FETCH_CLASS, 'Person');
$person = $stmt->fetch();
$person->tell();
$stmt->setFetchMode(PDO::FETCH_CLASS|PDO::FETCH_PROPS_LATE, 'Person');
$person = $stmt->fetch();
$person->tell();

• Running simple INSERT, UPDATE, or DELETE statements

print "\n[Running Simple INSERT, UPDATE, or DELETE statements]\n";
$affected_rows = $db->exec("INSERT INTO t1(name) VALUES ('John'), ('Marry')");
print $affected_rows." were affected\n";

• Getting the last insert id

print "\n[Getting the Last Insert Id]\n";
$affected_rows = $db->exec("INSERT INTO t1(name) VALUES ('Smith')");
$insertId = $db->lastInsertId();
print "last insert id : ".$insertId."\n";

• Running statements with parameters

print "\n[Running Statements With Parameters]\n";

print "\n#1 array\n";
$id = 1;
$name = 'John';

$stmt = $db->prepare("SELECT * FROM t2 WHERE id=? AND name=?");
$stmt->execute(array($id, $name));
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

var_dump($rows);

print "\n#2 bindValue\n";
$id = 2;
$name = 'Smith';

$stmt = $db->prepare("SELECT * FROM t2 WHERE id=? AND name=?");
$stmt->bindValue(1, $id, PDO::PARAM_INT);
$stmt->bindValue(2, $name, PDO::PARAM_STR);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

var_dump($rows);

• Named placeholders

print "\n[Named Placeholders]\n";

print "\n#1 array\n";
$id = 1;
$name = 'John';

$stmt = $db->prepare("SELECT * FROM t2 WHERE id=:id AND name=:name");
$stmt->execute(array(':name' => $name, ':id' => $id));
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

var_dump($rows);

print "\n#2 bindValue\n";
$id = 2;
$name = 'Smith';

$stmt = $db->prepare("SELECT * FROM t2 WHERE id=:id AND name=:name");
$stmt->bindValue(':id', $id, PDO::PARAM_INT);
$stmt->bindValue(':name', $name, PDO::PARAM_STR);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

var_dump($rows);

• Executing prepared statements in a loop

print "\n[Executing prepared statements in a loop]\n";
$values = array('bob', 'alice', 'lisa', 'john');
$name = '';
$stmt = $db->prepare("INSERT INTO t1(name) VALUES(:name)");
$stmt->bindParam(':name', $name, PDO::PARAM_STR);
foreach($values as $name)
{
   $stmt->execute();
}

foreach($db->query('SELECT * FROM t1') as $row)
{
    print $row[0].' '.$row[1]."\n";
}

• Calling a stored procedure with an output parameter

print "\n[Calling a stored procedure with an output parameter]\n";
$stmt = $db->prepare("CALL sp_out_string(?)");
$stmt->bindParam(1, $value, PDO::PARAM_STR, 32); 
$stmt->execute();

print "procedure returned $value\n";

• Calling a stored procedure with an input/output parameter

print "\n[Calling a stored procedure with an input/output parameter]\n";
$stmt = $db->prepare("CALL sp_inout_string(?)");
$value = 'HELLO';
$stmt->bindParam(1, $value, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 32); 
$stmt->execute();

print "procedure returned $value\n";

• Transactions

print "\n[Transactions]\n";

print "\n#1 commit\n";
try
{
    $db->beginTransaction();
 
    $db->exec("INSERT INTO t1(name) VALUES('kim')");

    $name = 'lee';
    $stmt = $db->prepare("INSERT INTO t1(name) VALUES(?)");
    $stmt->execute(array($name));
 
    $id = 3;
    $name = 'park';
    $stmt = $db->prepare("INSERT INTO t2 VALUES(?, ?)");
    $stmt->execute(array($id, $name));
 
    $db->commit();
}
catch(PDOException $ex)
{
    //Something went wrong rollback!
    $db->rollBack();
    print $ex->getMessage()."\n";
}

print "\nT1\n";
foreach($db->query('SELECT * FROM t1') as $row)
{
    print $row[0].' '.$row[1]."\n";
}

print "\nT2\n";
foreach($db->query('SELECT * FROM t2') as $row)
{
    print $row[0].' '.$row[1]."\n";
}

print "\n#2 rollback\n";
try
{
    $db->beginTransaction();
 
    $db->exec("INSERT INTO t1(name) VALUES('KIM')");

    $name = 'LEE';
    $stmt = $db->prepare("INSERT INTO t1(name) VALUES(?)");
    $stmt->execute(array($name));
 
    $id = 3;
    $name = 'PARK';
    $stmt = $db->prepare("INSERT INTO invalid VALUES(?, ?)");
    $stmt->execute(array($id, $name));
 
    $db->commit();
}
catch(PDOException $ex)
{
    //Something went wrong rollback!
    $db->rollBack();
    print $ex->getMessage()."\n";
}

print "\nT1\n";
foreach($db->query('SELECT * FROM t1') as $row)
{
    print $row[0].' '.$row[1]."\n";
}

print "\nT2\n";
foreach($db->query('SELECT * FROM t2') as $row)
{
    print $row[0].' '.$row[1]."\n";
}

• Inserting an image into a database

print "\n[Inserting an image into a database]\n";
try
{
    $stmt = $db->prepare("INSERT INTO images (id, contenttype, imagedata) VALUES (?, ?, ?)");

    $id = 1;
    $type = "image/png";
    $fp = fopen("logo.png", "rb");

    $stmt->bindParam(1, $id, PDO::PARAM_INT);
    $stmt->bindParam(2, $type, PDO::PARAM_STR, 256);
    $stmt->bindParam(3, $fp, PDO::PARAM_LOB);

    $db->beginTransaction();
    $stmt->execute();
    $db->commit();
}
catch(PDOException $ex)
{
    $db->rollBack();
    print $ex->getMessage()."\n";
}

foreach($db->query('SELECT id, contenttype, lengthb(imagedata) FROM images') as $row)
{
    print $row[0].' '.$row[1].' '.$row[2]."\n";
}

• Displaying an image from a database

print "\n[Displaying an image from a database]\n";
try
{
    $stmt = $db->prepare("SELECT contenttype, imagedata FROM images WHERE id = ?");

    $id = 1;
    $stmt->execute(array($id));
    $stmt->bindColumn(1, $type, PDO::PARAM_STR, 256);
    $stmt->bindColumn(2, $lob, PDO::PARAM_LOB);
    $stmt->fetch(PDO::FETCH_BOUND);

    print 'Content-Type: '.$type."\n";
    print 'size: '.file_put_contents($id.".png", $lob)."\n";
}
catch(PDOException $ex)
{
    print $ex->getMessage()."\n";
}