Getting MS SQL Server Exception User Message in Delphi exception

Question

I´m using Datasnap.DBClient to access the SQL Server Database from my Delphi application.

I have a logger that logs every exception in my app, but I´m finding SQL exceptions messages really unfriendlies.

Example: When I catch the SQL exception in Delphi the message I get is:

EOleException: SQL State: 42S22, SQL Error Code: 207

If I open a trace in SQL Profiler I get two messages:

The first one, Exception is the same message that the apps shows:

Exception: Error: 207, Severity: 16, State: 1

The second one, is:

User Error Message: Invalid column name 'Column1'

Is there any possibility to get this UserErrorMessage from the catched Exception in my Delphi app?

MartynA MartynA · Accepted Answer · 2017-02-02T14:48:21

I'm not sure if you are using Ado or DBExpress, but if it's DBExpress, you probably wouldn't be asking the question (see Update below).

The TAdoConnection has an Errors object (see definition in AdoInt.Pas). To investigate it, I used a stored proc on the server defined as

create PROCEDURE [dbo].[spRaiseError](@AnError int)
AS
BEGIN
  declare @Msg Char(20)
  if @AnError > 0
    begin
      Select @Msg = 'MyError ' + convert(Char(8), @AnError)
      RaisError(@Msg, 16, -1)
    end
  else
    select 1
END

Then, in my Delphi code I have something like this:

uses [...] AdoInt, AdoDB, [...]

procedure TForm1.Button1Click(Sender: TObject);
var
  S : String;
  IErrors : Errors;
  IError : Error;
  ErrorCount : Integer;
  i : Integer;
begin
  S := 'exec spRaiseError ' + Edit1.Text;
  AdoQuery1.SQL.Text := S;
  try
    AdoQuery1.Open;
  except
    IErrors := AdoConnection1.Errors;
    ErrorCount := IErrors.Count;
    for i := 0 to ErrorCount - 1 do begin
      IError := IErrors.Item[i];
      S := Format('error: %d, source: %s description: %s', [i, IError.Source, IError.Description]);
      Memo1.Lines.Add(S);
    end;
    Caption := IntToStr(ErrorCount);
  end;
end;

If you try it out, you should find that the contents of the Errors collection is cumulative, so it should capture the second message you are after. However, I'm not entirely sure what you mean when you say you are using "Datasnap dbclient": "DBClient" is the unit which declares the TClientDataSet, but you may be using that with ADO or DBExpress. I'm not sure that the DBX Sql Server driver has any counterpart to the AdoConnections Errors collection that you can get at through using the DBX components.

Update Using the above code, if I do a SELECT which deliberately refers to a non-existent column, 'aname', I get this in Memo1:

error: 0, source: Microsoft OLE DB Provider for SQL Server description: Invalid column name 'aname'.

, but it does not report the error 207 you get.

If I do the same SELECT in a DBX project and trap the exception as I open the ClientDataSet connected to the SqlQuery, as in the following:

procedure TForm1.Button1Click(Sender: TObject);
var
  S : String;
begin
  S := 'select id, aname from atable';
  ClientDataSet1.CommandText := S;
  try
    ClientDataSet1.Open;
  except
    Memo1.Lines.Add(Exception(ExceptObject).Message);
  end;
end;

I get this

SQL State: 42000, SQL Error Code: 8180 Statement(s) could not be prepared. SQL State: 42S22, SQL Error Code: 207 Invalid column name 'aname'.

which in some ways is a bit more informative. At the same time, your logging function logs this:

Fecha: 2017-02-03 18:44:02
Sesion: {2E7176CB-56FD-41C4-BE67-7B9E1D3486B4}
Proyecto: dbxerrors.exe
Aplicación:    
Ruta: D:\aaad7\Ado\
Usuario: ???
Error: SQL State: 42000, SQL Error Code: 8180
Statement(s) could not be prepared.
SQL State: 42S22, SQL Error Code: 207
Invalid column name 'aname'.

Exception EDatabaseError: SQL State: 42000, SQL Error Code: 8180
Statement(s) could not be prepared.
SQL State: 42S22, SQL Error Code: 207
Invalid column name 'aname'.

    Exception 
    UnitName : 
    Procedure : 
    Line : 0
    BinaryFileName : 


Fecha: 2017-02-03 18:44:02
Sesion: {2E7176CB-56FD-41C4-BE67-7B9E1D3486B4}
Proyecto: dbxerrors.exe
Aplicación:    
Ruta: D:\aaad7\Ado\
Usuario: ???
Error: SQL State: 42000, SQL Error Code: 8180
Statement(s) could not be prepared.
SQL State: 42S22, SQL Error Code: 207
Invalid column name 'aname'
Exception EOleException: SQL State: 42000, SQL Error Code: 8180
Statement(s) could not be prepared.
SQL State: 42S22, SQL Error Code: 207
Invalid column name 'aname'
    Exception 
    UnitName : 
    Procedure : 
    Line : 0
    BinaryFileName :

In case it helps, this is a partial extract of my test project's DFM

  object SQLConnection1: TSQLConnection
    ConnectionName = 'MSSQLConnection'
    DriverName = 'MSSQL'
    GetDriverFunc = 'getSQLDriverMSSQL'
    LibraryName = 'dbexpmss.dll'
    LoginPrompt = False
    Params.Strings = (
      'DriverName=MSSQL'
      'HostName=MAT410\ss2014'
      'DataBase=MATest'
      'User_Name=sa'
      'Password=sa'
      'BlobSize=-1'
      'ErrorResourceFile='
      'LocaleCode=0000'
      'MSSQL TransIsolation=ReadCommited'
      'OS Authentication=False')
    VendorLib = 'oledb'
    Left = 40
    Top = 32
  end
  object SQLQuery1: TSQLQuery
    MaxBlobSize = -1
    Params = <>
    SQLConnection = SQLConnection1
    Left = 88
    Top = 32
  end
  object DataSetProvider1: TDataSetProvider
    DataSet = SQLQuery1
    Options = [poAllowCommandText]
    Left = 136
    Top = 32
  end
  object ClientDataSet1: TClientDataSet
    Aggregates = <>
    Params = <>
    ProviderName = 'DataSetProvider1'
    Left = 184
    Top = 32
  end

As you can see, it is pretty minimalist. Delphi version is D7 on Win10 64-bit and server is Sql Server 2014.

Getting MS SQL Server Exception User Message in Delphi exception

1 Answers