Consuming Results¶

Returning Results Inline¶

The most common way of consuming results is by passing a complete callback to connection.execute(). When the statement has finished executing and the result is ready to be consumed, the complete callback is invoked with the result rows returned inline:

connection.execute({
  sqlText: 'SELECT * FROM sometable',
  complete: function(err, stmt, rows) {
    if (err) {
      console.error('Failed to execute statement due to the following error: ' + err.message);
    } else {
      console.log('Number of rows produced: ' + rows.length);
    }
  }
});

Copy

Streaming Results¶

It is also possible to consume a result as a stream of rows. This can be done by calling the statement.streamRows() method; this returns a Node.js Readable stream that can be used to consume rows as they are received. For more information about the Readable stream, refer to the Node.js documentation.

For example:

var statement = connection.execute({
  sqlText: 'SELECT * FROM sometable'
});

var stream = statement.streamRows();

stream.on('error', function(err) {
  console.error('Unable to consume all rows');
});

stream.on('data', function(row) {
  // consume result row...
});

stream.on('end', function() {
  console.log('All rows consumed');
});

Copy

Batch Processing Results¶

By default, the statement.streamRows() method produces a stream that includes every row in the result. However, if you only want to consume a subset of the result, or if you want to consume result rows in batches, you can call streamRows() with start and end arguments. When these additional options are specified, only rows in the requested range are streamed:

connection.execute({
  sqlText: 'SELECT * FROM sometable',
  streamResult: true, // prevent rows from being returned inline in the complete callback
  complete: function(err, stmt, rows) {
    // no rows returned inline because streamResult was set to true
    console.log('rows: ' + rows); // 'rows: undefined'

    // only consume at most the last 5 rows in the result
    rows = [];
    stmt.streamRows({
      start: Math.max(0, stmt.getNumRows() - 5),
      end: stmt.getNumRows() - 1,
    })
    .on('error', function(err) {
      console.error('Unable to consume requested rows');
    })
    .on('data', function(row) {
      rows.push(row);
    })
    .on('end', function() {
      console.log('Number of rows consumed: ' + rows.length);
    });
  }
})

Copy

Data Type Casting¶

When result rows are produced, the driver automatically maps SQL data types to their corresponding JavaScript equivalents. For example, values of type TIMESTAMP and DATE are returned as JavaScript Date objects.

For the full mapping of JavaScript to SQL data types, refer to the table below:

SQL Data Type	JavaScript Data Type	Notes
VARCHAR, CHAR, CHARACTER, STRING, TEXT	String
INT, INTEGER, BIGINT, SMALLINT	Number	This is the default mapping. Use the session parameter JS_TREAT_INTEGER_AS_BIGINT to map to JavaScript Bigint.
NUMBER(precision, scale), DECIMAL(p, s), NUMERIC(p, s) where scale = 0	Number	This is the default mapping. Use the session parameter JS_TREAT_INTEGER_AS_BIGINT to map to JavaScript Bigint.
NUMBER(precision, scale), DECIMAL(p, s), NUMERIC(p, s) where scale > 0	Number
FLOAT, FLOAT4, FLOAT8, DOUBLE, DOUBLE PRECISION, REAL	Number
TIMESTAMP, TIMESTAMP_LTZ, TIMESTAMP_NTZ, TIMESTAMP_TZ	Date	TIMESTAMP_NTZ values are returned in UTC.
DATE	Date
TIME	String	The TIME data type in SQL has no equivalent in JavaScript, so it is mapped to a JavaScript string.
BOOLEAN	Boolean
VARIANT, ARRAY, OBJECT	JSON

Fetching Integer Data Types as Bigint¶

By default, Snowflake INTEGER columns (including BIGINT, NUMBER(p, 0), etc.) are converted to JavaScript’s Number data type. However, the largest legal Snowflake integer values are larger than the largest legal JavaScript Number values. To convert Snowflake INTEGER columns to JavaScript Bigint, which can store larger values than JavaScript Number, set the session parameter JS_TREAT_INTEGER_AS_BIGINT.

There are two ways to set this parameter:

• Use the ALTER SESSION statement, as shown below:

  connection.execute( {
                      sqlText: 'ALTER SESSION SET JS_TREAT_INTEGER_AS_BIGINT = TRUE',
                      complete: function ...
                      }
                    );
  

  Copy

• Specify the parameter in the connection configuration information:

  var connection = snowflake.createConnection(
        {
        username: 'fakeusername',
        password: 'fakepassword',
        account: 'fakeaccountidentifier',
        jsTreatIntegerAsBigInt: true
        }
      );
  

  Copy

Fetching Data Types as Strings¶

When connection.execute() is called, the fetchAsString option can be set to force all numbers or dates to be returned as strings. This can be used to get:

• Formatted versions of values of type DATE and TIMESTAMP (or its variants).

• String versions of numerical SQL types that can’t be converted to JavaScript numbers without loss in precision.

For example:

connection.execute({
  sqlText: 'SELECT 1.123456789123456789123456789 as "c1"',
  fetchAsString: ['Number'],
  complete: function(err, stmt, rows) {
    if (err) {
      console.error('Failed to execute statement due to the following error: ' + err.message);
    } else {
      console.log('c1: ' + rows[0].c1); // c1: 1.123456789123456789123456789
    }
  }
});