Javascript Helpers

A list of helpers functions in javascript that procedures in SnowFlake can use, in order to better support several Teradata language features.

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;
    };
  • EXEC()

    The EXEC function is used to execute a SQL statement with bindings (if provided). It is executed within a try-catch block and the catch function can be customized. There are a couple of other small functions used by the EXEC routine.

    • formatDate(), a helper function to handle the UTC Time offset in dates values.

    • fixBind(), a helper function to handle bindings with undefined and dates values.

    • INTO(), a helper function to return the value (if any) of the immediately executed statement.

    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;
    };
    var EXEC = function (stmt,binds,noCatch,catchFunction) {
    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();
    }
    };
    } 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);
    }
    };
  • Cursor routines, a collection of routines to help with cursor-related operations.

    • 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(), 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;
      };
    • 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(), drops the temporary table (if it was created with INSERT_TEMP()) and unsets the necessary variables.

    • CURSOR(), the main routine which declares the needed variables and other sub-routines.

      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 = INSERT_TEMP(finalStmt,finalBinds);
      finalStmt = `SELECT * FROM ` + 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) {
      var dropStmt = `DROP TABLE ` + resultsetTable;
      snowflake.createStatement({
      sqlText : dropStmt,
      binds : []
      }).execute();
      tablelist.splice(tablelist.indexOf(resultsetTable),1);
      }
      rs = rows = row_count = undefined;
      opened = false;
      resultsetTable = '';
      };
      };

    • 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 exit or continue handlers for NOT FOUND EXCEPTIONS.

    • HANDLE_NOTFOUND(), this function uses the above IS_NOT_FOUND function to validate when an artificial error 'NOT FOUND' is being thrown.

      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;
      };
      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;
      };