Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,366,371
Community Members
 
Community Events
168
Community Groups

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

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

2 answers

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

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

image.png

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

image.png

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

image.pngimage.png

Hi Suresh,

I understand you are using Microsoft SQL Server. To concatenate strings, you would have to use the concat keyword.

Please can you try the following?

Retrieval/validator SQL

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

Search SQL

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

You should use LIKE operator instead of EQUAL(=) operator. For the retrieval SQL, if the ID column is alphanumeric, you don't have to cast it.

Cheers,
Helmy

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. 

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%'

image.png

@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.

Suggest an answer

Log in or Sign up to answer