Zeoslib Portal

Posted: **12.09.2007, 14:02**

Hi all!

I have ZEOS 6.6.1-beta, Delphi 2006 and FB 2.0.

When I try to update a blob in DB in such a way:

procedure TUserSettings.WriteToDb;
var
  stream: TMemoryStream;
begin
  with TZQuery.Create(fDbConnection) do
    try
      Connection := fDbConnection;
      SQL.Clear;
      SQL.Add(Format('SELECT user_settings AS data FROM g2_user WHERE id=%d', [fUserId]));
      Open;
      Edit;
      stream := TMemoryStream.Create;
      fData.SaveToStream(stream); //Here, fData is TXMLDocument (doesn't much matter imho)
      (FieldByName('data') as TBlobField).LoadFromStream(stream);
      Post;
    finally
      Free
    end;
end;

the blobs in all records (not only where id=fUserId) are updated with the content from the stream (seems like there is no WHERE condition in SQL).

Is this a bug?

Posted: **12.09.2007, 15:11**

try to replace:

Code: Select all

SQL.Add(Format('SELECT user_settings AS data FROM g2_user WHERE id=%d', [fUserId]));

with:

Code: Select all

SQL.Add('SELECT user_settings AS data FROM g2_user WHERE id=' + IntToStr(fUserId));

Posted: **13.09.2007, 07:16**

Thanks, but after replacement I see absolutely the same story... I have to submit a bug, haven't I?

Posted: **13.09.2007, 07:42**

I've found the following workaround for this case:

Code: Select all

procedure TUserSettings.WriteToDb;
var
  stream: TMemoryStream;
begin
  with TZQuery.Create(fDbConnection) do
    try
      Connection := fDbConnection;
      SQL.Clear;
      SQL.Add(Format('UPDATE g2_user SET user_settings=:settings WHERE id=%d', [fUserId]));
      ParamByName('settings').AsBlob := fData.XML.Text;
      ExecSQL;
    finally
      Free
    end;
end;

Posted: **13.09.2007, 07:55**

The problem is that you didn't add the primary (or a unique) key of the table in your query.
Zeos tries to find out which update statement should be issued. As it can't use a blob field to do the record lookup (or maybe it can, but all records have the same value??) the update statement becomes 'update g2_user set user_settings=<blob>' without a where clause. You can check this by makinga log using the TZSQLMonitor component.
This could be called a bug, but the only way we can solve it is by making this query 'not updateable' because there's no where clause present.

The same gotcha occurs when you add other columns to the query but duplicates exists on all fields in the query (so no unique key included). Then the update statement has a where clause but multiple records will be updated.

Conclusion : it's not really a bug but an unconvenient 'feature'.

Mark

Posted: **13.09.2007, 08:00**

Oops, overlapping posts.

Your workaround is definitely better as your proc just does this single action.

In case you use my solution by including id in the select statement zeos wil probably create an identical update without the open/close of the select query.

Mark

Zeoslib Portal

Blob writing problem

Blob writing problem