stmt.execute callback function's "result" parameter

[Matt V]

I am confused on the result parameter in the execute's callback function. 

ibmdb.open(cn,function(err,conn){
  conn.prepare("insert into hits (col1, col2) VALUES (?, ?)", function (err, stmt) {
    if (err) {
      //could not prepare for some reason
      console.log(err);
      return conn.closeSync();
    }

    //Bind and Execute the statment asynchronously
    stmt.execute(['something', 42], function (err, result) {
      result.closeSync();

      //Close the connection
      conn.close(function(err){}));
    });
  });
});

What is this data type and why is it closed in the example along with the conn.close()? I'm attempting to run the result.close() function as in the example, but on error prone statements (bad sql stmts), the function fails and returns a 

502 Bad Gateway: Registered endpoint failed to handle the request.

Does this mean that the result.close() should only be called on successful executions?

[bimaljha]

Datatype of result is object. You can get it using "console.log(typeof result);". It is called to free the internally allocated statement handles. It internally calls ODBC API SQLFreeStmt(). conn.close() disconnects a database connection and then frees the internal connection handle.

It is result.closeSync() and not result.close(). On error prone statement, stmt would be undefined and hence calling stmt.closeSync() would return "Uncaught TypeError: Cannot call method 'closeSync' of undefined". I never get 502 Bad Gateway error.

Yes, result.closeSync() should only be called if there is no error. The code should be as below:

    //Bind and Execute the statment asynchronously
    stmt.execute(['something', 42], function (err, result) {

          if(err) console.log(err);
          else result.closeSync();

[Matt V]

That helps a lot, thank you!

[Berni Atelšek]

In my case (#node_v12_branch) result is object: { fetchMode: 4 } and nothing else. How can I get the "(err, rows, moreResultSets)" like in

    conn.query("select * from customers fetch first 10 rows only", function (err, rows, moreResultSets) {

[bimaljha]

Use below test program and share the result:

$ cat customer.js
var ibmdb = require("ibm_db"),
    cn = "DATABASE=sample;HOSTNAME=localhost;PORT=3000;PROTOCOL=TCPIP;UID=mypass;PWD=xxxx;";

try {
    var conn = ibmdb.openSync(cn);
    var result = conn.querySync("create table mycustomer (custid int, custname varchar(20))");
    result = conn.querySync("insert into mycustomer values (1, 'john'),(2, 'tony'),(3, 'Mohan'),(4, 'Shyam'),(5,'Gopal'),(6,'Hari')");
    conn.query("select * from mycustomer fetch first 5 rows only", function (err, rows, moreResultSets) {
        if (err) {
            console.log(err);
        } else {
          console.log(rows);
        }
        result = conn.querySync("drop table mycustomer");
        conn.close();  
    });
} catch (e) {
    console.log(e.message);

[Berni Atelšek]

With QUERY is ok. Only with PREPARE and EXECUTE I get result: { fetchMode: 4 }.

[bimaljha]

Ok. Initially you mentioned about QUERY only. Since, query is working for you, you can use it.
Seems, prepare & execute is not working properly for select statement. we'll look into it. Thanks.

[bimaljha]

you need to call result.fetchAll() or result.fetchAllSync() to get data returned by executed statement as below:

var query = "select 1 from sysibm.sysdummy1";

pool.open(connectionString, function (err, conn) {

        conn.prepare(query, function (err, stmt) {

            if (err) {

                console.error('error: ', err.message);

                return conn.close(function (err) { });

            } else {

                stmt.execute([], function (err, result) {

                    if (err) console.log(err); // no error

                    else console.log(result.fetchAllSync()); 

                    if (err) {

                        console.error('error: ', err.message);

                    }

                    result.closeSync();

                    conn.close(function (err) { });

                });

            }

        });

    });

Thanks.

[Valerio Riva]

Hi bimaljha! Can you please share this piece of code on ibm_db npm/git docs? I had a hard time find how to get results from a statement, until this post popped up on my google searches!