How can I execute a sql command with a blob param in dbx?

Question

I have a TSqlDataSet which has a blob field, I need to get the data of this blob field in the BeforeUpdateRecord event of the provider and execute an update command, I've tried this:

Cmd := TSQLQuery.Create(nil);
try
  Cmd.SQLConnection := SQLConnection;
  Cmd.CommandText := 'UPDATE MYTABLE SET IMAGE = :PIMAGE WHERE ID = :PID';
  Cmd.Params.CreateParam(ftBlob, 'PIMAGE ', ptInput).Value := DeltaDS.FieldByName('IMAGE').NewValue; //blob field
  Cmd.Params.CreateParam(ftString, 'PID', ptInput).Value := DeltaDS.FieldByName('ID').NewValue;
  Cmd.ExecSQL;
finally
  Cmd.Free;
end;

When I execute that I get an EDatabaseError with message: 'No value for parameter PIMAGE.

What am I missing?

Answer 1

Have you tried testing with another driver (e.g. ODBC)? It's possible that the error is not in your code. This approach (changing data providers/drivers) has helped me with some confusing problems that turned out not to be mine.

Answer 2

Answering my own question, the correct way to do it is the following:

const
  SQL = 'UPDATE MYTABLE SET IMAGE = :PIMAGE WHERE ID = :PID;';
var
  Params: TParams;
begin
  Params := TParams.Create(nil);
  try
    Params.CreateParam(ftBlob, 'PIMAGE', ptInput).AsBlob := DeltaDS.FieldByName('IMAGE').NewValue;
    Params.CreateParam(ftString, 'PID', ptInput).Value := DeltaDS.FieldByName('ID').NewValue;
    SQLConnection.Execute(SQL, Params);
  finally
    Params.Free;
  end;
end;

Answer 3

Gracias!!!

pLiNIo aLf..