0
Follow
0
View

Executing sql from a javascipt UDF

anhui_my 注册会员
2023-01-24 21:47

A much cleaner approach using parameters binding:

create or replace procedure event(stamp string)
RETURNS  VARCHAR
LANGUAGE JAVASCRIPT
COMMENT = 'SP to log an event message with timestamp to table'
EXECUTE AS CALLER
AS
$$  
    // some variables to log in our event table
    var str_stamp = (new Date()).toISOString();
    to_log = `insert into dummy_table values(2, 'Bill', '${str_stamp}');`;
    
    sql = `INSERT INTO event_table (event,event_stamp)
    VALUES(?, try_to_timestamp(?));`;
    
    var stmnt = snowflake.createStatement({sqlText: sql, binds:[to_log, str_stamp]});
    stmnt.execute();
    
    return "logged: "+ to_log
$$;

Call:

call insert_to_event(current_timestamp());
-- logged: insert into dummy_table values(2, 'Bill', '2022-02-03T17:45:44.140Z');

select * from event_table;

sonbinsonbin 注册会员
2023-01-24 21:47

Found a solution/workaround.

Rather than using javascripts escape/unescape functions to remove special chars from the logs, we use a regex replace eg.

create or replace procedure event(stamp string)
RETURNS  VARCHAR
LANGUAGE JAVASCRIPT
COMMENT = 'SP to log an event message with timestamp to table'
EXECUTE AS CALLER
AS
$$  
    // some variables to log in our event table
    var str_stamp = (new Date()).toISOString();
    to_log = `insert into dummy_table values(2, 'Bill', `+STAMP+`);`;
    to_log = to_log.replace(/[`~!@#$%^&*|+=?'"<>\{\}\[\]\\\/]/gi, '');
    
    sql = 
    `INSERT INTO event_table (
        event,
        event_stamp
    )
    VALUES
        ('`+to_log+`', to_timestamp('`+str_stamp+`'));`;
    
    var stmnt = snowflake.createStatement({ sqlText: sql });
    stmnt.execute();
    
    return "logged: "+ to_log
$$;

call insert_to_event(current_timestamp());
select * from event_table;

Which writes to the log table in an easily readable format with no need for additional stored procedures/UDF's.

About the Author

Question Info

Publish Time
2023-01-24 21:47
Update Time
2023-01-24 21:47

Related Question

学习sql增删改查照着教程打但是一直报错

使用气流/python将JSON数据插入SQL DB

Oracle SQL语句,CASE中嵌套的AND操作符

SQL查询将列合并为一个,并用逗号分隔[重复]

sql函数如何使用 其中campaignid为某波投放活动的id

网上的MySQL8.0 - Linux版安装教程,通过Linux的远程连接工具Finalshell 3.9连上了某个IP地址提示连接超时?

How to obtain value from span tag in selenium

sqlsever触发器设置不了

SQL 要怎么求所有系的平均工资啊

ECshop Mysql抛异常