William Graham’s blog

April 19, 2007

Using Oracle with CakePHP: 15 Minute Blog Tutorial

Filed under: CakePHP,Oracle,PHP,Programming — liamgraham @ 9:39 pm

As I mentioned in my ‘About Me’ page, I’m fairly new to PHP, having been primarily a Java guy for the last 10 years. Having used or investigated several of the webapp frameworks from the Java world (Struts, Stripes, Spring), when I found myself needing to program in PHP, I naturally looked around for a good MVC framework. There are several out there, as most of you no doubt know. The one that has really got me interested right now is CakePHP. I think the thing I’m most impressed with about it is the ‘convention over configuration’ philosophy that I believe it takes from Rails. Having seen how configuration-heavy Struts and Spring are in the Java world, this really was a breath of fresh air.

One thing that initially concerned me, though, is that there isn’t an Oracle driver currently released for Cake. This led me to find Jeff Loiselle’s dbo_oracle driver. While it’s not in the current release, it seems to be pretty mature, and I’ve started to use it as I learn the framework. Actually, I’ve ‘translated’ the DDL for some of the easy tutorials from MySQL syntax to Oracle syntax. If anyone is interested below is the Oracle DDL for the 15 Minute Blog Tutorial. My apologies if someone has already done this! Please let me know, and I’ll give proper credit to whoever did it first. πŸ˜‰

First, You’ll need to get the dbo_oracle driver from the link to Jeff’s blog that I posted above, where he gives the url to his svn trunk. Save it to your cake/libs/model/dbo directory along with all the other database drivers. Next, you’ll need to configure Cake to connect to Oracle. Here’s a sample entry for Oracle in the /app/config/database.php file:

----------------------------------------------------------
var $default = array('driver' => 'oracle',
 			'connect' => 'oci_connect',
			'host' => 'mypcname.Vanderbilt.edu',
			'login' => 'mylogin',
			'password' => 'notgonnatellyou',
			'database' => 'mySID',
			'prefix' => '');
----------------------------------------------------------

Now here’s the DDL in Oracle syntax for creating the POSTS table and inserting the three initial rows. Also notice that I’m mimicing the MySQL autoincrement by using an Oracle sequence and a before insert trigger to populate the ‘id’ value:

----------------------------------------------------------
CREATE TABLE POSTS
( ID NUMBER,
  TITLE VARCHAR2(50),
  BODY VARCHAR2(4000),
  CREATED DATE,
  MODIFIED DATE,
   PRIMARY KEY (ID)
);

CREATE SEQUENCE POSTS_SEQ
  MINVALUE 1
  MAXVALUE 999999999999999999999999999
  INCREMENT BY 1
  START WITH 1
  NOCACHE
  NOORDER
  NOCYCLE ;

CREATE OR REPLACE TRIGGER POSTS_BEF_UPDATE_TRG
BEFORE INSERT ON POSTS
FOR EACH ROW
BEGIN
      select posts_seq.nextval into :new.id from dual;
END;
/
ALTER TRIGGER POSTS_BEF_UPDATE_TRG ENABLE;

INSERT INTO POSTS (TITLE, BODY, CREATED) VALUES ('The title', 'This is the post body.', SYSDATE);
INSERT INTO POSTS (TITLE, BODY, CREATED) VALUES ('A title once again', 'And the post body follows.', SYSDATE);
INSERT INTO POSTS (TITLE, BODY, CREATED) VALUES ('Title strikes back', 'This is really exciting! Not.', SYSDATE);

commit;
----------------------------------------------------------

Okay, the above info basically corresponds to sections 3 and 4 of the tutorial. With that, you should be set up to do the rest of the tutorial on Oracle instead of MySQL. Enjoy, and please let me know if you have any problems with this. It works for me, but I know that doesn’t guarantee anything for you … πŸ˜‰

-William

Advertisements

9 Comments

  1. […] William Graham was kind enough to modify the 15 Minute Blog Tutorial for CakePHP to cater to Oracle users. If you’re looking for a fast way to get started with CakePHP and Oracle, check out his blog. […]

    Pingback by Jeff Loiselle » Blog Archive » CakePHP Blog Tutorial For Oracle Users — April 19, 2007 @ 9:56 pm

  2. this is the error i am getting when i try browsing to my cakephp installation with this seting

    Notice: Please change the value of CAKE_SESSION_STRING in app/config/core.php to a salt value specific to your application in /var/www/html/auto/cake/bootstrap.php on line 54

    Your /app/tmp directory is writable.

    Your database configuration file is present.

    Notice (8): Undefined index: connect [CORE/cake/libs/model/datasources/dbo/dbo_oracle.php, line 144]

    Context | Code

    $config = array(“driver” => “oracle”, “persistent” => true, “host” => “leapsoft”, “login” => “system”, “password” => “sys”, “database” => “autoins”, “prefix” => “”)

    */

    function connect() {

    $config = $this->config;

    $connect = $config[‘connect’];

    $this->connected = false;

    DboOracle::connect() – CORE/cake/libs/model/datasources/dbo/dbo_oracle.php, line 144
    DboSource::__construct() – CORE/cake/libs/model/datasources/dbo_source.php, line 90
    ConnectionManager::getDataSource() – CORE/cake/libs/model/connection_manager.php, line 107
    include – CORE/cake/libs/view/templates/pages/home.ctp, line 59
    View::_render() – CORE/cake/libs/view/view.php, line 778
    View::render() – CORE/cake/libs/view/view.php, line 340
    Controller::render() – CORE/cake/libs/controller/controller.php, line 611
    PagesController::display() – CORE/cake/libs/controller/pages_controller.php, line 102
    Dispatcher::_invoke() – CORE/cake/dispatcher.php, line 341
    Dispatcher::dispatch() – CORE/cake/dispatcher.php, line 323
    [main] – CORE/app/webroot/index.php, line 83

    Fatal error: Function name must be a string in /var/www/html/auto/cake/libs/model/datasources/dbo/dbo_oracle.php on line 146

    Comment by Olawale — May 10, 2007 @ 11:28 am

  3. Good work, but I have this error :

    Your database configuration file is present.

    Fatal error: Call to undefined function oci8_connect() in D:\Reactor\cake\cake\libs\model\dbo\dbo_oracle.php on line 144

    can you help me please

    Comment by diarta — October 23, 2007 @ 8:18 am

  4. I mean oci_connect not oci8_connect

    Sorry

    Comment by diarta — October 23, 2007 @ 8:21 am

  5. Sorry folks … I haven’t been using Cake for some time now, and so haven’t kept this tutorial up with any changes to the driver. My apologies for any problems anyone is having.

    Olawale, I saw a similar problem to yours here:
    http://groups.google.com/group/cake-php/browse_thread/thread/2cc116946fb68513

    Once again, sorry for any problems anyone is having, but I ended up going with Zend Framework, and just don’t have the time to keep working with Cake as well.

    Best wishes!
    William

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

  6. Very good web site, great work and thank you for your service.+&

    Comment by samuil — December 22, 2007 @ 6:57 am

  7. I did exactily what is written but i cant connect… i am having this problem:

    Warning (2): ocierror() [function.ocierror]: OCIError: unable to find error handle [CORE/cake/libs/model/datasources/dbo/dbo_oracle.php, line 204]

    what i am supose to do?

    Comment by Germano Carvalho — July 31, 2008 @ 6:41 pm

  8. good article of cake with oracle

    Comment by zahid — September 17, 2008 @ 8:16 am

  9. hi william,

    i just started using cakephp. thanks for this very usefull posting.

    at work we are using oracle, so i started to use cakephp with oracle.
    a question that i have: is it possible to use cakephp without autoincrement and/or triggers for the primary key?

    can i set the primary key e.g. by “selecting the max from the primary key + 1” πŸ™‚

    we have a table, where i can’t add triggers 😦

    cheers,
    kerby

    Comment by kerby — October 7, 2008 @ 8:52 pm


RSS feed for comments on this post.

Create a free website or blog at WordPress.com.

%d bloggers like this: