How to use oracledb - 10 common examples

it('6.2.9 Negative test - bind value and type mismatch', function(done) {
      var wrongSQL = "UPDATE " + tableName + " SET content = :c WHERE num = :n RETURNING num, content INTO :rnum, :rcontent";
      var bindVar =
        {
          n: 0,
          c: { type: oracledb.STRING, dir: oracledb.BIND_IN, val: new Date(2003, 9, 23, 11, 50, 30, 123) },
          rnum: { type: oracledb.NUMBER, dir: oracledb.BIND_OUT },
          rcontent: { type: oracledb.DATE, dir: oracledb.BIND_OUT }
        };

      connection.execute(
        wrongSQL,
        bindVar,
        function(err, result) {
          should.exist(err);
          // console.log(err.message);
          // NJS-011: encountered bind value and type mismatch
          (err.message).should.startWith('NJS-011:');

          should.not.exist(result);
          done();
        }

it('70.8.3 val: null', function(done) {
      var emptybuf;
      if ( nodever6 )
        emptybuf = new Buffer.alloc ( 0 ) ;
      else
        emptybuf = new Buffer ( 0 );

      var bindVar = {
        p_inout : {
          dir:  oracledb.BIND_INOUT,
          type: oracledb.BUFFER,
          val:  emptybuf,
          maxSize: 32767
        }
      };

      connection.execute(
        sqlrun,
        bindVar,
        function(err, result) {
          should.not.exist(err);
          // console.log(result);
          should.strictEqual(result.outBinds.p_inout, null);
          done();
        }
      );
    }); // 70.8.3

it('70.5.1 basic case: a simple string', function(done) {
      var bindVar = {
        p_inout : {
          dir:  oracledb.BIND_INOUT,
          type: oracledb.STRING,
          val:  "PL/SQL Binding INOUT Scalar"
        }
      };

      connection.execute(
        sqlrun,
        bindVar,
        function(err, result) {
          should.not.exist(err);
          // console.log(result);
          should.strictEqual(result.outBinds.p_inout, "PL/SQL Binding INOUT Scalar");
          done();
        }
      );
    }); // 70.5.1

it('56.5 Fetch DATE, NUMBER column as STRING by-type and override at execute time', function(done) {
    oracledb.fetchAsString = [ oracledb.DATE, oracledb.NUMBER ];

    connection.execute(
      "SELECT 1234567 AS TS_NUM, TO_TIMESTAMP('1999-12-01 11:10:01.00123', 'YYYY-MM-DD HH:MI:SS.FF') AS TS_DATE FROM DUAL",
      [],
      {
        outFormat: oracledb.OUT_FORMAT_OBJECT,
        fetchInfo :
        {
          "TS_DATE" : { type : oracledb.DEFAULT },
          "TS_NUM"  : { type : oracledb.STRING }
        }
      },
      function(err, result) {
        should.not.exist(err);
        // console.log(result.rows[0]);
        result.rows[0].TS_DATE.should.be.an.Object;

it('221.2 set the property when using a standalone connection', async () => {
    try {
      oracledb.connectionClass = 'NODB_TEST';
      const conn = await oracledb.getConnection(dbconfig);

      await conn.close();
    } catch (err) {
      should.not.exist(err);
    }
  }); // 221.2
});

it('1.2.1 bind parameters in various ways', function(done){
      var bindValues = {
        i: 'Alan', // default is type STRING and direction Infinity
        io: { val: 'Turing', type: oracledb.STRING, dir: oracledb.BIND_INOUT },
        o: { type: oracledb.STRING, dir: oracledb.BIND_OUT }
      };
      connection.should.be.ok();
      connection.execute(
        "BEGIN nodb_bindingtest(:i, :io, :o); END;",
        bindValues,
        function(err, result){
          should.not.exist(err);
          (result.outBinds.io).should.equal('Turing');
          (result.outBinds.o).should.equal('Alan Turing');
          done();
        }
      );
    });
  });

function streamIntoClob(id, cb) {
      connection.execute(
        "INSERT INTO " + tableName + " VALUES (:n, EMPTY_CLOB()) RETURNING content INTO :lobbv",
        { n: id, lobbv: { type: oracledb.CLOB, dir: oracledb.BIND_OUT } },
        function(err, result) {
          should.not.exist(err);
          var lob = result.outBinds.lobbv[0];
          var inStream = fs.createReadStream(inFileName);

          inStream.pipe(lob);

          lob.on('close', function() {
            connection.commit( function(err) {
              should.not.exist(err);
              cb(); // insertion done
            });
          });

          inStream.on('error', function(err) {
            should.not.exist(err);

it('76.1.11 works with undefined and maxSize set to 1', function(done) {
      var sequence = insertID++;
      var bindVar = {
        i: { val: sequence, dir: oracledb.BIND_IN, type: oracledb.NUMBER },
        io: { val: undefined, dir: oracledb.BIND_INOUT, type: oracledb.STRING, maxSize: 1 }
      };

      plsqlBindInOut(sqlRun, bindVar, undefined, null, done);
    }); // 76.1.11

it('86.2.5 Number supported in fetchAsString', function(done) {
      should.doesNotThrow(
        function() {
          oracledb.fetchAsString = [ oracledb.NUMBER ];
        }
      );
      should.strictEqual(oracledb.fetchAsString.length, 1);
      should.strictEqual(oracledb.fetchAsString[0], oracledb.NUMBER);
      done();
    }); // 86.2.5

function(cb) {
        var bind = {
          c1: { val: sequence, type: oracledb.NUMBER, dir: oracledb.BIND_IN },
          c2: { val: inserted[0], type: inserted[1], dir: oracledb.BIND_IN }
        };
        // console.log(insertSql);
        connection.execute(
          insertSql,
          bind,
          function(err, result) {
            should.not.exist(err);
            should.strictEqual(result.rowsAffected, 1);
            cb();
          });
      },
      function(cb) {