test_statement.js

exports.test = function(sql, assert){
    // Create a database
    var db = new sql.Database();

    // Execute some sql
    sqlstr = "CREATE TABLE alphabet (letter, code);";
    db.exec(sqlstr);

    var result = db.exec("SELECT name FROM sqlite_master WHERE type='table'");
    assert.deepEqual(result, [{columns:['name'], values:[['alphabet']]}],
                                    "Table properly created");

    // Prepare a statement to insert values in tha database
    var stmt = db.prepare("INSERT INTO alphabet (letter,code) VALUES (?,?)");
    // Execute the statement several times
    stmt.run(['a',1]);
    stmt.run(['b',2.2]);
    stmt.run(['c']); // The second parameter will be bound to NULL

    // Free the statement
    stmt.free();

    result = db.exec("SELECT * FROM alphabet");
    assert.deepEqual(result,
                     [{columns:['letter', 'code'], values:[['a',1],['b',2.2],['c',null]]}],
                            "Statement.run() should have added data to the database");

    db.run("CREATE TABLE data (nbr, str, no_value); INSERT INTO data VALUES (5, '粵語😄', NULL);");
    stmt = db.prepare("SELECT * FROM data");
    stmt.step(); // Run the statement
    assert.deepEqual(stmt.getColumnNames(), ['nbr','str','no_value'], 'Statement.GetColumnNames()');
    var res = stmt.getAsObject();
    assert.strictEqual(res.nbr, 5, 'Read number');
    assert.strictEqual(res.str, '粵語😄', "Read string");
    assert.strictEqual(res.no_value, null, "Read null");
    assert.deepEqual(res, {nbr:5, str:'粵語😄', no_value:null}, "Statement.getAsObject()");
    stmt.free();

    // getColumnNames() should work even if query returns no data 
    stmt = db.prepare("SELECT * FROM data WHERE nbr = -1");
    assert.deepEqual(stmt.getColumnNames(), ['nbr','str','no_value'], 'Statement.GetColumnNames()');
    stmt.free();

    stmt = db.prepare("SELECT str FROM data WHERE str=?");
    assert.deepEqual(stmt.getAsObject(['粵語😄']), {'str':'粵語😄'}, "UTF8 support in prepared statements");

    // Prepare an sql statement
    stmt = db.prepare("SELECT * FROM alphabet WHERE code BETWEEN :start AND :end ORDER BY code");
    // Bind values to the parameters
    stmt.bind([0, 256]);
    // Execute the statement
    stmt.step();
    // Get one row of result
    result = stmt.get();
    assert.deepEqual(result, ['a',1], "Binding named parameters by their position");

    // Fetch the next row of result
    result = stmt.step();
    assert.equal(result, true);
    result = stmt.get();
    assert.deepEqual(result, ['b',2.2], "Fetching the next row of result");

    // Reset and reuse at once
    result = stmt.get([0, 1]);
    assert.deepEqual(result, ['a',1], "Reset and reuse at once");

    // Pass objects to get() and bind() to use named parameters
    result = stmt.get({':start':1, ':end':1});
    assert.deepEqual(result, ['a',1], "Binding named parameters");

    // Prepare statement, pass null to bind() and check that it works
    stmt = db.prepare("SELECT 'bind-with-null'");
    result = stmt.bind(null);
    assert.equal(result, true);
    stmt.step();
    result = stmt.get();
    assert.equal(result,"bind-with-null")

    // Close the database and all associated statements
    db.close();
};

if (module == require.main) {
	const target_file = process.argv[2];
  const sql_loader = require('./load_sql_lib');
  sql_loader(target_file).then((sql)=>{
    require('test').run({
      'test statement': function(assert){
        exports.test(sql, assert);
      }
    });
  })
  .catch((e)=>{
    console.error(e);
    assert.fail(e);
  });
}