In this lab we will:

Then we will get into the Snowconvert helpers inserted into the migrated SP

Snowflake Stored Procedures are written in JavaScript.

Snowflake provides a small API in order to interact with it.

create procedure stproc1()
  returns string not null
  language javascript
  as
  -- "$$" is the delimiter for the beginning and end of the stored procedure.
  $$
  // The "snowflake" object is provided automatically in each stored procedure.
  // You don't need to create it.
  //         |||||||||
  //         vvvvvvvvv
  var statement = snowflake.createStatement(...);
  ...
  $$
  ;
```  

It's main methods are `execute` and `createStatement`.

### Execute 

```sql
var resultset = snowflake.createStatement(
   {
   sqlText: "select * from table1 where name = ?;",
   binds:[variable1]
   }
);

CreateStatement

var stmt = snowflake.createStatement(
   {
   sqlText: "INSERT INTO table2 (col1, col2) VALUES (?, ?);",
   binds:["LiteralValue1", variable2]
   }
);

NOTE: An interesting detail with snowflake is that if you define an argument like create procedure stproc1(arg1 varchar) even if that arg1 argument was defined in lowercase, inside of your proc you need to use it in uppercase like ARG1. See doc

Bindings

When you are interacting with the database you can create your sql statements by doing string manipulation:

snowflake.execute({sqlText:"select * from table1 where a=" + VAR1 });

Which mostly is easy when the arguments go at the end.

However when arguments are in the middle:

snowflake.execute({sqlText:" with query1 as (select * from table1 where code1 > " + VAR1 + " AND code2 < " + VAR2 + "), query2 as (select current_date) select current_date, name2"});

Your code read-ability starts to become complicated.

In this situations you can consider to swith to use template-strings.

snowflake.execute({sqlText:` with query1 as (select * from table1 where code1 > ${VAR1} AND code2 < ${VAR2}), query2 as (select current_date) select current_date, name2`});

That way you do not need to do a weird combination of string concatenations and is likely to get errors.

However you still have problems becuase numbers can easily be used in that way, however there is an implicit conversion from number to string and string values will need to quotes (but only if they are not null).

The snowflake API offer another option that is Bindings:

snowflake.execute({sqlText:` with query1 as (select * from table1 where code1 > ? AND code2 < ?), query2 as (select current_date) select current_date, name2`,[VAR1, VAR2]});
// or
snowflake.execute({sqlText:` with query1 as (select * from table1 where code1 > :1 AND code2 < :2), query2 as (select current_date) select current_date, name2`,binds:[VAR1, VAR2]});

As you can use you can either use the ? mark or the :number marker. You then need to pass the arguments in the bind section of the execute call.

Bindings limitations

In Javascript when a variable is defined and it has no value, it's initial value is an special one called undefined, which is not exactly the same as null

When you pass value to a snowflake API call it can either have a value or be null. If your variable was not initialized then its value is undefined and the snowflake API will throw an error.

Another limitation is the usage of bindings for DATE or TIMESTAMP values.

In JS for both of these datatypes a JS Date object is used.

However when that value is used with a binding you will get an error.

create procedure stproc1()
  returns string
  language javascript
  as
  $$
    var arg1 = new Date();
    snowflake.execute({sqlText:`create or replace temporary table temp1(value1 date)`});
    snowflake.execute({sqlText:`insert into temp1(value1) values (?)`, binds:[arg1]})
  $$
  ;
100183 (P0000): Execution error in store procedure STPROC1:                     
Invalid binds argumentFri May 21 2021 15:55:55 GMT-0700 (Pacific Daylight Time). Error: Unsupported type for binding argument 2undefined
At Snowflake.execute, line 4 position 14

To fix that you need to do an intermediate step using a function like toISOString()

create or replace procedure stproc1()
  returns string
  language javascript
  as
  $$
    var arg1 = new Date();
    snowflake.execute({sqlText:`create or replace temporary table temp1(value1 date)`});
    snowflake.execute({sqlText:`insert into temp1(value1) values (?)`, binds:[arg1.toISOString()]})
  $$
  ;

Note: when working with TIMESTAMP using only toISOString might not be enough, because it does not consider the timezone offset.

You can then defined a function like this:

var formatted = function(d){ return (new Date(d - (d.getTimezoneOffset() * 60000))).toISOString().slice(0, -1); }
create or replace procedure stproc1()
  returns string
  language javascript
  as
  $$
    var arg1 = new Date();
    var formatted = function(d){ return (new Date(d - (d.getTimezoneOffset() * 60000))).toISOString().slice(0, -1); }    
    snowflake.execute({sqlText:`create or replace temporary table temp1(value1 timestamp)`});
    snowflake.execute({sqlText:`insert into temp1(value1) values (?)`, binds:[formatted(arg1)]})
  $$
  ;

Getting information about the execute statement

If you need to get information about the SQL statement, like:

You need to use the createStatement api. To get a list of the methods you can use with your statement you can use this snippet:

create or replace procedure stproc1()
  returns string
  language javascript
  as
  $$
    var stmt = snowflake.createStatement({sqlText:`select 1`});
    var methods = Object.getOwnPropertyNames(stmt);
    return methods.join('\n');
  $$
  ;

when you run this:

call stproc1();

You will see an output like this:

+----------------------------+                                                  
| STPROC1                    |
|----------------------------|
| execute                    |
| getQueryId                 |
| getSqlText                 |
| getStatus                  |
| getColumnCount             |
| getRowCount                |
| getNumRowsInserted         |
| getNumRowsUpdated          |
| getNumRowsDeleted          |
| getNumDuplicateRowsUpdated |
| getNumRowsAffected         |
| getRequestId               |
| getStatementId             |
| getColumnSqlType           |
| getColumnType              |
| getColumnName              |
| getColumnScale             |
| isColumnNullable           |
| isColumnText               |
| isColumnBinary             |
| isColumnBoolean            |
| isColumnNumber             |
| isColumnDate               |
| isColumnVariant            |
| isColumnObject             |
| isColumnArray              |
| isColumnTimestamp          |
| isColumnTime               |
+----------------------------+

Snowflake SP have a powerful but limited API. Unlike the SQL stored procedures in javascript some operations for parameter binding or data type conversion require more work or require some additional code to provide the same functionality as in SQL.

For that reason the SnowConvert tool introduces a series of Javascript snippets or ‘helpers'. This snippets handle common repetitive tasks like:

In the following section we will dive a little deeper into this helpers to understand more.

This is a very small helper, and its purpose is to deal with some of the limitations of the Snowflake API bindings mechanism

var fixBind = function (arg) {
     arg = arg == undefined ? null : arg instanceof Date ? arg.toISOString() : arg;
     return arg;
  };

It just uses a conditional ternary operator to first validate if the argument is undefined.

Just as reminder let's try this pieces of code:

CREATE OR REPLACE PROCEDURE PROC1() RETURNS STRING LANGUAGE JAVASCRIPT AS
$$
   var a; // any non initialized variable in JS is undefined
   snowflake.execute({sqlText:`select ?`,binds:[a]});
   return "completed";
$$;
-- 
call PROC1();

If you do you should get an output like:

mrojas#SNOWCONVERT_FUNDAMENTALS_WH@SNOWCONVERT_FUNDAMENTALS.PUBLIC>call PROC1();
100183 (P0000): Execution error in store procedure PROC1:                       
Unsupported type for binding argument 2undefined
At Snowflake.execute, line 3 position 13

Now let's try this:

CREATE OR REPLACE PROCEDURE PROC1() RETURNS STRING LANGUAGE JAVASCRIPT AS
$$
   var a = new Date();
   snowflake.execute({sqlText:`select ?`,binds:[a]});
   return "completed";
$$;
-- 
call PROC1();

If you do you should get an output like:

mrojas#SNOWCONVERT_FUNDAMENTALS_WH@SNOWCONVERT_FUNDAMENTALS.PUBLIC>call PROC1();
100183 (P0000): Execution error in store procedure PROC1:                       
Invalid binds argumentMon May 24 2021 06:05:00 GMT-0700 (Pacific Daylight Time). Error: Unsupported type for binding argument 2undefined
At Snowflake.execute, line 3 position 13

And why the to ISOString ?

Well as you can see following the link it is extended textual ISO format and it is recognized by Snowflake.

NOTE: While ISOString works fine in most cases. You must notice that it will return

CREATE OR REPLACE PROCEDURE PROC1() RETURNS STRING LANGUAGE JAVASCRIPT AS
$$
   var a = new Date();
   return a.toISOString();
$$;

If you call it you will get:

mrojas#SNOWCONVERT_FUNDAMENTALS_WH@SNOWCONVERT_FUNDAMENTALS.PUBLIC>call PROC1();
+--------------------------+                                                    
| PROC1                    |
|--------------------------|
| 2021-05-24T13:08:35.791Z |
+--------------------------+

Notice the Z at the end. It means that it is always zero UTC offset.

If timezone is important, we recommend using a helper like:

var ISOStringWithTimeOffset => (d) = (new Date(d - (d.getTimezoneOffset() * 60000))).toISOString().slice(0, -1);
CREATE OR REPLACE PROCEDURE PROC1() RETURNS STRING LANGUAGE JAVASCRIPT AS
$$
   var a = new Date();
   var ISOStringWithTimeOffset = (d) => (new Date(d - (d.getTimezoneOffset() * 60000))).toISOString().slice(0, -1);
   return ` ${a.toISOString()} vrs ${ISOStringWithTimeOffset(a)}`;
$$;

Not look at the result:

mrojas#SNOWCONVERT_FUNDAMENTALS_WH@SNOWCONVERT_FUNDAMENTALS.PUBLIC>call proc1();
+-------------------------------------------------------+                       
| PROC1                                                 |
|-------------------------------------------------------|
|  2021-05-24T13:12:17.730Z vrs 2021-05-24T06:12:17.730 |
+-------------------------------------------------------+
1 Row(s) produced. Time Elapsed: 0.525s

Compare those output with the output from SELECT CURRENT_TIMESTAMP.

We will not analyze the `EXEC helper in detail.

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

First lets look at its arguments: stmt,binds,noCatch,catchFunction

| Argument | Description | | :- | :- | | stmt | this the SQL that you will execute | | binds | this value can be undefined if omitted or if not it is expected to be an array that will hold that binding values that we expect to pass to the snowflake API. If you use the ? syntax you need to provide a binding value for each ? mark. If you use the positional bindings like :1 or :2 just make sure you pass all the required arguments. By default Teradata migrations only use ? marks. | | noCatch | an optional value is use to indicate the helper that we don't any ERROR_HANDLERS to be executed. | | catchFunction| an optional value that can be used to give a function that will be called if an exception happens.|

Error Handling related functions

Let's review first the ERROR_HANDLING

try {
   // omitted
     } 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);
     }
``` 

All the helper code is wrapped inside a [`try/catch`](https://docs.snowflake.com/en/sql-reference/stored-procedures-usage.html#catching-an-error-using-try-catch) if any error happens during code execution, an Sf API Exception will be thrown.

You can try it out with this example:

```js
create procedure broken()
      returns varchar not null
      language javascript
      as
      $$
      var result = "";
      try {
          snowflake.execute( {sqlText: "Invalid Command!;"} );
          result = "Succeeded";
          }
      catch (err)  {
          result =  "Failed: Code: " + err.code + "\n  State: " + err.state;
          result += "\n  Message: " + err.message;
          result += "\nStack Trace:\n" + err.stackTraceTxt; 
          }
      return result;
      $$
      ;

Returning the the catch block part:

MESSAGE_TEXT = error.message;
SQLCODE = error.code;
SQLSTATE = error.state;
var msg = `ERROR CODE: ${SQLCODE} SQLSTATE: ${SQLSTATE} MESSAGE: ${MESSAGE_TEXT}`;

The helper will set some global SP variables. These variables use names similar to the Teradata SP vars, to make the migrated code easier to compare with the original code. The msg var is then used to populate an Error object.

There is an ERROR_HANDLER global var that can hold a reference to a function.

In Teradata you can have CONTINUE AND EXIT handlers.

REPLACE PROCEDURE DIVISION_BY_ZERO ( IN n INTEGER)
BEGIN
   DECLARE x INTEGER DEFAULT 10.00;
   DECLARE CONTINUE HANDLER FOR SQLSTATE ‘22012' SET x=0; — handles the error
   BEGIN
   DECLARE EXIT HANDLER FOR SQLSTATE ‘22012' SET x = 1/0;
   BEGIN
       DECLARE EXIT HANDLER FOR SQLSTATE ‘22012' SET x = 1/0;
       SET x = x / n;
   END;
END; END;

CALL DIVISION_BY_ZERO(0);

A continue handler during migration will be turned into a JS function, that receives a reference to the error object.

For the example above the migration tool will generate a handler like:

var continue_handler_1 = function (error) {
           {
              x = 0;
           }
};

Notice that nothing is done with the exception object. Because it is a continue handler.

For an exit handler the exception will be rethrown at the end of the code block.

Because there could be several handlers, the migration tool then populate the ERROR_HANDLERS variable with a handler function that will act as switchboard triggering the proper handler.

var ERROR_HANDLERS = function (error) {
   switch(error.state) {
      default:continue_handler_1(error);
   }
};

The body of the EXEC helper

binds = binds ? binds.map(fixBind) : binds;
_RS = snowflake.createStatement({
      sqlText : stmt,
      binds : binds
   });
_ROWS = _RS.execute();
ROW_COUNT = _RS.getRowCount();
ACTIVITY_COUNT = _RS.getNumRowsAffected();
if (INTO) return {
   INTO : function () {
      return INTO();
   }
};

The first line will set check the binds variable. Because it is assumed it is an array a map call applies the fixBind helper to each value if needed.

Next an statement is created and then executed obtaining a resultset. Those values are stored into _RS and _ROWS.

In Teradata you usually have the ROW_COUNT so we populate a global var with this value, and if the operation was a DML we set the ACTIVITY_COUNT. In Teradata this values is set for both queries and DML so you might need to check whether you actually need way or the other.

The final piece just checks if the INTO function is defined. If that is the case we return a object with a reference to it. This is just done so instead of changing: SELECT name into :name from table1 to

EXEC(`SELECT name from table1`);
[name] = INTO();
``` 
you can also use a one liner like:

```js
[name] = EXEC(`SELECT name from table1`).INTO();

This helper just makes it easier to retrieve results from a previous SELECT statement.

If will check if the last statement returned any rows by using the ROW_COUNT variable and if it did it will use the fetch helper.

var INTO = function () {
    if (ROW_COUNT) return fetch(ROW_COUNT,_ROWS,_RS); else return [];
};

This helper will return an array and we take advantage of JS destructuring assignment to make then passing values to variables easier.

This helper is used to provide a CURSOR like behaviour.

It is intented to be used as

var c = new CURSOR(`select * from table where a=?`,[a])

Althougth it looks like a function, this is an class definition. This is just a another syntax that JS allows for it.

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();
           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 = '';
     };
};

CURSOR fields

This fields are just to keep control variables about the cursor.

  var rs, rows, row_count, opened = false, resultsetTable = '', self = this;
  this.CURRENT = new Object;

rs and rows are just references to an statement and resultset object. row_count holds the number of rows in the cursor. opened is a flag to know if the cursos is already open. The resultsetTable is used because if we need to pass the cursor results a temporary table will be used. That temporary table will be created with a dynamically generated name and this variable will hold that name. self is just a variable to hold the this reference in context where there could be ambiguity and CURRENT is the current row.

OPEN function

  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();
           opened = true;
           return this;
        } catch(error) {
           ERROR_HANDLERS && ERROR_HANDLERS(error);
        }
     };

When a cursor variable is created you can call it as c.OPEN([1]) which means that when executing the query the given arguments will be used as bindings.

One important aspect to consider is that for cursor. You can defined it before the values you use are set.

For example you can have:

var c = new CURSOR(`SELECT * FROM TABLE1 WHERE A = ?`,[VAR1])

At the declaration time VAR1 could be null. but you can then have:

VAR1 = 1;

And then later do:

c.OPEN();

If we just leave as is. Then cursor will execute the statement using the current value for VAR1.

To fix that we use some lazy evaluation.

var c = new CURSOR(`SELECT * FROM TABLE1 WHERE A = ?`,()=>[VAR1])

With this the value of VAR1 will not be capture at definition time, instead the bindings will be a function that we will execute later.

We can to the same with the statement.

var c = new CURSOR(()->`SELECT * FROM ${tableName} WHERE A = ?`,()=>[VAR1])

As mentioned before if we need the results for lated the INSERT_INTO_TEMP helper will create a dynamic table name, create a temp table and insert the rows there. We then will change the query to use a SELECT * from that temp table.

The rest of the code is similar to the EXEC helper, even supporting ERROR_HANDLERS.

Next function

The next function is just used to move to the next cursor. A CURRENT object will be populated with a value for each column.

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

FETCH function

The fetch function is similar to the NEXT function. If you use FETCH you will just get a row of the current row values.

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

Usign the destructuring assignment you can do:

var c = new CURSOR(...);
//...
[value1, value2,value3] = c.FETCH();

CLOSE function

This just clear some variables and drops the temporary table if needed.

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

INTO function

This helper is just used, so you can return a reference to the last row.

  this.INTO = function () {
        return self.res;
     };
```     

It is use to the FETCH and then reading the values of teradata can be mimic.

```js
c.FETCH();
[value1, value2, value3] = c.INTO();
//BetweenFunc, function to check if an expression is between two other expressions
var BetweenFunc = function (expression,startExpr,endExpr) {
   if ([expression,startExpr,endExpr].some((arg) => arg == null)) {
      return false;
   }
   return expression >= startExpr && expression <= endExpr;
};

This helper is used to compare dates with a similar behaviour as expected in js

For example in SQL if any of the values is ‘null' then the result is null.

This does not happen in JS therefore the introduction of this code.

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

In SQL you can use LIKE expression which are similar but not equal to regular expressions. The likeFunction just tries to turn the like pattern into an standard LIKE pattern. likeFunction, function for LIKE expressions

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