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 begin suspend; end
Note: enclosing the
end 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) as begin execute statement ('update CAS set STOCK = STOCK - :newstock') (newstock = VNEWSTOCK) on external 'C:\DB\DB.GDB' as user 'SYSDBA' password 'masterkey'; end
Be aware though, that using double precision for something that is stock doesn’t seem appropriate. Usually stock is discrete units, so
BIGINT would be more appropriate, or if you need decimal values, the exactness of
NUMERIC) is probably better than the inexactness of
CLICK HERE to find out more related problems solutions.