Adaptavist Scriptrunner Database Picker Searching on Multiple Attributes

Mark Williams June 15, 2021

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

2 votes
Answer accepted
Mark Williams June 15, 2021

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?

Mark Williams June 15, 2021

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 # people like this
Craig Castle-Mead May 15, 2023

Thanks @Mark Williams ! Just helped me solve a problem I'd been struggling with for an hour and trying so many combinations. I'd done a CONCAT(field1,field2) on the left of the LIKE, but the concat on the right side of the LIKE was what I was missing

 

CCM

Suggest an answer

Log in or Sign up to answer