Unknown token creating a stored procedure named dynamic table

I am trying to check for the existence of a record before inserting into a 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 the 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 is it an unknown token? I am trying to use an input parameter.

+3


source to share


1 answer


You cannot directly parameterize the object name (like table name, column name, etc.) like in your selection. Parameters can only be used for values.

If you want to parameterize the name of an object, you need to concatenate it into a query string, as you did for the insert statement. You just need to watch out for SQL injection (for example, by checking the name against a known set of recognized object names).

As an example (without checking if the table and column name is correct!):



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

      

By using a single (dynamically created) statement MERGE

, you can simplify this.

+2


source







All Articles