how to bind the variables passed through a function in snowflake stored procedure

user_15007796 0
Viewed 264

I created a stored procedure with a function in it for logging into a log table, the output is as below: 100132 (P0000): JavaScript execution error: Uncaught ERROR:LOG_LEVEL is not defined in FUNCTION_TEST at '     throw "ERROR:" +err.message   ' position 5 stackstrace: log_file line: 39 FUNCTION_TEST line: 5.

the code is show in below

create or replace procedure function_test()
returns varchar not null
language JAVASCRIPT
as
$$
const ProceName=Object.keys(this)[0];

try
{
 log("INFO",ProceName,0,"RUNNING","","")
 var copy_into_cmd = `copy into my_table from @my_stage
                      file_format=(format_name= 'my_file_format')
                      on_error = skip_file;`;
var copy_into_stmt = snowflake.createStatement({sqlText: copy_into_cmd});
log("INFO",ProceName,0,"EXECUTING",copy_into_stmt.getQueryId(),copy_into_stmt.getSqlText())
var result_set= copy_into_stmt.execute();
return "SUCCESS"
}

catch(err)
{
var query_id = copy_into_stmt.getQueryId();
var sqltext= copy_into_stmt.getSqlText();
log("INFO",ProceName,err.code,err.message,query_id,sqltext)

return err.message
}

function log(log_level,src_name,err,message,query_id,stmt_txt)
{
  try
  { 
    var load_cmd="insert into log_table(timestamp,log_level,usr_name,src_name,err,message,query_id,sql_text) values(current_timestamp(),?,current_user(),?,NULLIFZERO(?),?,?,?);"
    var load_stmt=snowflake.createStatement({sqlText: load_cmd, binds:[LOG_LEVEL,SRC_NAME,ERR,MESSAGE,QUERY_ID,STMT_TXT]}).execute();
  }
  catch(err)
  {
    throw "ERROR: "+err.message
  }
 }
    
$$;

any suggestions, would be helpful.

snowflake-cloud-data-platform stored-procedures
1 Answers

JavaScript is a case-sensitive language. So you need to update the line with LOG_LEVEL like this:

var load_stmt=snowflake.createStatement({sqlText: load_cmd, binds:[log_level,src_name,err,message,query_id,stmt_txt]}).execute();

When you call the main function/procedure from the SQL engine, the SQL engine will send the parameter names in uppercase (unless you wrap them in double-quotes); this is why the main function will reach the function params in uppercase.

user_125509650
Related