-
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
update query with in operator
Error:
please let me know the right way of passing array of elements to update query. |
Beta Was this translation helpful? Give feedback.
Replies: 1 comment 2 replies
-
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. |
Beta Was this translation helpful? Give feedback.
A WHERE IN needs parentheses around values.
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.
There can be times where you may need …