To pass document list variables as input to the Custom/Dynamic SQL query

, ,
1

Hi,

Can any please advise on how we can pass the variables associated with documentlist as an input to the input variables in the custom SQL query of the JDBC Adapter service?

for example:

select empname, empno from emp where empno IN (%/emprecord/empno[0]%,%/emprecord/empno[1]%,%/emprecord/empno[2]%)

2

Hi,

Can try this:
Loop on documentlist.Inside loop docList will appear as document, then you can map input i.e (%/emprecord/empno%) to input variables in the custom SQL query.

Regards,
Puneet Saxena

3

Hi,
in order to map document list to adapter service input ,as You wrote, you have to use dynamic SQL adapter service (not a custom SQL). And then use as SQL something like this:

select empname, empno from emp where empno IN (${listOfIds})

the adapter service during runtime, will replace ${listOfIds} with a string that you will provide, and than it will execute the query.
So in your case, you will have to map document list to string, where each element from the list will be separated with comma, so you have to transform a list:
1111 2222 3333

to 1111, 2222, 3333 (it’s quite easy to do), and than pass the string “1111, 2222, 3333”, as an entry to adapter service.

Hope this help,
Kasper

4

You should be able to use pub.string:makeString to easily accomplish what Kasper recommended (ie. create a comma-delimited list of values.)

CAUTION: Most databases (if not all) limit the number of arguments you can pass within an IN clause, so be careful with this approach. Consider using BETWEEN, IN (SELECT…), or a join (if possible). If you can’t come up with a different approach, you will likely have to build some type of logic to limit the number of arguments you pass to the IN clause.

  • Percio
5

I am trying to do same thing. But it is accepting ‘1111’,‘2222’,‘3333’ like this only.
Is there anything to do , so that it can accept like “1111,2222,3333”

6

The proper sql statement is
select empname, empno from emp where empno IN (1111,2222,3333)

or

select empname, empno from emp where empno IN (‘1111’,‘2222’,‘3333’)

in the second case the values are strings.
When I wrote “1111,2222,3333” I though about first case.
If You will create a select like
select empname, empno from emp where empno IN (‘1111,2222,3333’)

then it means that row will be selected if empno equals ‘1111,2222,3333’
Hope that it helps,
Best regards,
Kasper