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); } } });
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'); });
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); }); } })
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
= 0Number
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
> 0Number
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 ... } );
Specify the parameter in the connection configuration information:
var connection = snowflake.createConnection( { username: 'fakeusername', password: 'fakepassword', account: 'fakeaccountidentifier', jsTreatIntegerAsBigInt: true } );
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
}
}
});