nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Adaptavist Scriptrunner Database Picker Searching on Multiple Attributes

Mark Williams_xkjqkp

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!

Accepted answers

Mark Williams_xkjqkp

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?

All comments

Mark Williams_xkjqkp

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

Mark Williams_xkjqkp

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

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

server

jira

jira-server