Creole

Posted by Federico in Components

Description

Creole is inspired by Java’s JDBC API. If you are familiar with this API, then no doubt much of this will look similar.

Class Methods

Please visit the Creole Webiste for more information.

General Usage

Example: Using Creole API

$conn = self::creole();

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

$stmt->executeQuery();

The first parameter to !Model::creole() is a flags parameter. The following flags are currently supported by Creole:

Flag Description
self::PERSISTENT Open persistent database connection
self::COMPAT_ASSOC_LOWER Always lowercase the indexes of assoc arrays (returned by functions like *_fetch_assoc())
self::COMPAT_RTRIM_STRING Trim whitepace from end of string column types
self::COMPAT_ALL Enable all compatibility constants

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

$conn = self::creole(self::PERSISTENT | self::COMPAT_ASSOC_LOWER);

Getting Column Values

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

while($rs->next()) {
         // returns PHP string
        echo "n" . $rs->getString("name");

         // returns PHP int/long
        echo "n" . $rs->getInt("id");

         // returns formatted date
        echo "n" . $rs->getTimestamp("stamp", "m/d/y H:i:s");
}

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:

while($rs->next()) {
        print $rs->get("name") . " (" . $rs->get("id") . ")";
}

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

while ($rs->next()) {
        $row = $rs->getRow();
        foreach ($row as $key => $value) {
                echo $key . ": " . $value;
        }
}

Scrolling ResultSet Methods

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

// 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.

foreach ($rs as $row) {
        // $row is an assoc array
        print_r($row);
}

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


Do you have something to say? Say it below.