Nodejs in Visual Studio Code 07. Learning Oracle

1. Start

node.js:https://nodejs.org

Oracledb:https://github.com/oracle/node-oracledb/blob/master/install.md#instwin

Https://github.com/oracle/node-oracledb/blob/master/doc/api.md#resultsethandling

2.OracleDB Installation

• Python 2.7

Download and install, a little

• C Compiler with support for C + + (Xcode, gcc, Visual Studio or similar)

Open the installation file for Visual Studio and see if the C compiler is installed, see

• The small, free Oracle Instant Client "Basic" and "SDK" packages if your database is remote. Or use a locally installed database such as the free Oracle XE release

Open Oracle Instant Client, free download of basic and SDK two compressed packages, green software without installation

  Instantclient-basic-windows.x64-12.1.0.2.0.zip 69MB

  instantclient-sdk-windows.x64-12.1.0.2.0.zip 2.62MB

Extract two zip files into the same directory Z:\Softs\OracleClient\12GX64

• SetOCI_LIB_DIRandOCI_INC_DIRduring installation if the Oracle libraries and headers is in a non-default location

Open My Computer, properties, advanced properties, environment variables, add two environment variables Oracle_home64,oci_lib_dir and Oci_inv_dir

Oracle_home64:z:\softs\oracleclient\12gx64

Oci_lib_dir:%oracle_home64%\sdk\lib\msvc

Oci_inv_dir:%oracle_home64%\sdk\include

Add the Z:\Softs\OracleClient\12GX64 path%oracle_home64% to path.

• Execute cmd command

$ NPM Install OracleDB

3.OracleDB Normal Query

Dbconfig.js Configuring the database connection string

module.exports = {
  user          : process.env.NODE_ORACLEDB_USER || "test",

  // Instead of hard coding the password, consider prompting for it,
  // passing it in an environment variable via process.env, or using
  // External Authentication.
  password      : process.env.NODE_ORACLEDB_PASSWORD || "test",

  // For information on connection strings see:
  // https://github.com/oracle/node-oracledb/blob/master/doc/api.md#connectionstrings
  connectString : process.env.NODE_ORACLEDB_CONNECTIONSTRING || "192.168.1.100/orcl",

  // Setting externalAuth is optional.  It defaults to false.  See:
  // https://github.com/oracle/node-oracledb/blob/master/doc/api.md#extauth
  externalAuth  : process.env.NODE_ORACLEDB_EXTERNALAUTH ? true : false
};

App.js execution of a simple query statement

Var oracledb = require(‘oracledb‘);
Var dbConfig = require(‘./dbconfig.js‘);

//Open a link
oracledb.getConnection(
  {
    User : dbConfig.user,
    Password : dbConfig.password,
    connectString : dbConfig.connectString
  },
  Function(err, connection)
  {
    If (err) {
      Console.error(err.message);
      Return;
    }
    / / Execute the query statement
    Connection.execute(
      "SELECT department_id, department_name " +
        "FROM departments" +
        "WHERE manager_id < :id",
      [110], // bind value for :id
      { maxRows: 10 }, // a maximum of 10 rows will be returned. Default limit is 100
      Function(err, result)
      {
        If (err) {
          Console.error(err.message);
          doRelease(connection);
          Return;
        }
        Console.log(result.metaData);
        Console.log(result.rows);
        / / Remember to release the link resources after the query is over
        doRelease(connection);
      });
  });

Function doRelease(connection)
{
  Connection.release(
    Function(err) {
      If (err) {
        Console.error(err.message);
      }
    });
}

4.OracleDB ResultSet Query

Normal query, the default maximum return 100 data, if you need to query more data then need to establish a DataReader and database to maintain a connection and then a row of read data, this in Nodejs oracledb inside called ResultSet query.

You can use resultset this way to return one row of data at a time

Var oracledb = require(‘oracledb‘);
Var dbConfig = require(‘./dbconfig.js‘);

//Open a link
oracledb.getConnection(
  {
    User : dbConfig.user,
    Password : dbConfig.password,
    connectString : dbConfig.connectString
  },
  Function(err, connection)
  {
    If (err) {
      Console.error(err.message);
      Return;
    }
    / / Execute the query statement
    Connection.execute(
      "SELECT department_id, department_name " +
        "FROM departments" +
        "WHERE manager_id < :id",
      [110], // bind value for :id
      { maxRows: 10 }, // a maximum of 10 rows will be returned. Default limit is 100
      Function(err, result)
      {
        If (err) {
          Console.error(err.message);
          doRelease(connection);
          Return;
        }
        Console.log(result.metaData);
        Console.log(result.rows);
        / / Remember to release the link resources after the query is over
        doRelease(connection);
      });
  });

Function doRelease(connection)
{
  Connection.release(
    Function(err) {
      If (err) {
        Console.error(err.message);
      }
    });
}

Of course, you can also return multiple rows of data each time, using the NumRows parameter

var numRows = 10;  // number of rows to return from each call to getRows()

connection.execute(
  "SELECT employee_id, last_name FROM employees ORDER BY employee_id",
  [], // no bind variables
  { resultSet: true }, // return a result set.  Default is false
  function(err, result)
  {
    if (err) { . . . }
    fetchRowsFromRS(connection, result.resultSet, numRows);
  });
});

. . .

function fetchRowsFromRS(connection, resultSet, numRows)
{
  resultSet.getRows( // get numRows rows
    numRows,
    function (err, rows)
    {
      if (err) {
         . . .                        // close the result set and release the connection
      } else if (rows.length == 0) {  // no rows, or no more rows
        . . .                         // close the result set and release the connection
      } else if (rows.length > 0) {
        console.log(rows);
        fetchRowsFromRS(connection, resultSet, numRows);  // get next set of rows
      }
    });
}

Nodejs in Visual Studio Code 07. Learning Oracle