Error when creating a trigger

Error when creating a trigger

Postby Anders H » Fri 08 Apr 2005 08:18

Just downloaded, and installed dbexpoda.dll, version 2.50.2.
From Delphi2005 I want to create a trigger in code.
I use a ClientDataSet and sets CommandText to:

CREATE OR REPLACE TRIGGER MYTEST_TRIG before insert on MYTEST
for each row
begin
select MYTEST_SEQ.nextval into :new.logid from dual;
end;

where logid is the column in the oracle table MYTEST that I want to automatically get a new id from the sequence MYTEST_SEQ.
When I execute this code from Delphi I get an EDatabaseError:
"No value for parameter 'new.logid'"

The sql-code IS correct, if I run it from Toad e.g. it creates the trigger without problem, and it also works later on when doing inserts.

I guess the problem is that "something", which I believe is the dbexpress driver, interprets ":new.logid" to be a parameter (the colon), which it isn't in this case!

Surely there must be a way around this? Bug? Am I missing something?

Best regards,
Anders H

(By the way, the new dll which you call 2.50.2 is still listed in the file explorer as file version.2.50.0.0)
Anders H
 

Postby Paul » Tue 12 Apr 2005 13:43

It is not a problem of DbxOda driver. Borland SQL Express parsed your query and defined ":new.logid" as parameter before passing it to DbxOda driver. Please see function DB.TParams.ParseSQL
Paul
 
Posts: 725
Joined: Thu 28 Oct 2004 14:06

Postby Anders H » Wed 13 Apr 2005 07:52

OK, the Delphi help for ParsSQL led me to the solution:
"ParseSQL recognizes parameters by a preceding colon (:). Double colons (::) or colons contained in quoted strings are parsed as literals, and the immediately following name is not replaced with a question mark."
Actually I did try earlier to doublequote the colon (":"), but I couldn't get that to work.
However, by writing:
"CREATE OR REPLACE TRIGGER MYTEST_TRIG before insert on MYTEST
for each row
begin
select MYTEST_SEQ.nextval into ::new.logid from dual;
end;"
it now works (note the double colon ::new.logid).
Anders H
 


Return to dbExpress driver for Oracle