cancel
Showing results for 
Search instead for 
Did you mean: 

How to call a Stored Procedure via Node.JS

Former Member
0 Kudos

Hi,

I'm receiving errors when I want to call a Procedure via Node.JS:

app.route("/add/?")

        .post(function(req, res) {

           

            var data = req.body,

                client = req.db

            ;

           

            client.prepare("call my.namespace.procedures.subfolder::procName(?,?)",

                function(err, stmt) {

                    if (err) {

                        res.json({status: 400, message: "ERR", data: err});

                    } else {

                        stmt.exec({

                            a: "test A"

                            b: "test B"

                        }, function(err, param, dummy, result) {

                            if (err) {

                                res.json({status: 401, message: "ERR", data: err});

                            } else {

                                var returnData;

                                returnData.param = param;

                                returnData.dummy = dummy;

                                returnData.result = result;

                                res.json({status: 200, message: "OK", data: returnData});

                            }

                        });

                    }

                });

        });

The Error Message:

sql syntax error: database name not allowed in single-container mode: line 1 col 6 (at pos 6)

If I switch the call-Statement to:

call procName(?,?)


It returns the Error:

invalid name of function or procedure: PROCNAME: line 1 col 6 (at pos 5)



What am I doing wrong? The Name and Namespace definitively exists.

Accepted Solutions (1)

Accepted Solutions (1)

SergioG_TX
Active Contributor
0 Kudos

Soren,

I found this on the XSA Developer Guide p406:


The hdb package is not included in the XS_JAVASCRIPT archive of Node.js packages which is available for download from the SAP Service Marketplace; hdb must be obtained independently.

var hdbcon = new hdbext.getConnection(client);

hdbcon.loadProcedure('M', 'PROC_DUMMY', function(err, sp) {

sp.exec({A:3, B:4}, function(err, parameters, dummyRows, tableRows) {

if (err) {

return console.error('Exec error:', err);

}

console.log('Parameters:', parameters);

console.log('Dummies:', dummyRows);

//console.log('Tables:', tableRows);

});

});

hope this helps

Former Member
0 Kudos

Hi,

thank you for this! But I have two issues with that:

First, .getConnection() is marked as deprecated. The new Method should be .createConnection() but then I will get the Error, that hdbcon is undefined and cannot be instantiated. The next issue is, that it seems like .loadProcedure() just uses client.prepare. So my Error then is: client.prepare is not a function



But I#ve found out, we need to call the Namespace of this Table (the HDI-Name as the Scheme) in order to call the Procedure. You will find the Name within the Catalog (its the technical Databasename). So it looks like this:

[RANDOMHASH]_[NAME]_HDI_CONTAINER.my.namespace.procedures.subfolder::procName


Anyway now this logic shown above wont put data into my table, except its ID. All fields are now empty. I will try to fix that and respond here then.

Former Member
0 Kudos

We have fixed it. We need to handover the Variables in UPPER CASE, no matter, in which case the rows are listed in the DB. All must be UPPERCASE without any exception. So here is my working code:

client.prepare('call "[HASH]_[NAME]_HDI_CONTAINER"."my.namespace::procName"(?, ?)',

                function(err, stmt) {

                    if (err) {

                        res.json({status: 400, message: "ERR", data: err});

                    } else {

                        stmt.exec({

                            A: "Test a",

                            B: "Test b",

                            ex_return: ''

                        }, function(err, param, dummy, result) {

                            if (err) {

                                res.json({status: 401, message: "ERR", data: err});

                            } else {

                                var returnData = {};

                                returnData.param = param;

                                returnData.dummy = dummy;

                                returnData.result = result;

                                returnData.body = req.body;

                                res.json({status: 200, message: "OK", data: returnData});

                            }

                        });

                    }

                }

            );

Answers (0)