Nodejs in Visual Studio Code 07. Learning Oracle

Source: Internet
Author: User



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


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.