An array with the values or the variables to bind into the SQL statement.
NoCatch (optional)
Boolean to know if an error should not be catched.
catchFunction (optional)
A function to execute in case an error occurs during the execution of the exec function.
opts (optional)
A JSON object ({ temp : true }) to know if the query ID should be returned.
FixBind And FormatDate Functions
The Exec helper uses a function defined in the helpers called FixBind. This function uses the FormatDate function when it encounters that one of the binding variables is a date type, this is done to manage properly the date types in Snowflake.
Both functions are defined as below.
var formatDate = (arg) => (new Date(arg - (arg.getTimezoneOffset() * 60000))).toISOString().slice(0,-1);
var fixBind = function (arg) {
arg = arg == undefined ? null : arg instanceof Date ? formatDate(arg) : arg;
return arg;
};
Some parts of the output code are omitted for clarity reasons.
Exec Usage Sample
Teradata
IN -> Teradata_01.sql
-- Additional Params: -t JavaScript
REPLACE PROCEDURE ProcedureSample ()
BEGIN
case value
when 0 then
select * from table1
else
update table1 set name = "SpecificValue" where id = value;
end case
END;
Snowflake output
OUT -> Teradata_01.sql
CREATE OR REPLACE PROCEDURE ProcedureSample ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
// SnowConvert Helpers Code section is omitted.
switch(value) {
case 0:EXEC(`SELECT * from table1`,[]);
break;
default:EXEC(`UPDATE table1
set
name = "SpecificValue"
where
id = value`,[]);
break;
}
$$;
Exec Helper Definition
var EXEC = function (stmt,binds,noCatch,catchFunction,opts) {
try {
binds = binds ? binds.map(fixBind) : binds;
_RS = snowflake.createStatement({
sqlText : stmt,
binds : binds
});
_ROWS = _RS.execute();
ROW_COUNT = _RS.getRowCount();
ACTIVITY_COUNT = _RS.getNumRowsAffected();
HANDLE_NOTFOUND && HANDLE_NOTFOUND(_RS);
if (INTO) return {
INTO : function () {
return INTO();
}
};
if (_OUTQUERIES.length < DYNAMIC_RESULTS) _OUTQUERIES.push(_ROWS.getQueryId());
if (opts && opts.temp) return _ROWS.getQueryId();
} catch(error) {
MESSAGE_TEXT = error.message;
SQLCODE = error.code;
SQLSTATE = error.state;
var msg = `ERROR CODE: ${SQLCODE} SQLSTATE: ${SQLSTATE} MESSAGE: ${MESSAGE_TEXT}`;
if (catchFunction) catchFunction(error);
if (!noCatch && ERROR_HANDLERS) ERROR_HANDLERS(error); else throw new Error(msg);
}
};
