Help with Oracle connection

[Steve Westfall]

I'm trying to set-up a simple test app as a proof of concept for using a node stack for a mobile project. I decide to try loopback because of the oracle connectors provided, but I'm having trouble getting it set-up and working. The documentation at http://docs.strongloop.com/display/LB/Installing+the+Oracle+connector mentions the loopback-oracle-builder, but it's not available on npm. Is this a typo? Old documentation? Can someone please advise? 

I have run through setting up node, the strong-cli, etc. and I can get an empty app up and running locally on port 3000, but when I try to go a little more advanced and connect to oracle, I run into problems. 

I'm working on a 64bit Windows 7 machine. 

I have Python 2.7.7 installed

I have Oracle Instant Client installed (Basic and the SDK unzipped and placed in the C:\instantclient_12_1 directory)

I set-up the necessary environment variables OCI_INCLUDE_DIR, OCI_LIB_DIR and added C:\instantclient_12_1\vc10;C:\instantclient_12_1; to my Path.

I then cd into my app dir and run:

     npm install loopback-connector-oracle

     npm install strong-oracle

I have followed all of the instructions at: https://github.com/strongloop/node-oracle

Now, in app.js where do I place the following code?

var connectData = { "tns": "DBNAME", "user": "APP_USER", "password": "MYPWD", "database": "DBNAME" };

oracle.connect(connectData, function(err, connection) {

    if (err) { console.log("Error connecting to db:", err); return; }

    connection.execute("SELECT systimestamp FROM dual", [], function(err, results) {

        if (err) { console.log("Error executing query:", err); return; }

        console.log(results);

        connection.close(); // call only when query is finished executing

    });

});

Any and all pointers/suggestions are apreciated

[Raymond Feng]

There are a few things in the picture:

1. loopback-connector-oracle: A connector for loopback-datasource-juggler (ORM) to translate CRUD methods into Oracle SQL statements

2. strong-oracle: A Node.js driver for Oracle databases. It’s C++ add-on and has dependency on Oracle’s instant client.

If you plan to use the driver (strong-oracle) directly, add strong-oracle as a dependency to your app. You’ll have to install and set up Oracle instant client by yourself. ‘npm install’ for your app will build strong-oracle from source code.

If you want to use LoopBack with the Oracle connector, you can simply add loopback-connector-oracle as dependency to your app. Behind the scene, loopback-connector-oracle uses loopback-oracle-installer to download, install, and configure pre-compiled strong-oracle and Oracle instant client for you automatically as part of ‘npm install’. 

To use TNS or LDAP, please follow the instructions at http://docs.strongloop.com/display/LB/Oracle+connector#Oracleconnector-LocalandDirectoryNaming.

Thanks,

---

Raymond Feng

Co-Founder and Architect @ StrongLoop, Inc.

StrongLoop makes it easy to develop APIs in Node, plus get DevOps capabilities like monitoring, debugging and clustering.

--
You received this message because you are subscribed to the Google Groups "LoopbackJS" group.
To unsubscribe from this group and stop receiving emails from it, send an email to loopbackjs+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Steve Westfall]

Thanks for the quick reply Raymond.

So, let me make sure we're on the same page. 

If I install loopback-connector-oracle, I don't necessarily need strong-oracle to connect to my DB?

That is, as long as I have a proper connection string to my DB (tns_names) and the TNS_ADMIN environment variable set-up correctly?

[Steve Westfall]

OK, so on my Mac (I gave up on Windows) I was able to get the loopback-connector-oracle installed, but when I try to require oracle in app.js, it can't be found. What am I doing wrong? Here is the trace from my command line:

Stevens-MacBook-Air:proofOfConcept SWESTF01$ sudo npm install loopback-connector-oracle --save

Password:

npm WARN package.json proofOf...@0.0.1 No description

npm WARN package.json proofOf...@0.0.1 No repository field.

npm WARN package.json proofOf...@0.0.1 No README data

 

> loopback-ora...@1.2.1 install /Users/SWESTF01/Documents/proofOfConcept/node_modules/loopback-connector-oracle/node_modules/loopback-oracle-installer

> node pkginstall.js

Downloading/Extracting http://7e9918db41dd01dbf98e-ec15952f71452bc0809d79c86f5751b6.r22.cf1.rackcdn.com/loopback-oracle-MacOSX-x64-1.3.0.tar.gz

http://7e9918db41dd01dbf98e-ec15952f71452bc0809d79c86f5751b6.r22.cf1.rackcdn.com/loopback-oracle-MacOSX-x64-1.3.0.tar.gz is now extracted to /Users/SWESTF01/Documents/proofOfConcept/node_modules/loopback-connector-oracle/node_modules

/Users/SWESTF01/strong-oracle.rc

/Users/SWESTF01/strong-oracle.rc has been modified.

---------------------------------------------------------------------------

The node-oracle module and the Oracle specific libraries have been

installed in /Users/SWESTF01/Documents/proofOfConcept/node_modules/loopback-connector-oracle/node_modules/loopback-oracle-installer.

The default bashrc (/etc/bashrc) or  user's bash_profile (~/.bash_profile)

paths have been modified to use this path. If you use a shell other than

bash, please remember to set the DYLD_LIBRARY_PATH prior to using node.

Example:

  $ export DYLD_LIBRARY_PATH=":/Users/SWESTF01/Documents/proofOfConcept/node_modules/loopback-connector-oracle/node_modules/instantclient"

loopback-con...@1.4.0 node_modules/loopback-connector-oracle

├── de...@0.8.1

├── loopback-...@1.1.0

├── as...@0.9.0

└── loopback-ora...@1.2.1 (grace...@2.0.3, fst...@0.1.25, t...@0.1.19, req...@2.36.0, buffere...@0.0.1)

Stevens-MacBook-Air:proofOfConcept SWESTF01$ slc run

supervisor running without clustering (unsupervised)

connect.multipart() will be removed in connect 3.0

visit https://github.com/senchalabs/connect/wiki/Connect-3.0 for alternatives

connect.limit() will be removed in connect 3.0

2014-06-26T14:55:24.227Z pid:367 worker:supervisor INFO strong-agent not profiling, configuration not found.

2014-06-26T14:55:24.229Z pid:367 worker:supervisor Generate configuration with:

2014-06-26T14:55:24.230Z pid:367 worker:supervisor     npm install -g strong-cli

2014-06-26T14:55:24.230Z pid:367 worker:supervisor     slc strongops

2014-06-26T14:55:24.231Z pid:367 worker:supervisor See http://docs.strongloop.com/strong-agent for more information.

Browse your REST API at http://0.0.0.0:3000/explorer

LoopBack server listening @ http://0.0.0.0:3000/

GET / 200 5ms - 63b

GET /explorer/ 200 5ms - 2.92kb

GET /explorer/css/hightlight.default.css 200 6ms - 2.09kb

GET /explorer/css/screen.css 200 6ms - 46.93kb

GET /explorer/lib/jquery-1.8.0.min.js 200 6ms - 90.39kb

GET /explorer/lib/jquery.wiggle.min.js 200 6ms - 762b

GET /explorer/lib/jquery.slideto.min.js 200 5ms - 369b

GET /explorer/lib/jquery.ba-bbq.min.js 200 4ms - 4.02kb

GET /explorer/lib/underscore-min.js 200 15ms - 12.52kb

GET /explorer/lib/backbone-min.js 200 13ms - 16.08kb

GET /explorer/lib/handlebars-1.0.0.js 200 19ms - 71.06kb

GET /explorer/lib/swagger.js 200 13ms - 27.38kb

GET /explorer/lib/highlight.7.3.pack.js 200 12ms - 7.32kb

GET /explorer/swagger-ui.js 200 13ms - 71.93kb

GET /explorer/fonts/OsJ2DjdpjqFRVUSto6IffLO3LdcAZYWl9Si6vvxL-qU.woff 200 3ms - 24.79kb

GET /explorer/images/logo_small.png 200 3ms - 2.62kb

GET /explorer/fonts/0ihfXUL2emPh0ROJezvraLO3LdcAZYWl9Si6vvxL-qU.woff 200 4ms - 28.86kb

GET /explorer/config.json 304 1ms

GET /api/swagger/resources 304 24ms

GET /api/swagger/users 200 4ms - 12.42kb

GET /api/swagger/accessTokens 200 2ms - 7.81kb

GET /api/swagger/applications 200 3ms - 7kb

GET /api/swagger/push 304 1ms

GET /api/swagger/installations 200 1ms - 9.84kb

GET /api/swagger/notifications 200 3ms - 7.02kb

GET /explorer/images/throbber.gif 200 1ms - 9.04kb

The I stopped the app and added: var oracle = require('oracle'); to app.js

When I re-started the app I got the following error:

Stevens-MacBook-Air:proofOfConcept SWESTF01$ slc run

supervisor running without clustering (unsupervised)

module.js:340

    throw err;

    ^

Error: Cannot find module 'oracle'

    at Function.Module._resolveFilename (module.js:338:15)

    at Function.Module._load (module.js:280:25)

    at Module.require (module.js:364:17)

    at require (module.js:380:17)

    at Object.<anonymous> (/Users/SWESTF01/Documents/proofOfConcept/app.js:2:16)

    at Module._compile (module.js:456:26)

    at Object.Module._extensions..js (module.js:474:10)

    at Module.load (module.js:356:32)

    at Function.Module._load (module.js:312:12)

    at Object.<anonymous> (/usr/local/lib/node_modules/strong-supervisor/bin/slr:29:19)

Stevens-MacBook-Air:proofOfConcept SWESTF01$

 

Any suggestions as to what I'm doing wrong?

 

[Raymond Feng]

1. There are two modules involved:

- loopback-connector-oracle

- strong-oracle

After you run 'npm install loopback-connector-oracle —save’, loopback-connector-oracle should be present in node_modules folder and it can be required as follows:

var oracleConnector = require(‘loopback-connector-oracle’);

2. strong-oracle module is under node_modules/loopback-connector-oracle/node_modules/strong-oracle, it can be required as:

var oracleDriver = require(‘./node_modules/loopback-connector-oracle/node_modules/strong-oracle’);

This is not recommended. To install strong-oracle as the 1st level dep, run ‘npm install strong-oracle’ and it can be required as:

var oracleDriver = require(‘strong-oracle’);

3. Please add the following to your .bash_profile to set up dynamic lib path to oracle instant client.

if [ -f ~/strong-oracle.rc ]; then

  source ~/strong-oracle.rc

fi

Thanks,

---

Raymond Feng

Co-Founder and Architect @ StrongLoop, Inc.

StrongLoop makes it easy to develop APIs in Node, plus get DevOps capabilities like monitoring, debugging and clustering.

[Steve Westfall]

Raymond, thanks for all your help, but could you let me know where to run the npm install strong-oracle from?

I have tried to run it from myApp dir as well as from the loopback-connector-oracle dir, but keep getting the following error:

Stevens-MacBook-Air:proofOfConcept SWESTF01$ sudo npm install strong-oracle --save

npm WARN package.json proofOf...@0.0.1 No description

npm WARN package.json proofOf...@0.0.1 No repository field.

npm WARN package.json proofOf...@0.0.1 No README data

 

> strong...@1.2.0 install /Users/SWESTF01/Documents/proofOfConcept/node_modules/strong-oracle

> node-gyp rebuild

gyp ERR! build error 

gyp ERR! stack Error: not found: make

gyp ERR! stack     at F (/usr/local/lib/node_modules/npm/node_modules/which/which.js:43:28)

gyp ERR! stack     at E (/usr/local/lib/node_modules/npm/node_modules/which/which.js:46:29)

gyp ERR! stack     at /usr/local/lib/node_modules/npm/node_modules/which/which.js:57:16

ERR! stack     at Object.oncomplete (fs.js:107:15)

gyp ERR! System Darwin 11.4.2

gyp ERR! command "node" "/usr/local/lib/node_modules/npm/node_modules/node-gyp/bin/node-gyp.js" "rebuild"

gyp ERR! cwd /Users/SWESTF01/Documents/proofOfConcept/node_modules/strong-oracle

gyp ERR! node -v v0.10.29

gyp ERR! node-gyp -v v0.13.1

gyp ERR! not ok 

npm ERR! strong...@1.2.0 install: `node-gyp rebuild`

npm ERR! Exit status 1

npm ERR! 

npm ERR! Failed at the strong...@1.2.0 install script.

npm ERR! This is most likely a problem with the strong-oracle package,

npm ERR! not with npm itself.

npm ERR! Tell the author that this fails on your system:

npm ERR!     node-gyp rebuild

npm ERR! You can get their info via:

npm ERR!     npm owner ls strong-oracle

npm ERR! There is likely additional logging output above.

npm ERR! System Darwin 11.4.2

npm ERR! command "node" "/usr/local/bin/npm" "install" "strong-oracle" "--save"

npm ERR! cwd /Users/SWESTF01/Documents/proofOfConcept

npm ERR! node -v v0.10.29

npm ERR! npm -v 1.4.14

npm ERR! code ELIFECYCLE

npm ERR! 

npm ERR! Additional logging details can be found in:

npm ERR!     /Users/SWESTF01/Documents/proofOfConcept/npm-debug.log

npm ERR! not ok code 0

[Raymond Feng]

Apparently you don’t have the required tools on your Mac. Can you install Xcode command line tools?

BTW, you can simply copy strong-oracle from the loopback-connector-oracle installation as it uses prebuilt binary for your platform.

Thanks,

---

Raymond Feng

Co-Founder and Architect @ StrongLoop, Inc.

StrongLoop makes it easy to develop APIs in Node, plus get DevOps capabilities like monitoring, debugging and clustering.

[Steve Westfall]

Raymond, once again thank you for your help and patience as I am completely new to Node and Strongloop.

I check my Xcode Command line tools. They were not installed. I installed them and tried again but got a different error this time.

Stevens-MacBook-Air:proofOfConcept SWESTF01$ sudo npm install strong-oracle --save

Password:

npm WARN package.json proofOf...@0.0.1 No description

npm WARN package.json proofOf...@0.0.1 No repository field.

npm WARN package.json proofOf...@0.0.1 No README data

 

> strong...@1.2.0 install /Users/SWESTF01/Documents/proofOfConcept/node_modules/strong-oracle

> node-gyp rebuild

  CXX(target) Release/obj.target/oracle_bindings/src/connection.o

In file included from ../src/connection.cpp:2:

../src/connection.h:9:10: fatal error: 'occi.h' file not found

#include <occi.h>

         ^

1 error generated.

make: *** [Release/obj.target/oracle_bindings/src/connection.o] Error 1

gyp ERR! build error 

gyp ERR! stack Error: `make` failed with exit code: 2

gyp ERR! stack     at ChildProcess.onExit (/usr/local/lib/node_modules/npm/node_modules/node-gyp/lib/build.js:267:23)

gyp ERR! stack     at ChildProcess.emit (events.js:98:17)

gyp ERR! stack     at Process.ChildProcess._handle.onexit (child_process.js:809:12)

Stevens-MacBook-Air:proofOfConcept SWESTF01$ 

I have followed all the set-up instructions for adding paths, environment vars, etc. Any suggestions?

[Raymond Feng]

Now you should follow instructions on https://github.com/strongloop/strong-oracle to install Oracle instant client and configure the environment vars so that the compiler can find the included header files and library dependencies.

Again, you are on the advanced path. loopback-connector-oracle uses loopback-oracle-installer to install pre-built strong-oracle and oracle instant client without fighting with the native node add-on building process :-)

Thanks,

---

Raymond Feng

Co-Founder and Architect @ StrongLoop, Inc.

StrongLoop makes it easy to develop APIs in Node, plus get DevOps capabilities like monitoring, debugging and clustering.

[Steve Westfall]

Thanks again Raymond for all of your help.

I have followed all the instructions on github, but still can't get strong-oracle to install properly on my Mac.

Here's a snapshot of what I have in my environment:

Stevens-MacBook-Air:~ SWESTF01$ cd $OCI_LIB_DIR

Stevens-MacBook-Air:instantclient_11_2 SWESTF01$ ln -s libclntsh.dylib.11.1 libclntsh.dylib

ln: libclntsh.dylib: File exists

Stevens-MacBook-Air:instantclient_11_2 SWESTF01$ ln -s libocci.dylib.11.1 libocci.dylib

ln: libocci.dylib: File exists

Stevens-MacBook-Air:instantclient_11_2 SWESTF01$ printenv

OCI_INCLUDE_DIR=/Users/SWESTF01/Documents/instantclient_11_2/sdk/include

TERM_PROGRAM=Apple_Terminal

TERM=xterm-256color

SHELL=/bin/bash

TMPDIR=/var/folders/8z/bt9kgtn52kbc3j3fn7fl1xc00000gn/T/

Apple_PubSub_Socket_Render=/tmp/launch-2YMinC/Render

TERM_PROGRAM_VERSION=303.2

OLDPWD=/Users/SWESTF01

TERM_SESSION_ID=4FDD5293-0CD2-4C64-9A67-F6C581F4792B

OCI_HOME=/Users/SWESTF01/Documents/instantclient_11_2

USER=SWESTF01

COMMAND_MODE=unix2003

SSH_AUTH_SOCK=/tmp/launch-h5IsEF/Listeners

__CF_USER_TEXT_ENCODING=0x1F5:0:0

Apple_Ubiquity_Message=/tmp/launch-lOXcFy/Apple_Ubiquity_Message

PATH=/Library/Frameworks/Python.framework/Versions/2.7/bin:/usr/bin:/bin:/usr/sbin:/sbin:/usr/local/bin:/usr/X11/bin

PWD=/Users/SWESTF01/Documents/instantclient_11_2

LANG=en_CA.UTF-8

SHLVL=1

HOME=/Users/SWESTF01

LOGNAME=SWESTF01

DISPLAY=/tmp/launch-CQ75zq/org.x:0

OCI_LIB_DIR=/Users/SWESTF01/Documents/instantclient_11_2

SECURITYSESSIONID=186a4

_=/usr/bin/printenv

Stevens-MacBook-Air:instantclient_11_2 SWESTF01$ cd ..

Stevens-MacBook-Air:Documents SWESTF01$ cd proofOfConcept/

I think all of the environment variables are set-up correctly as outlined in the instructions, but the build for strong-oracle keeps failing. 

[Steve Westfall]

On Windows, I took another shot at it. I was able to get strong-oracle installed (there were several warnings, however).

My issue on Windows is that once I start trying to require the loopback-connector-oracle or strong-oracle, I get this error:

C:\nodejs\mafia>slc run

strong-agent not profiling, configuration not found.

Generate configuration with:

    npm install -g strong-cli

    slc strongops

See http://docs.strongloop.com/strong-agent for more information.

supervisor running without clustering (unsupervised)

connect.multipart() will be removed in connect 3.0

visit https://github.com/senchalabs/connect/wiki/Connect-3.0 for alternatives

connect.limit() will be removed in connect 3.0

C:\nodejs\mafia\node_modules\loopback-connector-oracle\lib\oracle.js:13

    throw err;

          ^

Error: The specified procedure could not be found.

C:\nodejs\mafia\node_modules\loopback-connector-oracle\node_modules\strong-oracle\build\Release\oracle_bindings.node

    at Module.load (module.js:356:32)

    at Function.Module._load (module.js:312:12)

    at Module.require (module.js:364:17)

    at require (module.js:380:17)

    at Object.<anonymous> (C:\nodejs\mafia\node_modules\loopback-connector-oracle\node_modules\strong-oracle\lib\oracle.js:4:14)

    at Module._compile (module.js:456:26)

    at Object.Module._extensions..js (module.js:474:10)

    at Module.load (module.js:356:32)

    at Function.Module._load (module.js:312:12)

    at Module.require (module.js:364:17)

C:\nodejs\mafia>

The initial empty app will run fine on windows as it is installed, but when I add the following code to app.js, I get the error above whenever I start the app.

var oracleConnector = require('loopback-connector-oracle');

var oracleDriver = require('strong-oracle');

var connectData = { 'tns': 'MY_DESC_NAME', 'user': 'USER_XXX', 'password': 'XXXXXXX', 'database': 'MY_DB' };

oracleConnector.connect(connectData, function(err, connection) {

    if (err) { console.log('Error connecting to db:', err); return; }

    connection.execute('SELECT systimestamp FROM dual', [], function(err, results) {

        if (err) { console.log('Error executing query:', err); return; }

        console.log(results);

        connection.close(); // call only when query is finished executing

    });

}); 

Once again, all help and suggestions are much appreciated 

[Raymond Feng]

Do you see 'occi.h' under /Users/SWESTF01/Documents/instantclient_11_2/sdk/include? When you unzip the instant client sdk, make sure it goes into the correct folder.

[Raymond Feng]

You have to make sure the Windows PATH env var is set up correctly to point the instant client dlls. Do you have Visual Studio 2012 installed?