Creating oracle triggers from php... DOESN'T WORK!

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
roibm
Forum Newbie
Posts: 4
Joined: Thu Oct 20, 2005 11:57 am

Creating oracle triggers from php... DOESN'T WORK!

Post by roibm »

Say I had enough of some problems with PHP and this is a top priority. Searched everywhere, seen this problem already posted and nobody got a single reply. Some posts were as old as 2001. Go figure ;)
Using php 5.0.2, but tested on 5.0.5 as well
Database: oracle 9i

Trying to create a trigger results in an invalid trigger being "created". Once that trigger is recompiled using an external tool, ther problem goes away. Say we have this trigger, a valid one that compiled just fine, no problem at all(except for php):

Code: Select all

CREATE OR REPLACE TRIGGER trigger_name AFTER DELETE ON table1 REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW 
BEGIN 
	IF((:OLD.col1 IS NOT NULL) AND (:OLD.col1 > 0)) THEN 
		UPDATE table2 
			SET col2 = col2 - 1 
		 WHERE (id = :OLD.col1); 
	END IF; 
END;
I tried all possibilities, w/out ;, w/out / etc.
The results are the same:
"Warning: ociexecute() [function.ociexecute]: OCIStmtExecute: OCI_SUCCESS_WITH_INFO: ORA-00000: normal, successful completion"
...and an invalid trigger in the database. Recompiled, it becomes 100% valid without adding/removing a single extra char. The ora user is the same in both cases.

This is what oracle has to say about the trigger php "creates":

Code: Select all

SQL> SHOW ERRORS TRIGGER intra_maila_afterd; 
Errors for TRIGGER PHPCMS.INTRA_MAILA_AFTERD: 

LINE/COL ERROR 

1/6 PLS-00103: Encountered the symbol "" when expecting one of the following: begin case declare exit for goto if loop mod null pragma raise return select update while with <an identifier> <a double-quoted delimited-identifier> <a bind variable> << close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge <a single-quoted SQL string> pipe <an alternatively-quoted SQL string> The symbol "" was ignored. 
2/64 PLS-00103: Encountered the symbol "" when expecting one of the following: begin case declare exit for goto if loop mod null pragma raise return select update while with <an identifier> <a double-quoted delimited-identifier> <a bind variable> << close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge <a single-quoted SQL string> pipe <an alternatively-quoted SQL string>
So... I guess the time is right to come clean about it. Any chance it would work, what should be done if so?
If anybody from the zend engine/oci team here, do you plan to do anything about this?

PS: yes, I am mad... I mean, c'mon, since 2001?

EDIT: sorry, wrong forum. please move this topic to the database forum. thanks.
Last edited by roibm on Fri Oct 21, 2005 12:02 pm, edited 1 time in total.
roibm
Forum Newbie
Posts: 4
Joined: Thu Oct 20, 2005 11:57 am

Post by roibm »

Come on guys, there has to be someone...
How do you create your triggers? Should I understand the php developers don't use no triggers?
User avatar
sweatje
Forum Contributor
Posts: 277
Joined: Wed Jun 29, 2005 10:04 pm
Location: Iowa, USA

Post by sweatje »

You might try trimming the SQL statement in PHP before parsing/executing, and I believe PHP does not require the final ;

I do use triggers, but primarly generated from a data modeling tool, and I execute them from within that tool. From a PHP perspective I just rely on them already being a part of the schema.
roibm
Forum Newbie
Posts: 4
Joined: Thu Oct 20, 2005 11:57 am

Post by roibm »

sweatje wrote:You might try trimming the SQL statement in PHP before parsing/executing, and I believe PHP does not require the final ;

I do use triggers, but primarly generated from a data modeling tool, and I execute them from within that tool. From a PHP perspective I just rely on them already being a part of the schema.
Tried that, no use.
While developing the stuff we also create the triggers via a db tool, but we can't tell our customers... "hey everything is ok, but take care when you install it, create the triggers from a db tool 'cause the stupid php can't do that for ya".

uhh.... :evil:
roibm
Forum Newbie
Posts: 4
Joined: Thu Oct 20, 2005 11:57 am

Post by roibm »

ok, got it... this is sick... just shows how some bugs are still here since who knows how long.
getting rid of all chr(13) and chr(10) fixes it.

this was posted back in september 2003:
http://phpbuilder.com/board/showpost.ph ... ostcount=3

saw posts complaining about this bug as early as 2001. sight... I knew java would have been a better choice for us, but there isn't enough workforce who can deal with it in our company... sight, again :(
Post Reply