0
210
Viewsuncaught reference error in snowflake function
I am getting error with below code, please advise:
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;
$$;
When I do > select function('this is test',' ');
Getting below error:
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
thanks.
·
Juan Pablo Isaza
2 answers
Answer question0
Binding variables should be used to pass the value into SQL statement:
-- 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;
$$;
Alternative approach is to use 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;
$$;
·
Juan Pablo Isaza
Report
0
We cannot write select statements in JavaScript UDF and need to build Stored Prod based off JS and below is same :
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','');
·
Juan Pablo Isaza
Report
Answer question