William Graham’s blog

June 5, 2007

Oracle modifications for Rob Allen’s Zend Framework Tutorial

Filed under: Oracle,PHP,Programming,Zend Framework — liamgraham @ 9:08 pm

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:

—————————–
CREATE TABLE album (
id number(11,0) NOT NULL,
artist varchar2(100) NOT NULL,
title varchar2(100) NOT NULL,
PRIMARY KEY (id)
);

create sequence album_seq
MINVALUE 1
MAXVALUE 999999999999999999999999999
INCREMENT BY 1
START WITH 1
NOCACHE
NOORDER
NOCYCLE ;

CREATE OR REPLACE TRIGGER ALBUM_BEF_UPDATE_TRG
BEFORE INSERT ON ALBUM
FOR EACH ROW
BEGIN
select album_seq.nextval into :new.id from dual;
END;
/

ALTER TRIGGER ALBUM_BEF_UPDATE_TRG ENABLE;

insert into album (artist, title) values (‘James Morrison’, ‘Undiscovered’);
insert into album (artist, title) values (‘Snow Patrol’, ‘Eyes Open’);
commit;

—————————–

Here are the settings to use in your config.ini (Rob, thanks for pointing out that I had initially forgotten to include this!):

—————————–

[general]
db.adapter = Oracle
db.config.host = myDatabaseHostName
db.config.username = myUserName
db.config.password = notGonnaTellYou
db.config.dbname = mySID

—————————–

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:

————–
<label for=”artist”>Artist</label>
escape(trim($this->album->artist));?>”/>
————–

In order for this to work in Oracle, it needs to be modified so that the ‘artist’ attribute is uppercased:

————–
<label for=”artist”>Artist</label>
escape(trim($this->album->ARTIST));?>”/>
————–

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

Blog at WordPress.com.