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

Roman Yankovsky · Answer 1 · 2011-04-10T07:49:51.270

2

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);

edited Apr 10 '11 at 07:49

answered Apr 10 '11 at 07:44

Roman Yankovsky

1,207
11
23

1

is ther any reason why your using Copy() and not LeftStr()? – Simon Apr 10 '11 at 08:37
1

@Simon, no, there's no reason :) – Roman Yankovsky Apr 10 '11 at 08:48
2

LeftStr was added only in D6, I think, so many people learnt to use Copy and still stick with it. – David Heffernan Apr 10 '11 at 09:23
Still wanting a way to change the – user700582 Apr 10 '11 at 12:53

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

1 Answers1