CODEWITHSUNDEEP
javascriptnode.jsdatabaseexpressoracle11g
help
help

Reputation: 851

what is the correct way to release connection after commit in nodejs for oracledb

I am currently using angularJS as my frontend framework while expressJS on node.JS to provide the REST API as my backend framework.

For my insertService function in node.JS, after I insert some value into the database, I want to commit and release the connection. However, I am getting the following error: 

NJS-032: connection cannot be released because a database call is in progress

These are my commit and release functions:

function doRelease(connection)
{
  console.log("before release");
  connection.release(
    function(err) {
      if (err)
        console.error(err.message);
    });
    console.log("after release");
}

function doCommit(connection)
{
    console.log("before commit");
    connection.commit(
    function(err) {
      if (err)
        console.error(err.message);
    });
    console.log("after commit");
    doRelease(connection);
}

This is how I am calling them:

app.post('/addService', function(req, res) 
{    
    console.log("addService is called");

    oracledb.getConnection(
        DBconfig,
        function(err, connection)
        {
         if (err) { console.error(err); return; }
         connection.execute(
           "Insert into mylist Values (MYLIST_ID_SEQUENCE.nextval ,'"+req.body.name+"','"+req.body.description+"')",
           function(err, result)
           {
             if (err) { console.error(err); doRelease(connection); return; }
             console.log("added mylist: "+req.body.name);
             doCommit(connection);
           }
         );   
        }
    );
})

This is the print out:

addService is called
before commit
after commit
before release
after release
NJS-032: connection cannot be released because a database call is in progress

How should I handle this issue? Should I sleep for 1 second before calling release? Should I recursively call release until it is successful? Thanks

Upvotes: 0

Views: 1067

Answers (1)

Dan McGhan
Dan McGhan

Reputation: 4659

Before I answer your question I have to point out that your code is currently open to SQL injection vulnerabilities. Values from end users (in this case from req.body) should not be concatenated into the SQL, they should be "bound" in with bind variables.

Also, you're API will not scale if you're getting one off connections. You should create a connection pool and get connections from the pool.

Finally, you can use autoCommit (in the execute options object) to save an unnecessary round trip.

Now to your question, you have to wait until the commit finishes before releasing the connection. In doCommit, move the call to doRelease so that it's in the callback to connection.commit:

function doCommit(connection) {
    console.log("before commit");

    connection.commit(function(err) {
      if (err) {
        console.error(err.message);
      }

      console.log("after commit");

      doRelease(connection);
    });
}

On another note, I have a series on building REST APIs you might want to check out. In part 2, on database basics, I show how you can simplify these types of simple statement executions. There are links to the code in GitHub so you should be able to pull it down to see how it works for you.

Upvotes: 1

Related Questions