New post
Question
· May 9, 2019

Node.js using SQL query

#InterSystems IRIS

I am attempting to use Node.js to perform a simple query against the database using some identifying information to get the ID. This is what I am attempting to do:

 

var irisobj = require('iris');
var myData = new irisobj.IRIS();
var result = myData.open({ path:"C:/InterSystems/IRIS/Mgr",
                       username: "USERNAME",
                       password: "PASSWORD",
                       namespace: "YOURNAMESPACE"
            }
        );

var result = myData.invoke_classmethod({class: "%SYSTEM.SQL", method: "Execute", arguments: ["SELECT TOP 1 ID FROM SOMETABLE"]});
console.log(result);

myData.close();

Why doesn't this work???

Discussion (3)1
Log in or sign up to continue
Jon Willeke · May 9, 2019

The %SYSTEM.SQL::Execute() method returns an %SQL.StatementResult object. I'm not that familiar with the iris.node module, but it appears that invoke_classmethod() does not return an object that you can pass to invoke_method() or get_property(). However, it seems that you can fake it by copying the "result" field into a new object as "oref":

> result = data.invoke_classmethod({class: '%SYSTEM.SQL', method: 'Execute', arguments: ['select 1']})
{ ok: 1,
  class: '%SYSTEM.SQL',
  method: 'Execute',
  arguments: [ 'select 1' ],
  result: '12' }
> data.get_property(result, '%SQLCODE')
Error: No object reference provided
> o = {oref: result.result}
{ oref: '12' }
> data.get_property(o, '%SQLCODE')
{ ok: 1, oref: 12, property: '%SQLCODE', value: '0' }
> data.invoke_method(o, '%GetData', 1)
{ ok: 1, oref: 12, method: '%GetData', result: '1' }
0 0
Cliff Akers · May 12, 2019

The code posted by Jon was close, we actually need to step into the first row before getting data. The snippet of code below will allow a developer to connect to IRIS and pass a query string then obtain the result, all using Node.js. I hope this helps someone else attempting the same thing. 

var irisobj = require('iris');
var myData = new irisobj.IRIS();
var result = myData.open({ path:"C:/InterSystems/IRIS/Mgr",
                       username: "USERNAME",
                       password: "PASSWORD",
                       namespace: "YOURNAMESPACE"
            }
        );

var result = myData.invoke_classmethod({class: "%SYSTEM.SQL", method: "Execute", arguments: ["SELECT TOP 1 ID FROM SOMETABLE"]});
var o = {oref: result.result};

myData.invoke_method(o, '%Next');
var value = myData.invoke_method(o, '%GetData', 1);

console.log(value.result);

myData.close();

0 0