elie29/oci-driver

OCI Driver

v1.0.20 2021-06-21 08:09 UTC

Requires

• php: ^7.1
• ext-json: *
• ext-oci8: *

Requires (Dev)

• hamcrest/hamcrest-php: ^2.0
• mayflower/php-codebrowser: ^2.0
• mockery/mockery: ^1.0
• pdepend/pdepend: ^2.5
• phploc/phploc: ^4.0
• phpmd/phpmd: ^2.6
• phpmetrics/phpmetrics: ^2.4
• phpstan/phpstan: ^0.10.3
• phpstan/phpstan-mockery: ^0.10.2
• phpunit/phpunit: ^7.1
• sebastian/phpcpd: ^4.1
• symfony/var-dumper: ^4.0
• zendframework/zend-coding-standard: ^1.0

MIT

• Elie NEHME

README

Documentation

Text file encoding

• UTF-8

Code style formatter

• PSR-2

Installation

Run the command below to install via Composer:

composer require elie29/oci-driver

Getting Started

OCI Query Builder provides a lightweight builder to dynamically create SQL queries. It does not validate the query at all.

Select builder

// SELECT * FROM params ORDER BY name ASC
$sql = Select::start() // aka (new Select)
    ->column('*')
    ->from('params')
    ->orderBy('name')
    ->build();

Select builder with union

// SELECT p.id FROM params p UNION SELECT p.id FROM params_his p ORDER BY id ASC
$sql = Select::start() // aka (new Select)
    ->column('p.id')
    ->from('params', 'p')
    ->union()
    ->column('p.id')
    ->from('params_his', 'p')
    ->orderBy('id')
    ->build();

Delete builder

// DELETE FROM params WHERE id = 2
$sql = Delete::start() // aka (new Delete)
    ->from('params')
    ->where('id = 2')
    ->build();

Update builder

// UPDATE users u SET u.name = 'O''neil' WHERE u.user_id = 1
$sql = Update::start() // aka (new Update)
    ->table('users', 'u')
    ->set('u.name', Update::quote("O'neil"))
    ->where('u.user_id = 1')
    ->build();

Insert builder

// INSERT INTO params (user_id, name) VALUES (:id, :name)
$sql = Insert::start() // aka (new Insert)
    ->into('params')
    ->values([
        'user_id' => ':id',
        'name'    => ':name',
    ])
    ->build();

More examples are found in tests/OCI/Query/Builder folder.

Using OCI Driver Class

Using the factory

Factory will automatically alter the session (@see OCI\Driver\Helper\SessionInit.php) in order to fix NLS_TIME_FORMAT and NLS_NUMERIC_CHARACTERS. So we won't need to use to_char or to_date to convert the format, especially in comparing dates with a given date:

$driver = Factory::create(Provider::getConnection(), 'test');

$sql = 'SELECT * FROM A1 WHERE N_DATE BETWEEN :YESTERDAY AND :TOMORROW';

$bind = (new Parameter())
    ->add(':YESTERDAY', date(Format::PHP_DATE, time() - 86400)) // N_DATE type is DATE
    ->add(':TOMORROW', date(Format::PHP_DATE, time() + 86400));

$rows = $driver->fetchAllAssoc($sql, $bind);

Insert/Update Example

With Autocommit

Autocommit is the default behaviour of OCI Driver:

$connection = oci_pconnect('username', 'pass', 'schema', 'UTF8');
$driver = Factory::create($connection, 'dev');

$sql = 'INSERT INTO A1 (N_NUM) VALUES (5)';
$count = $driver->executeUpdate($sql);
echo $count; // displays 1

With Transaction

In order to start a transaction, you should use beginTransaction as follow:

$connection = oci_pconnect('username', 'pass', 'schema', 'UTF8');
$driver = Factory::create($connection, 'dev');

$driver->beginTransaction();

try {
   $count = $driver->executeUpdate($sql);
   $driver->commitTransaction();
   echo $count; // displays 1
} catch (DriverException $e) {
   echo $e->getMessage();
}

N.B.: When an error occurred using a transaction, rollback is called automatically.

Bind parameters

$connection = oci_pconnect('username', 'pass', 'schema', 'UTF8');
$driver = Factory::create($connection, 'dev');

$sql = 'INSERT INTO A1 (N_CHAR, N_NUM, N_NUM_3) VALUES (:N1, :N2, :N3)';

$parameter = (new Parameter())
    ->add(':N1', 'c')
    ->add(':N2', 1)
    ->add(':N3', 0.24);

$count = $driver->executeUpdate($sql, $parameter);
echo $count; // displays 1

Fetch one row

$connection = oci_pconnect('username', 'pass', 'schema', 'UTF8');
$driver = Factory::create($connection, 'dev');

$sql = 'SELECT * FROM A1 WHERE N_NUM = 2';

$row = $driver->fetchAssoc($sql);

N.B.: For binding parameters, follow the same insertion example above.

Fetch many rows

$connection = oci_pconnect('username', 'pass', 'schema', 'UTF8');
$driver = Factory::create($connection, 'dev');

$sql = 'SELECT * FROM A1';

$rows = $driver->fetchAllAssoc($sql);

N.B.: For binding parameters, follow the same insertion example above.

Prepare for test

Before launching unit tests, you should follow these steps:

Create A1 and A2 tables

In order to launch tests, A1 and A2 tables should be created as follow:

    CREATE TABLE A1
    ("N_CHAR" CHAR(5 BYTE),
     "N_NUM" NUMBER,
     "N_NUM_3" NUMBER(6,3),
     "N_VAR" VARCHAR2,
     "N_CLOB" CLOB,
     "N_DATE" DATE,
     "N_TS" TIMESTAMP,
     "N_LONG" LONG);

    CREATE TABLE A2
    ("N_LONG_RAW" LONG RAW);

Rename config file

Rename config-connection.php.dist in ./tests/OCI/Helper to config-connection.php

   mv config-connection.php.dist config-connection.php

Modify configuration

Modify USERNAME, PASSWORD and SCHEMA according to your Oracle Database Information

SCHEMA could be one of the following:

• SID name if you are executing the tests on the same database server or if you have a configured SID in tnsnames.ora

• IP:PORT/SID eg: 11.22.33.25:12005/HR

• Use the following TNS :

  (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DATABASE_IP)(PORT=DATABASE_PORT))(CONNECT_DATA=(SID=DATABASE_SCHEMA)(SERVER=DEDICATED|POOLED)))

Development Prerequisites

Code style formatter

• Zend Framework coding standard

Composer commands

• clean: Cleans all generated files
• test: Launches clean and php unit test
• cover: Launches unit test and a local server
• cs-check: For code sniffer check
• cs-fix: For code sniffer fix
• check: Launches cs-check and test

Ant commands

This project uses build.xml to perform static analysis and generate project documentation.

You should have apache-ant installed in order to launch ant.

• Run ant -p to print out default and main targets.