2. PDO Driver

This chapter describes how to use the Altibase PDO driver to interact with Altibase.

The Altibase PDO driver (pdo_altibase) is available as a pecl package from the official Altibase site. The pdo_altibase was developed using the Altibase CLI driver, so the CLI must be installed to use pdo_altibase. It is also affected by the environment variables used by the CLI.

Installation and Setup

Here we describes how to download and install pdo_altibase and set environment.

Download

To use pdo_altibase, you have to use pecl environment. Download the pecl package from the url below.

• Go to http://support.altibase.com/en/product
• Download PDO_ALTIBASE-*.*.*.tgz
  • for PHP 5.3.3, PHP 7.1.20 : PDO_ALTIBASE-1.*.*.tgz
  • for PHP 8.1.8 : PDO_ALTIBASE-2.*.*.tgz

Software requirements

• Altibase : Altibase 6.5.1.5.1 or later

• OS : Linux (Refer the Altibase Release Notes for a detailed version.)

• PHP : PHP 5.3.3, PHP 7.1.20, PHP 8.1.8

Installation

Install pdo_altibase using pecl.

pecl install PDO_ALTIBASE-1.0.0.tgz

Environment Setup

Add extension usage settings to the configuration file such as php.ini.

extension=pdo_altibase.so

Restrictions and Cautions

Restrictions

1. pdo_altibase can not be used with pdo_odbc at the same time. Enabling both extensions can cause unexpected errors.

2. The following items in the PDO manual are not supported:

   • PDO::lastInsertId
   • PDO::getAttribute, PDO::setAttribute
     • PDO::ATTR_CONNECTION_STATUS
     • PDO::ATTR_ORACLE_NULLS
     • PDO::ATTR_PERSISTENT
     • PDO::ATTR_SERVER_INFO
     • PDO::ATTR_STRINGIFY_FETCHES
     • PDO::ATTR_EMULATE_PREPARES
     • PDO::MYSQL_ATTR_USE_BUFFERED_QUERY
   • PDOStatement::getAttribute, PDOStatement::setAttribute
     • PDO::ATTR_CURSOR_NAME
   • PDOStatement::getColumnMeta

3. For other restrictions, refer to parameter bindings, column bindings, and other binding constraints..

Cautions

• The default commit mode for pdo_altibase is independent of the server's configuration and is always autocommit. In other words, even if the default server is set to non-autocommit, the commit mode is autocommit by default.
• When closing a pdo object, the task is rolled back unless commit or rollback is specified explicitly.
• When binding an empty string ('') to numeric data types in Altibase DB, for both native types (double, real, bigint, integer, smallint) and non-native types (numeric, decimal, number, float) It is entered as NULL.
• "Date type value-?" In the query "ERR-21038: Literals in the input do not match the format string." An error occurs. In this case, the date type value includes a date type column and a date type return function.This error occurs because pdo_altibase internally treats strings as numeric values when the Altibase query processor attempts to convert strings to date types when string subtraction is performed on date type values. In this case, replace "?" With "to_number (?)" and etc can be changed explicitly to have a data type other than string.

How to Use

DSN Configuration

The DSN configuration of pdo_altibase is as follows.

Attribute	Description
DSN prefix	"altibase"
Server	Host name or IP address
Port	Server port number
NLS_USE	Character set. US7ASCII is the default and the specified value is used when the environment variable ALTIBASE_NLS_USE is set.

Example

"altibase:Server=127.0.0.1;Port=20333;NLS_USE=US7ASCII"

Connection Attributes

The following properties are available for PDO :: getAttribute() and PDO :: setAttribute(). See "Restrictions and Cautions" for attributes that are not available.

Property ID	Description
PDO::ALTIBASE_DATE_FORMAT	The format to use for representing DATE. Refer to "Changing the DATE Format".
PDO::ALTIBASE_EXPLAIN_PLAN	Whether or not to get a plan of action. - PDO :: ALTIBASE_EXPLAIN_PLAN_OFF: Do not get a plan of action. - PDO :: ALTIBASE_EXPLAIN_PLAN_ON: Obtain the determined performance plan after Prepare and Execution. - PDO :: ALTIBASE_EXPLAIN_PLAN_ONLY: After Prepare Obtain the execution plan determined before Execution. For more information, see Using Samples > Checking Action Plans.
PDO::ALTIBASE_DEFER_PROTOCOLS	Prepare and execute the call repeatedly to set the protocol optimization to improve the performance of the program written. To use this attribute, one connection object should not be shared by multiple threads. Also, for performance, you need to write a program with a structure that calls prepare once and then calls execute repeatedly. - 0 : Do not optimize the protocol (default) - 1 : execute related protocol optimization - 2 : execute, close Related protocol optimization ex> $db->setAttribute(PDO::ALTIBASE_DEFER_PROTOCOLS, 1);

Parameter Binding

pdo_altibase does not support named parameters.

The fourth argument, length, is used only for the current hint and is not treated as a valid value. If length is required, it should be truncated instead of the length argument.

Binding Example

$stmt = $db->prepare("SELECT * FROM t1 WHERE val = ? OR val = ?");
$stmt->bindParam(1, $val1);
$stmt->bindParam(2, $val2);
$stmt->execute();

Column Binding

pdo_altibase does not affect the behavior if you specify type and maxlen of bindColumn().

Depending on the SQL data type, the value is taken as a binary or string, and the processing follows the PDO operation.

Other Binding Constraints

BIT, VARBIT, BYTE, VARBYTE, NIBBLE, LOB, and GEOMETRY types are limited. It is limited to a few queries such as simple INSERT.

Examples

Connection Setup and Query Execution

$db = new PDO("altibase:Server=127.0.0.1;Port=20333", "sys", "manager");
foreach ($db->query("SELECT * FROM dual") as $row) {
    print_r($row);
}

DATE Format Setup

You can set which string format to use as the default for DATE.

$db->setAttribute(PDO::ALTIBASE_DATE_FORMAT, "YYYY-MM-DD");
$attr_dateform = $db->getAttribute(PDO::ALTIBASE_DATE_FORMAT);
echo "attr_dateform = $attr_dateform\n";
$stmt->execute();
echo $stmt->fetchColumn(), "\n";

Output Result

attr_dateform = YYYY-MM-DD
2017-04-18

Execution Plan Check

The PDO object's setAttribute() function can be used to set whether or not to execute the plan.

$attr_plan = $db->getAttribute(PDO::ALTIBASE_EXPLAIN_PLAN);
echo "attr_plan = $attr_plan\n";

$db->setAttribute(PDO::ALTIBASE_EXPLAIN_PLAN, PDO::ALTIBASE_EXPLAIN_PLAN_ONLY);
$attr_plan = $db->getAttribute(PDO::ALTIBASE_EXPLAIN_PLAN);
echo "attr_plan = $attr_plan\n";
$stmt = $db->prepare("SELECT * FROM dual");
$stmt->execute();
print_r($stmt->fetchAll());
echo $stmt->getPlanText();
$stmt = null;
$db->setAttribute(PDO::ALTIBASE_EXPLAIN_PLAN, PDO::ALTIBASE_EXPLAIN_PLAN_ON);
$attr_plan = $db->getAttribute(PDO::ALTIBASE_EXPLAIN_PLAN);
echo "attr_plan = $attr_plan\n";
$stmt = $db->prepare("SELECT * FROM dual");
$stmt->execute();
print_r($stmt->fetchAll());
echo $stmt->getPlanText();
$stmt = null;

Output Result

attr_plan = 0
attr_plan = 2
Array
(
    [0] => Array
        (
            [dummy] => X
            [0] => X
        )
)
------------------------------------------------------------
PROJECT ( COLUMN_COUNT: 1, TUPLE_SIZE: 3, COST: 0.01 )
 SCAN ( TABLE: DUAL, FULL SCAN, ACCESS: ??, COST: 0.01 )
------------------------------------------------------------
attr_plan = 1
Array
(
    [0] => Array
        (
            [dummy] => X
            [0] => X
        )
)
------------------------------------------------------------
PROJECT ( COLUMN_COUNT: 1, TUPLE_SIZE: 3, COST: 0.01 )
 SCAN ( TABLE: DUAL, FULL SCAN, ACCESS: 1, COST: 0.01 )
------------------------------------------------------------

Cursor Holding

Executiong Commit and Rollback command will still maintain the cursor being fetched.

// AUTOCOMMIT attribute must be false to use HOLD
$db->setAttribute(PDO::ATTR_AUTOCOMMIT, false);

// HOLD attribute should be given when preparing
$stmt_sel = $db->prepare("SELECT * FROM pdo_hold", array(PDO::ALTIBASE_CURSOR_HOLD => PDO::ALTIBASE_CURSOR_HOLD_ON));
$stmt_del = $db->prepare("DELETE pdo_hold WHERE id = ?");

// TODO

// To replace AUTOCOMMIT you need to clean up all stmt
unset($stmt_sel);
unset($stmt_del);
$db->setAttribute(PDO::ATTR_AUTOCOMMIT, true);