CUBRID PDO Driver Tutorial

The scope of this tutorial is to introduce the CUBRID PDO Driver, which implements the PHP Data Objects (PDO) interface.

Note: In this tutorial, we will assume that the reader is already familiar with the PDO basic concepts and architecture.

You can download the files for this tutorial here.

Overview

“The PHP Data Objects (PDO) extension defines a lightweight, consistent interface for accessing databases in PHP.

PDO provides a data-access abstraction layer, which means that, regardless of which database you're using, you use the same functions to issue queries and fetch data. PDO does not provide a database abstraction; it doesn't rewrite SQL or emulate missing features.

PDO ships with PHP 5.1, and is available as a PECL extension for PHP 5.0; PDO requires the new OO features in the core of PHP 5, and so will not run with earlier versions of PHP.” (http://docs.php.net/manual/en/intro.pdo.php).

Using PDO as a database interface layer can have important advantages over “direct” PHP database drivers:

1. Portable PHP code between different databases/Database abstraction
2. SQL Parameters support/Bind support
3. Safer SQLs (syntax verification, escaping, it helps protect against SQL injections etc.)
4. Cleaner programming model

Installing the CUBRID PDO Driver

You can find all the necessary information regarding:

1. Compiling (or Downloading)
2. Installing
3. Configuring

the CUBRID PDO driver at the following online location: http://www.cubrid.org/php_cubrid_pdo.

If you encounter any issues with the installation, please contact the CUBRID team through the dedicated PDO forum, located at http://forum.cubrid.org.

If you want to verify that the CUBRID PDO driver is accessible, you can use the PDO::getAvailableDrivers() function:

<?php
echo'PDO Drivers available: <br />';
foreach(PDO::getAvailableDrivers()as $driver)
{
if($driver =="cubrid"){
echo" - Driver: <b>".$driver.'</b><br />';
}else{
echo" - Driver: ".$driver.'<br />';
}
}    
?>

This script will output all the currently installed PDO drivers:

PDO Drivers available: 
- Driver: mysql
- Driver: pgsql
- Driver: sqlite
- Driver: sqlite2
- Driver: cubrid

Connecting to CUBRID

The CUBRID PDO Data Source Name (DSN) is composed of the following parts (delimited by “;”):

1. DSN prefix: The DSN prefix is always “cubrid”
2. Host: The hostname on which the database server is located
3. Port: The port on which the database server is located
4. Dbname: The name of the database.

Let’s see an example of a simple PHP script which performs a PDO connection to the CUBRID “demodb” database:

<?php
$database ="demodb";
$host ="localhost";
$port ="30000";//use default value
$username ="dba";
$password ="";

try{
//cubrid:host=localhost;port=33000;dbname=demodb
$conn_str ="cubrid:dbname=".$database.";host=".$host.";port=".$port;
echo"PDO connect string: ".$conn_str."<br />";
$db =new PDO($conn_str, $username, $password );
echo"PDO connection created ok!"."<br />";
$db = null;//disconnect
}catch(PDOException $e){
echo"Error: ".$e->getMessage()."<br />";
}
?>

If everything goes ok, the output of this script is:

PDO connect string: cubrid:dbname=demodb;host=localhost;port=30000
PDO connection created ok!

Looking at the code above, you can notice that:

5. Errors are handling in PDO using a try-catch mechanism
6. The connection is closed by assigning NULL to the connection object

Executing a SELECT

In PDO, there is more than one way to choose from, to run a SQL query:

7. Use query(…)
8. Use prepared statements (prepare()/execute())
9. Use exec(…)  

Without going into details (as we specified in the beginning of the tutorial, we assume that the reader is already familiar with the basics of PDO), it depends on the specific query and the application context to recommend a best solution to use.

In the following example, we will use the simplest one – use the query(…) function:

<?php
include("_db_config.php");
include("_db_connect.php");

$sql ="SELECT * FROM code";
echo"Executing SQL: <b>".$sql.'</b><br />';
echo'<br />';

try{
foreach($db->query($sql)as $row){
echo $row['s_name'].' - '. $row['f_name'].'<br />';
}
}catch(PDOException $e){
echo $e->getMessage();
}

$db = null;//disconnect
?>

The output of this script is:

Executing SQL: SELECT * FROM code

X - Mixed
W - Woman
M - Man
B - Bronze
S - Silver
G - Goldie

As you can notice, you can retrieve the return values from the resultset (a PDOStatement object) by using the column names : $rs[“column_name”].

Executing an UPDATE

Let’s see how to execute an UPDATE statement, using CUBRID PDO. We will be using a prepared statement (btw, we could also use, as an alternative, the exec() function) and parameters:

<?php
include("_db_config.php");
include("_db_connect.php");

$s_name ='X';
$f_name ='test';
$sql ="UPDATE code SET f_name=:f_name WHERE s_name=:s_name";

echo"Executing SQL: <b>".$sql.'</b><br />';
echo'<br />';

echo":f_name: <b>".$f_name.'</b><br />';
echo'<br />';
echo":s_name: <b>".$s_name.'</b><br />';
echo'<br />';

$qe = $db->prepare($sql);
$qe->execute(array(':s_name'=>$s_name,':f_name'=>$f_name));

$sql ="SELECT * FROM code";
echo"Executing SQL: <b>".$sql.'</b><br />';
echo'<br />';

try{
foreach($db->query($sql)as $row){
echo $row['s_name'].' - '. $row['f_name'].'<br />';
}
}catch(PDOException $e){
echo $e->getMessage();
}

$db = null;//disconnect  
?>

The output of the script is:

Executing SQL: UPDATE code SET f_name=:f_name WHERE s_name=:s_name

:f_name: test

:s_name: X

Executing SQL: SELECT * FROM code

X - test
W - Woman
M - Man
B - Bronze
S - Silver
G – Goldie

Using Prepare and Bind

Prepared statements are one of the major benefits offered by PDO:

1. SQL prepared statements need to be parsed only once, even if they are executed multiple times, with different parameter values. Therefore, using a prepared statement minimizes the resources and, in general, the prepared statements run faster.
2. Another important advantage is that it helps to prevent SQL injection attacks by eliminating the need to manually quote the parameters (however, if other parts of the SQL query are being built up with unescaped input, SQL injection would still be possible).

In the next example, we will use a prepared statement to retrieve data:

<?php
include("_db_config.php");
include("_db_connect.php");

$sql ="SELECT * FROM code WHERE s_name NOT LIKE :s_name";
echo"Executing SQL: <b>".$sql.'</b><br />';

$s_name ='xyz';
echo":s_name: <b>".$s_name.'</b><br />';

echo'<br />';

try{
$stmt = $db->prepare($sql);

$stmt->bindParam(':s_name', $s_name, PDO::PARAM_STR);
$stmt->execute();

$result = $stmt->fetchAll();
foreach($result as $row)
{
echo $row['s_name'].' - '. $row['f_name'].'<br />';
}
}catch(PDOException $e){
echo $e->getMessage();
}
echo'<br />';

$sql ="SELECT * FROM code WHERE s_name NOT LIKE :s_name";
echo"Executing SQL: <b>".$sql.'</b><br />';

$s_name ='X';
echo":s_name: <b>".$s_name.'</b><br />';

echo'<br />';

try{
$stmt = $db->prepare($sql);

$stmt->bindParam(':s_name', $s_name, PDO::PARAM_STR);
$stmt->execute();

$result = $stmt->fetchAll();
foreach($result as $row)
{
echo $row['s_name'].' - '. $row['f_name'].'<br />';
}
$stmt->closeCursor();
}catch(PDOException $e){
echo $e->getMessage();
}
echo'<br />';

$db = null;//disconnect
?>

The output of this script is:

Executing SQL: SELECT * FROM code WHERE s_name NOT LIKE :s_name
:s_name: xyz

X - Mixed
W - Woman
M - Man
B - Bronze
S - Silver
G - Goldie

Executing SQL: SELECT * FROM code WHERE s_name NOT LIKE :s_name
:s_name: X

W - Woman
M - Man
B - Bronze
S - Silver
G - Goldie

Using PDO::getAttribute(…)

The PDO::getAttribute(…) function is very useful to retrieve the database connection attributes, for example:

3. Driver name
4. Database versions
5. Auto-Commit state
6. Error mode
7. Etc.

Note: To set the attributes values (if they are writable!), the function PDO::setAttribute(…) should be used.

Let’s see a PHP PDO script which uses the getAttribute() function to retrieve the client and the server current versions:

<?php
include("_db_config.php");
include("_db_connect.php");

echo"Driver name: <b>".$db->getAttribute(PDO::ATTR_DRIVER_NAME)."</b>";
echo"<br>";
echo"Client version: <b>".$db->getAttribute(PDO::ATTR_CLIENT_VERSION)."</b>";
echo"<br>";
echo"Server version: <b>".$db->getAttribute(PDO::ATTR_SERVER_VERSION)."</b>";
echo"<br>";

$db = null;//disconnect  
?>

The output of the script, when run on CUBRID 3.0, is:

Driver name: cubrid
Client version: 8.3.0
Server version: 8.3.0.0337

CUBRID PDO extensions

Finally, let’s see an example of using a specific CUBRID PDO extension. A PDO extension is a set of features added “on top” of the standard PDO functionalities. It’s not unusual for a database to extend the standard set of PDO functionalities, by adding specific features.

In CUBRID, such an extension is the function PDO::cubrid_schema(…), which is used to retrieve the database schema/metadata information:

array PDO::cubrid_schema ( int $schema_type [, string $table_name [, string $col_name ]] )

You can find all the details regarding this extension here: http://php.net/manual/en/function.pdo-cubrid_schema.php.

And here below is a script which will return information about the primary key for the “nation” table, using the cubrid_schema(…) function:

<?php
include("_db_config.php");
include("_db_connect.php");
try{
echo"Get PRIMARY KEY for table: <b>nation</b>:<br /><br />";
$pk_list = $db->cubrid_schema(PDO::CUBRID_SCH_PRIMARY_KEY,"nation");
print_r($pk_list);
}catch(PDOException $e){
echo $e->getMessage();
}

$db = null;//disconnect
?>

The output of the script is:

Get PRIMARY KEY for table: nation:
Array ( [0] => Array ( [CLASS_NAME] => nation [ATTR_NAME] => code [KEY_SEQ] => 1 [KEY_NAME] => pk_nation_code ) )

Summary

As you can see from the examples above, using CUBRID PDO Driver is not difficult at all…!

We strongly encourage you to try by yourself the CUBRID PDO implementation, to check for the other functions and features which were not covered in this tutorial.

One other recommendation we make is to try to “port” existing PDO applications to CUBRID, to leverage the CUBRID PDO support and, at the same time, learn PDO programming “by example”.

Links & Resources

This concludes the CUBRID PDO Driver tutorial. Please let us know your feedback and remember to periodically check the CUBRID web site – www.cubrid.org/tutorials - for other tutorials and resources.

Thank you!

Tags:

pdo php