Adaptavist Scriptrunner Database Picker Searching on Multiple Attributes

[Mark Williams]

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!

[Mark Williams · Accepted solution]

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]

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(?), '%')

[Suresh Srini]

@Mark Williams 

Can you help with this error?

https://community.atlassian.com/t5/Jira-questions/Script-runner-database-picker-field-error-The-SQL-statement/qaq-p/2140133#M550210

[Craig Castle-Mead]

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