How can I pass dynamic array of items to %ExecDirect method.

Question

Question

Gautam Rishi · Jun 9, 2023

#InterSystems IRIS #InterSystems IRIS for Health #VSCode

I am having code like this,

set args = []

set resultSet = ##class(%SQL.Statement).%ExecDirect( , sql_whereClause, args)

Instead of placing args[1],args[2] etc into the above I want to pass the whole args at a time if it possible.?

Discussion (5)1

Log in or sign up to continue

Julius Kavay · Jun 9, 2023

Correct.
But (in case of need) there is a workaround (assuming, args is an JSON-array of arguments):

kill tmp
set tmp=args.%Size()
for i=1:1:tmp set tmp(i)=args.%Get(i-1)
set resultSet = ##class(%SQL.Statement).%ExecDirect( , sql_whereClause, tmp...)

1 0

score 2 · Answer 1 · 2023-06-09T11:09:31-04:00

kill args
set args($i(args))="arg1"
set args($i(args))="arg2"
set args($i(args))="arg3"
...
set resultSet = ##class(%SQL.Statement).%ExecDirect( , sql_whereClause, args...)

score 0 · Answer 2 · 2023-06-09T12:16:59-04:00

I tried this but it didn't work. Also here is how I am using args to set value inside.
do args.%Push(requestBody.firstname)

do args.%Push(requestBody.surname)

then I did the same thing. -

set resultSet = ##class(%SQL.Statement).%ExecDirect( , sql_whereClause, args...)

score 1 · Answer 3 · 2023-06-09T14:15:42-04:00

I had some doubt in your initial question.

NowYOUR mistake is evident
you compose a JSON array args=[arg1value,agr3value,arg3value,..]
using %Push(..)

to %SQL.Statement this is just kind of a strange structured String
%SQL.Statement doesn't deal with JSON Arrays or Objects

with arg... you have to pass a local variable array
which is a core structure of Object Script (since ever)

arg=3 ;;<max subscript count>
arg(1)=arg1value
arg(2)=arg2value
arg(3)=arg3value

as already described by @Julius Kavay

score 0 · Answer 4 · 2023-06-09T14:26:13-04:00

jaroslav rapp · Jun 9, 2023

Simple summary.
passing a dynamic array to %SQLStatement is not possible and not implemented

0 0