Skip to main content
  • EN

Hi,

i am trying to pass some values from a choice multiple into an sql executor as a published parameter ('$(Name)'). I can get it to work by using something like this but its not very efficient.

select Name

from Nametable

where

 '$(Name)' LIKE '%'+ columnName +'%'

 

i have done this manually: where columnName in (Andrew, David)

It works alot faster than the above but i cant get it to populate and run from the published parameter.

 

this wont work columnName =  '$(Name)' as it translates into

columnName = 'Andrew David' so there will never be a match as the name in column will be Andrew or David.

 

Any help or pointers would be appreciated.

 

Thanks,

 

Andrew

 

You probably need to do some manipulation of the parameter returned, so use a ParameterFetcher to get your parameter into an attribute then a string replacer to replace the space with a comma (if your choices have spaces in them it gets a bit more complicated) You can then use the attribute in the sqlexecutor

yeah i did think that but was trying to avoid adding transformers etc as i have quite a few parameters i want to pass in. Some of them have spaces in them so its a bit complicated. It would be a useful feature to be able to pass them in as a list directly from the published parameter.

 

Another option would be to create another (private) parameter for each list parameter you need to pass in, using the Python type and manipulate the space-separated string from the Published parameter into a comma-separated string you can reference in your SQL statements. Still more work than ideal, but at least there's no "extra" transformers 😉

 

I've suggested this as an idea for future improvement — visit https://community.safe.com/s/idea/0874Q000000j0RiQAI/detail to vote on it

@andrewj74​ 

 

Indeed use a scripted parameter to read the choice parameter.

You can then peel off the choice. I use tcl for that; of course regexp.

 

As @tomf​  suggests, though o leave the "In String" building to the workspace (the custom you see in the 2nd pic. Reason is this wokspace (built in 2016 en upgraded to 2018) is called (WSC) which seems to prevent the private parameter to execute.

Have not upgrade d it to latest verion.

 

And then acces it in the workspace.

 

Some pics from a workbench that applies this massively,

to illustrate the idea:

Choice pub parameter 

Private Para aquire choice from pub parameter 

Reply


Community Stats

30,163
Posts
114,500
Replies
37,814
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.