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; } } }

Script runner database picker field error 'The SQL statement should have exactly one parameter mark'

Suresh Srini

Hi,

I am trying to learn and use Script runner database picker field. I tried the query and used unique field for filtering and tried with some conditions as well.

But everytime I get an error "The SQL statement should have exactly one parameter marker" as in the picture. Any help is appreciated.

Database connection working fine for Elements connect section and trying to use the same database and table here.

Tried both query individually but got same error.

select CustomerID, FirstName from tblcustomer where lower(CustomerID) like '2%'

SELECT CustomerID, FirstName from tblcustomer

ScriptRunner Error-Sep-22.JPG

Accepted answers

All comments

Joaquin Fernández Morales_jn43zj

Hi Suresh Srini ,

The error message indicates that both queries , Retrieval/validation SQL and Search SQL are expecting a single parameter "marker" in the query which is represented with "?". To help you , you can check one example here:

https://docs.adaptavist.com/sr4js/latest/features/script-fields/built-in-script-fields/database-picker/database-picker-examples/database-picker-country-picker

I hope it helps.

Suresh Srini_zpj1li

Hi @Joaquin Fernández Morales

It is. Thank you.

Partially done. however 2nd Query is not seems to be accepted. This is my temporary tables and I have data. AgencyORI is "char" field type.

SELECT AgencyORI,AgencyName FROM AgencyLookup where AgencyORI =?

SELECT AgencyORI,AgencyName FROM AgencyLookup where lower(AgencyORI) like 'mn%'

Suresh Srini

@Joaquin Fernández MoralesI am able to have the 2nd query also accepted without any error. However the results are not showing up.

I created table with simple ID & name. Query accepted but no results popup with I type few letters of ID or Name.

select CustomerID,LastName from tblCustomer where CustomerID = cast(? as numeric)

select CustomerID,LastName from tblCustomer where lower(CustomerID) = concat(lower(?), '%')

Any help would be much appreciated.

Helmy Ibrahim _Adaptavist_

Hi @Suresh Srini

I am responding to your latest comment:

I created table with simple ID & name. Query accepted but no results popup with I type few letters of ID or Name.
select CustomerID,LastName from tblCustomer where CustomerID = cast(? as numeric)
select CustomerID,LastName from tblCustomer where lower(CustomerID) = concat(lower(?), '%')

For the Search SQL, you are searching against the 'CustomerID', not 'LastName' and the format seems different than the ones in our documentation.

Can you try changing it to:

select CustomerID,LastName from tblCustomer where lower(LastName) like lower(?) || '%'

If that doesn't help, may I know what database are you using?

Cheers,
Helmy

Suresh Srini_zpj1li

@Helmy Ibrahim _Adaptavist_

Hi,

2nd parameter is not accepting "||"

select CustomerID,LastName from tblCustomer where LastName = ?

select CustomerID,LastName from tblCustomer where lower(LastName) like lower(?) || '%'

Suresh Srini_zpj1li

SQL Info:

SQL Server Management Studio 15.0.18142.0
Microsoft Analysis Services Client Tools 15.0.1389.0
Microsoft Data Access Components (MDAC) 10.0.19041.2006
Microsoft MSXML 3.0 6.0
Microsoft Internet Explorer 9.11.19041.0
Microsoft .NET Framework 4.0.30319.42000
Operating System 10.0.19042

Suresh Srini_zpj1li

I was trying other table as well which had 'char' for PK column

Welcome!

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

Quick Links

Find more posts tagged with

server

scriptrunner

jira

adaptavist

jira-server