db-oracle is not flushing data

Question

I'm working on a simple service using Node.js. It receives uploaded files, stores them on the disk and records some metadata on an Oracle table. I'm using the `db-oracle` package together with connection pooling, following this article: http://nodejsdb.org/2011/05/connection-pooling-node-db-with-generic-pool/ However, I've noticed that the data I insert is only sent to the Oracle database after the connection pool closes the idle connection, by calling its `disconnect()` method. Is there a way to flush the data before sending the 'OK' signal to my client? The way it is working now, a crash on my webservice or on Oracle itself can cause loss of data, and the client of my service would not know about it. I actually tested this by killing my app process after some uploads, and the data was indeed lost. Here's a simplified version of the code: ``` var express = require('express'); var app = module.exports = express.createServer(); app.post('/upload', handleUpload); app.listen(4001, functi…

Accepted Answer

You are obviously missing a transaction commit.

node-db does not need to expose a commit API because in most RDBMS (including Oracle), COMMIT is a valid query. Since the package allows the execution of arbitrary queries, commit/rollback are supposed to be done using a simple execute()

The code should be changed as follows:

```
pool.acquire(function(err, conn) {
  if (err)
    return sendError(res, err, 500);

var q = conn.query().insert('FILES', fields, values);
  q.execute(function(err, result) {

if (err || result.affected < 1 ) {
       pool.release(conn);
       return sendError(res, err, 500);
    }

conn.query().execute("commit", function(err,result) {
      if (err) {
        pool.release(conn);
        return sendError(res, err, 500);
      }
      res.end('<ok />');
      pool.release(conn);
    });
  });
});
```

db-oracle is not flushing data

Solution

Problem