SQL exception message gets truncated

[uberbach]

Recently we went from driver version 4.55.0.22 to version 6.1.2.0. With this new version, when a SQL exception occurs, the message gets truncated to 255 characters. I suppose this has been changed since version 5.0.2, because there is a mention of it in History.html. The problem is that our software tries to present the user with a more readable message and therefor we parse the text of the exception. But with the new driver version, the part of the message that we rely on gets cut off.

Is there a workaround for this problem, or could you introduce a new extended driver option to disable these extended exceptions?

[AndreyZ]

Hello,

I cannot reproduce the problem. I have executed the following code:

Code: Select all

begin
  SQLConnection1.Open;
  try
    SQLConnection1.Execute('erroneous SQL statement', nil);
  except
    on E: Exception do begin
      ShowMessage(IntToStr(Length(E.Message))); // showed 332, so there is no truncating to 255 characters
      ShowMessage(E.Message);
    end;
  end;
end;

Please specify the exact situation when truncating occurs. Also, please specify the exact version of your IDE.

[uberbach]

I'm sorry, I should have been more specific.

I's using Delphi 7 and dbexpsda.dll 6.1.2.0 on a SQL Server 2008r2 database.

The problem occurs when using client datasets. I have included an example. It's a client dataset connected to a SQL Server database through a provider, a SQL dataset and a SQL connection. When an exeption occus in SQL Server, then at first it is received correctly as an exception in Delphi. But somewhere along the line Delphi decides to ignore the exception message. Instead the last error is queried through the driver, but a buffer of 255 characters is used, so sometimes part of the original message is lost.

To run the example you need a test table:

Code: Select all

CREATE TABLE [dbo].[Test_Table] (
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Test] [int] NOT NULL,
 CONSTRAINT [PK_Test_Table] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER  TRIGGER [dbo].[Test_Table_Trigger] ON [dbo].[Test_Table] 
FOR INSERT
AS
  Raiserror ('000000000011111111112222222222333333333344444444445555555555666666666677777777778888888888999999999900000000001111111111222222222233333333334444444444 IMPORTANT INFORMATION HERE', 16, 1)

GO

Then, in a form with one button add:

Code: Select all

procedure TTestForm.Button1Click(Sender: TObject);
var
  Connection:     TSQLConnection;
  SqlDataset:     TSQLDataset;
  Provider:       TDatasetProvider;
  ClientDataset:  TClientDataset;
  Server:         String;
  Database:       String;
  Username:       String;
  Password:       String;
begin
  Server                            := '<server>';
  Database                          := '<database>';
  Username                          := '<username>';
  Password                          := '<password>';

  Connection                        := TSQLConnection.Create(Application);
  SqlDataset                        := TSQLDataset.Create(Application);
  Provider                          := TDatasetProvider.Create(Application);
  ClientDataset                     := TClientDataset.Create(Application);

  try
    with Connection do begin
      LoginPrompt                   := False;
      ConnectionName                := 'SQLServerConnection';
      DriverName                    := 'SQLServer';
      GetDriverFunc                 := 'getSQLDriverSQLServer';
      LibraryName                   := 'dbexpsda.dll';
      VendorLib                     := 'sqloledb.DLL';
      Params.Values['DataBase']     := Server + '/' + Database;
      Params.Values['User_Name']    := Username;
      Params.Values['Password']     := Password;
    end;

    SqlDataset.SQLConnection        := Connection;
    SqlDataset.CommandText          := 'select * from Test_Table';

    Provider.DataSet                := SqlDataset;
    Provider.Name                   := 'Provider';

    ClientDataset.ProviderName      := Provider.Name;
    ClientDataset.OnReconcileError  := ReconcileError;
    ClientDataset.Open;

    ClientDataset.Append;
    ClientDataset.FieldByName('Test').AsInteger := 123;
    ClientDataset.Post;
    ClientDataset.ApplyUpdates(0);

  except
  end;

  ClientDataset.Free;
  Provider.Free;
  SqlDataset.Free;
  Connection.Free;
end;

procedure TTestForm.ReconcileError(
  DataSet: TCustomClientDataSet;
  E: EReconcileError;
  UpdateKind: TUpdateKind;
  var Action: TReconcileAction);
begin
  ShowMessage(E.Message);
  Action := raAbort;
end;

The message that gets shown is:

SQL Server Error: SQL State: 1, SQL Error Code: 50000, Level: 16, Procedure: Test_Table_Trigger, Line: 5
00000000001111111111222222222233333333334444444444555555555566666666667777777777888888888899999999990000000000111111111122222222223333333333444444444

For demonstration purposes I made the text of the exception quite long. But in practice your driver sometimes adds more information, so shorter exception messages get cut off too.

[AndreyZ]

This problem is caused by a bug in the midas library. For more information, please refer to http://qc.embarcadero.com/wc/qcmain.aspx?d=84960

[uberbach]

In case anyone runs into the same problem: you can "hack" MidasLib.dcu by using a hex editor. Find the byte sequence "28 FF 00 00 00". "FF 00 00 00" is the 32 bit integer value that controls the maximum size of error messages. Change it to FF 03 00 00 to increase the buffersize from 255 to 1023.
I suppose this also works for Midas.dll, but I have not tried it.

I still would prefer a driver option to be able to choose between the new error messages, or the old (short) ones.

[AndreyZ]

I still would prefer a driver option to be able to choose between the new error messages, or the old (short) ones.

As we do not develop the midas library, there is no way for us to solve this problem. You should write about this to the Embarcadero support. Only they can provide the solution for it.

[uberbach]

That's not what I meant. In History.html, that comes with the installation, below 5.0.2, there is this line:

Now raised exception contains SQL error code together with error message

I suppose this means that from version 5.0.2 you started to prefix error messages with extra information. What I would like is an option to get the errors from before 5.0.2 back.

[AndreyZ]

Many of our users asked us to add additional information to the error message. We do not plan to change this. As a solution, you can use the OnUpdateError event handler of the TDataSetProvider component. There, you can get the full error message.