Node.js Firebird SQL connector - getting result from INSERT ... RETURNING

Question

I am trying to write a function that lets me insert a value into the firebird database. The query works well, only I get no callback to tell me that the insert went well.

It is the first time I am using a firebird connector. In the past, when using mySql connectors I can recall having some sort of callback when inserting new values. Right now I am using the node-firebird library by Henri Gourvest to accomplish this: https://github.com/hgourvest/node-firebird/

I tried adding 'RETURNING FEATURE_ID' at the end, but an error "Cursor is not open" was thrown. The feature ID is generated by a trigger.

Any advice would be very kind.

pool.get(function(error, db) {
    if (error) {
        console.log(error)
        res.status(403)
        res.send()

    }
    else {
        var date = moment(req.body.date, "DD/MM/YYYY")
        var values = " VALUES ('" + date.format("MM/DD/YYYY") + "','Requested','" + req.body.type + "','" + req.body.description + "','" + req.body.memo +"')"
        var query = 'INSERT INTO "Features" (FEATURE_REQUESTDATE, FEATURE_STATUS, FEATURE_TYPE, FEATURE_DESCRIPTION, FEATURE_MEMO)' + values


        db.query( query , function(err, result) {
            if (result) { //why is there no result here?
                res.status(200)
                res.send('ok')
            }
            if (err) {
                console.log(err)
                res.status(403)
                res.send('error')
            }
        })


            db.detach();
        }
})

I hope you are aware your code is vulnerable to SQL injection. Use parameters instead of concatenating values into the query string. — Mark Rotteveel, Sep 18 '17 at 16:34
Thank you Mark, I will make sure to use parameters in the future. — Djov, Sep 19 '17 at 09:38

Arioch 'The · Accepted Answer · 2017-09-19T09:14:54.010

I tried adding 'RETURNING FEATURE_ID' at the end, but an error "Cursor is not open" was thrown.

Sure, there can be no cursor. Cursors (AKA rowsets) are only created by queries - SELECT-type SQL statements.

As stated in Firebird documentation, statements with RETURNING clause are not of query type, they are of procedure call type. You should execute them as you do with regular DELETE-type statements, then read the PARAMETERS of the statement executed.

Right now I am using the node-firebird library by Henri Gourvest to accomplish this: https://github.com/hgourvest/node-firebird/
Any advice would be very kind.

There are two advices.

NEVER do splice your data values into SQL command text. It makes your program very fragile. It would give you all the kinds of data conversion errors, and also it opens highways for your database corruption made by unexpected - erroneous or malicious - user input. See http://bobby-tables.com/ and http://en.wikipedia.org/wiki/SQL_injection
"Use the source Luke". The library you mentioned is open-source. So you have to check the examples in that library. Henri is known to be very laconic about documentation. However he supplies his different libraries with vast sets of examples and/or tests. Both suit for you, as they do use the library, and so you can just read how the library was intended to be used by its creator. This particular library has tests. And tests are always examples of intended use.

So you go into test folder and you see there run.js file. Open it.

https://github.com/hgourvest/node-firebird/blob/master/test/run.js

Now press Ctrl+F and search for "RETURNING" word. Not always first time, but one of its occurrences should be exactly test for the SQL feature you need.

Here it is, the very FIRST occurrence of it in the library text you already have on your machine. Granted, the first occurrence adds complexity of working with BLOBs that you do not need right off. So I would quote the THIRD example in the library you downloaded. But even the first example shows you how to properly execute queries with values and with RETURNING clauses.

function test_insert(next) {

....skip.......

   // Insert record without blob 
         database.query('INSERT INTO test (ID, NAME, CREATED, PARENT) VALUES(?, ?, ?, ?) RETURNING ID', [3, 'Firebird 3', now, 862304020112911], function(err, r) { 
             assert.ok(!err, name + ': insert without blob (buffer) (1) ' + err); 
             assert.ok(r['id'] === 3, name + ': without blob (buffer) returning value'); 
             next(); 
         }); 

     // Insert record without blob (without returning value) 
         database.query('INSERT INTO test (ID, NAME, CREATED) VALUES(?, ?, ?)', [4, 'Firebird 4', '2014-12-12 13:59'], function(err, r) { 
             assert.ok(!err, name + ': insert without blob (buffer) (2) ' + err); 
             assert.ok(err === undefined, name + ': insert without blob + without returning value'); 
             next(); 
         });

Thank you for this advice. You have taught me a valuable lesson! — Djov, Sep 19 '17 at 10:56
I absolutely not understand the point of this answer. We have a query with `RETURNING ID` clause. In example we have absolutely common `database.query` call with parameters. I do the same as in the example and get "Cursor is not open". What I miss? What does it mean `DELETE-type statements`? — Andrey Shaforostov, Aug 20 '23 at 15:37
https://github.com/hgourvest/node-firebird/issues/135 Old issue about this. Somebody said it works on 2.5. But not. — Andrey Shaforostov, Aug 20 '23 at 19:07

Node.js Firebird SQL connector - getting result from INSERT ... RETURNING

1 Answers1