Issue using IN SQL Statement and parameterization

[jtyme]

How to use parameterization and IN statements within Mirth.
I have the following code:

`
var idList = "'2', '3'";
var query = "SELECT * FROM TEST WHERE tableID IN (?)";
var param = new Lists.list().append(idList);

var dbConn = createDBConn('testdb');
var results = dbConn.executeCachedQuery(query, param)
`
I get 0 results. but if I run the SELECT * FROM TEST WHERE tableID IN ('2', '3') in SQL i get 2 results.

I have also tried using idList = new Lists.list().append('2').append('3') and many other combinations with no luck. If I remove the ? and hard code the values I get the expected 2 results. I am sure I am totally missing something just not sure what that is.

[jonbartels]

I do not know the answer but I think what you want to search for and learn about are:

1. What does JDBC want for a parameter class when passing a list of things as a parameter? Is it List or Array?
2. The MC method wants a List of Parameters. executeCachedQuery(String expression, List<Object> parameters) So your first ? is the first parameter.

I think you need a List where the first element is also a List which contains your values.

var idList = new List()
idList.add('2')
idList.add('3')
var query = "SELECT * FROM TEST WHERE tableID IN (?)";
var param = new List()
param.add(idList);

var dbConn = createDBConn('testdb');
var results = dbConn.executeCachedQuery(query, param)

^ This code is an idea. It is probably not exactly the right code!! ^ Search here on github or https://forums.mirthproject.io/ for more complete examples

[pacmano1]

While this is FAR from a typical use case I might present the below, I have found working with JSON blobs throughout channels to be easier. Postgres here

var sql = <>
SELECT * FROM services WHERE ?::jsonb @&gt; to_jsonb(id)
</>.toString();
// Above is really  `SELECT * FROM services WHERE ?::jsonb @> to_jsonb(id)`
var stuff = [1, 2]
var params = new java.util.ArrayList();
params.add(JSON.stringify(stuff))
var result = executeSharedDBStatement(sql,true,params). // this is just a dbconn call
return result

This technique gets very handy for stuff like:

var problems_sql = <>
	INSERT INTO public.ccd_problems (ccd_id, description, status, type, started, stopped, snomed, icd, code_system, code)
	SELECT data.ccd_id,
	       data.description,
	       data.status,
	       data.type,
	       data.started,
	       data.stopped,
	       data.snomed,
	       data.icd,
	       data.code_system,
	       data.code
	FROM jsonb_populate_recordset(null::public.ccd_problems, ?::jsonb) as data
	</>.toString();
$c('problems_sql', problems_sql)

and later on in the channel building a JSON array of those fields (in this case the data.ccd_id will be sourced and substituted.

params.add(JSON.stringify(updateCcdId($c('problems'),$c('ccdID')))).  // the function there fixes up the ccd id
result = executeSharedDBStatement($c('problems_sql'), false, params);

[jtyme]

I like this approach and yes we are looking at moving to using JSON in the DB for column storage.

[pacmano1]

You don't need JSON in the DB - just working with JSON in mirth here - the tables are normal column based.

[rjkroll]

The IN clause expects a list or array of values, but you're passing a single string containing comma-separated values.

var idList = ['2', '3']; 
var query = "SELECT * FROM TEST WHERE tableID IN (?)";
var param = new Lists.list(idList); 

var dbConn = createDBConn('testdb');
var results = dbConn.executeCachedQuery(query, param);

or something like this:

var ids = ['2', '3', '4', '5'];
var idList = new Lists.list(ids);
var query = "SELECT * FROM TEST WHERE tableID IN (?)";
var param = idList;

var dbConn = createDBConn('testdb');
var results = dbConn.executeCachedQuery(query, param);

[jtyme]

Thanks the information that the IN clause is using an array list is the information I did not know or was even trying. I was just trying to pass in a comma separate string.