Cursor Helper

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.

Cursor Sample Usage

Teradata

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 PUBLIC.procedure1 ()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
 	// REGION SnowConvert Helpers Code
	
	//------ Local variables --------
	var SQL_CMD = ` `;
	var NUM_COLS;
	var RESULTSET = new CURSOR(() => FIRSTSTATEMENT,[],true);
	//----- Declare cursor -------
	var CUR2 = new CURSOR(`SELECT count(columnname) FROM PUBLIC.table1`,[],false);
	//------ Set --------
	SQL_CMD = `SELECT * FROM PUBLIC.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 = '';
	      };
	};