snowflake-cloud-data-platformjava-stored-procedures

Javascript Procedure to create tables in Snowflake


I have tried below code to create tables that listed in tables array, but i get error:

JavaScript compilation error: Uncaught SyntaxError: Unexpected end of input in CREATE_TABLES_SP at '

CREATE OR REPLACE PROCEDURE create_tables_sp()   //Procedure to create tables
    RETURNS string
    LANGUAGE JAVASCRIPT
    AS
    $$   
       var tables = ['table_a', 'table_b', 'table_c'];
       for (var table of tables){
       try {
        snowflake.execute (
            {sqlText: "create or replace table PROD_DB.schema1.${table} (ename varchar);"}
            );
        return "Succeeded.";   // Return a success/error indicator.
        }
    catch (err)  {
        return "Failed: " + err;   // Return a success/error indicator.
        }
    $$; 

Does anyone know what i'm missing?


Solution

  • Using IDENTIFIER to provide table name as "variable' and binds to set actual value:

    CREATE OR REPLACE PROCEDURE create_table_sp()
    RETURNS string
    LANGUAGE JAVASCRIPT
    AS
    $$
        var tables = ['table_a', 'table_b', 'table_c'];
        
        for (var table of tables) {
            try {
                snowflake.execute(
                    {
                     sqlText: `CREATE OR REPLACE TABLE IDENTIFIER(?)(ename VARCHAR);`
                    ,binds: ['PROD_DB.schema1.' +  table]
                    }
                );
            }
            catch(err){
                return "Failed: " + err;
            };
        };
        
        return "Succeded.";
    $$;
    

    Check:

    CALL create_table_sp();
    
    SELECT * FROM PROD_DB.schema1.table_a;
    SELECT * FROM PROD_DB.schema1.table_b;
    SELECT * FROM PROD_DB.schema1.table_c;
    

    The proposed usage of ${table}(string interpolation) would also work:

    CREATE OR REPLACE PROCEDURE create_table_sp()
    RETURNS string
    LANGUAGE JAVASCRIPT
    AS
    $$
        var tables = ['table_a', 'table_b', 'table_c'];
        
        for (var table of tables) {
            try {
               var sql = `CREATE OR REPLACE TABLE PROD_DB.schema1.${table} (ename VARCHAR);`;
                snowflake.execute(
                    { sqlText: sql }
                );
            }
            catch(err){
                return "Failed: " + err;
            };
        };
        
        return "Succeded.";
    $$;
    

    But it is less secure and prone to SQL Injection attacks.