At 04:26 AM 25/08/2004 +0000, you wrote:

>I have a program that's assigning a simple UPDATE statement to a the
>SQL property of a TIB_DSQL instance at runtime. This operation is part
>of a while loop and I call ExecSQL after the SQL is set. The SQL looks
>like this:
>
>UPDATE MYTABLE SET MYVAR20FLD = MYSTRING1 WHERE MYVAR20FLD = MYSTRING2
>
>At the runtime the statment runs correctly a few times, then it errors
>saying that "conversion error from string QW-34-8907".
>
>Here's the problem. At the moment that the query runs the value of
>MYSTRING2 is NOT QW-34-8907. The value QW-34-8907 exists in my table,
>but is not in any way referenced in the currect SQL statement.
>
>I've unsuccessfully tried:
>
>1) clearing the parameters: DSQL1.Params.ClearBuffers(rsNone);

This wouldn't do anything, because you don't have any parameters.

>2) Using IB_DSQL2.ExecuteDML(Statement,nil);
>
>What else could be causing this?

"What else?" This is an exception returned from the server. It is
encountering a value that is of the wrong data type for the operation it
was asked to do, e.g. search or update.

Note that:-

a) if you have a single statement that you are executing repeatedly, with
changes only in the values of columns in the update statement or the search
criteria (WHERE clause) then use a parameterised statement in the SQL
property, e.g.

UPDATE MYTABLE
SET MYVAR20FLD = :CHANGED_VALUE
WHERE MYVAR20FLD = :ORIGINAL_VALUE

Then, in your execution block:

with MyIB_DSQL do
begin
if not Prepared then Prepare;
Params[0].AsString := MyString1;
Params[1].AsString := MyString2;
Execute;
end;

The big advantage of parameters is that it avoids the kind of problem you
have, if you use the AsWhateverType methods.

b) If every execution involves a totally different statement, then clearing
the old SQL property will clear everything associated with it. This is the
hard way to do SQL.

Make up the statement string as a string variable, e.g. AdHocStatement and
then do this:

procedure MyForm.GoDoIt (AdHocStatement: string);
begin
WithMyIB_DSQL do
begin
SQL.Clear;
SQL.Add(AdHocStatement);
Prepare; // SQL errors will except here
Execute; // Data errors will except here
end;
end;

Obviously, b) has more scope for errors in statements. When constructing
your string for the SQL property, make use of the QuotedStr function to
ensure that stray apostrophes get taken care of by Delphi, rather than
causing exceptions when the statement is prepared.

Helen