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

About these ads

14 Comments

  1. Hi,

    One thing you haven’t mentioned that would be useful, is what did you do in config.ini?

    Regards,

    Rob…

    Comment by Rob... — June 6, 2007 @ 5:42 am

  2. Hi, Rob,

    Doh! Thanks for pointing out that omission! The entry is corrected now … and thanks for the great tutorial!

    All the best,
    William

    Comment by liamgraham — June 6, 2007 @ 12:16 pm

  3. [...] link to some handy tips on combining the Getting Started tutorial with [...]

    Pingback by developercast.com » Rob Allen’s Blog: Tutorial Notes - Zend_Auth in French & Using Oracle — June 6, 2007 @ 1:51 pm

  4. Hi William,

    Perhaps you can use the ATTR_CASE/CASE_LOWER or the CASE_FOLDING/CASE_LOWER attributes? For example, the Zend framework wiki has this example:

    Zend_Db::CASE_UPPER
    );

    $params = array(
    ‘host’ => ‘127.0.0.1’,
    ‘username’ => ‘webuser’,
    ‘password’ => ‘xxxxxxxx’,
    ‘dbname’ => ‘test’,
    ‘options’ => $options
    );

    $db = Zend_Db::factory(‘Db2′, $params);

    ?>

    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.

    Andy

    Comment by Andy — June 23, 2007 @ 10:38 pm

  5. Hi, Andy,

    Thanks! I’ll give that a shot and see if it works.

    Regards,
    William

    Comment by liamgraham — June 25, 2007 @ 4:42 pm

  6. [...] And if you need to run the tutorial on Oracle instead of Mysql, well, of course, I recommend this blog post [...]

    Pingback by Ajax 101: A Simple Example of Using Ajax with the Zend Framework « William Graham’s blog — August 6, 2007 @ 6:15 pm

  7. Hi,

    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

    Comment by Klaus — November 7, 2007 @ 3:48 pm

  8. Hi, Klaus,

    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).

    Cheers,
    William

    Comment by liamgraham — November 12, 2007 @ 9:49 pm

  9. Hi Will:

    I did what you say in your example, but i got this error.

    Fatal error: Uncaught exception ‘Zend_Db_Statement_Oracle_Exception’ with message ‘942 ORA-00942: la tabla o vista no existe SELECT “albums”.* FROM *”albums”‘ in C:\homesite\Apache2\htdocs\zftutorial\library\Zend\Db\Statement\Oracle.php:244

    For yuor help Muchas Gracias.

    Rosendo Guzman.
    Acapulco, Gro. Mexico.

    Comment by Rosendo Guzman — June 5, 2008 @ 6:09 am

  10. Hi Rosendo,

    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.

    I hope this serves this wrong and if someone corrects me.

    Greetings,
    Diego

    Comment by Diego — November 5, 2008 @ 7:33 pm

  11. I was using a remote Oracle database and not using tnsnames.ora so I did:

    $descriptor = “(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =myhost123) (PORT = 1521)))(CONNECT_DATA = (SERVICE_NAME=myservicename)))”;

    $db = Zend_Db::factory(‘Oracle’, array(‘dbname’ => $descriptor, ‘username’ => ‘myusername, ‘password’ => ‘mypassword’));

    It works now.

    Alain

    Comment by Alain Denis — January 25, 2009 @ 9:02 pm

  12. One problem I can’t seem to fix is when I try to “add” an album.
    all is fine until It does the redirect, then I get this error:

    Fatal error: Uncaught exception ‘Zend_Db_Table_Row_Exception’ with message ‘Cannot refresh row as parent is missing’ in /var/www/localhost/htdocs/DEV/zf-tutorial/library/Zend/Db/Table/Row/Abstract.php:751 [...]

    The new row is inserted and committed in the album table.
    I just get this error when it redirects. ?

    Comment by Alain Denis — January 26, 2009 @ 4:57 am

  13. [...] Oracle modifications for Rob Allen’s Zend Framework Tutorial « William Graham’s blog Good read on using Zend Framework with an Oracle DB – Typically the standard is MySQL (tags: oracle zend framework tags zendframework zf) [...]

    Pingback by links for 2009-11-06 | AndySowards.com :: Professional Web Design, Development, Programming Freelancer, Hacks, Downloads, Math and being a Web 2.0 Hipster? — November 6, 2009 @ 3:50 pm

  14. Hi, I was looking for the same error that Rosendo had, it is:

    **************************************************************************

    Hi Will:

    I did what you say in your example, but i got this error.

    Fatal error: Uncaught exception ‘Zend_Db_Statement_Oracle_Exception’ with message ‘942 ORA-00942: la tabla o vista no existe SELECT “albums”.* FROM *”albums”‘ in C:\homesite\Apache2\htdocs\zftutorial\library\Zend\Db\Statement\Oracle.php:244

    For yuor help Muchas Gracias.

    Rosendo Guzman.
    Acapulco, Gro. Mexico.

    Comment by Rosendo Guzman — June 5, 2008 @ 6:09 am | Reply

    **********************************************************************

    I followed the Diego’s comment and of course, the problem was the getQuoteIdentifierSymbol() function, since Oracle adapter had no it.

    So, I add that function in Zend\Db\Adapter\Oracle.php, but with the symbol space, and that fixes the error. :)

    ******************************************
    //..Zend\Db\Adapter\Oracle.php
    public function getQuoteIdentifierSymbol()
    {
    return ” “;
    }
    ******************************************

    It works now.
    Alejandro

    Comment by aapiedra — November 9, 2009 @ 2:00 am


RSS feed for comments on this post.

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: