Firebird encode/decode base64

Question

Is there any function for encode/decode base 64 in Firebird? In SQL Server there are a technique using xml:

declare @source varbinary(max)=convert(varbinary(max),'AbdalrahmanIbnSewareddahab')
SELECT CAST('' AS XML).value('xs:base64Binary(sql:variable(''@source''))','VARCHAR(MAX)') as BASE64_ENCODED;

The result is QWJkYWxyYWhtYW5JYm5TZXdhcmVkZGFoYWI=

How can I do this in Firebird (2.5 or 3.0)?

you may write a UDF for it, like this forum thread (in Russian) https://www.sql.ru/forum/1320646/proshu-pomoshhi-po-udf - however BLOB-related UDFs require accurate coding. Alternatively you can write base64 algo in PSQL and wrap it into Stored Procedure or Stored Function (FB3-only). It is not hard, for base64 algo is straightforward. But maybe not very efficient, as PSQL does not have pointers or indexed arrays. That said, people used to calculate MD5 using SP, and that was more computational-intensive. Still worked for low to medium loads. — Arioch 'The, Mar 17 '20 at 13:09

Mark Rotteveel · Answer 1 · 2022-09-05T09:06:38.877

There is no such function built-in in Firebird 3 or earlier. Firebird 4 introduced the built-in functions BASE64_ENCODE and BASE64_DECODE to convert between binary data and base64 encoded strings.

select base64_encode('AbdalrahmanIbnSewareddahab') from rdb$database;

Result:
QWJkYWxyYWhtYW5JYm5TZXdhcmVkZGFoYWI=

(dbfiddle)

The alternative is to write one yourself, either as a UDF (Firebird 3 and earlier), a stored procedure (all Firebird versions), a UDR (Firebird 3 and higher), or a PSQL function (Firebird 3 and higher).

Firebird encode/decode base64

1 Answers1