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

Question

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!~~~

Answer 1

You can issue the command SET ANSI_WARNINGS OFF.

But this is not really recommendable, as there are functionality in SQL Server that requires ANSI_WARNINGS to be on.

So it's better to truncate the data yourself, i.e.

ParamByName('AFIELD').AsString := Copy(VeryLongString, 1, 10);