The cursor helper is a function that contains the main four actions that Teradata cursors perform such as Open, Fetch, Next, and Close.
-- 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;
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()) {