bomsy/node-odp

A node library that wraps the odp.net dll to provide bindings to oracle on windows

NODE-ODP

A node library that wraps the Oracle Data Provider for.NET (ODP.NET) library to provide bindings to oracle on windows.

Requirements

Install ODAC, making sure the policy dlls are shared (placed in the GAC). See installation instructions here

Install Microsoft visual C++ 2010 redistributable package (x86 or x64 or both).

x86

x64

Update: Install Node v0.10.x

Installation

npm install node-odp

How to use (Sample code) Edit to fit your scenario

var odpnode = require('node-odp');
var con = new odpnode.OracleConnection("Data Source=mhladmin;User Id=/;");

var parameterDirection = odpnode.OracleData.parameterDirection;
var datatypes = odpnode.OracleData.dbType;
var cmdType = odpnode.OracleCommand.commandType;

con.open();
var parameters = [
    {
	    name: "vResult",
	    type: datatypes.VARCHAR2,
	    size: 4000,
	    value: '',
	    direction: parameterDirection.OUTPUT
    },
    {
	    name: "pFOLDERID",
	    type: datatypes.INT32,
	    value: "1845",
	    direction: parameterDirection.INPUT
    }	
]

//SIMPLE SELECT STATEMENT
var cmdSelStatement = new odpnode.OracleCommand("SELECT m.FOLDERID,m.ZONE.ZONECODE FROM MID.MIFOLDER2 m,MID.MIVERSION s WHERE m.VERSION.VERSIONID = s.VERSIONID AND  m.ZONE.ZONECODE ='chwhrf-bldg'", cmdType.TEXT, con);
cmdSelStatement.executeReader(function(err, rows){
    if(err){
	    console.log(err);
    }else{
	    console.log(rows);
	    console.log(rows[1]["ZONE_ZONECODE"]);
    }	
});

//ORACLE FUNCTION WITH PARAMETERS
var cmdProc = new odpnode.OracleCommand("BEGIN :vResult := MID.MI_MAXSCRIPT.MITest( :pFOLDERID ); END;", cmdType.TEXT, parameters, con);
cmdProc.executeNonQuery(function(err, rowsAffected, params){
    if(err){
	    console.log(err);
    }else{
	    console.log(params);
    }	
});

var refParams = [
    {
        name: 'pJOBCODE',
        type: datatypes.VARCHAR2,
        value: "2378",
        direction: parameterDirection.INPUT,
        size: 2000
    },{
        name: 'pFilterDescription',
        type: datatypes.VARCHAR2,
        value: "",
        size: 2000,
        direction: parameterDirection.OUTPUT
    },{
        name: 'resultset',
        type: datatypes.REFCURSOR,
        size: 2000,
        value: "",
        direction: parameterDirection.OUTPUT
    }
];

//RETURNING REFCURSORS
var refCmd = new odpnode.OracleCommand("MID.MICHUNK_API.GetChunksByJob_cursor", commandType.STOREDPROCEDURE, refParams, con);
refCmd.executeReader(function(err, rowset){
    if(err){
        console.log(err);
    }else{
        console.log(rowset);
    }
});

API Definition

ORACLECONNECTION

OracleConnection(connectionString);

The constructor takes the connectionString as parameter and returns a new connection object. The connection string is any valid Oracle connection string. For oracle connectionstring formats see here

.open

The open method opens the connection

ORACLECOMMAND

OracleCommand(commandString, commandType, commandParameters, connectionObject);

The constructor takes in parameters and returns a new command object

commandString

This can either be a select statement or an oracle function call as shown in the sample code above. This is a required parameter.

commandType

This an object of the OracleCommand.commandType enumeration object. This is a required parameter.

commandParameters

This an array of parameter objects as shown above. Each parameter object is defined as

{
	name: parameterName,
	type: dataTypeObject,
	size: size(optional),
	value: parameterContent,
	direction: parameterDirectionObject
}

This parameter is optional.

connectionObject

This is a connection object created by OracleConnection. This is a required parameter.

.executeReader

executeReader(function(err, rows){

})

This function takes a callback and passes the err and rows objects to that callback.

The err object holds the error if there is any and undefined if there is none.

The rows holds the JSON object of the rows returned. These rows are identified by their row numbers and column names as shown in the sample code above.

.executeNonQuery

executeNonQuery(function(err, rowsAffected, parameters){

})

This function takes a callback and passes the err, rowsAffected and parameters objects to the callback.

The err object holds the error if there is any and undefined if there is none.

The rowsAffected returns the number of rows Affected by current execution, but returns -1 if there is none.

The parameters object returns all the parameters attached to the command, allowing for access to any out or in/out parameter data.

.executeScalar

executeScalar(function(err, rowsAffected, parameters){

})

Sames as for executeNonQuery

.commandType

Oracle Command Constants for defining data text type

            commandType.TEXT
                        .STOREDPROCEDURE

returns constants defining the type of the command.

ORACLEDATA

A data object storing key/value pairs of constants representing various oracle types

.parameterDirection

Various constants for specifying parameter direction

parameterDirection.INPUT
                  .OUTPUT
                  .INPUTOUTPUT
                  .RETURNVALUE

.dbType

Various constants for specifying data types

            dbType.BFILE
                 .BLOB
                 .BYTE
                 .CHAR
                 .CLOB
                 .DATE
                 .DECIMAL
                 .DOUBLE
                 .INT16
                 .INT32
                 .INT64
                 .INTERVALDS
                 .INTERVALYM
                 .LONG
                 .LONGRAW
                 .RAW
                 .REFCURSOR
                 .NVARCHAR
                 .SINGLE
                 .TIMESTAMP
                 .TIMESTAMPLTZ
                 .TIMESTAMPTZ
                 .VARCHAR2
                 .XMLTYPE

Release History

v 0.1.0 - just .node file .

v 0.1.1 - added all the source files, updated binding.gyp.

v 0.1.2 - corrections.

v 0.1.3 - bug fixes.

v 0.1.4 - bug fixes.

v 0.1.9 - added dependency dlls to package msvcp100d.dll, msvcr100d.dll.

v 0.1.10 - compiled to 32 bit node.

v 0.1.11 - complied to x64.

v 0.1.14 - fixed exception handling in odpconnection.cc

v 0.1.15 - changed dependency path in and PLATFORM to win32 binding.gyp

v 0.1.18 - updated readme

v 0.1.19 - deps on node-gyp, bindings not needed. updated readme, remove dlls not needed, added 32 bit support

v 0.2.0 - updated to support node v 0.10.0, might not be backward compatible

v 0.2.1 - changed Runtime Library for Release Mode to 'MultiThreadedDLL'. This was causing it not to run on release machines. Now fixed! Unit tests added.

v 0.2.2 - Readme.md fixes

v 0.2.3 - Readme.md corrections

Licence

Copyright (c) 2013 Hubert Boma Manilla
Licensed under the MIT license.

node-odp's Issues

Better Unittests

Create clean & better unittests.

ExecuteReader crashes on empty result set

Calling OracleCommand.executeReader for a query that returns no results causes node-odp to crash. Example code:

con.open();

var cmd;
cmd = new odpnode.OracleCommand("create table foo (key varchar2(20), 
    value varchar2(20))", commandType.TEXT, con);
cmd.executeNonQuery(function() {});

cmd = new odpnode.OracleCommand("select * from foo", commandType.TEXT, con);
cmd.executeReader(function(err, rows) {
    if (err) {
        console.log(err);
    } else {
        console.log(rows);
    }
});

con.close();

The problem is in OdpCommand::ToJSON (npm registered code base) or OdpCommand::LoopReader (current master code base): After the while loop, it should check to see if rows = "[" and simply return "[]".

When will be released a new version?

I've a problem when my query/sql don't return records,a bug occurs, this error can't be caught. In this case, what my app wants is just handle/treat the error. But there is no way from crashing the app.

Is there a date set to release a new version of the node-odp with the problem above corrected?

Thanks so much

NODE-ODP not compiled to release

Node-odp was not running because it needed two debug mode dlls msvcp100d.dll & msvcr100d.dll.
The error shown is as follows:
module.js:485
process.dlopen(filename, module.exports);
^
Error: The specified module could not be found.
J:\node_modules\mhl-dispatcher\node_modules\node-odp\libs\build\Release\odpnode.
node
at Object.Module._extensions..node (module.js:485:11)
at Module.load (module.js:356:32)
at Function.Module._load (module.js:312:12)
at Module.require (module.js:362:17)
at require (module.js:378:17)
at Object. (J:\node_modules\mhl-dispatcher\libs\db.js:1:73)
at Module._compile (module.js:449:26)
at Object.Module._extensions..js (module.js:467:10)
at Module.load (module.js:356:32)
at Function.Module._load (module.js:312:12)

Sample doesn't working

var odp = require('node-odp');
var con = new odpnode.OracleConnection("Data Source=mhladmin;User Id=/;");
give us : ReferenceError: odpnode is not defined.

And it doesn't work with var con = new odp.OracleConnection("Data Source=mhladmin;User Id=/;");
For our test we change the Connection String
Regards

Tahonas

ODP and RefCursors....

Do you support sys_refcurosrs? I can't seem to get this to work ok. The regular select statement works fine.

Here is my exception:

ORA-06550: line 1, column 123:
PLS-00103: Encountered the symbol "," when expecting one of the following:

. ( * @ % & = - + < / > at in is mod remainder not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like like2
like4 likec as between || indicator multiset member
submultiset

Here is my proc and code that is failing:

PROCEDURE MyProc
(
inDate DATE,
RC1 IN OUT SYS_REFCURSOR,
RC2 IN OUT SYS_REFCURSOR,
RC3 IN OUT SYS_REFCURSOR,
RC4 IN OUT SYS_REFCURSOR,
RC5 IN OUT SYS_REFCURSOR,
RC6 IN OUT SYS_REFCURSOR
)

...
var parameters = [
{
name: "inDate",
type: datatypes.DATE,
value: null,
direction: parameterDirection.INPUT
},
{
name: "RC1",
type: datatypes.REFCURSOR,
direction: parameterDirection.OUTPUT
},
{
name: "RC2",
type: datatypes.REFCURSOR,
direction: parameterDirection.OUTPUT
},
{
name: "RC3",
type: datatypes.REFCURSOR,
direction: parameterDirection.OUTPUT
},
{
name: "RC4",
type: datatypes.REFCURSOR,
direction: parameterDirection.OUTPUT
},
{
name: "RC5",
type: datatypes.REFCURSOR,
direction: parameterDirection.OUTPUT
},
{
name: "RC6",
type: datatypes.REFCURSOR,
direction: parameterDirection.OUTPUT
}
];

var cmdProc = new odpnode.OracleCommand("BEGIN MyProc( :inDate, :RC1, :RC2, :RC3, :RC4, :RC5, :RC6 ); END;", cmdType.STOREDPROCEDURE, parameters, con);

    cmdProc.executeNonQuery(function(err, rowsAffected, params){
        if(err){
            console.log(err);
        }else{
            console.log(params);
        }    
    });

I have also tried:

var cmdProc = new odpnode.OracleCommand("MyProc( :inDate )", cmdType.STOREDPROCEDURE, parameters, con);

and

var cmdProc = new odpnode.OracleCommand("MyProc( :inDate, :RC1, :RC2, :RC3, :RC4, :RC5, :RC6 )", cmdType.STOREDPROCEDURE, parameters, con);

In both of these cases, I get that RC1 is undefined or not bound. Any help would be appreciated.

Embedded control characters in strings

node-odp crashes when attempting to SELECT a row set where one of the field values contain embedded control characters, specifically CR and/or LF.

To reproduce, run the example select, using the following as the query:

select 'This is a ' || chr(13) || 'two-line string' from dual

The crash appears to occur somewhere in OracleCommand.executeReader().

Windows 7 x64
node.js 0.10.10 64-bit
ODAC 11.2.0.3 64-bit

-Steve

some issues

I am working on a project with your node-odp. there are some issues I have met so far.

1, scalar and reader

sql1 = select count() from a_table where xxx
sql2 = select count() as amount from a_table where xxx
when use scalar to run sql1, get nothing. if use reader to run sql2, you always get amount

2, catch error

my app loads records from oracle into mongodb. reader is used. problem is when error occurs (for instance, ora - xxxxxx, say SELECT TO_DATE("Column", 'mm/dd/yyyy') as ADATE FROM a_table but actually '2013-01-01' in the "Column", this causes a conversion error), this error can not be caught. In this case, what my app wants is just ignores the error and works on next row. BUT there is no way from crashing the app.

3, charset

Any other tools I use to get data from Oracle correctly deal with UTF8 chars such as Chinese characters, but node-odp does not. Is there a way to deal with charset?

I may do something wrong??? Need help! Thanks!

Gavin

not getting the conn under win7

trying this under windows7...not working!
var con = new odpnode.OracleConnection("SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XE)));uid=user;pwd=pass;");

any idea?

Error running a js file with only var odpnode = require('node-odp');

module.js:356
Module._extensions[extension](this, filename);
^
Error: %1 is not a valid Win32 application.
c:\nodejs\node_modules\node-odp\libs\build\Release\odpnode.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. (c:\nodejs\oda.js:1:77)
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 Function.Module.runMain (module.js:497:10)