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

[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]

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

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]

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

[Helmy Ibrahim _Adaptavist_]

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

[Joaquin Fernández Morales]

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]

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.