How to Implement Custom SQL using execSQL

, ,
1

I need to implement a custom sql statement to get a list of file names from the table. The SQL is
Select File_Name from table where file_id in ( ? )
Here I need to supply comma seperated value to the sql. I tried doing this with execSQL build-in-service, but its not working. Anybody done this before. Thanks in Advance

Muru.

2

I had some problems with this as well. The execSQL component “second guesses” you in the handling of a comma-delimited list, and places quotes around your list - so what is actually passed to the database is something like:
SELECT FILE_NAME FROM TABLE WHERE FILE_ID IN (“VALUE1,VALUE2,VALUE3,VALUE4,VALUE5”).
To get around it, I had to use the ‘$dbProcessReporterTokens’ parameter (set it to ‘true’).
Then, in your ‘$dbSQL’ parameter, use:
SELECT FILE_NAME
FROM TABLE
WHERE FILE_ID IN (%value commaDelimitedList%).
(You no longer need to set the $dbParam variables).
The documentation mentions a performance cost associatd with this method, but it hasn’t killed us yet.

Good luck,
Curtis

3

FYI - Oracle has a hard limit of 1000 expressions in a list, so you’ll have to check that you’re IN clause has less than 1000 items.
(Oracle error ORA-01795).