Question

雪花函数中未捕获的引用错误

2022-03-03

1227

javascript sql snowflake-cloud-data-platform

我在使用以下代码时遇到错误，请指教：

CREATE OR REPLACE FUNCTION fnParseString (STRINGTOP VARCHAR,DELIMITER1 VARCHAR)
RETURNS VARCHAR NOT NULL
language javascript
AS
$$ 
var sql_A = `SELECT CASE WHEN charindex(DELIMITER1,STRINGTOP) = 0 THEN 1 ELSE 0 END`;
var statement = snowflake.createStatement({sqlText:sql_A});
var rs = statement.execute();
return rs;
$$;

当我执行 > select function('this is test',' ');

出现以下错误：

JavaScript execution error: Uncaught ReferenceError: snowflake is not defined in FNPARSESTRING at 'var statement = snowflake.createStatement({sqlText:sql_A});' position 16 stackstrace: FNPARSESTRING line: 3

谢谢。

Answer 1

我们无法在 JavaScript UDF 中编写选择语句，需要基于 JS 构建存储产品，以下是相同的：

CREATE OR REPLACE PROCEDURE fnParseString1(STRINGTOP VARCHAR,DELIMITER1 VARCHAR)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS
$$
var cmd = "SELECT CASE WHEN charindex(:1, :2) = 0 THEN 1 ELSE 0 END";
var stmt = snowflake.createStatement(
          {
          sqlText: cmd,
          binds: [DELIMITER1,STRINGTOP]
          }
          );
var result1 = stmt.execute();
result1.next()
return result1.getColumnValue(1);
$$
;
call fnParseString1('this is test','');

Answer 2

绑定变量 应用于将值传递到 SQL 语句中：

-- though it cannot be used in JS UDF
CREATE OR REPLACE FUNCTION fnParseString (STRINGTOP VARCHAR,DELIMITER1 VARCHAR)
RETURNS VARCHAR NOT NULL
language javascript
AS
$$ 
var sql_A = `SELECT CASE WHEN charindex(:1, :2) = 0 THEN 1 ELSE 0 END`;
var statement = snowflake.createStatement({sqlText:sql_A,
                                           binds:[DELIMITER1,STRINGTOP]});
var rs = statement.execute();
return rs;
$$;

替代方法是使用 SQL UDF：

CREATE OR REPLACE FUNCTION fnParseString (STRINGTOP VARCHAR,DELIMITER1 VARCHAR)
RETURNS VARCHAR NOT NULL
language sql
AS
$$ 
CASE WHEN charindex(STRINGTOP , DELIMITER1) = 0 THEN 1 ELSE 0 END;
$$;