Long String as Parameter

[GNiessen]

I am doing an insert into a table that has a long VARCHAR(8000) field and it is appearing that the length of the first time I set the parameter is causing all future inserts to use that as the field length. So when I do a FieldByName('LongFld').AsString := LongString2000 and execute it, then the following FieldByName('LongFld').AsString := LongString4000 is truncated to 2000 characters. This is with Options.AutoPrepare := True;

If I set Options.AutoPrepare := False, then it seems to work ok and the full long string gets inserted. But isn't there a performance hit on the subsequent inserts if the statement has to be prepared each time?

[AndreyZ]

When you use the AutoPrepare option, SDAC automatically prepares SQL statements on the server before executing. It is done when you call the Execute method. As parameters are set before calling Execute, the length of data stored in parameters is used for preparing the statement. In this case, SQL Server truncates all values that are longer than the data length used for preparing.
To avoid the problem, you should set the AutoPrepare option to False and call the Prepare method yourself before assigning parameters. Here is a code example:

Code: Select all

MSQuery.SQL.Text := 'insert into tablename(LongFld) values(:LongFld)';
MSQuery.ParamByName('LongFld').DataType := ftString;
MSQuery.Prepare;
MSQuery.ParamByName('LongFld').AsString := 'test';
MSQuery.Execute;
MSQuery.ParamByName('LongFld').AsString := 'longtest';
MSQuery.Execute;