sagiegurari / simple-oracledb Goto Github PK

Hi,

I try to use your package (I found the link on oracledb git) because it seems that I have an issue with idle connections to oracle. I use your modified pool.getConnection for this, but it doesn't fixes my problem. The Problem is, that the pool connections seems to look healthy but sending a SELECT 1 FROM DUAL returns simply silence. I don't know where the issue is (client or server). Releasing the connection and force the pool to rebuild the connection seems to fix that.
So my question is, is there somewhere in your library an option to define a timeout on outstanding server response? It seems that your getConnections wait endless for return of the SELECT 1 FROM DUAL and doesn't release this connection, after a while with no response, as dead and try a new one. So my code is waiting endless to get a connection. I hope I wrote understandable.

The code I use to create the pool with parameters, allmost as suggested in your readme

 initDatabase:function(dbconf){
        return new Promise(function (resolve, reject) {
            // console.log("Node-oracledb version:", oracledb.version); // numeric version format is useful for comparisons
            // console.log("Node-oracledb version suffix:", oracledb.versionSuffix); // e.g. "-beta.1", or empty for production releases
            utils.logger("Node-oracledb version: " + oracledb.versionString); // version (including the suffix)
            //console.log("Oracle Client library version:", oracledb.oracleClientVersion); // numeric version format
            utils.logger("Oracle Client library version: " + oracledb.oracleClientVersionString);
            utils.logger("Waiting for the pool to be created ...");

            oracledb.createPool({
                retryCount: 5, //The max amount of retries to get a connection from the pool in case of any error (default to 10 if not provided)
                retryInterval: 500, //The interval in millies between get connection retry attempts (defaults to 250 millies if not provided)
                runValidationSQL: true, //True to ensure the connection returned is valid by running a test validation SQL (defaults to true)
                usePingValidation: false, //If runValidationSQL, this flag will define if validation should first attempt to use connection.ping instead of running a SQL
                validationSQL: 'SELECT 1 FROM DUAL', //The test SQL to invoke before returning a connection to validate the connection is open (defaults to 'SELECT 1 FROM DUAL')
                user: dbconf.user,
                password: dbconf.password,
                connectString: dbconf.connectString,
                poolMax: 20,
                poolMin: 0,
                poolIncrement: 1,
                poolAlias: "mainpool",
                poolTimeout: 0
              }, function (error, pool) {
                  if (error) {
                    _oraclestate = error.message;
                    reject(error);
                  } else {
                    _pool = pool;
                    _pool.getConnection( function onConnection(err, connection) {
                      if (err) {
                        _orastate = "connection error";
                        _oraclestate = err.message;
                        utils.logger("Oracle Connection error")
                        releaseconnection(connection)
                        reject(err);
                      } else {
                        _orastate = "connected";
                        _oracleServerVersion = connection.oracleServerVersionString;
                        utils.logger("Oracle Database version: " + connection.oracleServerVersionString);
                        utils.logger("Oracle Database Pool created: " + pool.poolAlias);
                        releaseconnection(connection)
                        resolve(true);
                      }
                    });
                }
            });
        })
    }

hi @sagiegurari
when i use connection.query its only return first 100 row...is that any property to change this behavior ???
without simple oracledb i solve it with using resultset

node version: 8.17.0
nodeoracledb version: 4.2.0
simple-oracledb version: 1.4.2
Hi, my application has recently got 'DPI-1040: LOB was already closed' error twice, which causes application crash. after google, I notice this error happens when you try to wirte into a lob stream after it's been closed. but the lob stream is created when connection.execute and will be closed after write finish, I also checked the TCP dump between application and database and it looks fine before application down and close the TCP connection. I can't figure out how this error happen and what to do to prevent it, have you meet this before or any suggestion I can track?
looking forward to your reply!

connection.batchInsert('<INSERT STATEMENT>', [{ //first row values
                id: 110,
                name: 'A'
            },
            { //second row values
                id: 111,
                name: 'B'
            }
        ], {
            autoCommit: false
        }
    },
    function onResults(err, output) {
        if (err) {
            console.log(JSON.stringify(err)); //prints empty object when there is a DB error- {}
        }
    });

It handles the error however there is nothing in the err object to debug/understand what kind of DB error occurred. Version: "simple-oracledb": "~1.1.39".

Is it fixed in any of the newer releases ? Or was it ever reported as an issue ?

Problem Description

Hi
i have a big data results that make me to recieve results by stream mode
but when i pipe stream to server response i get 'first argument must be a string or Buffer' error that mades by type of stream
now my question
is there any way to change the type of node oeacle given streams to text stream that able to pipe to server response ?
thank you

Code Example

dbi.getConnection(c.dbConfig, function (err, connection) {
            if (err) {
                done(err);
                log.error(config.e[1], err);
            } else {
                var stream = connection.query(input.query, [], {streamResults: true});
                stream.pipe(response)
            }
        });

Error Stack/Info (if any)

i use multi select query to get data. now i want to use streams to pipe results...i must to use stream result in any sub query or i can use it with whole transaction?

i have lot of rows in table(~1000). when i use "connection.query" its take about 13 sec to back results.
inestead when i set streamResults to true,the time dose not changed a lot.
what should i do to increase performance ?

Support multiple update executions with different data but same SQL with 1 call

this could be done similiar in pool.run:

db.task(function (t) {
// this = t = task protocol context;
// this.ctx = task config + state context;
return t.one("select * from users where id=$1", 123)
.then(function (user) {
return t.any("select * from events where login=$1", user.name);
});
})
.then(function (events) {
// success;
})
.catch(function (error) {
console.log("ERROR:", error.message || error);
});

#### Error Stack/Info (if any)

Problem Description

getConnection with Promise still doesn't work, without extension simple-oracledb
it works without problems

Code Example

//paste code here

Error Stack/Info (if any)

home/prounix/projekte/crm/utilora/node_modules/oracledb/lib/util.js:54
throw new Error(getErrorMessage(errorCode, messageArg1));
^

Error: NJS-009: invalid number of parameters
at Object.assert (/home/prounix/projekte/crm/utilora/node_modules/oracledb/lib/util.js:54:11)
at Oracledb.getConnection (/home/prounix/projekte/crm/utilora/node_modules/oracledb/lib/oracledb.js:75:12)
at Oracledb. (/home/prounix/projekte/crm/utilora/node_modules/oracledb/lib/util.js:73:19)
at getConnectionAsync (/home/prounix/projekte/crm/utilora/node_modules/simple-oracledb/lib/simple-oracledb.js:206:38)
at Oracledb.oracle.getConnection (/home/prounix/projekte/crm/utilora/node_modules/simple-oracledb/lib/simple-oracledb.js:218:21)
at oracleSenden (/home/prounix/projekte/crm/test/tester7.js:410:10)
at Object. (/home/prounix/projekte/crm/test/tester7.js:435:2)
at Module._compile (module.js:409:26)
at Object.Module._extensions..js (module.js:416:10)
at Module.load (module.js:343:32)

to be used if available in batch operations.

Problem Description

oracledb 1.10.1 works with promises, but after extending with simple-oracledb,
it errors with "invalid number of parameters"

oracledb.getConnection( { ..} ).then( ( con ) => {

});

Code Example

//paste code here

Error Stack/Info (if any)

This is a tracking issue for oracledb issue: oracle/node-oracledb#369

Add pool level function to process multiple queries in parallel.

Hi @sagiegurari i need your help
i work in Api server that responsable to fetch data from db and send it.
assume my table has 1,500,000 rows
it take about 50 seconds to server receive all data from db...is that normal ?
in apps like dbForge...the execution time is about 1 second
is it dependent to network infrastructure ? or any method in simple oracle db to make it faster ?
thank you

when i execute select query on table,some json object results have not property that are null in the table.
this cause my app throw error...is that a way to avoid this accidence ?
is this happen ?

Problem Description

query function returns columns with value null as undefined

the execute function of oracledb returns the columns correctly as nulls

Code Example

con.query( "select * from test",[],(err,result)=>{
the rows in result will have undefined instead of null

} );

Problem Description

pool.run(function (connection, callback) {
//run some query and the output will be available in the 'run' callback
connection.query('SELECT department_id, department_name FROM departments WHERE manager_id < :id', [110], callback);
}, function onActionDone(error, result) {
//do something with the result/error
});

pool.runs OK at first call.
But after first call, onActionDone callback function is not called or reponse too late.

I use "oracledb": "1.8.0"

Error Stack/Info (if any)

hi @sagiegurari
i have type of inserts that i need get back row id that newly inserted...is there any possible way Except select after insert, that get backs new rows id ?

Trying to convert some existing code to use promises instead of callbacks.
Got the following error:

Connection failed - Error: Callback not provided.

Code:

const oracledb = require('oracledb')
const SimpleOracleDB = require('simple-oracledb')

SimpleOracleDB.extend(oracledb)
const onConnectError = error => logger.error(`Connection failed - ${error}`)
const onQueryError = error => logger.error(`Query failed - ${error}`)
const onQueryResult = results => {
  console.log(`Rows received: ${results.length}`)
}

exports.execQuery = () => oracledb.getConnection(connectionConfig)
  .then(connection => {
    connection.query(createQuery(), [], {
      splitResults: true,
      bulkRowsAmount: 50,
    })
      .then(onQueryResult)
      .catch(onQueryError)
  })
  .catch(onConnectError)

Problem Description

Hi, it would be great to have Promise based examples. Currently, i have looked but not able to find any example which is utilizing Promises. Official driver do have examples

Code Example

oracledb.getConnection({
  user: "user",
  password: "password",
  connectString: "tns"
}).then(function (connection) {
  return connection.execute(
    "SELECT USER FROM DUAL"
  ).then(function (result) {
    console.log(result.metaData);
    console.log(result.rows);
    return connection.close();
  }).catch(function (err) {
    console.log(err.message);
    return connection.close();
  });
}).catch(function (err) {
  console.error(err.message);
});

Thanks

Hi 100000 line Can you give an example for a function returning sys_refcursor?

Example :
BEGIN :result := PACKAGENAME.SELECT_USER_LIST(); END;

Describe The Bug

If the parameter of a prepared statement has the value null an error occurs int he library code

To Reproduce

• query SELECT :a (does not have to be executed)
• bind :a to null

Error Stack

batchUpdate error: TypeError: Cannot read property 'getDate' of null
    at Connection.generateBindDefinitionEntry (/home/till/IdeaProjects/bas-syncserver/server/node_modules/simple-oracledb/lib/connection.js:1691:25)
    at Connection.generateBindDefinitions (/home/till/IdeaProjects/bas-syncserver/server/node_modules/simple-oracledb/lib/connection.js:1740:18)
    at Connection.insertOrUpdate (/home/till/IdeaProjects/bas-syncserver/server/node_modules/simple-oracledb/lib/connection.js:581:14)

Code Sample

oracleconnection.batchUpdate(`SELECT :a`,[{a: null}]);

The reason is here:

Connection.prototype.generateBindDefinitionEntry = function (bindDefinitions, key, item) {
 if (typeof item === 'object') {
        if (typeof item.getDate === 'function') {

as null is considered type object

Problem Description

Hi, it would be great to have TypeScript Definition Files as official oracle driver have that and it helps in rich experience in VSCode.

Thanks

This check is done before I return connection from the pool.
It is better to use ping if available and if not, fallback to old select from dual

when i use streaming feature in oracledb,and set timer on it,its take ~17 ms to gets firts row and ~320 to end event triggered and whole data comes out.(objectMode is true)
but in normal use case when i use traditional way without stream result,its only take about ~25 ms to get whole data.
what is wrong in this use case ?
everything is ok ?
I thought streaming is more powerful and faster

Problem Description

query method is not returning a Promise.

Code Example

var simpleOracledb = require('simple-oracledb'),
oracledb = require('oracledb');

simpleOracledb.extend(oracledb);

oracledb.createPool({
user: 'user',
password: 'password',
connectString: "127.0.0.1/orcl"
}).then(function (dbpool) {
dbpool.getConnection().then(function (conection) {
conection.query('select sysdate from dual').then(function(r){
console.log(r);
})
});
});

//paste code here

Error Stack/Info (if any)

/home/oracle/hermes/node_modules/simple-oracledb/lib/rows-reader.js:49
callback(error, jsRows);
^

TypeError: callback is not a function
at onAyncDone (/home/oracle/hermes/node_modules/simple-oracledb/lib/rows-reader.js:49:13)
at /home/oracle/hermes/node_modules/async/dist/async.js:3679:13
at /home/oracle/hermes/node_modules/async/dist/async.js:952:25
at iteratorCallback (/home/oracle/hermes/node_modules/async/dist/async.js:997:17)
at /home/oracle/hermes/node_modules/async/dist/async.js:847:20
at /home/oracle/hermes/node_modules/async/dist/async.js:3676:17
at /home/oracle/hermes/node_modules/async/dist/async.js:339:31
at onRowRead (/home/oracle/hermes/node_modules/simple-oracledb/lib/rows-reader.js:42:25)
at onAyncDone (/home/oracle/hermes/node_modules/simple-oracledb/lib/record-reader.js:117:9)
at /home/oracle/hermes/node_modules/async/dist/async.js:3679:13