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();
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
\;