Invalid identifier when binding variables in snowflake

user_16879173 0
Viewed 246

I have created a stored procedure which gets certain values from a table and uses those values into another sql query I successfully got the values I needed however I keep getting invalid identifier for the values I pass through binding Below is a snippet of my code

    var v_role       = sqlRS1.getColumnValue(1);
    var v_database   = sqlRS1.getColumnValue(2);
    var v_schema     = sqlRS1.getColumnValue(3);
    var v_table      = sqlRS1.getColumnValue(4);
    var v_column     = sqlRS1.getColumnValue(5);
    
    var v_tableColumn = v_table + `_` + v_column;
        
    var v_sqlCode5 = `SELECT * 
                      FROM  db.sch.tbl  
                      WHERE DATABASE_NAME = identifier(?) 
                      AND   SCHEMA_NAME   = identifier(?)  
                      AND   POLICY_NAME IN (identifier(?))`;
                     
    var sqlStmt2 = snowflake.createStatement({sqlText:v_sqlCode5,binds:[v_database,v_schema,v_tableColumn]});
    var sqlRS2   = sqlStmt2.execute();

the error will say that there is invalid identifier for the value passed in DATABASE_NAME I checked the query history and I see that the value passed is correct So I am unsure of how to solve this, any help would be greatly appreciated

snowflake-cloud-data-platform
1 Answers

Identifiers are using to replace table names (which can been seen in this example). Where what you have seems like it should be normal equality join:

Another way of saying this is your generated SQL:

SELECT * FROM DB.SCH.TBL 
WHERE DATABASE_NAME = identifier('AD') 
    AND SCHEMA_NAME = identifier('DB') 
    AND POLICY_NAME IN (identifier('M_E'));

does this run with no errors for you in the UI?

I suspect you need to change it to:

SELECT * FROM DB.SCH.TBL 
WHERE DATABASE_NAME = 'AD'
    AND SCHEMA_NAME = 'DB'
    AND POLICY_NAME IN ('M_E');

to get it to work.

What you are wanting is to make sure the passed in values are fully quoted. so things don't escape, which was my understand of what passing them as bound variables does.

which implies you source should be:

    var v_sqlCode5 = `SELECT * 
                      FROM  db.sch.tbl  
                      WHERE DATABASE_NAME = ?
                      AND   SCHEMA_NAME   = ? 
                      AND   POLICY_NAME IN (?)`;
user_439920
Related