Ufdjrw

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

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 delimiter

https://firebirdsql.org/refdocs/langrefupd21-aggrfunc-list.html

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 :-D

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}