Problem with ZSQLProcessor using oracle-Protocl

[ckempe]

Hello!

I want to do the following:

create or replace trigger INC_buchungen
before insert on buchungen
for each row
begin
if :new.Nummer is null then
select GEN_buchungen.nextval into :new.Nummer from dual;
end if;
end;
GO

The SQL-Processor Delimiter-Type is set to dtGO.
Here´s the output generated by the SQL-Monitor:
2006-07-11 12:32:34 cat: Execute, proto: oracle-9i, msg: create or replace trigger INC_buchungen
before insert on buchungen
for each row
begin
if :new.Nummer is null then
select GEN_buchungen.nextval into :new.Nummer from dual;
end if;
end;
, errcode: 1, error: OCI_SUCCESS_WITH_INFO

If I copy and paste the statement from the Logfile to SQL+ and execute, it works fine.
It also works fine if I chose ado-Protocol and connect to the same database, but ado returns all numeric colums as float (even for integer), so I can´t use it.

Any ideas??

[mdaems]

I have an idea : I suppose you get an info 'trigger created'.
Can you try this in ZDbcOracleUtils:

Code: Select all

procedure CheckOracleError(PlainDriver: IZOraclePlainDriver;
  ErrorHandle: POCIError; Status: Integer; LogCategory: TZLoggingCategory;
  LogMessage: string);
var
  ErrorMessage: string;
  ErrorBuffer: array[0..255] of Char;
  ErrorCode: SB4;
begin
  ErrorMessage := '';
  ErrorCode := Status;

  case Status of
    OCI_SUCCESS:
      Exit;
    OCI_SUCCESS_WITH_INFO:
      begin
        PlainDriver.ErrorGet(ErrorHandle, 1, nil, ErrorCode, ErrorBuffer, 255,
          OCI_HTYPE_ERROR);
        ErrorMessage := 'OCI_SUCCESS_WITH_INFO '+ StrPas(ErrorBuffer);
        DriverManager.LogError(LogCategory, PlainDriver.GetProtocol, LogMessage, ErrorCode, ErrorMessage);
        Exit;
      end;
    OCI_NEED_DATA:
      ErrorMessage := 'OCI_NEED_DATA';
    OCI_NO_DATA:
      ErrorMessage := 'OCI_NO_DATA';
    OCI_ERROR:
      begin
        PlainDriver.ErrorGet(ErrorHandle, 1, nil, ErrorCode, ErrorBuffer, 255,
          OCI_HTYPE_ERROR);
        ErrorMessage := StrPas(ErrorBuffer);
      end;
    OCI_INVALID_HANDLE:
      ErrorMessage := 'OCI_INVALID_HANDLE';
    OCI_STILL_EXECUTING:
      ErrorMessage := 'OCI_STILL_EXECUTING';
    OCI_CONTINUE:
      ErrorMessage := 'OCI_CONTINUE';
  end;

  if (ErrorCode <> OCI_SUCCESS) and (ErrorMessage <> '') then
  begin
    DriverManager.LogError(LogCategory, PlainDriver.GetProtocol, LogMessage,
      ErrorCode, ErrorMessage);
    raise EZSQLException.CreateWithCode(ErrorCode,
      Format(SSQLError1, [ErrorMessage]));
  end;
end;

This is not tested. Intention : No error, but information should be logged. Maybe the Info should be retrieved in an other way, but I know very little about OCI programming.
Can you try this? If it works I'll commit this change. If you can refine : even better.

Mark

[ckempe]

Hello!

No, that was not the problem.
The trigger is created, but marked as incorrect (see screenshot: ).
If I put the cursor after the final "end;" and hit enter and then click on "kompilieren" = "compile" it works.
The problem seems to be, that the sqlprocessor deletes too much (with trim or so). I´ve tested with an additional empty line between the "end;" and "GO", but it keeps the same.

[mdaems]

Hi ckempe,

I made some little test program, and indeed, it's terrible. Oracle acceptsthe trigger but does not compile and only gives some obscure error about invalid ""
Did you try with stored procs/functions as well?
About my patch, I don't know if Ishould apply it. From one side : it's not an error. I'm almost sure it would give the same result when the trigger is compiled without errors. On the other hand: you would not have seen the compilation failed. What to do with this?
I'll have tothink about it later. Time for food and family matters.

Mark

[ckempe]

Mark,

please try exactly the same connecting to the Oracle Database using ADO. You will see, that it works fine.
So I think the problem is somewhere where the scripts are checked or so.

Concerning the error: I would prefer to keep the exception appearing.

Claudius