1

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

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Majkl
  • 765
  • 1
  • 9
  • 25

1 Answers1

2

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.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
  • Perfect. It was exactly what I was looking for but not found. I went through the Firebird documentation regarding the blob, but there was no mention of the use of binary data in stored procedures (probably i not know how to properly search :) ) thanks – Majkl Dec 19 '16 at 10:58
  • @Majkl To be honest, I had some trouble finding the right reference as well. My answer initially included a link to the Firebird 2.5 release notes, and only later I found the right entry in the Firebird 2.5 language reference. I will see if I can get that improved, as I would have expected it under the section "Literals", but instead it was under "Common Language Elements". – Mark Rotteveel Dec 19 '16 at 11:01
  • he can also introduce his own BLOB type and then write BLOB-interpreting UDFs – Arioch 'The Dec 20 '16 at 07:53