update query not working using IN operator

[manojkvn]

Hi Mirth Experts,

i am storing a array of ids in channel map in source connector transformer step, and in destination connector i am using database writer with javascript mode, and i am passing the ids to update query, but i am getting below error when running the update query.

adding array of ids into channel map

var userIdsList = [];

while(i<length)
{
userIdsList.push(new java.lang.Integer(txt_userid));
}
channelMap.put('userIdsCM', userIdsList);

update query with in operator

	var updateQuery= "UPDATE chartAudit_ext_ SET txt_auditNote = 'STARTED' WHERE txt_userID in '"+$('userIdsCM')+"'";

	result = dbConn.executeUpdate(updateQuery);

Error:

ERROR  (com.mirth.connect.connectors.jdbc.DatabaseDispatcherScript:234): Error evaluating Database Writer (Database Writer "update records processing started" on channel 30371e1a-4089-4105-a7ad-9ed4365e8f6c).
com.mirth.connect.server.MirthJavascriptTransformerException: CHANNEL:
	Nextgen-db-reader-testCONNECTOR:
	update records processing startedSOURCE CODE:
	106: 107: 
	logger.info('userIds from channel Map'+$('userIdsCM'));108: 109: 
	var updateQuery= "UPDATE NGTest01.dbo.VWH_chartAudit_ext_ SET txt_auditNote = 'STARTED' WHERE txt_userID in '"+$('userIdsCM')+"'";110: 111: 
	result = dbConn.executeUpdate(updateQuery);112: } finally {113: 
	if (dbConn) { 114: 
	
	dbConn.close();115: 
	}LINE NUMBER:
	111DETAILS:
	Wrapped com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '108,245'.
	at 30371e1a-4089-4105-a7ad-9ed4365e8f6c_Database_Writer_6:111 (doScript)
	at 30371e1a-4089-4105-a7ad-9ed4365e8f6c_Database_Writer_6:118
	at com.mirth.connect.server.util.javascript.JavaScriptUtil.executeScript(JavaScriptUtil.java:620)
	at com.mirth.connect.connectors.jdbc.DatabaseDispatcherScript$DatabaseDispatcherTask.doCall(DatabaseDispatcherScript.java:202)
	at com.mirth.connect.server.util.javascript.JavaScriptTask.call(JavaScriptTask.java:114)
	at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
	at java.base/java.lang.Thread.run(Thread.java:833)Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '108,245'.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1632)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:872)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:767)
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7375)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3206)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:247)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:222)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.execute(SQLServerStatement.java:743)
	at com.mirth.connect.server.userutil.DatabaseConnection.executeUpdate(DatabaseConnection.java:156)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	

please let me know the right way of passing array of elements to update query.
using mirth connect 4.4.2
OS: mac os sonama 14.4.1

[pacmano1]

A WHERE IN needs parentheses around values.

...WHERE ColumnName IN ('Value1', 'Value2', 'Value3');  -- or a list of other data types of course.

You should also be using parameterized calls to the database.

And this way of calling SQL (credit to @tonygermano for showing me this a long time ago) can be a bit easier to read.

var params = new java.util.ArrayList();
var list_of_ids = [1, 2, 3];  // List of IDs
var placeholders = list_of_ids.map(function() { return '?'; }).join(', ');
var sql = 'UPDATE junk SET test = \'a\' WHERE id IN (' + placeholders + ')';
list_of_ids.forEach(function(id) {
      params.add(id);
  });
dbConn.executeUpdate(sql, params);

There can be times where you may need to explicitly cast data types. Other database engines make the above insert quite a bit easier due to their native array handling capabilities. The point though of that code was another way to handle building the SQL rather than the SQL itself.

I hardcoded the value "a", but that should be parameterized also.

[manojkvn]

thanks @pacmano1 it works and great explanation

[tonygermano]

This can actually be made more concise in current mirth versions.

var list_of_ids = [1, 2, 3];  // List of IDs
var placeholders = new Array(list_of_ids.length).fill('?').join(',')
var sql = 'UPDATE junk SET test = ? WHERE id IN (' + placeholders + ')';
var params = ['a'].concat(list_of_ids);
dbConn.executeUpdate(sql, params);

Array.prototype.fill used to be missing in mirth. I know it's not in 3.4, but it is in 3.11. I'm not sure exactly when in between it was added.

You should also be able to pass a javascript array as the params. You can initalize it with the non-list values, and then concat the list to the end. Concat returns a new array rather than modifying the one upon which it was called. In this case the new array will contain values ["a", 1, 2, 3].

To do the same thing with an ArrayList you could do this and avoid the forEach loop.

var params = new java.util.ArrayList(['a'])
params.addAll(list_of_ids)