Calling HANA SP from Node with out parameter

[former_member586258]

Hi there!

I got .hdbprocedure with such prototype:

PROCEDURE "SCHEMA_NAME"."PROJ_NAME::sp_name" (
  OUT RES TABLE (
    "FIELD_1" NVARCHAR(31),
    "FIELD_2" INTEGER,
    ...
  )
)

Also I got Node.js server, where I use package @sap/hana-client, so, I guess not depreceted one hdb.

The problem I faced is that I just have no idea how to call my SP from js code. What I'm doing is:

const getSomeQuery = 'CALL "SCHEMA_NAME"."PROJ_NAME::sp_name"'
function getSomeFunc() {
    let stmt = connection.prepare(getSomeQuery);
    stmt.execQuery((err, RES) => {
        if (err) {
            return console.error('SQL execute error:', err);
        }
        ...
    });
}

Of course,

const connection = require("@sap/hana-client").createConnection()

works well with SELECT statements.

In the represented form I receive such error:

Error: wrong number or types of parameters in call: RES is not bound

I might be too inattentive, but I didn't find solution either HANA Client Interface Programming Reference or this tutorial, or even any concepts from Git repo or this topic as as I guess they are for hdb package.

So, how to properly call SP and SF using node api? Should I mask parameters with "?" in query string? How to bind out parameters?

[pfefferf · Accepted solution]

It is possible to call it like following. The statement.exec callback gets as first argument an error object (in case of errors), the second one is an object containing the scalar output parameter (in your case an empty object, cause you have no scalar output parameters), the further arguments are the table output parameters (in your case one parameter for the RES output parameter):

connection.prepare('call "SCHEMA_NAME"."PROJ_NAME::sp_name" (?)', function(err, statement){
  if (err) {
    // error handling
    return;
  }
  statement.exec({}, function(err, outScalarParameters, outResultTable) {
    if (err) {
      // error handling
      return;
    }
    // use result in outResultTable
  });
});

I would recommend that you have a look to the @sap/hdbext module, which makes the access a little bit easier. Check the example in the documentation here.

[former_member586258 · Accepted solution]

Thank you!

I used a bit modified version of your code:

connection.prepare('call "SCHEMA_NAME"."PROJ_NAME::sp_name" (?)', (err, statement) => {
        if (err) {
            // error handling
            return;
        }
        statement.execQuery({}, (err, res) => {
            if (err) {
                // error handling
                return;
            }
            // use res, which behaves like coursor with res.next(), res.getValues(),... methods
        });
    });