June 5, 2007

Oracle modifications for Rob Allen’s Zend Framework Tutorial

After working with CakePHP, I also decided to give the Zend Framework a try. I initially had some problems with it back at beta 0.9.1, but was told by the DB team lead that many Oracle issues would be fixed by the first release. Well, Zend Framework 1.0.0 Release Candidate 1 is out now, so I revisited it.

I had chosen the tutorial written by Rob Allen (Akra), available here . This is a very neat and well-maintained tutorial on creating a simple Album / CD tracking system. Below is the DDL to create the database objects, converted to Oracle syntax. Note that, as in the CakePHP tutorial work I did earlier, I’m mimicing an auto-increment ID by using a before-insert trigger to assign a value from the sequence:

In addition to the DDL changes, there is some strange behavior due to the fact that Oracle converts all non-quoted database object names to uppercase in the data dictionary. This means that attribute names in your Zend Framework PHP code that refer to column names need to be in uppercase as well. For example, here is an excerpt from _form.phtml from the tutorial:

You will need to make this change everywhere that there is a reference to an attribute which maps to an Oracle column name. Just go through the code in the tutorial and uppercase artist, title, and id when they appear as attributes of album. It makes for rather strange-looking code, but it is the only way that I know of to get the Zend Framework and Oracle to work together. Actually, I take that back … it would probably work if you used quoted identifiers to force Oracle to store lowercase object names, but I hate doing that. It means that anytime you write raw SQL you have to embed database object names in quotes, and it just gets nasty, imho.

One note:
1) DON’T uppercase album in the above, i.e., don’t do this: $this->ALBUM->ARTIST. In this case, ‘album’ is a variable name, not something that needs to map to the Oracle data dictionary. That’s probably pretty obvious, but I thought I’d throw it out there anyway, just in case …

I hope this helps anyone who is trying to get going with Oracle on the Zend Framework. Please contact me with any questions, or post comments. Cheers!
–William

They use ‘CASE_UPPER’, by ‘CASE_LOWER’ and ‘CASE_NATURAL’ are other options. Even though they show it with Db2, you should be able to apply those options to Oracle (perhaps only works with PDO Oracle drivers and not oci8 extension, though).

Sorry, I’ve not tried this out so cannot say for sure whether this’d work for you.

well, I had problems with all things disgussed here. It took me some hours to figure everything out, but know it is working so far.

One more thing I had to do is that our oracle DB is on another DB Server. I tried a lot of things and I copied the TNSNAMES.ORA (I am running an instant client without the tnsnames.ora) connection string into the dbname. To put in the SID only was not working for some reason. Here is an example:
db.config.dbname = “(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = server.name.de)(PORT = 1521)) ) (CONNECT_DATA = (SID = instancename) ) )”

Another thing that might be intresting to know is the possibility to do an insert with the option lastInsertId() +1
I am not sure if that is possible, I just thought that might be intresting to make the code easier to run on another system

Yes, I can see that if you didn’t have a local TNSNAMES file, there would be issues.

Not sure about the lastInsertId. As this is specifically an Oracle ‘cheat sheet’ for Rob’s tutorial, I’m not really concerned about any other DB system … and Rob’s tutorial by itself is obviously fine for MySQL (and by extension, likely very close for Sybase or Microsoft SqlServer, both of which use a similar auto-incrementing primary key approach to MySQL).

This error is because in the class Zend/Db/Adapter/Oracle.php has not defined the method getQuoteIdentifierSymbol() and therefore uses the generic, and this causes the armed select this bad for Oracle.