CODEWITHSUNDEEP
javascriptsnowflake-cloud-data-platform
user1810575
user1810575

Reputation: 831

Snowflake Javascript executing more than one sql

Snowflake Javascript executing more than one sql

Multiple SQL statements in a single API call are not supported; use one API call per statement instead.

Used stmt.executemany() instead of execute() but no luck, another error "executemany() is not a function"

Please help

    CREATE OR REPLACE PROCEDURE GrantSchemaTablePermissions ()
        returns varchar
        language javascript
        AS
        $$
            var table_control = " SELECT DISTINCT TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA LIKE 'ABCD%' ";
            var sql_statement = snowflake.createStatement({sqlText: table_control});
            var resultSet = sql_statement.execute();
    
            while (resultSet.next()) {
                    var key_column_name = resultSet.getColumnValue(1);              
                    var InsertSelect  = "USE ROLE OPS; GRANT OWNERSHIP on all tables in schema ABCD." + resultSet.getColumnValue(1) + " TO ROLE LOADER;"

                //return InsertSelect

                 var stmt = snowflake.createStatement(
                     {
                      sqlText: InsertSelect
                      }
                    );               
       
                var res = stmt.execute(); //tried executemany() but no luck
                //Cursor.executemany()
               return InsertSelect
                //return stmt.getSqlText();
           }
        $$
        ;
        CALL GrantSchemaTablePermissions();

Upvotes: 1

Views: 872

Answers (1)

Greg Pavlik
Greg Pavlik

Reputation: 11046

If a function doesn't exist such as executemany, you can write it.

function executemany(statements) {

    let statements = statements.split(';');
    for (let i = 0; i < statements.length; i++) {
        if (statements[i].trim().length > 0)
        try {
            getResultSet(statements[i]);
        } catch (err){
            return {Error: err.message, statement: statements[i]};
        }
    }
}

You can add that to the very bottom of your SP and try running it.

I just wrote that for someone who wanted to put a large section of SQL statements into the body of a stored procedure and run them one at a time.

A couple of notes: 1) This does NOT check for semicolons inside of single quotes. It assumes that a semicolon separates one statement from another. 2) You can use backticks ` to open and close the string. This will let you put the statements in a multi-line block. When I did this with ~20 statements with the semicolons on a line by themselves between lines (the user's preference), it confused the web UI's parser between what was the body of the SP and what was outside it. I fixed that by escaping the single line semicolons with a backslash like this:

GRANT ROLE IDENTIFIER($ROLENAME) TO USER IDENTIFIER($USERNAME)
\;
GRANT ROLE IDENTIFIER($ROLENAME) TO ROLE ACCOUNTADMIN
\;

Upvotes: 1

Related Questions