1. Connecting to the database
  2. SELECTing Data
    1. Basic Loop
    2. Execute Options
    3. Limiting Results
    4. How many values returned?
    5. Getting Column Values
      1. ResultSet Indexing
    6. Scrolling ResultSet Methods
    7. ResultSetIterator
  3. Updating the Database
    1. More on PreparedStatements?
      1. Basic Example
      2. Using Generic set() Method
      3. Using Parameters Array
  4. Working with LOB (Locator Object) Columns
    1. Inserting a binary file into your database
    2. Retrieving a binary file from your database
    3. Using raw strings instead of creole.Blob, creole.Clob
      1. Setting LOB values from string
      2. Retrieving LOB values to string
  5. Stored Procedures
  6. DB Metadata
  7. Appendix A: Using Exceptions in Creole

Creole Guide

Creole is inspired by Java's JDBC API. If you are familiar with this API, then no doubt much of this will look similar. While Creole seeks to work in the JDBC idiom, it also adopts some conventions and conveniences of the PEAR::DB and PEAR::MDB (both available at http://pear.php.net/) db abstraction packages.

Connecting to the database

Creole uses PEAR-style DSN arrays for connecting to the database. Creole also provides a parseDSN() method (also from PEAR) for converting a connection URL to the DSN hash. 

<?php
$dsn = array('phptype' => 'mysql',
             'hostspec' => 'localhost',
             'username' => 'dbuser',
             'password' => 'dbpass',
             'database' => 'testdb');

$conn = Creole::getConnection($dsn);

This could also be written as: 

<?php
$dsn = "mysql://dbuser:dbpass@localhost/testdb";
$conn = Creole::getConnection($dsn);

The second parameter to DriverManager::getConnection() is a flags parameter. The following flags are currently supported by Creole:

FlagDescription
Creole::PERSISTENTOpen persistent database connection
Creole::COMPAT_ASSOC_LOWERAlways lowercase the indexes of assoc arrays (returned by functions like *_fetch_assoc())
Creole::COMPAT_RTRIM_STRINGTrim whitepace from end of string column types
Creole::COMPAT_ALLEnable all compatibility constants

These flags are bits, so you may specify more than one by adding (or using bitwise | operator) them together. 

<?php
$conn = Creole::getConnection($dsn, Creole::PERSISTENT | Creole::COMPAT_ASSOC_LOWER);

SELECTing Data

Executing queries (SQL that returns results, as opposed to SQL that changes the database) is accomplished using the executeQuery() method. The executeQuery() method will return a ResultSet? subclass for your RDBMS.

Basic Loop

Here's a very basic query and result-fetch loop: 

<?php
$rs = $conn->executeQuery("SELECT id, name FROM users");
while($rs->next()) {
  echo $rs->getString("name") . " (" . $rs->getInt("id") . ")\n"
}

Execute Options

There are a few options for executing queries using Creole:

  • Connection::executeQuery($sql)
    • most direct execute method
    • no support for specifying limit/offset
    • no support for replacing params in SQL
  • Statement::executeQuery()
    • Slightly more overhead, since Statement class instantiated: $conn->createStatement()->executeQuery($sql);
  • !PreparedStatement::executeQuery()
    • Recommended method for executing queries with values set from PHP script.

Limiting Results

Creole supports setting LIMIT/OFFSET for queries. For drivers that support this in SQL, the SQL query will be modified. For other drivers (e.g. MS SQL Server) the LIMIT/OFFSET will be emulated by the ResultSet. The behavior of the ResultSet methods will be identical regardless of whether LIMIT/OFFSET is emulated or performed natively (in SQL).

You must use a Statement object if you want to limit results. 

<?php
// non-prepared statement
$stmt = $conn->createStatement();
$stmt->setLimit(10);
$stmt->setOffset(5);

$rs = $stmt->executeQuery("SELECT * FROM user");
// for MySQL transformed to SELECT * FROM user LIMIT 10,5

// prepared statement
$stmt = $conn->prepareStatement("SELECT * FROM user WHERE id = ?");
$stmt->setInt(1, $id);
$stmt->setLimit(10);
$stmt->setOffset(5);

$rs = $stmt->executeQuery();

Creole does not support using OFFSET w/o LIMIT. Some drivers (e.g. Oracle) can do this natively, but others (MySQL, PostgreSQL, SQLite) cannot.

How many values returned?

Use the getRecordCount() method to return the number of records returned by current SELECT statement.

If you specified a LIMIT/OFFSET then getRecordCount() will only return the number of results after LIMIT/OFFSET has been applied.

Getting Column Values

The default method to get column values it to use the appropriate get*() method for the column datatype. E.g. use getString() to return data from a CHAR/VARCHAR/TEXT column. Creole will perform any unescaping and type conversions. For example: 

<?php
while($rs->next()) {
  print "\n" . $rs->getString("name"); // returns PHP string
  print "\n" . $rs->getInt("id"); // returns PHP int/long
  print "\n" . $rs->getTimestamp("stamp", "%c"); // returns formatted date
  //(2nd param can be either date() or strftime() format str)
}

You can also use the generic !ResultSet::get() method, if you do not want to perform any type conversions on the data you are returning: 

<?php
while($rs->next()) {
  echo $rs->get("name") . " (" . $rs->get("id") . ")\n";  
}

And finally, you can also use the getRow() to return an associative array of the current row: 

<?php
while ($rs->next()) {
  $row = $rs->getRow();
  foreach($row as $key => $value) {
     print "$key: $value\n";
  }
}

The advantage to using the get*() type-converting methods over doing the casting yourself is that the type-converting methods will return null if the database returns null. If you are doing the type conversions yourself you would have to check for this: 

<?php
$val = $rs->get("id");
if ($val !== null) $val = (int) $val;

Also you cannot rely on the type of the returned results. Many (most?) native PHP db driver methods will return strings for every column type, but others (e.g. MS SQL Server) will return integers for numeric columns, etc. So if you are doing type-sensitive PHP scripting, you should always use the type-casting get*() methods.

ResultSet Indexing

By default results are indexed by field name. You can specify numeric indexing when executing the query.

Following JDBC convention, numerically indexed columns start with 1. 

<?php
$sql = "SELECT login, login_stamp FROM user";
$rs = $conn->executeQuery($sql, ResultSet::FETCHMODE_NUM);

while($rs->next()) {
  print "Login: " . $rs->getString(1);
  print "Last login: " . $rs->getTimestamp(2, "m/d/y H:i:s");
}

Creole does not support both numeric and assoc indexing (e.g. default results of mysql_fetch_array()) because not all drivers handle this natively.

Compatibility note: For database-neutral code, you should consider using the Creole::COMPAT_ASSOC_LOWER flag to always lowercase the fieldnames in resulsets. Different databases behave quite differently with regard to case sensitivity in result set fields:

* MySQL returns mixed-case results that match the way the case is described in the select statement (or that maches the case of column in db if you do "SELECT *"). * PostgreSQL always returns lowercase column names unless you explicitly quote the column names in the SELECT clause. * Oracle always returns uppercase column names. * MS SQL Server returns mixed-case column names. * SQLite can be configured to do any of the above

We introduced this portability feature so that developers could avoid doing massive code rewrites when moving to a new database. Disabling it will (depding on num of cols in query, etc.) produce a slight performance increase for databases where array_change_key_case() is being used (e.g. MySQL, Oracle).

Scrolling ResultSet Methods

Scrolling ResultSet features are supported well in MySQL, PostgreSQL, and SQLite. For Oracle, however, this behavior has to be emulated. For this reason, reverse scrolling is not currently supported in Oracle. 

<?php

// Move to first record
$rs->first();

// Move ahead 3 rows
$rs->relative(3);

// Scroll backwards
$rs->previous();

// Move to last position
$rs->last();

ResultSetIterator

In addition to using the traditional scrolling functions, you can also use an SPL Iterator to iterate over the query results. Some drivers (e.g. SQLite) have optimized iterators that will be as fast or faster than the traditional scrolling methods. 

<?php
$rs = $conn->executeQuery("SELECT * FROM user");
foreach($rs as $row) {
  print_r($row); // $row is an assoc array
}

This method of iterating over a ResultSet? exists for convenience, and does not provide any type-conversion methods.

Note that the ResultSetIterator will reset the cursor to the beginning of the recordset if it is not already at the beginning. For Oracle, which doesn't support reverse scrolling, this will cause a SQLException to be thrown. To be safe, therefore, you should not mix Iterators with traditional scrolling functions and you should re-run the query if you need to iterate over the results a second time.

Updating the Database

To perform a database update, use the executeUpdate() method. executeUpdate() will return the number of affected rows, rather than a ResultSet?. Similar, to executeQuery(), there are several options for calling executeUpdate(): 

<?php
$numaffected = $conn->executeUpdate("DELETE from user");
// or
$stmt = $conn->createStatement();
$numaffected = $stmt->executeUpdate("DELETE from user");
// or
$stmt = $conn->prepareStatement("DELETE form user WHERE id = ?");
$stmt->setInt(1, 2);
$numaffected = $stmt->executeUpdate();

More on PreparedStatements?

The recommended means of performing database queries and updates is using prepared queries. Prepared queries will be emulated if the native driver does not support them; there is a very small performance penalty for the preparation, but you gain the ability to use the set*() (and generic set()) methods to ensure that data is properly formatted for the database.

/You should always use the set*() methods to add values to a SQL statement, as this will prevent SQL injection attacks by ensuring that data is converted to the proper type, and when appropriate escaped & quoted./

Basic Example

<?php
$stmt = $conn->prepareStatement("INSERT INTO users (id, name, created) VALUES (?,?,?)");
$stmt->setInt(1, $id);
$stmt->setString(2, $name);
$stmt->setTimestamp(3, time());
$stmt->executeUpdate();

Using Generic set() Method

A generic set() method uses the PHP native type, to guess which setter method to invoke. 

<?php
$stmt = $conn->prepareStatement("INSERT INTO users (id, name, created) VALUES (?,?,?)");

$stmt->set(1, 2); // setInt()
$stmt->set(2, "Myname"); // setString()

include_once 'Date.php'; // using PEAR Date
$stmt->set(3, new Date(time())); // setTimestamp()

$stmt->executeUpdate();

Using Parameters Array

You may also pass an array of parameters to PreparedStatement::executeUpdate() and PreparedStatement::executeQuery(). 

<?php
$stmt = $conn->prepareStatement("INSERT INTO users (id, name, created) VALUES (?,?,?)");

include_once 'Date.php'; // using PEAR Date

$stmt->executeUpdate(array(2, "Myname", new Date(time()));

The PHP type of the parameters is used to guess at which setter method should be called. The code above is identical to: 

<?php
$stmt = $conn->prepareStatement("INSERT INTO users (id, name, created) VALUES (?,?,?)");

$stmt->set(1,2);
$stmt->set(2, "Myname");
include_once 'Date.php'; // using PEAR Date
$stmt->set(3, new Date(time()));

Note that because this method relies on PHP types, wrapper classes have to be used for things like Date values which otherwise would look like string (or integer).

Working with LOB (Locator Object) Columns

Creole has a very simple API for working with BLOB / CLOB columns. We'll look here at how to insert a file into a database and then how to read it back out and dump it to the browser.

/CLOB values are handled using the Clob class which has the same API as the Blob class and can be set/retrieved using the PreparedStatement?->setClob() and ResultSet?->getBlob() methods, respectively./

Inserting a binary file into your database

Here's an example of how to insert an images into your database. 

<?php
include_once 'creole/util/Blob.php';
$blob = new Blob();
$blob->setInputFile('/path/to/your/file.gif');

$sql = "INSERT INTO blobtable (name, image) VALUES (?,?)";
$stmt = $con->prepareStatement($sql);
$stmt->setString(1, 'file.gif');
$stmt->setBlob(2, $blob);
$stmt->executeUpdate();

Retrieving a binary file from your database

Here's an example of how to retrieve the image we just inserted. 

<?php
$sql = "SELECT name, image FROM blobtable";
$rs = $con->executeQuery($sql);
$rs->next(); // grab first rec

$name = $rs->getString("name");
$blob = $rs->getBlob("image");

// Now you can choose to either save it to file
$blob->writeToFile('/path/to/new/file.gif');

// Or you could dump the contents straight to the browser 
// (you'd want to precede it with the correct header, of course)
$blob->dump();

Note that some database backends have specific requirements about BLOBs. Creole handles the proper escaping of BLOB values (and integration w/ native BLOB commands if they exist, e.g. as in Oracle) but does not modify queries or perform other database operations that might be required. For example, in PostgreSQL you need to enclose any BLOB select or update statements in a transaction; Creole will not (cannot, in the case of SELECT statements) do this for you.

Using raw strings instead of creole.Blob, creole.Clob

Setting LOB values from string

You can also use strings containing file contents directly if you need a more consistent API for getting/setting values (e.g. in the case where calling code is just blindling calling a setter and passing a value). 

<?php
// you can pass the data directly to 
$file_contents = file_get_contents('/path/to/file.txt');
$stmt->setBlob(1, $file_contents);

Internally the passed string may be wrapped in a Blob object if there is an advantage to doing so (e.g. Oracle's LOB support makes it advantageous to work with the Blob/Clob objects, since it can read directly from file, etc.)

Retrieving LOB values to string

When retrieving Blob/Clob values using getBlob() / getClob() you will always get the respective creole.util.Blob or creole.util.Clob object. These classes, however, have the magic toString() method and a getContents() method which will return the value as a string. 

<?php
$blob = $rs->getBlob(1);
// $blob isA creole.util.Blob class

$blob = $rs->getBlob(1)->getContents(); 
// $blob is now the string contents of the creole.util.Blob class

// similarly:
$blob = $rs->getBlob(1);
print $blob; // implicit __toString() call

Stored Procedures

Creole supports CallableStatement? for invoking stored procedures on systems that have special stored procedure API. Currently this only applies to MS SQL Server. Unlike JDBC, Creole does not abstract the syntax for stored procedures. Each RDBMS may have it's own syntax for bound variables, getting the return value, etc.

The following is based on the example for MSSQL stored procedures in the PHP online manual.

Using the following stored procedure in an MS SQL Server database: 

CREATE PROCEDURE [myprocedure] 
( 
   @sval varchar(50) OUTPUT, 
   @intval int OUTPUT, 
   @floatval decimal(6,4) OUTPUT 
) AS 

if @intval is null 
    select '@intval is null' as answer 
else     
    select '@intval is NOT null' as answer 

set @sval='Hello ' + @sval 
set @intval=@intval+1 
set @floatval=@floatval+1 

return 10

You would use the following code to invoke and return the value for this stored procedure: 

<?php
$stmt = $conn->prepareCall("myprocedure");

// 3rd param, specifies whether we want
// to get a return value for this parameter
$stmt->setString("@sval", "Frank", true);
$stmt->setInt("@intval", 11, true);
$stmt->setFloat("@floatval", 2.1416, true);

// for other (non-input) variables, you
// use a registerOutParameter() method.  This method
// does not apply to input variables (above)
$stmt->registerOutParameter("RETVAL", CreoleTypes::INTEGER);
    
// get the returned results
$result = $stmt->executeQuery(ResultSet::FETCHMODE_NUM); 
$result->next();
    
print "Answer: " . $result->getString(1) . "\n"; 

// MSSQL "quirk"
// must advance to end of result sets (i.e. there could be more
// than one result set returned by a stored proc).  If you do
// not do this, then the output parameters will not be set (changed).
$stmt->getMoreResults();

// now the out-params can be fetched
$val = $stmt->getInt("RETVAL");
$ival = $stmt->getInt("@intval");
$fval = $stmt->getFloat("@floatval");
$sval = $stmt->getString("@sval");
print "RETVAL = $val ; intval = $ival ; floatval = $fval ; string = $sval\n";

DB Metadata

Creole offers a simpler metadata solution than the DatabaseMetadata? and ResultSetMetadata? JDBC classes. The database "Info" classes are loosely based on some ((http://www.hibernate.org)(Hibernate)) (Java) classes, and provide very comprehensive data about a database. 

<?php
$dbinfo = $conn->getDatabaseInfo();
foreach($dbinfo->getTables() as $tbl) {
    print "Table: " . $tbl->getName() . "\n";
    foreach($tbl->getColumns() as $col) {
        print "\t".$col->getName()." (".$col->getNativeType().")";
        // see API docs for other methods
    }
    $pk = $tbl->getPrimaryKey();
    foreach($pk->getColumns() as $pkcol) {
        print "Pk col: ".$pkcol->getName();
    }
    // other TableInfo methods include getForeignKeys(), getIndexes().
}

Appendix A: Using Exceptions in Creole

One of the reasons why Creole was originally written for PHP5 was to take advantages of the new PHP5 support of Exception and the try/catch/throw language constructs. Methods in the Creole classes will throw only /SQLException/ objects; SQLException is a subclass of the built-in Exception class and contains a few additional properties (e.g. the native driver error and the SQL statement that triggered the error).

A full explanation of how to use Exceptions is beyond the scope of this guide; I would refer you to the PHP5 Exceptions documentation .

Here's an example of how you might execute a query & print any errors that occur: 

<?php
try {
  $conn->executeQuery($sql);
} catch (SQLException $sqle) {
  print "There was an error executing $sql\n";
  print $sqle;  // will implicitly invoke __toString()
}

Exceptions can be particularly useful when using transactions, as you can rollback the transaction in the catch block. For example: 

<?php
try {
  $conn->setAutoCommit(false); // start a transaction
  $conn->executeUpdate("DELETE FROM mytable WHERE id = 3");
  $conn->executeUpdate("DELETE FROM myothertable WHERE id = 4");
  $conn->executeUpdate("UDPATE stillanothertable SET col = 1 WHERE id = 5");
  $conn->commit(); // commit the transaction
} catch (Exception $e) {
  $conn->rollback(); // abort all delete/update queries in the transaction
  print "Aborted the transaction because: " . $e->getMessage();
}