SQL exception message gets truncated
SQL exception message gets truncated
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?
Is there a workaround for this problem, or could you introduce a new extended driver option to disable these extended exceptions?
Re: SQL exception message gets truncated
Hello,
I cannot reproduce the problem. I have executed the following code:Please specify the exact situation when truncating occurs. Also, please specify the exact version of your IDE.
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;
Re: SQL exception message gets truncated
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:
Then, in a form with one button add:
The message that gets shown is:
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
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;
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.SQL Server Error: SQL State: 1, SQL Error Code: 50000, Level: 16, Procedure: Test_Table_Trigger, Line: 5
00000000001111111111222222222233333333334444444444555555555566666666667777777777888888888899999999990000000000111111111122222222223333333333444444444
Re: SQL exception message gets truncated
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
Re: SQL exception message gets truncated
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.
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.
Re: SQL exception message gets truncated
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 wrote:I still would prefer a driver option to be able to choose between the new error messages, or the old (short) ones.
Re: SQL exception message gets truncated
That's not what I meant. In History.html, that comes with the installation, below 5.0.2, there is this line:
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.Now raised exception contains SQL error code together with error message
Re: SQL exception message gets truncated
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.