This section describes the usage of different functions to achieve functional equivalence for Teradata cursors in JavaScript.
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.
Some parts of the output code are omitted for clarity reasons.
Cursor Sample Usage
Teradata
IN -> Teradata_01.sql
-- 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
OUT -> Teradata_01.sql
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 = '';
};
};
