Is it possible to return more detailed error message from Firebird?

[L_VV]

Good day,

Sorry if I just did not figure it out, I work with ZeosLib relatively short time.

Can you tell me, please, is it possible to return all the error messages from the Firebird, when an error occurs, and not just a one element from FB status vector?
If there is no such feature, is it possible to implement it?

For example, a such error occurs:

Code: Select all

Unsuccessful execution caused by a system error that precludes successful execution of subsequent statements.
Your user name and password are not defined. Ask your database administrator to set up a Firebird login.
SQLCODE: -902
SQLSTATE: 28000
GDSCODE: 225544472

But from the ZDbcFirebird unit only part of it is returned:

Code: Select all

Unsuccessful execution caused by a system error that precludes successful execution of subsequent statements
Code: -902 Message: Connect to "localhost/3050:Test.db" as user "TEST_APPSRV".

From this error message is completely incomprehensible what happened.


Fragment of code from the ZDbcFirebird module:

Code: Select all

    if FAttachment = nil then begin
      PrepareDPB;
      FLogMessage := Format(SConnect2AsUser, [ConnectionString, URL.UserName]);;
      FAttachment := FProvider.attachDatabase(FStatus, @FByteBuffer[0], Length(DPB), Pointer(DPB));
      
      vvvvv
      if ((Fstatus.getState and {$IFDEF WITH_CLASS_CONST}IStatus.STATE_ERRORS{$ELSE}IStatus_STATE_ERRORS{$ENDIF}) <> 0) then
        HandleErrorOrWarning(lcConnect, PARRAY_ISC_STATUS(FStatus.getErrors),
          FLogMessage, IImmediatelyReleasable(FWeakImmediatRelPtr));
      ^^^^^

      { Logging connection action }
      if DriverManager.HasLoggingListener then
        DriverManager.LogMessage(lcConnect, URL.Protocol, FLogMessage);
    end;

Although the function is named FStatus.getErrors (in plural), only one error from the Firebird status vector is returned.

Now I am developing an application using ZeosLib and mORMot2, and to the client is returned such an error:

Code: Select all

{
"errorCode":500,
"error":
{"EZIBSQLException":
{
        "ClassName": "EZIBSQLException",
        "Address": "7ffff5e9e000",
        "Message": "SQL Error: Unsuccessful execution caused by a system error that precludes successful execution of subsequent statements \nCode: -902 Message: Connect to \"localhost/3050:Test.db\" as user \"TEST_APPSRV\""
}}

This is not a very informative message.
And more detailed information is not saved even into the log...

[EgonHugeist]

Hi,

usually you should see all error messages from IB/FB except the AddLogMsgToExceptionOrWarningMsg is turned off.

see: TZInterbaseFirebirdConnection.HandleErrorOrWarning

Code: Select all

  for i := Low(InterbaseStatusVector) to High(InterbaseStatusVector) do begin
    AppendSepString(ErrorString, InterbaseStatusVector[i].IBMessage, '; ');
    if AddLogMsgToExceptionOrWarningMsg and (InterbaseStatusVector[i].IBMessage = '') then
      AppendSepString(ErrorString, InterbaseStatusVector[i].SQLMessage, '; ');
  end;

So can you debug why you did not get them @all?

Btw. FStatus.getErrors returns just a flag...

[L_VV]

Hello,

I have had a little time to debug the TZInterbaseFirebirdConnection.HandleErrorOrWarning() method.

Code: Select all

unit ZDbcFirebirdInterbase

procedure TZInterbaseFirebirdConnection.HandleErrorOrWarning()
...
  InterbaseStatusVector := InterpretInterbaseStatus(StatusVector);
  ErrorCode := InterbaseStatusVector[0].SQLCode;
  ErrorString := '';
  for i := Low(InterbaseStatusVector) to High(InterbaseStatusVector) do begin
    AppendSepString(ErrorString, InterbaseStatusVector[i].IBMessage, '; ');
    if AddLogMsgToExceptionOrWarningMsg and (InterbaseStatusVector[i].IBMessage = '') then
      AppendSepString(ErrorString, InterbaseStatusVector[i].SQLMessage, '; ');
  end;
...

I found something, maybe it will help.

1. Error executing a stored procedure - the types of formal and actual parameters do not matches.

Debugging:

Code: Select all

ErrorCode := InterbaseStatusVector[0].SQLCODE = -303
InterbaseStatusVector[0].IBDataInt (== GDSCODE) = 335544569


i = 0
InterbaseStatusVector[i].IBMessage = 'Dynamic SQL Error'
InterbaseStatusVector[i].SQLMessage = 'Incompatible column/host variable data type'
InterbaseStatusVector[i].SQLCODE = -303
InterbaseStatusVector[i].IBDataInt (== GDSCODE) = 335544569

i = 1
InterbaseStatusVector[i].IBMessage = 'SQL error code = -303' <== Maybe when returning the error from this status vector element, it was better to display SQLMessage, and not IBMessage?
InterbaseStatusVector[i].SQLMessage = 'Incompatible column/host variable data type'
InterbaseStatusVector[i].SQLCODE = -303
InterbaseStatusVector[i].IBDataInt (== GDSCODE) = 335544436

i = 2
InterbaseStatusVector[i].IBMessage = 'arithmetic exception, numeric overflow, or string truncation'
InterbaseStatusVector[i].SQLMessage = 'Arithmetic overflow or division by zero has occurred.'
InterbaseStatusVector[i].SQLCODE = -802
InterbaseStatusVector[i].IBDataInt (== GDSCODE) = 335544321

i = 3
InterbaseStatusVector[i].IBMessage = 'string right truncation'
InterbaseStatusVector[i].SQLMessage = 'Arithmetic overflow or division by zero has occurred.'
InterbaseStatusVector[i].SQLCODE = -802
InterbaseStatusVector[i].IBDataInt (== GDSCODE) = 335544914

i = 4
InterbaseStatusVector[i].IBMessage = ''
InterbaseStatusVector[i].SQLMessage = 'This user does not have privilege to perform this operation on this object.'
InterbaseStatusVector[i].SQLCODE = -551
InterbaseStatusVector[i].IBDataInt (== GDSCODE) = 335545033

Resulting error message:

Code: Select all

SQL Error: Dynamic SQL Error; SQL error code = -303; arithmetic exception, numeric overflow, or string truncation; string right truncation; This user does not have privilege to perform this operation on this object.
Code: -303

As you can see, the final message does not contain a true cause of the error:
'Incompatible column/host variable data type'.

This happened due to the IF operator:

Code: Select all

    if AddLogMsgToExceptionOrWarningMsg and (InterbaseStatusVector[i].IBMessage = '') then
      AppendSepString(ErrorString, InterbaseStatusVector[i].SQLMessage, '; ');

In this situation, both fields are filled - and IBMessage, and SQLMessage.
It would be good in such a situation somehow combine these error messages.


Also, starting with Firebird v.2.5.1 SQLCODE context variable is deprecated, and instead is recommend to use SQLSTATE.
However, in this method (HandleErrorOrWarning), the error message returns only SQLCODE, but SQLSTATE and GDSCODE contained in the InterbaseStatusVector[0].IBDataInt field, are not returned.
I think, it would be most informative to return all three context variables: SQLSTATE, GDSCODE and SQLCODE.


2. Error connecting to a database due to an incorrect username / password.

Debugging:

There is only one element in the Firebird status vector:

Code: Select all

ErrorCode := InterbaseStatusVector[0].SQLCODE = -902
InterbaseStatusVector[0].IBDataInt (== GDSCODE) = 335544472 (== 'Your user name and password are not defined. Ask your database administrator to set up a Firebird login')

Resulting error message:

Code: Select all

SQL Error: Unsuccessful execution caused by a system error that precludes successful execution of subsequent statements
Code: -902

As you can see, a detailed description of the error did not fall into the final message:
'Your user name and password are not defined. Ask your database administrator to set up a Firebird login'.
Here I could not understand why this message is missing, I did not meet it anywhere under the debugger.
Maybe it can somehow be obtained by GDSCODE.

[marsupilami]

Hello,

Egonhugeist cheked in a possible solution. Could you please test again with the latest Zeos trunk / master?

Best regards,

Jan

[L_VV]

Hello,

I tried to repeat on the latest version of Zeoslib the same cases as last time.

1. Error executing a stored procedure - the types of formal and actual parameters do not matches.

Was:

Code: Select all

SQL Error: Dynamic SQL Error; SQL error code = -303; arithmetic exception, numeric overflow, or string truncation; string right truncation; This user does not have privilege to perform this operation on this object.
Code: -303

Became:

Code: Select all

SQL Error: Dynamic SQL Error; Incompatible column/host variable data type; SQL error code = -303; Incompatible column/host variable data type; arithmetic exception, numeric overflow, or string truncation; Arithmetic overflow or division by zero has occurred.; string right truncation; Arithmetic overflow or division by zero has occurred. \nCode: -303 SQL: EXECUTE PROCEDURE USR_PKG.create_usr_hex(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

Yes, it became better.
Could you also return the GDSCODE (and maybe the SQLSTATE too)?

And probably it would be better to add line breaks after each pair of messages.
Now it is returns a long-long string, and in Lazarus it stretches far beyond the screen.

Maybe it will look like a little clearer:

Code: Select all

SQL Error: Dynamic SQL Error; Incompatible column/host variable data type;
SQL error code = -303; Incompatible column/host variable data type;
arithmetic exception, numeric overflow, or string truncation; Arithmetic overflow or division by zero has occurred.;
string right truncation; Arithmetic overflow or division by zero has occurred.
SQLCODE: -303
GDSCODE: 335544569
SQLSTATE: 
SQL: EXECUTE PROCEDURE USR_PKG.create_usr_hex(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

2. Error connecting to a database due to an incorrect username / password.

Was:

Code: Select all

SQL Error: Unsuccessful execution caused by a system error that precludes successful execution of subsequent statements Code: -902

Became:

Code: Select all

SQL Error:  <== Now nothing is returned here. 
Code: -902 Message: Connect to "localhost/3050:Test.db" as user "TEST_APPSRV"

The message about the wrong user name / password is also not displayed, as before.

Maybe it will look like better:

Code: Select all

SQL Error: Unsuccessful execution caused by a system error that precludes successful execution of subsequent statements;
Your user name and password are not defined. Ask your database administrator to set up a Firebird login.
Message: Connect to "localhost/3050:Test.db" as user "TEST_APPSRV"
SQLCODE: -902
GDSCODE: 335544472
SQLSTATE: 

Maybe it could also be possible to look at other Firebird components (UIB / IBX),
how the SQLSTATE is evaluated, and how an error message in general is generated?

[Fr0sT]

This code shows every entry of Firebird status vector in my app

Code: Select all

function GetIBSpecificData(E: Exception): TZIBSpecificData;
begin
  if E is EZDatabaseError then
    Result := (E as EZDatabaseError).SpecificData as TZIBSpecificData
  else
  if E is EZIBSQLException then
    Result := (E as EZIBSQLException).SpecificData as TZIBSpecificData
  else
    Result := nil;
end;

function ShowIBError(E: Exception; ...): TModalResult;
var
  IBData: TZIBSpecificData;
begin
  Result := mrNone;

  IBData := GetIBSpecificData(E);
  if IBData = nil then Exit; // no specific info

  for i := Low(IBData.StatusVector) to High(IBData.StatusVector) do
  begin
    ... StatusVector[i] has fields IBDataInt, IBDataStr, IBMessage, SQLCode, SQLMessage ...
  end;
...
end;

The pain is that sometimes SQL message is more informative and sometimes FB's one so I just output everything

[eversun]

Btw, It handles custom errors differently if they are "direct" or have dynamic parameters. That is a nightmare

raise exception myerror 'error'; -- OK
raise exception myerror 'error ' || aaa; -- FAILS getting error text