Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

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

CUBRID has support for PDO for versions 3.0 and 3.1, and for both PHP 5.2.x and PHP 5.3.x (http://www.cubrid.org/php_cubrid_pdo).

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

In particular, having a CUBRID PDO Driver means that any application that uses PDO as a database interface should work with CUBRID!

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.

CUBRID DSN Example: cubrid:dbname=demodb;host=192.168.0.10;port=30000;

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:

  1. Errors are handling in PDO using a try-catch mechanism
  2. 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:

  1. Use query(…)
  2. Use prepared statements (prepare()/execute())
  3. 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”].

Note: When using query(…), it is your responsibility to ensure that the query code is properly escaped.

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

Remember: Using prepare(…) for SQL statements that will be issued multiple times, with different parameter values, will most likely optimize the overall CUBRID application performance.

Using PDO::getAttribute(…)

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

  1. Driver name
  2. Database versions
  3. Auto-Commit state
  4. Error mode
  5. 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

CUBRID Online Manual

http://www.cubrid.org/webmanual/3.1/

PHP PDO Manual

http://docs.php.net/manual/en/book.pdo.php

CUBRID PDO Manual

http://docs.php.net/manual/en/ref.pdo-cubrid.php

CUBRID PDO Code Examples

http://cubridsrccode.svn.sourceforge.net/viewvc/cubridsrccode/PHP-PDO/

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!

See also

CUBRID PHP API Installer - Install Wizard

The CUBRID PHP API Installer is a Windows Installer which automatically detects the CUBRID and PHP version, and installs the proper driver for...

CUBRID PHP Driver Build Guide for Windows x64

This article will guide you through the steps you need to follow to build Windows x64 CUBRID PHP Driver with PHP 5.3 and VC9. If you are looki...

Install CUBRID with AutoSet

AutoSet is a software bundle for Windows, which provides seamless installation of Apache, PHP, MySQL, CUBRID, and Subversion. It is distributed und...

Tags:




You are either using a very old browser or a browser that is not supported.
In order to browse cubrid.org you need to have one of the following browsers:



Internet Explorer: Mozilla Firefox: Google Chrome: