Configuring PHP
My first mistake when configuring PHP to support Oracle was to assume that it would be as easy as MySQL; that is to say, on Windows, simply enabling the Oracle DLL that ships with PHP. No such luck, and the error message you see — when you finally remember to check your PHP error log — isn't very helpful:
PHP Warning: PHP Startup: Unable to load dynamic library 'C:\Program Files\Zend\Core\lib\phpext\php_oci8.dll' - The specified module could not be found.
in Unknown on line 0
Thanfully, I found a document on the Oracle website that explains how to configure PHP on Linux and Windows for Oracle support. I'd say this was the first straightforwardly helpful piece of Oracle documentation I'd ever used. Frustrations nevertheless ensued. When I downloaded Oracle 10g Express to load on my desktop, the website mentioned that I wouldn't need any additional Oracle client downloads. (It was a gigantic enough download!) So, naturally, I figured that the client libraries needed to for PHP/Oracle support would all be somewhere in my install folder. Incorrect! As the configuration instructions suggested, I also had to download the (39-megabyte) Instant Client Basic for Windows, which contained all three libraries. I plunked them into a new directory, put that directory on my path, and revved up PHP. This time, no errors.
Easy Alternative: Zend Core offers a "for Oracle" version with Oracle support built in, out of the box.
Configuring CodeIgniter
I've started using CodeIgniter heavily in the last few months, and it's a real time-saver once you get the hang of it. While I have a couple hangups about the CI User Guide — mainly that it's slanted too severely toward the "quick example" side without comprehensive reference information to fill in the gaps — the user community tends to be quite helpful. Accordingly, the User Guide only says that CI supports an Oracle database, with no instructions on how to complete the built-in database configuration to use Oracle. So, I browsed the forums and found a few folks who had posted their Oracle configurations, and noted that, in general, one's database config should look like this:
$db['default']['hostname'] = "//localhost/XE";
$db['default']['username'] = "USERNAME";
$db['default']['password'] = "PASSWORD";
$db['default']['database'] = "DATABASE_NAME";
$db['default']['dbdriver'] = "oci8";
That hostname field can optionally contain a port number, if it differs from the standard 1521:
$db['default']['hostname'] = "//localhost:9999/XE";
The "XE" in both examples is the default SID that Oracle 10g Express uses. (Unfortunately, I'm still oblivious as to the utility of an SID.) Also note that username, password, and database fields probably ought to be in uppercase — more case sensitivity implications on their way, stay tuned.
That done, we're ready to query for some data. But wait! I tested my existing application code (previously running on a MySQL database) and got these warnings:
ERROR - 2007-12-02 15:02:05 --> Severity: Warning --> ocifetchinto() expects parameter 1 to be resource, null given C:\codeigniter\system\database\drivers\oci8\oci8_result.php 159
ERROR - 2007-12-02 15:02:05 --> Severity: Warning --> ociexecute() expects parameter 1 to be resource, null given C:\codeigniter\system\database\drivers\oci8\oci8_result.php 46
Yet again, the CodeIgniter forum came to the rescue: there's an Oracle-specific bug in CI's platform-independent database abstraction class. It's fixed with a simple cut-and-paste. Around line 324, the $RES->num_rows line needs to be moved down below the if block, a la:
$driver = $this->load_rdriver(); $RES = new $driver(); $RES->conn_id = $this->conn_id; $RES->result_id = $this->result_id; if ($this->dbdriver == 'oci8') { $RES->stmt_id = $this->stmt_id; $RES->curs_id = NULL; $RES->limit_used = $this->limit_used; } $RES->num_rows = $RES->num_rows(); No more warnings.
Using Active Record
CodeIgniter's Active Record class makes most database queries dead simple and, combined with centralized configuration and automatic connections, cuts down significantly on lines of code. Using Active Record in my model class, I have a login method which checks a users table for the proper username and password:
$query = $this->db->getwhere('users', array('username' => $username, 'password' => md5($password)));
Then, I check the result for an active user by inspecting the flags field for a non-negative value:
if (isset($query->row()->flags) && $query->row()->flags >= 0) { ... }
For some reason, I just couldn't log in, but I wasn't getting any error messages from PHP or CodeIgniter. Then, I recalled my case sensitivity wrangling from earlier, and my cogs started turning. My database had been constructed with all uppercase table names and column names, so perhaps my result's fields would be uppercase, too. A simple var_dump of the result object proved my theory:
object(CI_DB_oci8_result)#17 (9) { ["stmt_id"]=> resource(33) of type (oci8 statement) ["curs_id"]=> NULL ["limit_used"]=> NULL ["conn_id"]=> resource(24) of type (oci8 persistent connection) ["result_id"]=> bool(true) ["result_array"]=> array(1) { [0]=> array(3) { ["USERNAME"]=> string(8) "username" ["PASSWORD"]=> string(32) "..." ["FLAGS"]=> string(1) "0" } } ["result_object"]=> array(0) { } ["current_row"]=> int(0) ["num_rows"]=> int(1) } Now, I reference the uppercase member variables in my code and it all works fine:
if (isset($query->row()->FLAGS) && $query->row()->FLAGS >= 0) { ... }
I'm not sure whom to implicate for the misunderstanding. When I write SQL queries — against MySQL or Oracle — my schema, table, and columns names may be written in any case, and I get a proper result. Shouldn't that case insensitivity carry through into Active Record and/or PHP?
Jonathan. This is a great rundown. Thanks for sharing with us. I've been wanting to play with Oracle and Postgres more lately, and hopefully this will be helpful.
Hi. Intresting article, thanks for sharing your experiense.
After i found this:
function escape_str($str)
{
return $str;
}
in oracle driver in CI, i made a decision to use PDO instead of CI libraries. Using PDO now in a big project and feeling good.
If you understand russian, you can read more in my blog
It looks like the adapter should be using real OCI bind arguments, and not using the generic adapter's bind mechanism. When using OCIBindByName(), the arguments are automatically escaped to Oracle's satisfaction.
Did you try scaffolding, the view data page shows error:
Message: Undefined property: stdClass::$2
Filename: views/view.php
Line Number: 14
I checked views/view.php
foreach($query->result() as $row) shows:
object(CI_DB_oci8_result)#15 (9) {
["stmt_id"]=>
resource(32) of type (oci8 statement)
["curs_id"]=>
NULL
["limit_used"]=>
bool(true)
["conn_id"]=>
resource(27) of type (oci8 persistent connection)
["result_id"]=>
bool(true)
["result_array"]=>
array(1) {
[0]=>
array(1) {
["NUMROWS"]=>
string(1) "2″
}
}
["result_object"]=>
array(1) {
[0]=>
object(stdClass)#14 (1) {
["NUMROWS"]=>
string(1) "2″
}
}
["current_row"]=>
int(0)
["num_rows"]=>
int(1)
}
Any clue?
[…] Using Oracle with PHP and CodeIgniter, Oracle and CI teamed together. I use oracle xe […]
Hello…
how to configure if we use PHP ADODB library to connect database with oracle 9.2 + codeigniter?
[…] Using Oracle with PHP and CodeIgniter […]