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).
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;
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'.
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.
