Oracle - Create TRIGGER - TZSQLProcessor

[aperger]

Hi All,

i have trigger:

Code: Select all

CREATE TRIGGER TRIPORTOK_INSERT_AZON BEFORE
INSERT ON TRIPORTOK FOR EACH ROW DECLARE
BEGIN
  EXECUTE IMMEDIATE 'SELECT TRIPORTOK_FAZONOSITO_SEQ.NEXTVAL FROM DUAL' INTO :NEW.FAZONOSITO;
END;

If I run this SQL command in "Oracle 9i SQL Plus" it works OK. If I call this command in my code with a TZSQLProcessor it is not working. The trigger will be available, but it is invalid...????

Can anybody help me? How can I create it to make it work?
Attila

ps: The sequenser is exist: TRIPORTOK_FAZONOSITO_SEQ.

[mdaems]

Hi Aperger,

Somebody else already mentionned this. I tested as well and indeed... Must have something to do with the line endings that are fed into the oracle OCI that shouldn't. We should add this to the bug list, I'm afraid.

Mark

[aperger]

Hi,

Yes, the "end-of-line.... " is the prblem. But there are a most important problem for me: Can you take a look? http://zeos.firmos.at/viewtopic.php?t=671

Thank you.

Attila

Only If you have time :-)

[rzufall]

Hi,

for me it worked with this hack:

ZDBcOracleStatement.pas:

Code: Select all

[...]
procedure TZOraclePreparedStatement.Prepare;
var
  I: Integer;
  Status: Integer;
  TypeCode: ub2;
  CurrentVar: PZSQLVar;
begin
  if not Prepared then
  begin
    { Allocates statement handles. }
    if (FHandle = nil) or (FErrorHandle = nil) then
    begin
      AllocateOracleStatementHandles(FPlainDriver, Connection,
        FHandle, FErrorHandle);
    end;

    // new Line ->
    OracleSQL:=trim(OracleSQL);
    //<- new Line
[...]

With that change i could generate my trigger.

But i had to put a second ';' after the last 'end;' of my Trigger-Code.

For Example:

Code: Select all

CREATE TRIGGER TRIPORTOK_INSERT_AZON BEFORE
INSERT ON TRIPORTOK FOR EACH ROW DECLARE
BEGIN
  EXECUTE IMMEDIATE 'SELECT TRIPORTOK_FAZONOSITO_SEQ.NEXTVAL FROM DUAL' INTO :NEW.FAZONOSITO;
END;;

No idea where one of the ';' got lost!?

Rainer