javascript - 雪花绑定变量

Question

如何使用雪花存储过程将 current_date() 绑定变量插入表中

创建或替换过程 abc ( "P_MESSAGE_ID" FLOAT, "P_THEATER" STRING, "P_MONTH" STRING, "P_MESSAGE" STRING, "P_START_DATE" STRING, "P_END_DATE" STRING, "P_ACTION" STRING, "P_MSG_TYPE" STRING, "P_LOGGED_USER" STRING ) 返回变体 LANGUAGE JAVASCRIPT AS $$

var query="INSERT INTO abc(MESSAGE_ID,MESSAGE,THEATER,CREATED_BY,CREATE_DATE,UPDATED_BY, UPDATE_DATE,MONTH,START_DATE,END_DATE,MESSAGE_TYPE)
VALUES(E2E_BANNER_MSG_S.NEXTVAL,
:2,
:3,
:4,
:5,
:6 ,
:7,
:8,
:9,
:10,
:11);" var sql = snowflake.createStatement ({ sqltext: 查询，绑定：[P_MESSAGE_ID, P_MESSAGE, P_THEATER, P_LOGGED_USER, current_date(), P_LOGGED_USER, current_date(), P_MONTH, P_START_DATE, P_END_DATE, P_MSG_TYPE]});

var 结果 = sql.execute(); $$;

我收到以下错误：

执行错误：未捕获的 ReferenceError：CURRENT_DATE 未在 ABC 中的“P_LOGGED_USER”位置 40 处定义

你能帮我解决这个问题吗

谢谢，尼基尔

标签： javascriptstored-proceduressnowflake-cloud-data-platform

Answer 1

您不能将函数添加为绑定变量，但为什么需要添加它们？您可以直接将它们放入您的 SQL 中：

create table abc(MESSAGE_ID number ,MESSAGE varchar,THEATER varchar ,CREATED_BY varchar,CREATE_DATE varchar,UPDATED_BY varchar, UPDATE_DATE varchar,MONTH
                 varchar,START_DATE varchar,END_DATE varchar,MESSAGE_TYPE varchar);
                                                 
                                                 
CREATE OR REPLACE PROCEDURE abc ( P_THEATER STRING, P_MONTH STRING, P_MESSAGE STRING, P_START_DATE STRING, P_END_DATE STRING, P_MSG_TYPE STRING, P_LOGGED_USER STRING )
RETURNS variant LANGUAGE JAVASCRIPT 
AS $$
var P_MESSAGE = P_MESSAGE;
var query= "INSERT INTO ABC (MESSAGE_ID,MESSAGE,THEATER,CREATED_BY,CREATE_DATE,UPDATED_BY, UPDATE_DATE,MONTH,START_DATE,END_DATE,MESSAGE_TYPE) VALUES (E2E_BANNER_MSG_S.NEXTVAL,:1,:2,:3,current_date,:3,current_date,:4,:5,:6, :7)"; 
var sql = snowflake.createStatement ({ sqlText: query, binds :[P_MESSAGE, P_THEATER, P_LOGGED_USER, P_MONTH, P_START_DATE, P_END_DATE, P_MSG_TYPE ]});
var result = sql.execute(); 
$$;
                                                 
CALL abc( 'Arena', 'Jan', 'Test message', '2021-01-01' , '2021-01-10', 'Open', 'Jack' );

select * from abc;