How to pass a dynamic list to ESQL Passthru query in IIB

Question

I need to pass a dynamic list of parameters to SQL query in IIB. Please help me with any code snippets for this. Please note that we dont have privileges to create a Stored Procedure at DB end. So we need to pass the list of parameters to SQL query directly from IIB.

Scenario:

SET A= InputRoot.XMLNSC.Field1; SET B= InputRoot.XMLNSC.Field2;

The number of above fields may vary dynamically.

SET query = select * from table where values in (?) SET OutputRoot.XMLNSC.Result[] =Passthru (query To Database.DB) values (list)

score 0 · Answer 1 · answered Sep 06 '19 at 18:11

Below is pseudocode. Assuming that you have a array of [LIST OF VALUES]. Try it out.

DECLARE PARM_1 CHAR '';
DECLARE QUERY CHAR;
FOR CURR AS {LIST OF VALUES} DO
   SET PARM_1 = Write code to get list of values in the format => 'A','B','C'
END FOR;

--Comment: PARM_1 value now should be 'A','B','C'
--Comment : Below forming the query string and then use EVAL 
SET QUERY = 'SELECT * FROM TABLE WHERE VALUE IN (' || PARM_1 || ')';
SET Environment.RESULT[] = EVAL(QUERY);

How to pass a dynamic list to ESQL Passthru query in IIB

1 Answers1