CODEWITHSUNDEEP
mysqlsqldatabasedelphiblob
t1f
t1f

Reputation: 3181

Update a single BLOB column for a specific row with a Null or any other value using a sql syntax

I have 2 identical tables (both column count, name and settings). I have 1 identical row on each table, identical information. One of the columns is a BLOB type column and contains an Image (bmp).

Both rows / tables have an id column, auto increment, so id's are identical for every row in both tables. The column set to blob type CAN be NULL (it's set).

I'm using an action that has the following query's:

dbmodule.arhivaQuery.SQL.Clear;
dbmodule.arhivaQuery.SQL.Add('UPDATE `database_name`.`2nd_table_name` SET `column_name`=deleted WHERE `id`=''' + inttostr(dbmodule.comenziDataSetid.Value) + ''';');
dbmodule.arhivaQuery.ExecSql(true);

This should theoretically update the row in the 2nd table by removing the bmp from the blob column, or rather, replacing the bmp with the word "deleted". It doesn't, the image is still in the column / row.

A few things to clarify:

dbmodule is the name of a data module that contains the dataset, data source, sql connection and query. (TSimpleDataSet, TDataSource, TSQLConnection, TSQLQuery).

arhivaQuery is the name of the query I'm using (a TSQLQuery) column_name = name of the column, I've edited in this paste so you can get a clearer picture.

You notice at the end I use the id of the row from the 1st table to change the data in the 2nd, so that's why that is there (ids are identical for the row in both tables).

When I execute this it should keep both rows in both tables but update just the row in the 2nd table by removing its image from the blob column. So after this I should have row in 1st table with the image in the blob column and same row in the 2nd table with no image in the blob column.

I'm guessing my sql syntax is wrong (got some warnings saying so too), can anyone correct it for me please?

Upvotes: 0

Views: 1794

Answers (1)

Ken White
Ken White

Reputation: 125728

If you want to clear the content of a blob field, assign it the value NULL.

dbmodule.arhivaQuery.SQL.Add('UPDATE `database_name`.`2nd_table_name` SET `column_name` = NULL WHERE `id`=''' + inttostr(dbmodule.comenziDataSetid.Value) + ''';');

If you want to display deleted for those columns that have no value, do that in your SELECT statement when retrieving the content using IFNULL() or COALESCE(), whichever your DBMS supports.

An additional improvement you could make (both for coding ease and prevention of SQL injection) is to stop concatenating your SQL and switch to using parameters. It also means you can stop with all of the ''' double/triple/quadruple quoting nonsense and data type conversions, because the DB driver will take care of all of that for you.

dbmodule.arhivaQuery.SQL.Add('UPDATE `database_name`.`2nd_table_name` SET `column_name` = NULL WHERE `id`= :ID;');
// Use AsInteger, AsString, AsFloat, or AsBoolean, whichever fits your
// column data type. Notice no quotes, no call to IntToStr or FloatToStr.
dbmodule.arhivaQuery.ParamByName('ID').AsString := dbmodule.comenziDataSetid.Value;

NOTE: Some DB drivers will need Params.ParamByName instead. If one doesn't work, the other will.

Finally, break your long lines of SQL into manageable pieces so you can stop all of the scrolling around to read it.

dbmodule.arhivaQuery.SQL.Add('UPDATE `database_name`.`2nd_table_name`');
dbmodule.arhivaQuery.SQL.Add('SET `column_name` = NULL WHERE `id`= :ID;');
dbmodule.arhivaQuery.ParamByName('ID').AsString := dbmodule.comenziDataSetid.Value;

Upvotes: 3

Related Questions