Passing 'text' variables values in SQL queries

Submitted by isha.d.gupta_1378267 on Sun, 02/03/2019 - 14:20

Hello,
I am trying to pass some variables in my SQL Server queries in the database connector.

I have two cases

SELECT * FROM DOCUMENT WHERE DOC_ID IN(${documentidnumber}) -- This works fine.
SELECT * FROM DOCUMENT_CODE WHERE CODE_ID IN(${doccode}) --- I have issues here.

This is the error - Declared return type class java.lang.Integer is not compatible with evaluated type class java.lang.String for expression doccode"

The variable 'doccode' contains comma separated (integer) values, ex. 6783, 3590, 5312. The data type of this column is text.

Is there a different way to pass text variables values in SQL Server?

I am on v7.2.3.

Thanks.

Comments

Submitted by Dibyajit.Roy on Sun, 02/03/2019 - 15:21

Hi Isha

this is how i execute a SQl Query.
getWBSTypes = SELECT WBSTYPE FROM IRCNETWORKWBS where PROJECTCATEGORY = :projectCategory group by WBSTYPE

The projectCategory is a parameter which i pass from the Page in the API call.

Permalink

1 answer

Submitted by claudia.avila_1... on Fri, 03/13/2020 - 21:59

I use Bonita 7.5.4 and I have the same problem in one Query!

For my case, it worked by adding after the variable the function .toString ();

Some like this:SELECT * FROM DOCUMENT_CODE WHERE CODE_ID IN(${doccode.toString()})

I hope it works for you or you have already solved it!