How to execute multiples procedures from nodejs and node-firebird?

Question

I'm using Firebird 2.5 and node-firebird 0.8.6. I have to run SQL files with multiple stored procedures but I always got errors like the ones below

Error: Dynamic SQL Error, SQL error code = -104, Token unknown - line 1, column 5, term at doCallback (/home/somasys/Downloads/testefb/node_modules/node-firebird/lib/index.js:1234:18) at /home/somasys/Downloads/testefb/node_modules/node-firebird/lib/index.js:2929:21 at /home/somasys/Downloads/testefb/node_modules/node-firebird/lib/messages.js:151:25 at search (/home/somasys/Downloads/testefb/node_modules/node-firebird/lib/messages.js:117:13) at /home/somasys/Downloads/testefb/node_modules/node-firebird/lib/messages.js:54:21 at FSReqCallback.wrapper [as oncomplete] (fs.js:477:5)

Here it's some parts of my SQL file:

set term ^;
CREATE OR ALTER PROCEDURE PRC_CALCULATRIBUTA() 
   BEGIN 
      ...
   END^
set term ;^
commit work;

set term ^;
CREATE OR ALTER PROCEDURE PRC_CORRIGEENCERR() 
   BEGIN 
      ...
   END^
set term ;^
commit work;

I've already tried to remove these set term and commit work and ran it (SQL script) inside a

EXECUTE BLOCK AS 
BEGIN
  ...
END

but even so I got the same errors like the one described above. Is there any instruction or statement to put inside my SQL script?

Answer 1

Firebird's statement API can only execute individual statements. In addition, the SET TERM statement is not part of the Firebird SQL syntax. It is only a client-side feature in ISQL and other Firebird tools to determine when a statement is done. See also firebird procedural query throwing "token unknown" error at "SET TERM #;".

You will need to:

• split up your SQL script in individual statements,
• remove the SET TERM statements
• remove any statement terminators outside the procedure bodies, and
• execute the statements individually.

I would also suggest to not execute commit work, but instead use the transaction control options from node-firebird. I'm not sure if executing commit work will work in node-firebird, but some drivers will break because you just closed a transaction on them without using their transaction API.

In other words, you will need to execute:

CREATE OR ALTER PROCEDURE PRC_CALCULATRIBUTA() 
   BEGIN 
      ...
   END

optionally execute the commit, or commit explicitly using the node-firebird API, and then

CREATE OR ALTER PROCEDURE PRC_CORRIGEENCERR() 
   BEGIN 
      ...
   END

etc.

You cannot use execute block for this, because execute block doesn't support execution of DDL. There are workarounds to that limitation (using execute statement), but it is generally not a good use of execute block.

As an aside, committing between creating stored procedures is unnecessary.