How to perform update on binary blob in Firebird stored procedure?

Question

The challenge is in stored procedures to assemble a binary value to blob and then save the table. The problem is that the stored procedure works with the blob as text, e.g., if I want to insert into blob byte 4, than byte 52 is inserted (for byte ASCII number 4).

I tried different cast, but nothing works. If stored procedure update blob into a table, which is a parameter of the function, so it works fine.

How to work with binary values (full byte) in a stored procedure?

CREATE PROCEDURE PROC__TESTBLOB (
INID Integer,
INDATABLOB Blob sub_type 0 )
RETURNS (
RESULT Varchar(20) )
AS
DECLARE VARIABLE VAR_BLOB blob sub_type 0;
BEGIN
  BEGIN
  BEGIN
    --update tab_test set datablob = :INDATABLOB where id = :INID; --This work fine

    VAR_BLOB = CAST(4 AS BLOB(0));--not work, blob is still byte 52 (ascii number 4)
    VAR_BLOB = 3;--not work, blob is still byte 51 (ascii number 3)
    update tab_test set datablob = :VAR_BLOB where id = :INID;--This does not work

    RESULT = 'OK';
  END
  WHEN ANY DO RESULT = 'ERR';
  END
  SUSPEND;
END^

UPDATE Firebird version 3.0.0.32483 64bit

Answer 1

The reason your code doesn't work, is that cast(4 as blob(0)) will convert to '4' which is indeed 0x34 or decimal 52.

To be able to assign binary values, you need to use binary string literals (introduced in Firebird 2.5):

VAR_BLOB = x'04'

Note that binary literals are comprised of pairs of hexadecimal digits.