Connecting to a remote Oracle DB with Nodejs through Oracledb Driver

Question

Hey I'm really trying to figure out how to connect to a remote Oracle test DB and I have no experience in Java. So if I could get your help, I would be forever grateful.

I have a remote test database I'm trying to connect to and I have a jdbc connection with an old style SID. According to this link: https://github.com/oracle/node-oracledb/blob/master/doc/api.md#notjdbc, I'm supposed to create a tnsnames.ora file to put the connection in, like so:

tnsnames.ora:

appDB =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))
   (CONNECT_DATA =
     (SID = ORCL)
   )
 )

and then I'm supposed to reference it in my node server.js file, like so

server.js:

const oracledb = require('oracledb');
oracledb.getConnection(
  {
    user          : process.env.ORACLE_USER,
    password      : process.env.ORACLE_PASSWORD,
    connectString : "appDB"
  },
  function(err, connection)
  {
    if (err) {
      console.error(err.message);
      return;
    }
    connection.execute(
      "SELECT * " +
        "FROM BOS_course",
      function(err, result)
      {
        if (err) {
          console.error(err.message);
          doRelease(connection);
          return;
        }
        console.log(result.rows);
        doRelease(connection);
      });
  });

module.exports = {
    oracledb
};

However, I have no idea where I'm supposed to put the tnsnames.ora file. I found this link online $ORACLE_HOME/network/admin but I don't know what it's referring to. I only have limited access to the database. Excuse my Java ignorance. How can I use this connection in my node app, which lives completely separate from the database? How does my app know what "appDB" is and how to find it in the tnsnames.ora file?

Thanks in advance!

lsalamon · Accepted Answer · 2017-04-26 23:45:37Z

28

put the complete connection address in the connectString variable

{ user : process.env.ORACLE_USER, password : process.env.ORACLE_PASSWORD, connectString : "(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))(CONNECT_DATA =(SID= ORCL)))" }

answered Apr 26, 2017 at 23:45

lsalamon

8588 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Brooke Clonts Over a year ago

You are my favorite person right now. Thank you @Isalamon!

Steve Workman Over a year ago

That's a bucket load better than messing with tnsnames.ora and far more portable. You are a champion!

Christopher Jones · Accepted Answer · 2017-05-03 22:50:40Z

2

To technically answer the question, set the environment variable TNS_ADMIN. Then node-oracledb (and other OCI-based language APIs like Python cx_Oracle, PHP OCI8, Ruby ruby-oci8 etc) will look for $TNS_ADMIN/tnsnames.ora.

answered May 3, 2017 at 22:50

Christopher Jones

11.1k7 gold badges32 silver badges64 bronze badges

Comments

user557657 · Accepted Answer · 2017-08-18 14:58:10Z

1

I have a similar case where I have two VM on azure. One for Oracle12c DB and other is for nodejs oracledb package with Oracle client-side libraries. This is my connection string from client-side to connect with remote DB.

module.exports = {
  user          : process.env.NODE_ORACLEDB_USER || "hr",
  password      : process.env.NODE_ORACLEDB_PASSWORD || "welcome",
  connectString : process.env.LOCAL || "myipaddress:1521/servicename",
};

answered Aug 18, 2017 at 14:58

user557657

9061 gold badge14 silver badges43 bronze badges

Collectives™ on Stack Overflow

Connecting to a remote Oracle DB with Nodejs through Oracledb Driver

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related