How to Update a Blob in SQL Server Using Tsql

How To Update A BLOB In SQL SERVER Using TSQL

There are two ways to SELECT a BLOB with TSQL:

SELECT * FROM OPENROWSET (BULK 'C:\Test\Test1.pdf', SINGLE_BLOB) a

As well as:

SELECT BulkColumn FROM OPENROWSET (BULK 'C:\Test\Test1.pdf', SINGLE_BLOB) a

Note the correlation name after the FROM clause, which is mandatory.

The second version can be used for a UPDATE as in the following example:

UPDATE MyTable 
SET blobField = 
   (SELECT BulkColumn FROM OPENROWSET (BULK 'C:\Test\Test1.pdf', SINGLE_BLOB) a) 
WHERE (CriteriaField = @criteria)

For partial updates one can use the SET .WRITE mutator as described in this MSDN article, here is the syntax:

UPDATE MyTable SET BlobField .WRITE (expression, @offset, @length) WHERE (CriteriaField = @criteria)

Note that the WRITE mutator can only be used on NON-NULL fields.

In fact this can also be used to do a full update (if the column does not contain NULL), by setting @offset to 0 and @length to NULL (or to the actual length), as in the following example:

DECLARE @tmp VARBINARY(MAX) --Change to the correct datatype here
SELECT @tmp = BulkColumn FROM OPENROWSET (BULK 'C:\Test\Test1.pdf', SINGLE_BLOB) a
UPDATE MyTable SET BlobField .WRITE (@tmp, 0, NULL) WHERE (CriteriaField = @criteria)

How to insert a blob into a database using sql server management studio

You can insert into a varbinary(max) field using T-SQL within SQL Server Management Studio and in particular using the OPENROWSET commmand.

For example:

INSERT Production.ProductPhoto 
(
    ThumbnailPhoto, 
    ThumbnailPhotoFilePath, 
    LargePhoto, 
    LargePhotoFilePath
)
SELECT ThumbnailPhoto.*, null, null, N'tricycle_pink.gif'
FROM OPENROWSET 
    (BULK 'c:\images\tricycle.jpg', SINGLE_BLOB) ThumbnailPhoto

Take a look at the following documentation for a good example/walkthrough

Working With Large Value Types

Note that the file path in this case is relative to the targeted SQL server and not your client running this command.

Can I append to a BLOB in MSSQL database from C# without reading the original data?

Read about the UPDATETEXT sql statement at http://msdn.microsoft.com/en-us/library/3517w44b.aspx. The Msdn article contains example code that appends binary data to a blob in the StorePhoto method.

What is the easiest way to update an image field with the content of a file

UPDATE YourTable
SET BlobColumn = 
    (SELECT  BulkColumn FROM OPENROWSET(BULK  N'C:\YourFile.png', SINGLE_BLOB) AS x)
WHERE ...

Replacing text in a BLOB Column

REPLACE works on the following datatypes:

Both search_string and replacement_string, as well as char, can be any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

You have chosen to store character data as a collection of bytes (BLOB). These can not be worked on directly because a BLOB has no context and is only a very very big number. It can't be converted to characters without your input: you need its character set to convert binary data to text.

You'll have to either code the function REPLACE yourself (using DBMS_LOB.instr for instance) or convert your data to a workable CLOB and use standard functions on the CLOB.

I would advise strongly to change the datatype of your column. This will prevent any further character set conversion error you will likely run into in the future.

If you really want to work with blobs, use functions like these:

SQL> CREATE OR REPLACE FUNCTION convert_to_clob(l_blob BLOB) RETURN CLOB IS
  2     l_clob         CLOB;
  3     l_dest_offset  NUMBER := 1;
  4     l_src_offset   NUMBER := 1;
  5     l_lang_context NUMBER := dbms_lob.default_lang_ctx;
  6     l_warning      NUMBER;
  7  BEGIN
  8     dbms_lob.createtemporary(l_clob, TRUE);
  9     dbms_lob.converttoclob(dest_lob     => l_clob,
 10                            src_blob     => l_blob,
 11                            amount       => dbms_lob.lobmaxsize,
 12                            dest_offset  => l_dest_offset,
 13                            src_offset   => l_src_offset,
 14                            blob_csid    => nls_charset_id('AL32UTF8'),
 15                            lang_context => l_lang_context,
 16                            warning      => l_warning);
 17     RETURN l_clob;
 18  END convert_to_clob;
 19  /

Function created

SQL> CREATE OR REPLACE FUNCTION convert_to_blob(l_clob CLOB) RETURN BLOB IS
  2     l_blob         BLOB;
  3     l_dest_offset  NUMBER := 1;
  4     l_src_offset   NUMBER := 1;
  5     l_lang_context NUMBER := dbms_lob.default_lang_ctx;
  6     l_warning      NUMBER;
  7  BEGIN
  8     dbms_lob.createtemporary(l_blob, TRUE);
  9     dbms_lob.converttoblob(dest_lob     => l_blob,
 10                            src_clob     => l_clob,
 11                            amount       => dbms_lob.lobmaxsize,
 12                            dest_offset  => l_dest_offset,
 13                            src_offset   => l_src_offset,
 14                            blob_csid    => nls_charset_id('AL32UTF8'),
 15                            lang_context => l_lang_context,
 16                            warning      => l_warning);
 17     RETURN l_blob;
 18  END convert_to_blob;
 19  /

Function created

You can call these functions directly from SQL:

SQL> UPDATE ape1_item_version
  2     SET DYNAMIC_DATA = convert_to_blob(
  3                          REPLACE(convert_to_clob(DYNAMIC_DATA),
  4                                 'Single period',
  5                                 'Single period period set1')
  6                          )
  7   WHERE NAME = 'PRIT ALL POOL for Duration Telephony 10_NA_G_V_H_N_Z2';

1 row updated

BLOB to String, SQL Server

Problem was apparently not the SQL server, but the NAV system that updates the field. There is a compression property that can be used on BLOB fields in NAV, that is not a part of SQL Server. So the custom compression made the data unreadable, though the conversion worked.

The solution was to turn off compression through the Object Designer, Table Designer, Properties for the field (Shift+F4 on the field row).

After that the extraction of data can be made with e.g.:
select convert(varchar(max), cast(BLOBFIELD as binary))
from Table

Thanks for all answers that were correct in many ways!

insert a BLOB via a sql script?

For testing, you can insert literal hex bytes or use the RAWTOHEX(string) function, as shown below.

create table a(id integer, item blob);
insert into a values(1,'54455354');
insert into a values(2, RAWTOHEX('Test'));
select UTF8TOSTRING(item) from a;
TEST
Test

Addendum: For loading BLOB fields from a file, FILE_READ(fileNameString) may be a useful alternative.

insert into a values(3, FILE_READ('file.dat'));

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

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;

---

Related Topics