Auto-truncate assigned string to the length of the field?

Jim_2011 · Post by **Jim_2011** » Sun 10 Apr 2011 03:48

Hi Devart:
I met a problem when migrating dbexpress driver from 10 to 11.

I have the following codes to execute an update statement in SQL Server:
sql.add('UPDATE mytable SET myfield=:AFIELD');
ParamByName('AFIELD').AsString := 'Some random string that is too long for the field';
Open;

It will raise an SQL Error Exception and stop the execution.
string or binary data would be truncated

This is caused by the string in myfield been longer then the tables field length, myfield is Varchar(10)

Previously, the codes worked fine and the string goes into the field is truncated to 10 characters automatically.

I was wondering if you can provide any hints about configuring the connection to make the auto-truncation work. Or any work-arounds. Thanks a heap!~~~

AndreyZ · Post by **AndreyZ** » Mon 11 Apr 2011 09:01

Hello,

It's a correct behaviour of dbExpress driver for SQL Server. Please specify the exact version of dbExpress driver for SQL Server that you used before upgrade, and the version you are using after upgrade.

Jim_2011 · Post by **Jim_2011** » Tue 12 Apr 2011 23:58

Hi AndreyZ,

Thank you for the reply.

I wish to correct the original post:

The issue is not caused by upgrading the driver.

The problem can be described as:

If the statement is executed by TQuery.ExecSQL, the oversized value would be truncated;

If the statement is executed by TSQLDataSet.ExecSQL, the oversized value would NOT be truncated;

Is there a way to make TSQLDataSet to do that?

I am using Delphi 2007, the dbexpress driver is
File properties:
- File Size: 252 KB
- File Version: 11.0.2902.10471
- Product Version: 11.0

Apologies for the misleading information in the original post~

AndreyZ · Post by **AndreyZ** » Wed 13 Apr 2011 09:54

The point is that BDE turns off the ANSI_WARNINGS option of SQL Server. You can find more information about this option here: http://msdn.microsoft.com/en-us/library/ms190368.aspx
To obtain the same behaviour as in BDE, you should turn off this option in one of the following ways:

Code: Select all

SQLDataSet.CommandText := 'SET ANSI_WARNINGS OFF; UPDATE mytable SET myfield=:AFIELD';
SQLDataSet.ParamByName('AFIELD').AsString := 'Some random string that is too long for the field';
SQLDataSet.ExecSQL;

or

Code: Select all

SQLDataSet.CommandText := 'SET ANSI_WARNINGS OFF';
SQLDataSet.ExecSQL;
SQLDataSet.CommandText := 'UPDATE mytable SET myfield=:AFIELD';
SQLDataSet.ParamByName('AFIELD').AsString := 'Some random string that is too long for the field';
SQLDataSet.ExecSQL;

You can use any of these ways with the same result, they differ only in usability.
If you don't want to turn off this option, you should truncate the string value yourself before executing query. Here is a code example that you can use for this:

Code: Select all

procedure TMainForm.BitBtnExecuteClick(Sender: TObject);
var
  str: string;
begin
  str := 'Some random string that is too long for the field';
  SQLDataSet.CommandText := 'SELECT * FROM mytable WHERE 1=0';
  SQLDataSet.Open;
  if Length(str) > SQLDataSet.FieldByName('myfield').Size then
    str := Copy(str, 1, SQLDataSet.FieldByName('myfield').Size);
  SQLDataSet.Close;
  SQLDataSet.CommandText := 'UPDATE mytable SET myfield=:AFIELD';
  SQLDataSet.ParamByName('AFIELD').AsString := str;
  SQLDataSet.ExecSQL;
end;

Jim_2011 · Post by **Jim_2011** » Thu 14 Apr 2011 01:14

Thank you very much Andrey!

AndreyZ · Post by **AndreyZ** » Thu 14 Apr 2011 06:55

I am happy that this problem was solved. Feel free to contact us if you have any further questions about dbExpress driver for SQL Server.