How to properly handle this query in Firebird

Question

As far as I've searched and looked into documentation, the following query is supposed to go through.

CREATE OR ALTER FUNCTION func_string_de_processos(p_num_processo int)
RETURNS BLOB
AS
DECLARE VARIABLE text BLOB;

DECLARE c1 CURSOR FOR
(SELECT vap.NOME_ANEXO AS nome
   FROM VW_ANEXOS_PROCESSOS vap
  WHERE vap.CODIGO_PROCESSO = :p_num_processo);
    
BEGIN
    
    text = '';

    OPEN c1;
    WHILE(1=1) DO
    BEGIN
        FETCH c1 INTO :current_value;

        IF (ROW_COUNT = 0) THEN
          LEAVE;
    
        IF (ROW_COUNT = 1) THEN
            text = text || current_value;
        ELSE 
            text = text || current_value || ' OR ';
    END
    CLOSE c1;

    RETURN text;
END

I want to concatenate some text to a variable and return, so I made the function, while it does not seem (at least to me, to have any logical problems) it doesn't compile.

This error keeps appearing and it doesn't give me much detail:

SQL Error [335544851] [42000]: Dynamic SQL Error; SQL error code = -104; Unexpected end of command - line 4, column 23 [SQLState:42000, ISC error code:335544851]

I see nothing wrong where it's pointing. And I've rewritten this three times at this point.

What do I need to change to make the query valid?

Answer 1

This answer is written with the assumption that you're using DBeaver to execute this, as that is the tool where I could reproduce this. It looks like DBeaver now splits statements on ; and executes them individually, even when using the "Execute SQL query" option (instead of "Execute SQL script"). IIRC, "Execute SQL query" didn't do so in the past (but maybe I'm misremembering that, because DBeaver behaves correctly if there are no DECLARE statements, and there are only ; terminated statements between BEGIN and END).

The problem is that within Firebird PSQL code, the ; is also used as the statement terminator, so the statement should not be split on ;. As a result, instead of your full code, only the following is sent to the server:

CREATE OR ALTER FUNCTION func_string_de_processos(p_num_processo int)
RETURNS BLOB
AS
DECLARE VARIABLE text BLOB

And as that is not a full PSQL body, Firebird then produces the "Unexpected end of command" error.

There are two possible solutions/workarounds:

1. Right click the data source, and select "Edit Connection", under "SQL Editor" > "SQL Processing", enable "Datasource settings" (or alternatively, go to "Global settings").

   Enable the option "Ignore native delimiter", and at "Statements delimiter" enter a character other than ; (e.g. #).

   The downside of this approach is that it will apply for all statements executed on this data source, or even globally if you changed this in "Global settings". I recommend the next approach instead.

2. Add the statement set term #; before your statement, end your statement with # (you can leave it off if it is the last statement), and optionally add set term ;# after it to set the delimiter back (only relevant if you want to execute subsequent statements with the normal statement terminator).

   The SET TERM statement changes the delimiter on the fly. It is not really a Firebird statement, but it is a command for the Firebird ISQL tool to address this same problem, and a lot of Firebird tools support it too, and so does DBeaver.

   For example, execute:

   set term #;
   CREATE FUNCTION func_string_de_processos(p_num_processo int)
   RETURNS BLOB
   AS
   DECLARE VARIABLE text BLOB;
   
   DECLARE c1 CURSOR FOR
   (SELECT vap.NOME_ANEXO AS nome
      FROM VW_ANEXOS_PROCESSOS vap
     WHERE vap.CODIGO_PROCESSO = :p_num_processo);
   
   BEGIN
   
       text = '';
   
       OPEN c1;
       WHILE(1=1) DO
       BEGIN
           FETCH c1 INTO :current_value;
   
           IF (ROW_COUNT = 0) THEN
             LEAVE;
   
           IF (ROW_COUNT = 1) THEN
               text = text || current_value;
           ELSE 
               text = texto || current_value || ' OR ';
       END
       CLOSE c1;
   
       RETURN text;
   END#
   set term ;#