Executing statements

Statements can be executed by calling the connection.execute() method. The execute() method accepts an options object that can be used to specify the SQL text and a complete callback. The complete callback is invoked when a statement has finished executing and the result is ready to be consumed:

var statement = connection.execute({
  sqlText: 'CREATE DATABASE testdb',
  complete: function(err, stmt, rows) {
    if (err) {
      console.error('Failed to execute statement due to the following error: ' + err.message);
    } else {
      console.log('Successfully executed statement: ' + stmt.getSqlText());
    }
  }
});
Copy

Note

The maximum payload size of a single request is 16 MB.

Binding statement parameters

Occasionally, you might want to bind data in a statement with a placeholder. Executing statements in this manner is useful because it helps prevent SQL injection attacks. Consider the following statement:

connection.execute({
  sqlText: 'SELECT c1 FROM (SELECT 1 AS c1 UNION ALL SELECT 2 AS c1) WHERE c1 = 1;'
});
Copy

You can achieve the same result using the following bindings:

connection.execute({
  sqlText: 'SELECT c1 FROM (SELECT :1 AS c1 UNION ALL SELECT :2 AS c1) WHERE c1 = :1;',
  binds: [1, 2]
});
Copy

The ? syntax for bindings is also supported:

connection.execute({
p
  sqlText: 'SELECT c1 FROM (SELECT ? AS c1 UNION ALL SELECT ? AS c1) WHERE c1 = ?;',
  binds: [1, 2, 1]
});
Copy

Note

There is an upper limit to the size of data that you can bind, or that you can combine in a batch. For details, see Limits on Query Text Size.

Executing a batch of SQL statements (multi-statement support)

With version 1.6.18 and later of the Node.js connector, you can send a batch of SQL statements separated by semicolons to be executed in a single request.

Note

By default, Snowflake returns an error for queries issued with multiple statements to protect against SQL injection attacks. The multiple statements feature makes your system more vulnerable to SQL injections, and so it should be used carefully. You can reduce the risk by using the MULTI_STATEMENT_COUNT parameter to specify the number of statements to be executed, which makes it more difficult to inject a statement by appending to it.

You can execute multiple statements as a batch in the same way you execute queries with single statements, except that the query string contains multiple statements separated by semicolons. Note that multiple statements execute sequentially, not in parallel. The MULTI_STATEMENT_COUNT parameter specifies the exact number of statements the batch contains.

For example, if you set MULTI_STATEMENT_COUNT=3, a batch statement must include precisely three statements. If you submit a batch statement with any other number of statements, the Node.js driver rejects the request. You can set MULTI_STATEMENT_COUNT=0 to allow batch queries to contain any number of statements. However, be aware that using this value reduces the protection against SQL injection attacks.

You can set this parameter at the session level using the following command, or you can set the value separately each time you submit a query.

ALTER SESSION SET multi_statement_count = <n>
Copy

By setting the value the session level, you do not need to set it when you execute each time you execute a batch statement. The following example sets the number of statements at the session level to three and then executes three SQL statements:

var statement = connection.execute({
    sqlText: "ALTER SESSION SET multi_statement_count=0",
    complete: function (err, stmt, rows) {
      if (err) {
        console.error('1 Failed to execute statement due to the following error: ' + err.message);
      } else {
        testMulti();
      }
    }
  });
function testMulti() {
  console.log('select bind execute.');
  var selectStatement = connection.execute({
    sqlText: "create or replace table test(n int); insert into test values(1), (2); select * from test order by n",
    complete: function (err, stmt, rows) {
      if (err) {
        console.error('1 Failed to execute statement due to the following error: ' + err.message);
      }
      else {
        console.log('==== complete');
        console.log('==== sqlText=' + stmt.getSqlText());
        if(stmt.hasNext())
        {
          stmt.NextResult();
        }
        else {
          // do something else, e.g. close the connection
        }
      }
    }
  });
}
Copy

You can also set the number of statements in a batch each time you execute a multi-statement query by setting MULTI_STATEMENT_COUNT as a parameter for the connection.execute function. The following example sets the number of statements to three for the batch and includes three SQL statements in the batch query:

var selectStatement = connection.execute({
    sqlText: "CREATE OR REPLACE TABLE test(n int); INSERT INTO test values(1), (2); SELECT * FROM test ORDER BY n",
    parameters: { MULTI_STATEMENT_COUNT: 3 },
    complete: function (err, stmt, rows) {
      if (err) {
        console.error('1 Failed to execute statement due to the following error: ' + err.message);
      }
      else {
        console.log('==== complete');
        console.log('==== sqlText=' + stmt.getSqlText());
        if(stmt.hasNext())
        {
          stmt.NextResult();
        }
        else {
          // do something else, e.g. close the connection
        }
      }
    }
  });
Copy

Binding an array for bulk insertions

Binding an array of data is supported for bulk INSERT operation. Pass an array of arrays as follows:

connection.execute({
  sqlText: 'INSERT INTO t(c1, c2, c3) values(?, ?, ?)',
  binds: [[1, 'string1', 2.0], [2, 'string2', 4.0], [3, 'string3', 6.0]]
});
Copy

Note

Binding a large array will impact performance and might be rejected if the size of data is too large to be handled by the server.

Canceling statements

A statement can be canceled by calling the statement.cancel() method:

statement.cancel(function(err, stmt) {
  if (err) {
    console.error('Unable to abort statement due to the following error: ' + err.message);
  } else {
    console.log('Successfully aborted statement');
  }
});
Copy

Resubmitting requests

If you are unsure whether Snowflake successfully executed an SQL statement, perhaps due to a network error or timeout, you can resubmit the same statement using its request ID. For example, suppose you submit an INSERT command to add data but did not receive an acknowledgement in a timely manner, so you don’t know what happened with the command. In this scenario, you don’t just want to execute the same command as a new command because it could result in executing the command twice, producing data duplication.

By including the request ID in the SQL statement, you can avoid the potential for data duplication. Resubmitting the request with the request ID from the initial request ensures that the resubmitted command executes only if the initial request failed. For more information, refer to Resubmitting a request to execute SQL statements.

Note

To resubmit a query using a request ID, you must use the same connection that generated the request ID. If you want to retrieve the result of query from a different connection, refer to RESULT_SCAN.

The following code samples demonstrate how you can save and use a request ID to resubmit a statement. When you execute a statement, you can use the getRequestId() function to retrieve the ID of the submitted request. You can then use that ID to execute the same statement at a later time. The following example executes an INSERT statement and saves its request ID in the requestId variable.

var requestId;
connection.execute({
  sqlText: 'INSERT INTO testTable VALUES (1);',
    complete: function (err, stmt, rows)
    {
      var stream = stmt.streamRows();
      requestId = stmt.getRequestId();   // Retrieves the request ID
      stream.on('data', function (row)
      {
        console.log(row);
      });
      stream.on('end', function (row)
      {
        console.log('done');
      });
    }
});
Copy

If you do not receive an acknowledgement that the command executed successfully, you can resubmit the request using the saved request ID as shown below.

connection.execute({
  sqlText: 'INSERT INTO testTable VALUES (1);',  // optional
    requestId: requestId,  // Uses the request ID from before
    complete: function (err, stmt, rows)
    {
      var stream = stmt.streamRows();
      stream.on('data', function (row)
      {
        console.log(row);
      });
      stream.on('end', function (row)
      {
        console.log('done');
      });
  }
});
Copy

If you choose to resubmit a request with a requestId and sqlText, be aware of the following interactions:

  • If the requestId already exists, meaning it matches a previous request, the command ignores the sqlText query and resubmits the query from the original command.

  • If the requestId does not exist, meaning it does not match a previous request, the command executes the sqlText query.