SnowConvert: Teradata Procedures Helpers¶
Cursor Helper¶
The cursor helper is a function that contains the main four actions that Teradata cursors perform such as Open, Fetch, Next, and Close.
CURSOR(), the main routine which declares the needed variables and other sub-routines.
OPEN(), opens the cursor executing the given statement, and updates the necessary variables.
NEXT(), moves the cursor to the next row (if any) of the statement and sets every column value to the current row.
FETCH(), obtains the values (if any) from the response of the statement executed.
CLOSE(), removes the temporary table from the _OUTQUERIES (if it was added in the EXEC helper) and unsets the necessary variables.
Note
Some parts of the output code are omitted for clarity reasons.
Cursor Sample Usage¶
Teradata
-- Additional Params: -t JavaScript
Replace procedure procedure1()
dynamic result sets 2
begin
-------- Local variables --------
declare sql_cmd varchar(20000) default ' ';
declare num_cols integer;
------- Declare cursor with return only-------
declare resultset cursor with return only for firststatement;
------- Declare cursor -------
declare cur2 cursor for select count(columnname) from table1;
-------- Set --------
set sql_cmd='sel * from table1';
-------- Prepare cursor --------
prepare firststatement from sql_cmd;
-------- Open cursors --------
open resultset;
open cur1;
-------- Fetch -------------
fetch cur1 into val1, val2;
-------- Close cursor --------
close cur1;
end;
Snowflake output
CREATE OR REPLACE PROCEDURE procedure1 ()
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.
//------ Local variables --------
var SQL_CMD = ` `;
var NUM_COLS;
var RESULTSET = new CURSOR(() => FIRSTSTATEMENT,[],true);
//----- Declare cursor -------
var CUR2 = new CURSOR(`SELECT
count(columnname)
from
table1`,[],false);
//------ Set --------
SQL_CMD = `SELECT
* from
table1`;
//------ Prepare cursor --------
var FIRSTSTATEMENT = SQL_CMD;
//------ Open cursors --------
RESULTSET.OPEN();
CUR1.OPEN();
//------ Fetch -------------
CUR1.FETCH() && ([val1,val2] = CUR1.INTO());
//------ Close cursor --------
CUR1.CLOSE();
return PROCRESULTS();
$$;
Cursor Helper Function Definition¶
var CURSOR = function (stmt,binds,withReturn) {
var rs, rows, row_count, opened = false, resultsetTable = '', self = this;
this.CURRENT = new Object;
this.INTO = function () {
return self.res;
};
this.OPEN = function (usingParams) {
try {
if (usingParams) binds = usingParams;
if (binds instanceof Function) binds = binds();
var finalBinds = binds && binds.map(fixBind);
var finalStmt = stmt instanceof Function ? stmt() : stmt;
if (withReturn) {
resultsetTable = EXEC(finalStmt,finalBinds,true,null,{
temp : true
});
finalStmt = `SELECT * FROM TABLE(RESULT_SCAN('${resultsetTable}'))`;
finalBinds = [];
}
rs = snowflake.createStatement({
sqlText : finalStmt,
binds : finalBinds
});
rows = rs.execute();
row_count = rs.getRowCount();
ACTIVITY_COUNT = rs.getRowCount();
opened = true;
return this;
} catch(error) {
ERROR_HANDLERS && ERROR_HANDLERS(error);
}
};
this.NEXT = function () {
if (row_count && rows.next()) {
this.CURRENT = new Object;
for(let i = 1;i <= rs.getColumnCount();i++) {
(this.CURRENT)[rs.getColumnName(i)] = rows.getColumnValue(i);
}
return true;
} else return false;
};
this.FETCH = function () {
self.res = [];
self.res = fetch(row_count,rows,rs);
if (opened) if (self.res.length > 0) {
SQLCODE = 0;
SQLSTATE = '00000';
} else {
SQLCODE = 7362;
SQLSTATE = '02000';
var fetchError = new Error('There are not rows in the response');
fetchError.code = SQLCODE;
fetchError.state = SQLSTATE;
if (ERROR_HANDLERS) ERROR_HANDLERS(fetchError);
} else {
SQLCODE = 7631;
SQLSTATE = '24501';
}
return self.res && self.res.length > 0;
};
this.CLOSE = function () {
if (withReturn && _OUTQUERIES.includes(resultsetTable)) {
_OUTQUERIES.splice(_OUTQUERIES.indexOf(resultsetTable),1);
}
rs = rows = row_count = undefined;
opened = false;
resultsetTable = '';
};
};
Known Issues ¶
No issues were found.
Exec Helper¶
Syntax¶
EXEC(stmt)
EXEC(stmt, binds)
EXEC(stmt, binds, noCatch)
EXEC(stmt, binds, noCatch, catchFunction)
EXEC(stmt, binds, noCatch, catchFunction, opts)
Parameters¶
stmt¶
The string of the SQL statement to execute.
binds (optional)¶
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;
};
Note
Some parts of the output code are omitted for clarity reasons.
Exec Usage Sample¶
Teradata
-- 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
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);
}
};
Known Issues ¶
No issues were found.
Related EWIs ¶
No related EWIs.
Functional Equivalence Helpers¶
Depending on what is in each Stored Procedure in Teradata, SnowConvert will create one or more of the following javascript functions inside them.
CompareDates¶
A function that compares dates handling nullity. In Javascript, it is needed to call .getTime() for date comparisons.
var CompareDates = function(value1, value2) {
var value1Time = value1 && value1.getTime() || null;
var value2Time = value2 && value2.getTime() || null;
if (value1Time == null && value2Time == null) return null; /*in SQL null == null is equal to null as well as any other comparison */
return value1Time > value2Time? 1 : value1Time<value2Time? -1 : 0;
}
BetweenFunc¶
A function to handle the BETWEEN statement in Teradata.
var BetweenFunc = function (expression,startExpr,endExpr) {
if ([expression,startExpr,endExpr].some((arg) => arg == null)) {
return false;
}
return expression >= startExpr && expression <= endExpr;
};
LikeFunction()¶
A function to handle the LIKE statement in Teradata.
var likeFunction = function (leftExpr,rightExpr) {
RegExp.escape = function (text) {
if (!arguments.callee.sRE) {
var specials = ['/','.','*','+','?','|','(',')','[',']','{','}','\\'];
arguments.callee.sRE = new RegExp('(\\' + specials.join('|\\') + ')','g');
}
return text.replace(arguments.callee.sRE,'\\$1');
}
var likeExpr = RegExp.escape(rightExpr);
var likeResult = new RegExp(likeExpr.replace('%','.*').replace('_','.')).exec(leftExpr) != null;
return likeResult;
};
ERROR\HANDLERS()**_¶
The main error-handling routine.
var continue_handler_1 = function (error) {
{
V_SQL_VALUE = SQLSTATE;
V_EXCEPTION_FLAG = `Y`;
}
};
// Main error-handling routine
var ERROR_HANDLERS = function (error) {
switch(error.state) {
//Conversion Warning - handlers for the switch default (SQLWARNING/SQLEXCEPTION/NOT FOUND) can be the following
default:continue_handler_1(error);
}
};
INSERT\TEMP**_¶
Warning
This helper has been deprecated in stored procedures since version 2.0.15.
A function to create a temporary table using the argument query with the given parameters.
var procname = `PUBLIC.Procedure1`;
var temptable_prefix, tablelist = [];
var INSERT_TEMP = function (query,parameters) {
if (!temptable_prefix) {
var sql_stmt = `select current_session() || '_' || to_varchar(current_timestamp, 'yyyymmddhh24missss')`;
var rs = snowflake.createStatement({
sqlText : sql_stmt,
binds : []
}).execute();
temptable_prefix = rs.next() && (procname + '_TEMP_' + rs.getColumnValue(1) + '_');
}
var tablename = temptable_prefix + tablelist.length;
tablelist.push(tablename);
var sql_stmt = `CREATE OR REPLACE TEMPORARY TABLE ${tablename} AS ${query}`;
snowflake.execute({
sqlText : sql_stmt,
binds : parameters
});
return tablename;
};
IS\NOT_FOUND()**_¶
A function that validates when a SELECT returns no values or a sentence affects zero rows. This is done in order to emulate the same behavior as Teradata, when there are exits or continue handlers for NOT FOUND EXCEPTIONS.
let IS_NOT_FOUND = (stmt) => {
let n = -1;
let cmd = stmt.getSqlText().replace(new RegExp("\\/\\*.*\\*\\/","gsi"),"").replace(new RegExp("--.*?\\n","gsi"),"");
let matched = cmd.match(new RegExp("\\s*(\\w+)\\s+"),"");
if (matched) {
cmd = matched[1].toUpperCase();
switch(cmd) {
case "CALL":
case "DROP":
case "CREATE":
case "ALTER":
case "SELECT":
n = stmt.getRowCount();
break;
default:n = stmt.getNumRowsAffected();
break;
}
}
return n == 0;
};
HANDLE\NOTFOUND()**_¶
This function uses the above IS_NOT_FOUND function to validate when an artificial error ‘NOT FOUND’ is being thrown.
let HANDLE_NOTFOUND = (stmt) => {
if (IS_NOT_FOUND(stmt) && (error = new Error('NOT_FOUND')) && (NOT_FOUND = true) && ([error.code,error.state] = ['020000','020000'])) throw error;
};
PROCRESULTS()¶
A function that takes zero or multiple output parameters and binds them with the _OUTQUERIES in an array in order to be returned.
let PROCRESULTS = (...OUTPARAMS) => JSON.stringify([...OUTPARAMS,[..._OUTQUERIES]]);
Known Issues ¶
No issues were found.
Related EWIs ¶
No related EWIs.
Into Helper¶
Fetch Function¶
The INTO helper uses a fetch function to get the row from a resulting query. The definition of the Fetch Function is described below.
var fetch = (count,rows,stmt) =>
(count && rows.next() && Array.apply(null,Array(stmt.getColumnCount())).map((_,i)
=> rows.getColumnValue(i + 1))) || [];
Note
Some parts of the output code are omitted for clarity reasons.
Into Sample Usage¶
Teradata
-- Additional Params: -t JavaScript
REPLACE PROCEDURE SubQuerypoc ()
BEGIN
DECLARE monat INTEGER;
SET monat = (SELECT column1
FROM table1);
END;
Snowflake output
CREATE OR REPLACE PROCEDURE SubQuerypoc ()
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.
var MONAT;
EXEC(`(SELECT column1 FROM table1)`,[]);
var subQueryVariable0;
[subQueryVariable0] = INTO();
MONAT = subQueryVariable0;
$$;
Into Helper function Definition¶
var INTO = () => fetch(ROW_COUNT,_ROWS,_RS);
Known Issues ¶
No issues were found.
Related EWIs ¶
No related EWIs.