JavaScript Stored Procedures API¶
This topic covers the JavaScript API for Snowflake stored procedures. The API consists of JavaScript objects and the methods in those objects.
Object: snowflake
¶
The snowflake
object is accessible by default to the JavaScript code in a stored procedure; you do not need to create the object.
This object contains the methods in the stored procedure API. For example:
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(...); ... $$ ;
More extensive code examples are provided in Working with Stored Procedures.
Constants¶
None.
Methods¶
-
addEvent
(name[, attributes])¶ Adds an event for tracing.
For more information about trace events with JavaScript, refer to Emitting Trace Events in JavaScript
- Parameters
name
The name of the event to add.
attributes
An object specifying attributes to associate with the event.
- Errors
Throws a JavaScript Error if:
name
is not a string.There are zero or more than two arguments.
- Examples
Add a
my_event
event withscore
andpass
attributes.snowflake.addEvent('my_event', {'score': 89, 'pass': true});
-
createStatement
(sql_command_object)¶ Creates a
Statement
object and returns it. Theexecute()
method for the object can be executed later.- Parameter(s)
sql_command_object
The input parameter is a JSON object (dictionary) that contains the text of the statement to be executed, and any values that should be bound to that statement.
- Returns
A
Statement
object.- Errors
Throws a JavaScript Error if:
sqlText
is missing or contains an empty query text.The statement tries to bind an argument whose data type is not supported. For information about data type mapping, see SQL and JavaScript Data Type Mapping. For more information about binding, see Binding Variables.
- Examples
This example does not bind any values:
var stmt = snowflake.createStatement( {sqlText: "INSERT INTO table1 (col1) VALUES (1);"} );
This example binds values:
var stmt = snowflake.createStatement( { sqlText: "INSERT INTO table2 (col1, col2) VALUES (?, ?);", binds:["LiteralValue1", variable2] } );
For more information about binding, including additional examples, see Binding Variables.
-
execute
(command)¶ Executes a SQL command.
- Parameters
The input is the same as for the
createStatement()
method.- Returns
A result set in the form of a
ResultSet
object.- Errors
Throws a JavaScript Error if:
An error, such as a compile error, occurred while executing the query.
sqlText
is missing or contains an empty query text.The statement tries to bind an argument whose data type is not supported. For information about data type mapping, see SQL and JavaScript Data Type Mapping. For more information about binding, including additional examples, see Binding Variables.
Note
This
execute()
method (e.g.snowflake.execute()
) is not exactly the same as the method in theStatement
object (e.g.Statement.execute()
).
-
log
(level, message)¶ Logs a message at the specified severity level.
- Parameters
level
The severity level at which to log the message. You can specify one of the following strings:
'off'
'trace'
'debug'
'info'
'warn'
'error'
'fatal'
message
The message to log.
- Errors
Throws a JavaScript error if:
level
is not a string.level
is not one of the supportedlevel
values listed above.
- Examples
snowflake.log("error", "Error message");
-
setSpanAttribute
(key, value)¶ Sets an attribute for the current span when tracing events.
For more information about trace events with JavaScript, refer to Emitting Trace Events in JavaScript
- Parameters
key
The attribute’s key.
value
The attribute’s value.
- Errors
Throws a JavaScript error if:
Two arguments aren’t specified.
key
is not a string.
- Examples
Set an attribute whose key is
example.boolean
and whose value istrue
.snowflake.setSpanAttribute("example.boolean", true);
Object: Statement
¶
A stored procedure Statement
object provides the methods for executing a query statement and accessing
metadata (such as column data types) about the statement.
At the time the Statement object is created, the SQL is parsed, and a prepared statement is created.
Constants¶
None.
Methods¶
-
execute
() This method executes the prepared statement stored in this
Statement
object.- Parameters
None because the method uses information that is already stored in the
Statement
object.- Returns
A result set in the form of a
ResultSet
object.- Errors
Throws a JavaScript Error if the query fails.
- Examples
Note
This
execute()
method (e.g.Statement.execute()
) is not exactly the same as the method in thesnowflake
object (e.g.snowflake.execute()
).snowflake.execute(statement_in_JSON_form)
requires a parameter, which is the SQL statement to be executed.Statement.execute()
takes no parameter; it uses the SQL statement that was specified at the time the Statement object was created.
-
getColumnCount
()¶ This method returns the number of columns in the result set for an executed query. If the query has not yet been executed, this method throws an Error.
- Parameters
None.
- Returns
The number of columns.
- Errors
Throw a JavaScript Error if the statement has not yet been executed (and thus the number of returned columns cannot necessarily be determined).
- Examples
var column_count = statement.getColumnCount();
-
getColumnName
(colIdx)¶ This method returns the name of the specified column.
- Parameters
The index number of the column (starting from
1
, not0
).- Returns
The name of the column.
- Errors
Throws a JavaScript Error if:
The
Statement
has not yet been executed.No column with the specified index exists.
-
getColumnScale
(colIdx)¶ This method returns the scale of the specified column. The scale is the number of digits after the decimal point. The scale of the column was specified in the CREATE TABLE or ALTER TABLE statement. For example:
create table scale_example ( n10_4 numeric(10, 4) // Precision is 10, Scale is 4. );
Although this method can be called for any data type, it is intended for use with numeric data types.
- Parameters
The index of the column for which you want the scale (starting from
1
, not0
).- Returns
The scale of the column (for numeric columns);
0
for non-numeric (columns).- Errors
Throws a JavaScript Error if:
The
Statement
has not yet been executed.No column with the specified index exists.
- Examples
See Working with Stored Procedures (search for
getColumnScale()
).
-
getColumnSqlType
(colIdx|colName)¶ This method returns the SQL data type of the specified column.
- Parameters
Either the index number of the column (starting from
1
, not0
) or the name of the column. (The method is overloaded to accept different data types as parameters.)The column name should be all uppercase unless double quotes were used in the column name when the table was created (i.e. the case of the column name was preserved).
- Returns
The SQL data type of the column.
- Errors
Throws a JavaScript Error if:
The
Statement
has not yet been executed.No column with the specified name or index exists.
-
getColumnType
(colIdx|colName)¶ This method returns the JavaScript data type of the specified column.
- Parameters
Either the index number of the column (starting from
1
, not0
) or the name of the column. (The method is overloaded to accept different data types as parameters.)The column name should be all uppercase unless double quotes were used in the column name when the table was created (i.e. the case of the column name was preserved).
- Returns
The JavaScript data type of the column.
- Errors
Throws a JavaScript Error if:
The
Statement
has not yet been executed.No column with the specified index or name exists.
-
getNumDuplicateRowsUpdated
()¶ This method returns the number of “duplicate” rows (often called multi-joined rows) updated by this Statement. (For information about how multi-joined rows are formed, see the Usage Notes and Examples for the UPDATE statement.)
- Parameters
None.
- Returns
A value of type Number that indicates the number of multi-joined rows updated.
- Errors
Throws a JavaScript error if the statement has not yet been executed.
-
getNumRowsAffected
()¶ This method returns the number of rows affected (e.g. inserted/updated/deleted) by this Statement.
If more than one type of change applies (e.g. a MERGE operation inserted some rows and updated others), then the number is the total number of rows affected by all of the changes.
- Parameters
None.
- Returns
A value of type Number that indicates the number of rows affected.
- Errors
Throws a JavaScript error if the statement has not yet been executed.
-
getNumRowsDeleted
()¶ This method returns the number of rows deleted by this Statement.
- Parameters
None.
- Returns
A value of type Number that indicates the number of rows deleted.
- Errors
Throws a JavaScript error if the statement has not yet been executed.
-
getNumRowsInserted
()¶ This method returns the number of rows inserted by this Statement.
- Parameters
None.
- Returns
A value of type Number that indicates the number of rows inserted.
- Errors
Throws a JavaScript error if the statement has not yet been executed.
-
getNumRowsUpdated
()¶ This method returns the number of rows updated by this Statement.
- Parameters
None.
- Returns
A value of type Number that indicates the number of rows updated.
- Errors
Throws a JavaScript error if the statement has not yet been executed.
-
getRowCount
()¶ This method returns the number of rows in the result set for an executed query. If the query has not yet been executed, this method throws an Error.
- Parameters
None.
- Returns
The number of rows.
- Errors
Throw a JavaScript Error if the statement has not yet been executed (and thus the number of returned rows cannot be determined).
- Examples
var row_count = statement.getRowCount();
-
getQueryId
()¶ This method returns the UUID of the most recent query executed.
- Parameters
None.
- Returns
A string containing a UUID, which is the query ID.
- Errors
If no query has been executed yet by this statement, the method throws the error “Statement is not executed yet.”
- Examples
var queryId = statement.getQueryId();
-
getSqlText
()¶ This method returns the text of the prepared query in the
Statement
object.- Parameters
None.
- Returns
A string of the prepared query text.
- Errors
None.
- Examples
var queryText = statement.getSqlText();
-
isColumnNullable
(colIdx)¶ This method returns whether the specified column allows SQL NULL values.
- Parameters
The index of the column (starting from
1
, not0
).- Returns
true
if the column allows SQL NULL values; otherwise,false
.- Errors
Throws a JavaScript Error if:
The
Statement
has not yet been executed.No column with the specified index exists.
-
isColumnText
(colIdx)¶ This method returns true if the column data type is one of the following SQL text data types:
CHAR or CHAR(N), as well as their synonyms CHARACTER and CHARACTER(N)
VARCHAR or VARCHAR(N)
STRING
TEXT
Otherwise, it returns false.
- Parameters
The index of the column (starting from
1
, not0
).- Returns
true
if the column data type is one of the SQL text data types;false
for all other data types.- Errors
Throws a JavaScript Error if:
The
Statement
has not yet been executed.No column with the specified index exists.
Note
The API provides several methods for determining the data type of a column. The first method is described in detail above. The remaining methods have the same parameters and errors; the only difference is the return value.
-
isColumnArray
(colIdx)¶ - Returns
true
if the column data type is ARRAY (for semi-structured data);false
for all other data types.
-
isColumnBinary
(colIdx)¶ - Returns
true
if the column data type is BINARY or VARBINARY;false
for all other data types.
-
isColumnBoolean
(colIdx)¶ - Returns
true
if the column data type is BOOLEAN;false
for all other data types.
-
isColumnDate
(colIdx)¶ - Returns
true
if the column data type is DATE;false
for all other data types.
-
isColumnNumber
(colIdx)¶ - Returns
true
if the column data type is one of the SQL numeric types (NUMBER, NUMERIC, DECIMAL, INT, INTEGER, BIGINT, SMALLINT, TINYINT, BYTEINT, FLOAT, FLOAT4, FLOAT8, DOUBLE, DOUBLE PRECISION, or REAL);false
for all other data types.
-
isColumnObject
(colIdx)¶ - Returns
true
if the column data type is OBJECT (for semi-structured data);false
for all other data types.
-
isColumnTime
(colIdx)¶ - Returns
true
if the column data type is TIME or DATETIME;false
for all other data types.
-
isColumnTimestamp
(colIdx)¶ - Returns
true
if the column data type is one of the SQL timestamp types (TIMESTAMP, TIMESTAMP_LTZ, TIMESTAMP_NTZ, or TIMESTAMP_TZ);false
for all other data types, including other date and time data types (DATE, TIME, or DATETIME).
-
isColumnVariant
(colIdx)¶ - Returns
true
if the column data type is VARIANT (for semi-structured data);false
for all other data types.
Object: ResultSet
¶
This object contains the results returned by a query. The results are treated as a set of zero or more rows, each of which contains one or more columns. The term
“set” is not used here in the mathematical sense. In mathematics, a set is unordered, whereas a ResultSet
has an order.
A ResultSet
is similar in some ways to the concept of a SQL cursor. For example, you can see one row at a time in a ResultSet
, just as you can see
one row at a time in a cursor.
Typically, after you retrieve a ResultSet
, you iterate through it by repeating the following operations:
Call
next()
to get the next row.Retrieve data from the current row by calling methods such as
getColumnValue()
.
If you do not know enough about the data in the ResultSet
(e.g. you do not know the data type of each column), then you can call other methods that provide information about
the data.
Some of the methods of the ResultSet
object are similar to the methods of the Statement
object. For example, both objects have a
getColumnSqlType(colIdx)
method.
Constants¶
None.
Methods¶
-
getColumnCount
() This method returns the number of columns in this ResultSet.
- Parameters
None.
- Returns
A value of type Number that indicates the number of columns.
- Errors
None.
-
getColumnSqlType
(colIdx|colName) This method returns the SQL data type of the specified column.
- Parameters
Either the index number of the column (starting from
1
, not0
) or the name of the column. (The method is overloaded to accept different data types as parameters.)The column name should be all uppercase unless double quotes were used in the column name when the table was created (i.e. the case of the column name was preserved).
- Returns
The SQL data type of the column.
- Errors
Throws a JavaScript Error if:
ResultSet
is empty ornext()
has not yet been called.No column with the specified index or name exists.
-
getColumnValue
(colIdx|colName)¶ This method returns the value of a column in the current row (i.e. the row most recently retrieved by
next()
).- Parameters
Either the index number of the column (starting from
1
, not0
) or the name of the column. (The method is overloaded to accept different data types as parameters.)The column name should be all uppercase unless double quotes were used in the column name when the table was created (i.e. the case of the column name was preserved).
- Returns
The value of the specified column.
- Errors
Throws a JavaScript Error if:
ResultSet
is empty ornext()
has not yet been called.No column with the specified index or name exists.
- Examples
Convert a row in the database into a JavaScript array:
var valueArray = []; // For each row... while (myResultSet.next()) { // Append each column of the current row... valueArray.push(myResultSet.getColumnValue('MY_COLUMN_NAME1')); valueArray.push(myResultSet.getColumnValue('MY_COLUMN_NAME2')); ... // Do something with the row of data that we retrieved. f(valueArray); // Reset the array before getting the next row. valueArray = []; }
Also, a column’s value can be accessed as a property of the
ResultSet
object (e.g.myResultSet.MY_COLUMN_NAME
).var valueArray = []; // For each row... while (myResultSet.next()) { // Append each column of the current row... valueArray.push(myResultSet.MY_COLUMN_NAME1); valueArray.push(myResultSet.MY_COLUMN_NAME2); ... // Do something with the row of data that we retrieved. f(valueArray); // Reset the array before getting the next row. valueArray = []; }
Note
Remember that unless the column name was delimited with double quotes in the CREATE TABLE statement, the column name should be all uppercase in the JavaScript code.
-
getColumnValueAsString
(colIdx|colName)¶ This method returns the value of a column as a string, which is useful when you need a column value regardless of the original data type in the table.
The method is identical to the method
getColumnValue()
except that it returns a string value.For more details, see
getColumnValue()
.
-
getNumRowsAffected
() This method returns the number of rows affected (e.g. inserted/updated/deleted) by the Statement that generated this ResultSet.
If more than one type of change applies (e.g. a MERGE operation inserted some rows and updated others), then the number is the total number of rows affected by all of the changes.
- Parameters
None.
- Returns
A value of type Number that indicates the number of rows affected.
- Errors
None.
-
getQueryId
() This method returns the UUID of the most recent query executed.
- Parameters
None.
- Returns
A string containing a UUID, which is the query ID.
- Examples
var queryId = resultSet.getQueryId();
-
getRowCount
() This method returns the number of rows in this ResultSet. (This is the total number of rows, not the number of rows that haven’t been consumed yet.)
- Parameters
None.
- Returns
A value of type Number that indicates the number of rows.
- Errors
None.
-
next
()¶ This method gets the next row in the
ResultSet
and makes it available for access.This method does not return the new data row. Instead, it makes the row available so that you can call methods such as
ResultSet.getColumnValue()
to retrieve the data.Note that you must call
next()
for each row in the result set, including the first row.- Parameters
None.
- Returns
true
if it retrieved a row andfalse
if there are no more rows to retrieve.Thus, you can iterate through
ResultSet
untilnext()
returns false.- Errors
None.
Object: SfDate
¶
JavaScript does not have a native data type that corresponds to the Snowflake SQL data types
TIMESTAMP_LTZ, TIMESTAMP_NTZ, and TIMESTAMP_TZ. When you retrieve a value of type TIMESTAMP from the database
and want to store it as a JavaScript variable (for example, copy the value from a ResultSet to a JavaScript variable),
use the Snowflake-defined JavaScript data type SfDate
.
The SfDate
(“SnowFlake Date”) data type is an extension of the JavaScript date data type.
SfDate
has extra methods, which are documented below.
Constants¶
None.
Methods¶
Unless otherwise specified, the examples below assume UTC time zone.
-
getEpochSeconds
()¶ This method returns the number of seconds since the beginning of “the epoch” (midnight January 1, 1970).
- Parameters
None.
- Returns
The number of seconds between midnight January 1, 1970 and the timestamp stored in the variable.
- Examples
Create the stored procedure:
CREATE OR REPLACE PROCEDURE test_get_epoch_seconds(TSV VARCHAR) RETURNS FLOAT LANGUAGE JAVASCRIPT AS $$ var sql_command = "SELECT '" + TSV + "'::TIMESTAMP_NTZ;"; var stmt = snowflake.createStatement( {sqlText: sql_command} ); var resultSet = stmt.execute(); resultSet.next(); var my_sfDate = resultSet.getColumnValue(1); return my_sfDate.getEpochSeconds(); $$ ;
Pass the procedure different timestamps and retrieve the number of seconds since the epoch for each timestamp.
CALL test_get_epoch_seconds('1970-01-01 00:00:00.000000000'); +------------------------+ | TEST_GET_EPOCH_SECONDS | |------------------------| | 0 | +------------------------+
CALL test_get_epoch_seconds('1970-01-01 00:00:01.987654321'); +------------------------+ | TEST_GET_EPOCH_SECONDS | |------------------------| | 1 | +------------------------+
CALL test_get_epoch_seconds('1971-01-01 00:00:00'); +------------------------+ | TEST_GET_EPOCH_SECONDS | |------------------------| | 31536000 | +------------------------+
-
getNanoSeconds
()¶ This method returns the value of the nanoseconds field of the object. Note that this is just the fractional seconds, not the nanoseconds since the beginning of the epoch. Thus the value is always between 0 and 999999999.
- Parameters
None.
- Returns
The number of nanoseconds.
- Examples
Create the stored procedure:
CREATE OR REPLACE PROCEDURE test_get_nano_seconds2(TSV VARCHAR) RETURNS FLOAT LANGUAGE JAVASCRIPT AS $$ var sql_command = "SELECT '" + TSV + "'::TIMESTAMP_NTZ;"; var stmt = snowflake.createStatement( {sqlText: sql_command} ); var resultSet = stmt.execute(); resultSet.next(); var my_sfDate = resultSet.getColumnValue(1); return my_sfDate.getNanoSeconds(); $$ ; -- Should be 0 nanoseconds. -- (> SNIPPET_TAG=query_03_01 CALL test_get_nano_seconds2('1970-01-01 00:00:00.000000000');
Pass the procedure different timestamps and retrieve the number of nanoseconds from each.
CALL test_get_nano_seconds2('1970-01-01 00:00:00.000000000'); +------------------------+ | TEST_GET_NANO_SECONDS2 | |------------------------| | 0 | +------------------------+
CALL test_get_nano_seconds2('1970-01-01 00:00:01.987654321'); +------------------------+ | TEST_GET_NANO_SECONDS2 | |------------------------| | 987654321 | +------------------------+
CALL test_get_nano_seconds2('1971-01-01 00:00:00.000123456'); +------------------------+ | TEST_GET_NANO_SECONDS2 | |------------------------| | 123456 | +------------------------+
-
getScale
()¶ This method returns the precision of the data type, i.e. the number of digits after the decimal point. For example, the precision of TIMESTAMP_NTZ(3) is 3 (milliseconds). The precision of TIMESTAMP_NTZ(0) is 0 (no fractional seconds). The precision of TIMSTAMP_NTZ is 9 (nanoseconds).
The minimum is 0. The maximum is 9 (precision is to 1 nanosecond). The default precision is 9.
- Parameters
None.
- Returns
The number of digits after the decimal place (number of digits in the fractional seconds field).
- Examples
Create the stored procedure:
CREATE OR REPLACE PROCEDURE test_get_scale(TSV VARCHAR, SCALE VARCHAR) RETURNS FLOAT LANGUAGE JAVASCRIPT AS $$ var sql_command = "SELECT '" + TSV + "'::TIMESTAMP_NTZ(" + SCALE + ");"; var stmt = snowflake.createStatement( {sqlText: sql_command} ); var resultSet = stmt.execute(); resultSet.next(); var my_sfDate = resultSet.getColumnValue(1); return my_sfDate.getScale(); $$ ; -- Should be 0. -- (> SNIPPET_TAG=query_04_01 CALL test_get_scale('1970-01-01 00:00:00', '0');
In this example, the timestamp is defined as TIMESTAMP_NTZ(0), so the precision is 0.
CALL test_get_scale('1970-01-01 00:00:00', '0'); +----------------+ | TEST_GET_SCALE | |----------------| | 0 | +----------------+
In this example, the timestamp is defined as TIMESTAMP_NTZ(2), so the precision is 2.
CALL test_get_scale('1970-01-01 00:00:01.123', '2'); +----------------+ | TEST_GET_SCALE | |----------------| | 2 | +----------------+
In this example, the timestamp is defined as TIMESTAMP_NTZ, so the precision is 9, which is the default.
CALL test_get_scale('1971-01-01 00:00:00.000123456', '9'); +----------------+ | TEST_GET_SCALE | |----------------| | 9 | +----------------+
-
getTimezone
()¶ This method returns the timezone as the number of minutes before or after UTC.
- Parameters
None.
- Returns
The timezone as a number of minutes before or after UTC.
- Examples
Create the stored procedure:
CREATE OR REPLACE PROCEDURE test_get_Timezone(TSV VARCHAR) RETURNS FLOAT LANGUAGE JAVASCRIPT AS $$ var sql_command = "SELECT '" + TSV + "'::TIMESTAMP_TZ;"; var stmt = snowflake.createStatement( {sqlText: sql_command} ); var resultSet = stmt.execute(); resultSet.next(); var my_sfDate = resultSet.getColumnValue(1); return my_sfDate.getTimezone(); $$ ;
In this example, the time zone is 8 hours (480 minutes) behind UTC.
CALL test_get_timezone('1970-01-01 00:00:01-08:00'); +-------------------+ | TEST_GET_TIMEZONE | |-------------------| | -480 | +-------------------+
In this example, the time zone is 11 hours (660 minutes) ahead of UTC.
CALL test_get_timezone('1971-01-01 00:00:00.000123456+11:00'); +-------------------+ | TEST_GET_TIMEZONE | |-------------------| | 660 | +-------------------+
-
toString
()¶ - Parameters
None.
- Returns
This method returns a string representation of the timestamp.
- Examples
This shows a simple example of creating an
SfDate
and calling itstoString
method:CREATE OR REPLACE PROCEDURE test_toString(TSV VARCHAR) RETURNS VARIANT LANGUAGE JAVASCRIPT AS $$ var sql_command = "SELECT '" + TSV + "'::TIMESTAMP_TZ;"; var stmt = snowflake.createStatement( {sqlText: sql_command} ); var resultSet = stmt.execute(); resultSet.next(); var my_sfDate = resultSet.getColumnValue(1); return my_sfDate.toString(); $$ ;
CALL test_toString('1970-01-02 03:04:05'); +------------------------------------------------------------------+ | TEST_TOSTRING | |------------------------------------------------------------------| | "Fri Jan 02 1970 03:04:05 GMT+0000 (Coordinated Universal Time)" | +------------------------------------------------------------------+