Codes in EIBCError.SQLErrorMsg

[Boro]

Hallo,
when raising custom exception in stored procedure, the try-except catches the EIBCError exception. The property SQLErrorMsg contains string with a code, for example 'exception 19804069'

Please, where can I found list of these codes ?
Is this code somehow correlated with GDSCode or SQLCodes ?

Thank you in advance for any explanation.

Boro

[Plash]

This code is the exception ID. Each exception in the database has its unique ID.

[Boro]

Thanks for your response.

This code is the exception ID. Each exception in the database has its unique ID.

What ID do you mean ?
This exception is raised intentionally when a Select into statement returns null. This exception was created in db and its ID is number 12.
At the moment of rasing excpetion the EIBCError.SQLErrorMsg property contains exactly

exception 268745157

What does this 9-digit number mean, please ?
And, please, try to answer all questions posted

Please, where can I found list of these codes ?
Is this code somehow correlated with GDSCode or SQLCodes ?

Thankx,
Boro

[Plash]

We could not reproduce this situation. Please provide the script to create the stored procedure and other database objects used by the procedure.
Also provide the Pascal code that you are using.

Specify the exact version of InterBase or Firebird that you are using.

[Boro]

Here is sql db script:

Code: Select all

SET SQL DIALECT 3;

SET NAMES WIN1250;

SET CLIENTLIB 'C:\Program Files\Firebird\Firebird_2_0\bin\fbclient.dll';

CREATE DATABASE 'E:\FBData\TIBCError.fdb'
USER 'STUDENT'
PAGE_SIZE 16384
DEFAULT CHARACTER SET WIN1250;

CREATE EXCEPTION EX_NAMENOTFOUND 'The supplied name was not found.';

SET TERM ^ ; 

CREATE PROCEDURE SP_NAME2ID (
    CNAME VARCHAR(4))
RETURNS (
    NID INTEGER)
AS
BEGIN
  SUSPEND;
END^


CREATE PROCEDURE SP_RAISEEXCEPT (
    CTRYNAME VARCHAR(4))
AS
BEGIN
  EXIT;
END^

SET TERM ; ^

CREATE TABLE TABLE1 (
    ID    INTEGER,
    NAME  VARCHAR(4)
);

SET TERM ^ ;

ALTER PROCEDURE SP_NAME2ID (
    CNAME VARCHAR(4))
RETURNS (
    NID INTEGER)
AS
begin
  SELECT FIRST 1 Table1.ID From Table1
  WHERE Table1.name = :cName
  INTO :nID;

  exit;
end
^

ALTER PROCEDURE SP_RAISEEXCEPT (
    CTRYNAME VARCHAR(4))
AS
declare variable nfoundid integer = null;
begin
  EXECUTE PROCEDURE sp_name2id (:cTryName)
    RETURNING_VALUES :nFoundID;
  if (nFoundID IS NULL) then
    EXCEPTION ex_namenotfound 'Name ' || cTryName || ' not exist.';

  exit;
end
^

SET TERM ; ^

Here is the Pascal code that I am using. It is a project created for this simulation and constaining only this one form:

Code: Select all

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, DBAccess, IbDacVcl, DB, IBC, MemDS;

type
  TForm1 = class(TForm)
    btnConnect: TButton;
    IBCConnection1: TIBCConnection;
    IBCConnectDialog1: TIBCConnectDialog;
    IBCTransaction1: TIBCTransaction;
    Edit1: TEdit;
    Label1: TLabel;
    btnFindID: TButton;
    IBCStoredProc1: TIBCStoredProc;
    procedure btnConnectClick(Sender: TObject);
    procedure btnFindIDClick(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

uses
  MyConnectForm, IBCError;

{$R *.dfm}

procedure TForm1.btnConnectClick(Sender: TObject);
begin
  if IBCConnection1.Connected then
  begin
    IBCConnection1.Disconnect;
    btnConnect.Caption := 'Connect';
  end
  else
  begin
    try
      IBCConnectDialog1.DialogClass := 'TfmMyConnect';
      IBCConnection1.Connect;
      if IBCConnection1.Connected then
        btnConnect.Caption := 'Disconnect';
    except
      ShowMessage('Connection failed');
    end;
  end;
end;

procedure TForm1.btnFindIDClick(Sender: TObject);
begin
  //
  try
    IBCTransaction1.StartTransaction;
    IBCStoredProc1.UnPrepare;
    IBCStoredProc1.StoredProcName := 'SP_RAISEEXCEPT';
    IBCStoredProc1.Prepare;
    IBCStoredProc1.ParamByName('CTRYNAME').AsString :=
       Edit1.Text;
    IBCStoredProc1.Execute;
    IBCTransaction1.Commit;
  except
    on E:EIBCError do
    begin
      ShowMessage(E.SQLErrorMsg);
      ShowMessage(IntToStr(E.Errorcode));
      ShowMessage(E.Message);
      IBCTransaction1.Rollback;
    end;
    else
      IBCTransaction1.Rollback;
  end;
end;

end.

There is in the E.SQLErrorMsg always the text:

exception 268745157

I would assume that this text is always the same for this exception. To my surprise, sometimes it changes to a new number and continues for a while with this new number. Never get back to the previous one.

[Boro]

Sorry, I forgot to mention versions of Firebird, etc. So here they are:

Firebird server 2.0.1.12855 running localy on the same machine
Turbo Delphi Pro with update 2 and hotfixes up to 12
IBDac trial version 2.10.0.10 for Delphi 2006
on WinXP SP2.

[Plash]

We have created the database object from your script, but we still cannot reproduce the problem. In our tests the error message always contains the real exception ID.

Anyway this problem is not related to IBDAC because IBDAC gets error messages from the InterBase client, and puts this text to the Message property of TIBCError 'as is'. So format of error messages depends on InterBase client and server.

[Boro]

Firstly

We have created the database object from your script, but we still cannot reproduce the problem. In our tests the error message always contains the real exception ID

Probably you have looked only at the property TIBCError.Message (inherited from Exception class). This one truly contains the real exception ID. Here it is:

exception 1
EX_NAMENOTFOUND
Name ABCD not exist.
At procedure 'SP_RAISEEXCEPT'

But the same error object contains in property TIBCError.SQLErrorMsg different 'ID' or whatever it is:

exception 268745157

and this SQLErrorMsg is unclear. What's that number ?

Secondly, but more interesting:

Anyway this problem is not related to IBDAC because IBDAC gets error messages from the InterBase client, and puts this text to the Message property of TIBCError 'as is'.

Agree in context of the Message property only. As I wrote above, the Message property is ok. But this is not a message for the end user. How to provide customized error message ? How to recognize from the error object what specific situation caused it to rise ? Only through parsing the Message for either the exception ID or better the line with my own error descritpion or is there a simplier way ?

[Plash]

We have no information about the meaning of a number in the SQLErrorMsg.

To get user message from the exception, you can use the following code:

Code: Select all

var
  List: TStringList;
begin
  try
    IBCStoredProc.Execute;
  except
    on E: EIBCError do begin
      if E.ErrorNumber = 335544517 then begin
        List := TStringList.Create;
        try
          List.Text := E.Message;
          E.Message := List[3];
        finally
          List.Free;
        end;
      end;
      raise;
    end;
  end;
end;

[Boro]

Thank you for the code snippet suplied.
I am wondering where does the number 335544157 come from. Did you use it as example or it comes from the sample case (see above) ?

Anyway I found it in the Firebird list of SQLCODEs and GDSCODEs. There is a pair GDSCODE=335544157 and SQLCODE=-836. This one SQLCCODE is in our sample case in the property IBCError.Errorcode. But from where did you get the GDSCODE ?

Interestingly you wrote

We have no information about the meaning of a number in the SQLErrorMsg

In our sample case this property is filled with

exception 268745157

It is somehow similar to the GDSCODE in your code snippet, isn't it ? Could it be that GDSCCODE consists from two parts and the first one is somehow misinterpreted in the SQLErrorMsg property ?

[Plash]

We got code 335544517 from the InterBase documentation. The documentation says that GDSCODE=335544157 and SQLCODE=-836 correspond to 'Exception '.

I think that a number in the SQLErrorMsg is not GDSCODE.

[Boro]

Oh, now I see in your code snippet above that you have tested property E.ErrorNumber. This one isn't even mentioned in the documentation. Thanks for showing it here. It will be usefull.
The undocumented property E.ErrorNumber holds GDSCODE and the documented property E.Errorcode holds the corresponding SQLCODE. Both build a pair describing the error and are defined in FB error list.

Meaning of the E.SQLErrorMsg remains unclear, at least for now.
Ok, now I want how to get both SQLCODE-GDSCODE pair and also how to extract the error description mesage, either a standard one or my customized.
Thank you very much for your help.

[Plash]

To get SQLCODE use the ErrorCode property of TIBCError.
To get GDSCODE use the ErrorNumber property.
To get an error message use the Message property. You can use the sample code from my previous post to get a part of the message.