Executing statements¶

Execute queries asynchronously¶

The Snowflake Node.js Driver supports asynchronous queries (i.e. queries that return control to the user before the query completes). You can start a query, then use polling to determine when the query has completed. After the query completes, you can read the result set.

You enable asynchronous queries by including asyncExec: true in the connection.execute method.

The following example shows how to execute queries asynchronously using a Promise.

let queryId;
let statement;

// 1. Execute query with asyncExec set to true
await new Promise((resolve) =>
{
  connection.execute({
    sqlText: 'CALL SYSTEM$WAIT(3, \'SECONDS\')',
    asyncExec: true,
    complete: async function (err, stmt, rows)
    {
      queryId = stmt.getQueryId(); // Get the query ID
      resolve();
    }
  });
});

// 2. Get results using the query ID
const statement = await connection.getResultsFromQueryId({ queryId: queryId });
await new Promise((resolve, reject) =>
{
  var stream = statement.streamRows();
  stream.on('error', function (err)
  {
    reject(err);
  });
  stream.on('data', function (row)
  {
    console.log(row);
  });
  stream.on('end', function ()
  {
    resolve();
  });
});

You can also use callbacks to monitor asynchronous queries, as shown in the following example.

1. Enable asynchronous queries by including asyncExec: true in the connection.execute method.

   // 1. Execute query with asyncExec set to true
   connection.execute({
     sqlText: 'CALL SYSTEM$WAIT(3, \'SECONDS\')',
     asyncExec: true,
     complete: async function (err, stmt, rows)
     {
       let queryId = stmt.getQueryId();
   
       // 2. Get results using the query ID
       connection.getResultsFromQueryId({
         queryId: queryId,
         complete: async function (err, _stmt, rows)
         {
           console.log(rows);
         }
       });
     }
   });
   

   Copy

2. Check on the status of the query, which was submitted to be executed asynchronously.

   let queryId;
   
   // 1. Execute query with asyncExec set to true
   await new Promise((resolve, reject) =>
   {
     statement = connection.execute({
       sqlText: 'CALL SYSTEM$WAIT(3, \'SECONDS\')',
       asyncExec: true,
       complete: async function (err, stmt, rows)
       {
         queryId = statement.getQueryId();
         resolve();
       }
     });
   });
   
   // 2. Check query status until it's finished executing
   const seconds = 2;
   let status = await connection.getQueryStatus(queryId);
   while (connection.isStillRunning(status))
   {
     console.log(`Query status is ${status}, timeout for ${seconds} seconds`);
   
     await new Promise((resolve) =>
     {
       setTimeout(() => resolve(), 1000 * seconds);
     });
   
     status = await connection.getQueryStatus(queryId);
   }
   
   console.log(`Query has finished executing, status is ${status}`);
   

   Copy

Execute 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.

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>

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

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

You can also bind arrays of VARIANT data. To illustrate, assume you create a table with a column of VARIANT data, as follows:

create or replace table test(id int, foo variant);

You could then execute the following script:

// standard stuff like defining connection, etc
var statement = connection.execute({
    // table columns are id: int, foo: variant
    sqlText: 'insert into test_db.public.test select value:id, value:foo from table(flatten(parse_json(?)))',
    binds: [JSON.stringify([
    {id: 1, foo: [{a: '1', b: '2'}]},
    {id: 2, foo: [{c: '3', d: '4'}]}
    ])],
    complete: function(err, stmt, rows) {
        if (err) {
            console.error('Failed to execute statement due to the following error: ' + err.message);
        } else {
            console.log('[queryID ' + statement.getStatementId() + ', requestId ' + statement.getRequestId() + '] Number of rows produced: ' + rows.length);
            // rest of the code
          }
    }
});

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.

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.