Tokene unknown creating a stored procedure with dynamic table name

Question

I try to check existence of a record before inserting into table.

SET TERM ^ ;

CREATE PROCEDURE add_videorecord(tab_name varchar(31), col_name varchar(31),
col_value varchar(100))
RETURNS (status int)
 AS
BEGIN
status=1;
if (not exists(
select * from :tab_name where :col_name = :col_value))
then
execute statement 'insert into "'||:tab_name||'" ("'||:col_name||'") values("'||:col_value||'")';
else
status=0;
END^

SET TERM ; ^

And get FlameRobin error:

Message: isc_dsql_prepare failed

SQL Message : -104
Invalid token

Engine Code    : 335544569
Engine Message :
Dynamic SQL Error
SQL error code = -104
Token unknown - line 10, column 15
:

Why it's unknown token? I try to use the input parameter.

If the table name is a variable (as in "select * from :tab_name") you need to use "execute statement". http://www.firebirdfaq.org/faq322/ — Mark Leiber, May 12 '15 at 16:39

Mark Rotteveel · Accepted Answer · 2015-05-12T18:42:28.590

You can't directly parametrize an object name (like a table name, column name, etc) like you do in your select. Parameters can only be used for values.

If you want to parametrize an object name, you need to concatenate it into a query string, like you already do for your insert statement. You just need to watch out for SQL injection (eg by checking the name against a known set of accepted object names).

As an example (without checking for valid table and column name!):

CREATE PROCEDURE ADD_RECORD (
    TAB_NAME VARCHAR(31),
    COL_NAME VARCHAR(31),
    COL_VALUE VARCHAR(100) )
RETURNS (
    STATUS INTEGER )
AS
DECLARE temp INTEGER;
BEGIN
    status=1;
    execute statement 
      ('select 1 from "' || tab_name || '" where "' || col_name || '" = ?') (:col_value) 
      into :temp;
    if (temp is null)
    then
        execute statement 
          ('insert into "'|| tab_name||'" ("' || col_name|| '") values(?)') (:col_value);
    else
        status=0;
END

Using a single (dynamically created) MERGE statement, you might be able to simplify this further.

Tokene unknown creating a stored procedure with dynamic table name

1 Answers1