Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Adaptavist Scriptrunner Database Picker Searching on Multiple Attributes

Hi,

I have been trying to use the Database Picker from Adaptavist to search on multiple attributes of a simple database.

The aim is to have a Database Picker field where users can start to enter part numbers or part descriptions and have the results filter to a single selection.

I have tested this searching on one attribute, say part number and works ok. It is connecting to a Microsoft SQL Server.

If I follow the examples to search on two attributes I get an error message on validation saying "The SQL statement should have exactly one parameter marker". But with this approach there will be more than one ? marker.

Search SQL is:

import com.onresolve.scriptrunner.canned.jira.fields.editable.database.SqlWithParameters

getSearchSql = { inputValue ->
new SqlWithParameters("select PartNumber, PartDescription from PartDetails where PartNumber like concat(lower(?), '%') or PartDescription like concat(lower(?), '%')", [inputValue, inputValue])
}

 Could anyone offer any support on what could be wrong with this and why I get the error mentioned above?

I would also like to be able to do a true wildcard search allowing text from mid-description to filter the results. The approach at the moment requires the search to start with the entered characters. I.e. typing in "wire" would return "equipment wire black" in the results, not just results starting with "wire".

Thanks in advance!

1 answer

1 accepted

Ok I sussed out how to do the partial search easy enough. I.e. like below



select PartNumber, PartDescription from PartDetails
where lower(PartNumber) like concat('%', lower(?), '%')

But this is just for a single attribute. I would still like it to search on PartNumber and PartDescription but if I have a statement with two ? then I get the error about only permitting one parameter marker?

Ok, I have worked out how to do this ignoring the examples in the Adaptavist documentation for Database Picker. I have read over the examples several times but really it was more of a SQL creation issue. Their examples do show two ? in a single statement which seems to be rejected when I try.

However it would appear the following does what I need:

select PartNumber, PartDescription from PartDetails
where concat(lower(PartNumber), lower(PartDescription)) like concat('%', lower(?), '%')
Like Vikrant Yadav likes this

Suggest an answer

Log in or Sign up to answer
TAGS

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you