Updating Firebird BLOB field appending text

Question

Problem

Can't append text to a blob field, when this value is NULL, using simple concat (||).

Docs base: https://firebirdsql.org/refdocs/langrefupd21-blob.html

Test Env

Assuming types:

• fieldTarget ~ BLOB
• tablePk ~ VARCHAR(5)

Like this:

UPDATE tablename
SET fieldTarget = fieldTarget || :string
WHERE tablePk = :pkTarget;

After execute, no one error returns but the field still null

Answer 1

All string operations (like ||-concatenation) are bound by maximum VarChar size limit (which is 32 KBytes, which is less than 8200 letters in UTF-8 encoded text).

However there seems to be BLOB-oriented functions. It also does not need escaping NULL-values with COALESCE.

LIST returns a string consisting of the non-NULL argument values in the group, separated either by a comma or by a user-supplied separator.

See LIST().

Hence, using derived tables:

Select LIST(ALL X, '') From
 ( Select fieldTarget as x From tablename Where tablePk = :pkTarget
      UNION ALL
   Select Cast( :string AS VarChar(8191) ) From RDB$DATABASE ) 
As TMP

The typecasting is to assign data-type to the parameter-only expression.

P.S. the documentation also claims that

The ordering of the list values is undefined.

P.P.S. Ready to run example, tested on FB 2.1.7

Select LIST(ALL X, '') From
 ( Select Cast( :str_pre AS VarChar(8191) ) as X From RDB$DATABASE
      UNION ALL
   Select /* DATA column */ RDB$TRIGGER_SOURCE
          From /* DATA table */ RDB$TRIGGERS
          Where /* PK ID */ RDB$TRIGGER_NAME  = :pkTarget
      UNION ALL
   Select Cast( :str_post AS VarChar(8191) ) From RDB$DATABASE
)

Passing names like RDB$TRIGGER_1 would give you a system trigger that has no source text, thus has a NULL value in the field. Easy to check that you need no COALESCE-screening here.

Answer 2

The primary problem you are having is that the original value is NULL. Operations like addition, concatenation, etc on a NULL value will yield NULL. For a deep-dive into the details of NULL, consider reading the Firebird Null Guide.

The solution is to use COALESCE to provide a default (eg empty string) when the column is null*.

UPDATE tablename
SET fieldTarget = COALESCE(fieldTarget, '') || :string
WHERE tablePk = :pkTarget;

---

^{*: you already had this in your now deleted answer, but it was a bit obscured by the casts to varchar which shouldn't be necessary}

Answer 3

You can try to use a CAST("your blob null field" as type of "MY BLOB DOMAIN OF SUBTYPE_1") inside a COALESCE, it works for me (Tested in firebird 2.1):

Firebird CAST

Firebird COALESCE

In the case I tested I had a "null" blob field (let's call it "NOTES") with the next domain information: BLOB SUB_TYPE 0 SEGMENT SIZE 80

Doing a COALESCE(NOTES, '') returns me the next error:

Datatypes are not comparable in expression COALESCE

The trick was to create a domain of the BLOB SUB_TYPE 1 (let's call it "TEXT") and make a CAST to this domain before concat or get it:

SELECT COALESCE(CAST(NOTES AS TYPE OF TEXT), '') FROM MY_TABLE

In your case, the next example should work properly:

UPDATE tablename
SET fieldTarget = COALESCE(CAST(:fieldTarget AS TYPE TEXT), '') || :string
WHERE tablePk = :pkTarget

Take in account to create in the database the domain TEXT as a blob sub_type 1 before.

PS: The real trick would be to cast to the domain of the field that is going to be updated.