0

After lots of google-fu, I figured out a way to query the ancient sybase database to start building a rest api for a mobile app I'm working on using node-odbc (https://github.com/markdirish/node-odbc/blob/master/README.md#callprocedurecatalog-schema-name-parameters-callback).

I have managed to successfully query with a select statement, but would prefer not to do that for security purposes. when I try to use a stored procedure I'm getting a weird response. I need a sanity check here and figure out what I'm missing.

an example stored procedure I tried is a simple sp with one parameter to check for an email address using an account number (this is a fake sp of course, but formated the same way):

"DB"."spGetEmail"(@acctnum varchar(50) )

the docs show this:

const odbc = require('odbc');

odbc.connect(${process.env.CONNECTION_STRING}, (error, connection) => { connection.callProcedure(null, null, 'MY_PROC', [undefined], (error, result) => { if (error) { console.error(error) } // handle // result contains an array of results, and has a parameters property to access parameters returned by the procedure. console.log(result); }); });

I interpreted it to look like this:

odbc.connect(${process.env.CONNECTION_STRING}, (error, connection) => { connection.callProcedure(null, "DB", "spGetEmail", [123456], (error, result) => { if (error) { console.error(error) } // handle // result contains an array of results, and has a parameters property to access parameters returned by the procedure. console.log(result); }); });

then I get this:

[Error: [odbc] The number of parameters the procedure expects and and the number of passed parameters is not equal] { odbcErrors: [] } undefined Segmentation fault

seems straight forward, that I'm missing some parameters, but I KNOW that I'm not. The dang sp takes one parameter!

What am I missing here??

Jasmine Logan
  • 1
  • What exact Sybase DB do you mean? – Anatoly Dec 14 '20 at 17:05
  • sybase 16, SQL Anywhere 16 to be precise. – Jasmine Logan Dec 14 '20 at 17:50
  • Did you try `sqlanywhere` package? – Anatoly Dec 14 '20 at 18:38
  • yup. it crashed before it installed giving the following error message: npm ERR! code ELIFECYCLE npm ERR! errno 4294967295 npm ERR! sqlanywhere@1.0.24 install: `node build.js` npm ERR! Exit status 4294967295 npm ERR! npm ERR! Failed at the sqlanywhere@1.0.24 install script. npm ERR! This is probably not a problem with npm. There is likely additional logging output above. npm ERR! A complete log of this run can be found in: npm ERR! C:\Users\jasmine.logan\AppData\Roaming\npm-cache\_logs\2020-12-14T19_39_17_409Z-debug.log – Jasmine Logan Dec 14 '20 at 19:40
  • also tried the manual version where you npm install --global windows-build-tools as admin in powershell. It didn't crash, but didn't work out in the code either. – Jasmine Logan Dec 14 '20 at 19:44
  • was able to do a sql statement query after following these instructions, still not sure of the correct syntax for stored procedures. https://www.cdata.com/kb/tech/sybaseiq-odbc-nodejs.rst – Jasmine Logan Dec 14 '20 at 20:09

1 Answers1

0

[SOLVED] I'm not sure if the built in .callProcedure() method is broken or just not suited to Sybase16, but I found a way around it in case it's helpful to anyone in the future. Instead of using the callProcedure() method, do this:

  odbc.connect(connectionString, (error, connection) => {

    let my_procedure = "exec spGetEmail @acct='12345678'"

    connection.query(my_procedure, (error, result) => {
        if (error) { console.error(error) }
        console.log(result);
    });
});
Jasmine Logan
  • 1
  • Hey @Jasmine Logan , Could you please tell me the format of your connection string? I am also in need of connecting to Sybase via NodeJS – Anand Sep 14 '21 at 06:46
  • @Jasmine Logan - Not sure how you could get OUT params from a sproc like this. – backdesk Aug 08 '22 at 12:48