Token unkown error in stored procedure when updating on external database

The error code 13:896 is usually shown as error code 336397184, which translates to message “Invalid token”. (as an aside: The “can’t format message” error is an indication that you’re using a fbclient.dll that can’t find the firebird.msg file with error messages, or you’re using an older version that doesn’t contain the specific message)

In this specific case, the problem is that you made a syntax error: your statement is missing the word DO, as shown in FOR EXECUTE STATEMENT in the Firebird 2.5 Language Reference. As a result, the Firebird parser finds a SUSPEND in a position it doesn’t expect it (it either expects DO, or another token from the FOR EXECUTE STATEMENT syntax). The – obvious, but incorrect – fix would be:

for execute statement LCCOMANDO2 on external 'C:\DB\DB.GDB' as
  user 'SYSDBA' password 'masterkey' do

Note: enclosing the suspend; in beginend is not necessary, but in my opinion it improves readability.

This would solve the immediate problem, but will then result in another error, because the FOR EXECUTE STATEMENT is intended to execute statements that produce a result set, and UPDATE does not produce a result set.

Instead, you need to use EXECUTE STATEMENT without FOR. I would also highly recommend that you appropriately parameterize your update statement, instead of concatenating values into the query string. Given your stored procedure doesn’t produce any data (it has no RETURNS clause), use of the SUSPEND clause is also inappropriate.

The final code should be something like:

create or alter procedure UPDATECAS (VNEWSTOCK double precision)
  execute statement ('update CAS set STOCK = STOCK - :newstock') (newstock = VNEWSTOCK) 
    on external 'C:\DB\DB.GDB' as user 'SYSDBA' password 'masterkey';

Be aware though, that using double precision for something that is stock doesn’t seem appropriate. Usually stock is discrete units, so INTEGER or BIGINT would be more appropriate, or if you need decimal values, the exactness of DECIMAL (or NUMERIC) is probably better than the inexactness of DOUBLE PRECISION.

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top