Archive for the ‘Oracle’ Category

Multiple databases support on ZF

Wednesday, June 8th, 2011

Zend Framework is nice but learning it is very confusing and time demanding. Official doc isn’t that clear, lacks of good examples and sometimes is outdated. Whenever I want something to be done I read the official documentation and then google it so I can get a good picture of what everyone did.

One thing that I recently needed was to enable multiple schema support for a specific project. One schema stores client info, the other stores products. One very interesting thing is that the products schema depends on the account so clients for region 1 should use a schema called reg1, while clients from region 2 a schema called reg2.

So the products schema kind of switches dynamically according to users setup.

The first thing to do is setup the config file. This is the easy part, I added some lines that looked like this:

resources.multidb.maindb.adapter = oracle
resources.multidb.maindb.persistent = true
resources.multidb.maindb.host =
resources.multidb.maindb.username = usr
resources.multidb.maindb.password = usr123
resources.multidb.maindb.dbname = "(DESCRIPTION= (ADDRESS= (PROTOCOL=TCP)(HOST=oracle.host.com)(PORT=1521)) (ADDRESS= (PROTOCOL=TCP)(HOST=oracle2.host.com)(PORT=1521)) (LOAD_BALANCE=yes) (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=srvcnm)))"

resources.multidb.seconddb.adapter = oracle
resources.multidb.seconddb.persistent = true
resources.multidb.seconddb.host =
resources.multidb.seconddb.username = usr2
resources.multidb.seconddb.password = usr2123
resources.multidb.seconddb.dbname = "(DESCRIPTION= (ADDRESS= (PROTOCOL=TCP)(HOST=soracle.host.com)(PORT=1521)) (ADDRESS= (PROTOCOL=TCP)(HOST=soracle2.host.com)(PORT=1521)) (LOAD_BALANCE=yes) (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=srvcnm2)))"

resources.multidb.thirddb.adapter = oracle
resources.multidb.thirddb.persistent = true
resources.multidb.thirddb.host =
resources.multidb.thirddb.username = usr3
resources.multidb.thirddb.password = usr3123
resources.multidb.thirddb.dbname = "(DESCRIPTION= (ADDRESS= (PROTOCOL=TCP)(HOST=toracle.host.com)(PORT=1521)) (ADDRESS= (PROTOCOL=TCP)(HOST=toracle2.host.com)(PORT=1521)) (LOAD_BALANCE=yes) (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=srvcnm3)))"

Notice I’m storing everything in a multidb array with three different elements: maindb, seconddb and thirddb. Let’s consider the maindb to be the users schema, for products I’ll be switching between seconddb and thirddb.

First of all the main connection needs to be set so we can login, update, delete and o more stuff with a user account. Alo this connection will be set as the default adapter. I did this in the bootstrap. Here is the code:

/**
* Init the main database connection.
*/
protected function _initDatabase(){
    $applicationConfig = new Zend_Config_Ini(APPLICATION_PATH . '/configs/application.ini', APPLICATION_ENV);
    $config = $applicationConfig->toArray();
    $dbIni = $config['resources']['multidb']['maindb'];
    $db = Zend_Db::factory($dbIni['adapter'], $dbIni);
    Model_DbTable_Db::setDefaultAdapter($db);
}

So now we have the main connection up and working as a default adapter. This works the same as a basic database configuration. Now to the interesting part… set up the other database connection according to the user region. In my case the user’s account has a field/property that stores the correct products schema name so one of the things I’ll be doing is reading that property to know which schema I need to connect to for the products details. I won’t explain the account part because it’s quite simple.

We use a baseController from which we inherit all other controllers so we can easily concentrate most of the common methods in one single place. Within that baseController I added the following method:

/**
* Depending on the account we set up the adapter for the products tables
* @param Account A valid account object
*/
private function setProductsSchema(Account $account){
    if(!Zend_Registry::isRegistered('erpdb')){
        $applicationConfig = new Zend_Config_Ini(APPLICATION_PATH . '/configs/application.ini', APPLICATION_ENV);
        $config = $applicationConfig->toArray();
        $dbIni = $config['resources']['multidb'][strtolower($account->getSchemaName())]; // In this case $account->getSchemaName() may return 'seconddb' or 'thirddb' 😀
        $erpdb = Zend_Db::factory($dbIni['adapter'], $dbIni);
        Zend_Registry::set('erpdb',$erpdb);
    }
}

Now the application is able to dynamically choose the proper schema for products according to the account. It’s important to add it to the registry with a name, in this case is erpdb. This is necessary in order to let the DbTable objects know which schema they’ll use. Be sure to call this method before making a query to the secondary schema.

Now let’s set up the DbTable objects so they know which connection they’ll be using. Right now all DbTable objects are using the default adapter set in the bootstrap, for those objects that refer to products we need to make them aware that they’ll be using the secondary connection. doing this is pretty straightforward.

Go to your DbTable object and add the following method:

/**
* Set the default adapter for this object
*/
public function init(){
    $this->_setAdapter('erpdb');
}

That’s it! Notice that we set the adapter name the same as the db registry added before.

And that’s basically all we need to do to make the connection dynamic 😀

Hope it helps.

PHP, Oracle and encodings

Thursday, July 22nd, 2010

As I had previously posted I’m involved in several PHP-Oracle projects.

One involves ecommerce.

We’re doing this ecommerce for Latin America so it should handle special characters such as accents, ñ and others. Web page encoding is iso8859-1.

Every time a user made an insert, the database was recording a ‘?’ character instead of accents or other latin chars.

We tested for utf8_encode and utf8_decode functions, they were useless so we figured it out that the database was storing info in some other encoding. We added apache conf and meta headers to view the page as utf8 but again garbagge was being stored.

We were really angry so we started googling for some answers.

First of all we needed to know what encoding was the database using. We found out with the following query:

select * from NLS_DATABASE_PARAMETERS;

We got this from here.

After that we understood that we needed to enable the proper encoding for Oracle and PHP, how did we do it?

After a while we ended here.

So the solution was very simple. We added the following line to the main conf file:

putenv(“NLS_LANG=AMERICAN_AMERICA.AL32UTF8”);

After that we added utf8_encode and utf8_decode to the code and voilá! everything is working fine!

It was indeed a very exhausting work to figure this out so I’m posting it here to have a quick access to the solution.

PHP & Oracle

Wednesday, July 21st, 2010

Long time no see.

Changed job and been working on fixing an app.

In my new job PHP and Oracle are the standards so I’m back to Oracle and I already had my first issues.

I have a small database with a couple of related tables.

I wanted to do an insert and get the id of the recently added row, searched for it on Google and there it was, I’ll need to use RETURNING…INTO.

It’s pretty simple actually so I coded it, the primary key is an integer that runs a trigger with a sequence, kind of a MySQL autoincrement field.

So this is how the code ended looking:

$query = “SELECT id, field1, field2 FROM table WHERE field1 IS NOT NULL RETURNING id into :id”;

$con = new connection()

$stmt = oci_parse($con,$query);

if($stmt!==false){

$binded = oci_bind_by_name($stmt,’:id’,$id);

$executed = oci_execute($stmt);

if($executed!==false){

$row = oci_fetch_array($stmt,OCI_BOTH+OCI_RETURN_NULLS+OCI_RETURN_LOBS);

}

}

It worked fine for a couple of days, then id started working oddly. I analized the code and found out that id was returning a 3 digits id instead of 4 digits, so I’ll be getting 123 for ids 1230 – 1239 besides the previous original 123. That was messing my database integrity and making my code to fail.

Googled it and found nothing! What the hell was wrong with it?

Finally after a day I calmed down and started to remember the old times when I started to work with Oracle and suddenly I remembered!!!!

I was missing a parameter on an oci function. I added it and voila! It worked!

What was I missing? Simple, I was missing the maxLength parameter for the oci_bind_by_name function.

Finally it ended looking like this:

$binded = oci_bind_by_name($stmt,’:id’,$id,100);

Hope this helps others with the same issue.