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!~~~
Auto-truncate assigned string to the length of the field?
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~
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~
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:orYou 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:
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;
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;
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;