Snowflake working with stored procedure on error handling

Question

I have a sample stored procedure in Snowflake where it does the below steps one after the other

1. Insert Metadata with start details
2. Get the total count of table which is passed as argument
3. Update Metadata with success/failure

Issue here is When the given table name doesn't exist on database, The stored procedure exits with error however its not updating the Metadata with error details and the Metadata entry shows status as "In-Progress" while the stored procedure failed at point #2.

I'm looking for a clean exit when the table doesn't exist in database then stored procedure should update metadata with error details and exit cleanly.

Can someone have a look on below code and suggest if I'm missing something here please. I'm new to snowflake and procedure's as well. Thanks.

CREATE OR REPLACE PROCEDURE abc.PROC_GET_COUNT(table_name varchar)
RETURNS VARCHAR(16777216)
LANGUAGE JAVASCRIPT
EXECUTE AS OWNER
AS $$

// SQL Queries
var get_execution_id_sql = "select t.nextval from table(getnextval(EXECUTION_SEQUENCE)) t";

var get_count_sql = `select count(*) from abc.`+ TABLE_NAME +;

var result_set1 = snowflake.createStatement({sqlText: get_execution_id_sql}).execute();

result_set1.next();

var seq_num= result_set1.getColumnValue(1);

var insert_meta_sql1= `INSERT into abc.ERROR_LOG (EXECUTION_ID, STATUS, START_TS) values ( '` +seq_num+ `', 'In_Progress', CURRENT_TIMESTAMP)`;

try {
    message = 'In insert Metadata with start details';
    snowflake.execute({sqlText: insert_meta_sql1});

    message = 'In Process of get count';
    get_count_out = snowflake.execute ({sqlText: get_count_sql});
    get_count_out.next();
    rec_count = get_count_out.getColumnValue(1);
    
    upd_meta_sql = `UPDATE abc.ERROR_LOG SET  END_TS = current_timestamp, STATUS = 'SUCCESS', MESSAGE = '` + TABLE_NAME + ` - Total count: ` + rec_count + `'  where EXECUTION_ID = '` + seq_num + `';
    
    message = 'In update Metadata with end details';
    snowflake.execute ({sqlText: upd_meta_sql});
    
} catch (err)  {
    upd_meta_sql = `UPDATE abc.ERROR_LOG SET  
                                END_TS = current_timestamp,
                                STATUS = 'FAILED', 
                                MESSAGE = '` + message + `. Error Details -- \n Code: `+ err.code +`\n State: `+ err.state +`\n Message: `+ err.message +`\n Stack Trace: `+ err.stackTraceTxt +`' 
                    where EXECUTION_ID = '` + seq_num + `';
    snowflake.execute ({sqlText: upd_meta_sql});
    return "Failed: " + message + ' -- ' +err; 
    }

return 'SUCCESS';
$$;

Answer 1

There are some syntax errors in the sample code, but I assume that all of them are caused when you are copying the script here. The main issue is err.message contains text with single quote characters which breaks the last SQL.

Here is the scripts for my test environment (it would be very helpful for others if you share yours when asking a question which needs debugging):

create schema abc;
create table deneme ( id number ) as select seq4()
from table(generator(rowcount=>100));
create or replace table ERROR_LOG (EXECUTION_ID number, 
MESSAGE varchar, STATUS varchar, START_TS timestamp , END_TS timestamp);
create or replace sequence seq1;

Here is the fixed version of the procedure:

CREATE OR REPLACE PROCEDURE abc.PROC_GET_COUNT(table_name varchar)
RETURNS VARCHAR(16777216)
LANGUAGE JAVASCRIPT
EXECUTE AS OWNER
AS $$

var get_execution_id_sql = "select t.nextval from table(getnextval(seq1)) t";

var get_count_sql = `select count(*) from abc.`+ TABLE_NAME ;

var result_set1 = snowflake.createStatement({sqlText: get_execution_id_sql}).execute();

result_set1.next();

var seq_num= result_set1.getColumnValue(1);

var insert_meta_sql1= `INSERT into abc.ERROR_LOG (EXECUTION_ID, STATUS, START_TS) values ( ` + seq_num + `, 'In_Progress', CURRENT_TIMESTAMP)`;

try {
    message = 'In insert Metadata with start details';
    snowflake.execute({sqlText: insert_meta_sql1});

    message = 'In Process of get count';
    get_count_out = snowflake.execute ({sqlText: get_count_sql});
    get_count_out.next();
    rec_count = get_count_out.getColumnValue(1);
    
    upd_meta_sql = `UPDATE abc.ERROR_LOG SET  END_TS = current_timestamp, STATUS = 'SUCCESS', MESSAGE = '` + TABLE_NAME + ` - Total count: ` + rec_count + `'  where EXECUTION_ID = '` + seq_num +`'`;
    
    
    message = 'In update Metadata with end details';
    snowflake.execute ({sqlText: upd_meta_sql});
    
} catch (err)  {
    upd_meta_sql = `UPDATE abc.ERROR_LOG SET  
                                END_TS = current_timestamp,
                                STATUS = 'FAILED',
                                MESSAGE = '` + message + `. Error Details -- \n Code: `+ err.code +`\n State: `+ err.state +`\n Message: `+ err.message.replace( /'/g , "''" )  +`\n Stack Trace: `+ err.stackTraceTxt +`' 

                    where EXECUTION_ID = '` + seq_num + `'`;
    snowflake.execute ({sqlText: upd_meta_sql});
    return "Failed: " + message + ' -- ' +err; 
    }

return 'SUCCESS';

$$;

As you may notice, I used err.message.replace( /'/g , "''" ) instead of err.message when generating the SQL.

call PROC_GET_COUNT( 'deneme' ); -- succesful as the table exits

call PROC_GET_COUNT( 'deneme21' ); -- shows error

This is the content of the message column of second try:

In Process of get count. Error Details -- 
 Code: 100183
 State: P0000
 Message: SQL compilation error:
Object 'GOKHAN_DB.ABC.DENEME21' does not exist or not authorized.
 Stack Trace: At Snowflake.execute, line 20 position 30

Answer 2

I see the issue has to do with single quotes being returned in the error message.

I recommend, setting the message variable. While you are writing shorter code, its more cryptic and harder to find the issue.

By breaking the code down into simpler steps you will find the issue easier.

CREATE OR REPLACE SCHEMA abc;

USE SCHEMA ABC;


CREATE  SEQUENCE  IF NOT EXISTS EXECUTION_SEQUENCE
   WITH 
   START  WITH   =  1
   INCREMENT  BY  =  1
   COMMENT = 'DEMO SEQUENCE ' ;



CREATE OR REPLACE TABLE abc.ERROR_LOG(EXECUTION_ID number , STATUS varchar, MESSAGE VARCHAR, START_TS timestamp_ltz , END_TS timestamp_ltz);


CREATE TABLE ABC.TEST_TABLE1(ID NUMBER, MESSAGE VARCHAR);

INSERT INTO ABC.TEST_TABLE1(ID, MESSAGE)
SELECT SEQ4() + 1 , RANDSTR(50, RANDOM()) 
FROM TABLE(GENERATOR(ROWCOUNT=>50));

SELECT * FROM ABC.TEST_TABLE1 ORDER BY 1 ASC;


CREATE OR REPLACE PROCEDURE abc.PROC_GET_COUNT(table_name varchar)
RETURNS VARCHAR(16777216)
LANGUAGE JAVASCRIPT
EXECUTE AS OWNER
AS $$

// SQL Queries
var get_execution_id_sql = "select t.nextval from table(getnextval(EXECUTION_SEQUENCE)) t";

var get_count_sql = `select count(*) from abc.`+ TABLE_NAME +`;`

var result_set1 = snowflake.createStatement({sqlText: get_execution_id_sql}).execute();

result_set1.next();

var seq_num= result_set1.getColumnValue(1);

var insert_meta_sql1= `INSERT into abc.ERROR_LOG (EXECUTION_ID, STATUS, START_TS,MESSAGE) values ( '` +seq_num+ `', 'In_Progress', CURRENT_TIMESTAMP(), '`+TABLE_NAME+`')`;

try {
    message = 'In insert Metadata with start details';
    snowflake.execute({sqlText: insert_meta_sql1});

    message = 'In Process of get count';
    get_count_out = snowflake.execute ({sqlText: get_count_sql});
    get_count_out.next();
    rec_count = get_count_out.getColumnValue(1);
    
    upd_meta_sql = `UPDATE abc.ERROR_LOG SET END_TS = current_timestamp(), STATUS = 'SUCCESS', MESSAGE = '` + TABLE_NAME + ` - Total count: ` + rec_count + `'  where EXECUTION_ID = ` + seq_num + `;`
    
    message = 'In update Metadata with end details';
    snowflake.execute ({sqlText: upd_meta_sql});
    
} catch (err)  {
    message = `Error Details\r\nCode: ` + err.code + `\r\nState:` + err.state +`\r\nMessage: ` + err.message + `\r\nStack Trace: ` + err.stackTraceTxt;

    // **** you need to replace the single quotes that are being returned in your error message
    message = message.replace(/'/g, `"`);
    upd_meta_sql = `UPDATE abc.ERROR_LOG 
                    SET END_TS = current_timestamp()
                    ,STATUS = 'FAILED'
                    , MESSAGE = '` + message +`' where EXECUTION_ID = ` + seq_num + `;`
    snowflake.execute ({sqlText: upd_meta_sql});
    return "Failed: " + message + ' -- ' +err; 
    }

return 'SUCCESS';
$$;



CALL abc.PROC_GET_COUNT('TEST_TABLE1');



CALL abc.PROC_GET_COUNT('TEST_TABLE_MISSING');


SELECT * FROM abc.ERROR_LOG ORDER BY 1 ASC;

Solution is here: