crazysacx / node-jdbc-example Goto Github PK

View Code? Open in Web Editor NEW

13.0 13.0 10.0 5.3 MB

Node JDBC Example Project

Shell 2.97% JavaScript 97.03%

node-jdbc-example's People

Contributors

Stargazers

Watchers

Forkers

otterley syahiaoui manuelvalino vahidtaslimi patelvipulgk karora2 neelimabenny markddrake ayushub shuanand

node-jdbc-example's Issues

Query bind with a lot of parameters !

Hi,

I have a problem and I wanted to know is there anyway to write a clean code for inserting data in for example 16 columns without using nested if & else?

Because if i do in a more clean way without nested if & else, it always returns randomly(sometimes it inserts sometimes returns error) an error which is like below

[Error: Error running instance method
java.sql.SQLException: Missing IN or OUT parameter at index:: 1
        at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:1937)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3657)
        at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1426)
        at oracle.jdbc.driver.OraclePreparedStatement.executeLargeUpdate(OraclePreparedStatement.java:3756)
        at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1063)
        at jdk.internal.reflect.GeneratedMethodAccessor6.invoke(Unknown Source)
        at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.base/java.lang.reflect.Method.invoke(Method.java:567)
] {
  cause: nodeJava_java_sql_SQLException {},
  level: 'error',
  [Symbol(level)]: 'error'
}

And, also the code is not clean and looks like below

statement.setInt(1, l_id, function (err) {
                                if (err) {
                                    console.log(err);
                                    res.status(500).json({ "status": 0, "statusDescription": err.toString() });
                                } else {
                                    statement.setString(2, nome, function (err) {
                                        if (err) {
                                            console.log(err);
                                            res.status(500).json({ "status": 0, "statusDescription": err.toString() });
                                        } else {
                                            statement.setString(3, cognome, function (err) {
                                                if (err) {
                                                    console.log(err);
                                                    res.status(500).json({ "status": 0, "statusDescription": err.toString() });
                                                } else {
                                                    statement.setString(4, stato, function (err) {
                                                        if (err) {
                                                            console.log(err);
                                                            res.status(500).json({ "status": 0, "statusDescription": err.toString() });
                                                        } else {
                                                            .....
}

My idea is to use another .js file to do this job for me with using switch case for binding types and using a function for executing the query. So, my queryBind.js is like below

module.exports = {
    setVal: setVal,
    executeRicetta: executeRicetta,
    executeRicettaDettagli: executeRicettaDettagli,
    executePazienti: executePazienti

}
async function setVal(statement, type, col, val) {
    switch (type) {
        case "setInt":
            statement.setInt(col, val, function (err) {
                if (err) {
                    return false;
                } else {
                    return true;
                }
            });
            break;
        case "setString":
            statement.setString(col, val, function (err) {
                if (err) {
                    return false;
                } else {
                    return true;
                }
            });
            break;
    }
}

async function executeRicetta(statement, callback, res) {
    statement.executeUpdate(function (err, count) {
        if (err) {
            console.log(err);
            res.status(500).json({ "status": 0, "statusDescription": err.message });
        } else {
            console.log('Statement executed for ricetta insert!');
            callback(null, count);
            //console.log(count)
        }
    });
}

async function executeRicettaDettagli(statement, callback, res) {
    statement.executeUpdate(function (err, count) {
        if (err) {
            console.log(err);
            res.status(500).json({ "status": 0, "statusDescription": err.message });
        } else {
            console.log('Statement executed for ricetta_dettagli insert!');
            callback(null, count);
            //console.log(count)
        }
    });
}

async function executePazienti(statement, callback, res, req) {
    statement.executeUpdate(function (err, count) {
        var statement_str = JSON.stringify(statement)
        if (err) {
            console.log(err);
            create_logFile(req, -1, JSON.stringify(statement));
            console.log("SATEMENT",statement)
            res.status(500).json({ "status": 0, "statusDescription": err.message });
        } else {
            console.log('Statement executed for pazienti insert!');
            callback(null, count);
            //console.log(count)
        }
    });
}

And, I call this file in my controller like below

oracledb.reserve(function (err, connObj) {
        //         // The connection returned from the pool is an object with two fields
        //         // {uuid: <uuid>, conn: <Connection>}
        if (err) {
            res.status(500).json({ "status": 0, "statusDescription": err.toString() });
            logger.create_logFile(req, 1, err.message);
        } else if (connObj) {
            console.log("Using connection: " + connObj.uuid);
            // Grab the Connection for use.
            var conn = connObj.conn;

 asyncjs.series([
                function (callback) {
                    conn.prepareStatement(query.importaAnagrafica, function (err, statement) {
                        if (err) {
                            callback(err);
                            console.log(err);
                            res.status(500).json({ "status": 0, "statusDescription": err.toString() });
                        } else {
                            console.log('Statement prepared for pazienti insert');
                            l_step = "[/queryBind for pazienti]"

                            queryBind.setVal(statement, "setInt", 1, pazientiJson.id);
                            console.log("BIND 1", pazientiJson.id);
                            queryBind.setVal(statement, "setString", 2, pazientiJson.nome);
                            console.log("BIND 2", pazientiJson.nome);
                            queryBind.setVal(statement, "setString", 3, pazientiJson.cognome);
                            console.log("BIND 3", pazientiJson.cognome);
                            queryBind.setVal(statement, "setString", 4, pazientiJson.stato);
                            console.log("BIND 4", pazientiJson.stato);
                            queryBind.setVal(statement, "setString", 5, pazientiJson.sesso);
                            console.log("BIND 5", pazientiJson.sesso);
                            queryBind.setVal(statement, "setString", 6, pazientiJson.codice_fiscale);
                            console.log("BIND 6", pazientiJson.codice_fiscale);
                            queryBind.setVal(statement, "setString", 7, data_di_nascita_str);
                            console.log("BIND 7", data_di_nascita_str);
                            queryBind.setVal(statement, "setString", 8, pazientiJson.indirizzo_residenza);
                            console.log("BIND 8", pazientiJson.indirizzo_residenza);
                            queryBind.setVal(statement, "setString", 9, pazientiJson.cap_residenza);
                            console.log("BIND 9", pazientiJson.cap_residenza);
                            queryBind.setVal(statement, "setString", 10, pazientiJson.indirizzo_domicilio);
                            console.log("BIND 10", pazientiJson.indirizzo_domicilio);
                            queryBind.setVal(statement, "setString", 11, pazientiJson.cap_domicilio);
                            console.log("BIND 11", pazientiJson.cap_domicilio);
                            queryBind.setVal(statement, "setString", 12, pazientiJson.id_comune_domicilio);
                            console.log("BIND 12", pazientiJson.id_comune_domicilio);
                            queryBind.setVal(statement, "setString", 13, pazientiJson.id_provincia_domicilio);
                            console.log("BIND 13", pazientiJson.id_provincia_domicilio);
                            queryBind.setVal(statement, "setString", 14, pazientiJson.id_comune_residenza);
                            console.log("BIND 14", pazientiJson.id_comune_residenza);
                            queryBind.setVal(statement, "setString", 15, pazientiJson.id_provincia_residenza);
                            console.log("BIND 15", pazientiJson.id_provincia_residenza);
                            queryBind.setVal(statement, "setString", 16, data_inserimento_str);
                            console.log("BIND 16", data_inserimento_str);
                            console.log("STATEMENT", JSON.stringify(statement))
                            queryBind.executePazienti(statement, callback, res, req);
 }
                    });
                }
            ], function (err, results) {
                // Results can also be processed here.
                if (err) {
                    console.log(err)
                    logger.create_logFile(req, -1, err.message);
                    res.status(500).json({ "status": 0, "statusDescription": err.message });
                } else {
                    console.log(results)
                    res.status(200).send({ "status": "1", "statusDescription": "Ok" });
                    l_message = "status:1, statusDescription: Ok";
                    logger.create_logFile(req, 1, l_message);
                    // Release the connection back to the pool.
                    oracledb.release(connObj, function (err) {
                        if (err) {
                            console.log(err.message);
                            res.status(500).json({ "status": 0, "statusDescription": err.message });
                        }
                    });
                }

            });

If I write above code(which is much more clean than nested if & else), I will get the error that I have mentioned at the beggining.

[Error: Error running instance method
java.sql.SQLException: Missing IN or OUT parameter at index:: 1
        at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:1937)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3657)
        at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1426)
        at oracle.jdbc.driver.OraclePreparedStatement.executeLargeUpdate(OraclePreparedStatement.java:3756)
        at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1063)
        at jdk.internal.reflect.GeneratedMethodAccessor6.invoke(Unknown Source)
        at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.base/java.lang.reflect.Method.invoke(Method.java:567)
] {
  cause: nodeJava_java_sql_SQLException {},
  level: 'error',
  [Symbol(level)]: 'error'
}

How to execute Batch Select

Hi
I have 100 SELECT queries that differ by one input.
How to use the a PreparedStatement to execute batch select.

Thanks,
Sai

How to purge the pool

Hi @CraZySacX (Jason),

var derby = new JDBC({
url: 'jdbc:derby://localhost:1527/testdb;create=true',
minpoolsize: 5,
maxpoolsize: 10
});

Above function mentioned in example will create 5 connection and hold in pool. How to clean/drop connections from the pool on application exit?

Thanks,
Vikram

How to create multiple connections with jars of same kind(Athena) but differently configured for separate accounts?

How to run this example with my existing JDBC in Netbeans?

I have 2 problems.
First, I installed node-jdbc in my project and I couldn't make it work. Then I try this example to understand more about how node-jdbc works. But I had following error during npm install.

Second, how do you suggest I can use node-jdbc to connect to my db using NodeJS+Express?
Below is my current JDBC details in glassfish

Unable to build under node 5.0

Tried to install under node 5.0, npm 3.3.6 and java open idk 1.8.0, the package wouldn't build. Here are the error messages:

CXX(target) Release/obj.target/nodejavabridge_bindings/src/java.o
In file included from ../../nan/nan_new.h:190:0,
from ../../nan/nan.h:80,
from ../src/java.h:9,
from ../src/java.cpp:1:
../../nan/nan_implementation_12_inl.h: In static member function ‘static NanIntern::FactoryBasev8::Signature::return_t NanIntern::Factoryv8::Signature::New(NanIntern::Factoryv8::Signature::FTH, int, NanIntern::Factoryv8::Signature::FTH_)’:
../../nan/nan_implementation_12_inl.h:181:76: error: no matching function for call to ‘v8::Signature::New(v8::Isolate_, NanIntern::Factoryv8::Signature::FTH&, int&, NanIntern::Factoryv8::Signature::FTH_&)’
return v8::Signature::New(v8::Isolate::GetCurrent(), receiver, argc, argv);
^
In file included from ../src/java.h:5:0,
from ../src/java.cpp:1:
/home//.node-gyp/5.0.0/include/node/v8.h:4674:27: note: candidate: static v8::Localv8::Signature v8::Signature::New(v8::Isolate*, v8::Localv8::FunctionTemplate)
static Local New(
^
/home//.node-gyp/5.0.0/include/node/v8.h:4674:27: note: candidate expects 2 arguments, 4 provided
In file included from ../src/java.h:9:0,
from ../src/java.cpp:1:
../../nan/nan.h: At global scope:
../../nan/nan.h:171:25: error: redefinition of ‘template v8::Local _NanEnsureLocal(v8::Local)’
NAN_INLINE v8::Local NanEnsureLocal(v8::Local val) {
^
../../nan/nan.h:166:25: note: ‘template v8::Local NanEnsureLocal(v8::Handle)’ previously declared here
NAN_INLINE v8::Local NanEnsureLocal(v8::Handle val) {
^
../../nan/nan.h:564:13: error: ‘node::smalloc’ has not been declared
, node::smalloc::FreeCallback callback
^
../../nan/nan.h:564:35: error: expected ‘,’ or ‘...’ before ‘callback’
, node::smalloc::FreeCallback callback
^
../../nan/nan.h: In function ‘v8::Localv8::Object NanNewBufferHandle(char, size_t, int)’:
../../nan/nan.h:568:50: error: ‘callback’ was not declared in this scope
v8::Isolate::GetCurrent(), data, length, callback, hint);
^
../../nan/nan.h:568:60: error: ‘hint’ was not declared in this scope
v8::Isolate::GetCurrent(), data, length, callback, hint);
^
../../nan/nan.h: In function ‘v8::Localv8::Object NanNewBufferHandle(const char, uint32_t)’:
../../nan/nan.h:575:67: error: no matching function for call to ‘New(v8::Isolate, const char*&, uint32_t&)’

And quite some similar messages. It mainly looks v8 interface mismatch.

Recommend Projects

React

A declarative, efficient, and flexible JavaScript library for building user interfaces.
Vue.js

🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
Typescript

TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
TensorFlow

An Open Source Machine Learning Framework for Everyone
Django

The Web framework for perfectionists with deadlines.
Laravel

A PHP framework for web artisans
D3

Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

javascript

JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
web

Some thing interesting about web. New door for the world.
server

A server is a program made to process requests and deliver data to clients.
Machine learning

Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Visualization

Some thing interesting about visualization, use data art
Game

Some thing interesting about game, make everyone happy.

Recommend Org

Facebook

We are working to build community through open source technology. NB: members must have two-factor auth.
Microsoft

Open source projects and samples from Microsoft.
Google

Google ❤️ Open Source for everyone.
Alibaba

Alibaba Open Source for everyone
D3

Data-Driven Documents codes.
Tencent

China tencent open source team.