I would appreciate help to pass a multi-valued delimited HTML parameter across to an SQL query.
This is the full manual SQL query in the data pipe, for the original version with a single-valued parameter:
SELECT DISTINCT
VACANCIES_IN_FOLDER_V.CONTENTSTARTDATE ,
VACANCIES_IN_FOLDER_V.CONTENTID ,
VACANCIES_IN_FOLDER_V.VARIANTID ,
VACANCIES_IN_FOLDER_V.DISPLAYTITLE ,
VACANCIES_IN_FOLDER_V.VACANCY_CLOSINGDATE ,
VACANCIES_IN_FOLDER_V.CURRENTREVISION ,
VACANCIES_IN_FOLDER_V.ASSEMBLYURL
FROM VACANCIES_IN_FOLDER_V
WHERE
VACANCIES_IN_FOLDER_V.VARIANTID = :“PSXParam/idx_variantid” AND
VACANCIES_IN_FOLDER_V.VACANCY_BAND = ‘:“PSXParam/idx_band”’
ORDER BY
VACANCIES_IN_FOLDER_V.CONTENTSTARTDATE DESC
The parameter I want to make multi-valued is idx_band (salary band), data type string, so that my WHERE clause could be similar to:
VACANCIES_IN_FOLDER_V.VACANCY_BAND IN (’:“PSXParam/idx_band”’)
Of course, this won’t work straight off, because the HTML parameter will be passed through as if it is a single value , e.g. ‘4,4T,IT’, whereas I need it to be B.[/B]
I have tried reformatting the query to use the Rx WHERE table instead of manual SQL, so that I can use the Rx function IN-TEXT, but have not got this to work - possibly through issues with syntax.
Another possible solution would use more complex SQL to convert the delimited list. But I think that’s DBMS-specific (and more to the point, hard!), so if there’s a best practice Rx way I’d much rather use it.
I’m now using generic/sys_PrepareInClause to transform the single HTML parameter (see attachment):
My WHERE clause in the Selector is as follows - using the replacement parameter:
WHERE
BOE_VACANCIES_IN_FOLDER_V.VARIANTID = :“PSXParam/idx_variantid” AND
BOE_VACANCIES_IN_FOLDER_V.VACANCY_BAND IN (:“PSXParam/roztestband”)
Everything still works OK if I use a single value for idx_band, for instance in this query URL.:
/Rhythmyx/boe_VacancyIndex_support/vacancies_by_band.txt?idx_variantid=456&idx_band=IT%20Specialist
But it still fails when using a multivalued parameter, e.g.:
/Rhythmyx/boe_VacancyIndex_support/vacancies_by_band.txt?idx_variantid=456&idx_band=2,3,IT%20Specialist
Tracing shows that the parameters are as follows:
07/18 12:07:04.277 HTML/CGI post PreProc exit
HTML Parameters:
idx_variantid = 456
roztestband = ‘2,3,IT Specialist’
idx_band = 2,3,IT Specialist
The WHERE clause actually going to the DB is:
WHERE
BOE_VACANCIES_IN_FOLDER_V.VARIANTID = 456 AND
BOE_VACANCIES_IN_FOLDER_V.VACANCY_BAND IN (‘2,3,IT Specialist’)
I may be failing to understand the purpose of the exit, or having syntax problems, but I would have expected (‘2’,‘3’,‘IT Specialist’). I’ve tried setting the exit’s encloseInQuotes parameter to 0 and shifting the quotes back to the WHERE clause, but the result is the same. An example would be most handy.
That exit is designed to handle “Multi-Value” parameters, not parameters that
have a single value. These are represented in Rhythmyx as a List of Strings rather than a delimited string.
Rhythmyx will convert parameters that occur multiple times (either in the query string or in a POSTed body) into a list.