QueryExecute throws error when specifying list of numerics

Description

There is a deviation between how QueryExecute and new Query() behave in that QueryExecute does not seem to handle lists of integers in the same way as Query.

The following code will error indicating that the supplied string cannot be cast to a number value

arrayOfNumbers = [ 1 , 2 , 3 , 4 , 5 ]; // This will fail with the following error // cannot cast [1,2,3,4,5] string to a number value queryExecute( params = { myNumbers: { value = ArrayToList( arrayOfNumbers , ',' ) , sqltype = 'integer' , list = true , separator = ',' } }, options = { datasource = application.config.datasource }, sql = " SELECT * FROM myTableWithIDsIn WHERE id IN ( :myNumbers ) " );

However the following example will operate as intended and use the list of numerics as a list of numerics

arrayOfNumbers = [ 1 , 2 , 3 , 4 , 5 ]; // This will work (assuming the table exists) var q = new Query( datasource = application.config.datasource ); q.addParam( name = 'myNumbers' , value = ArrayToList( arrayOfNumbers , ',' ) , sqltype = 'integer' , list = true , separator = ',' ); q.execute( sql = " SELECT * FROM myTableWithIDsIn WHERE id IN ( :myNumbers ) " );

Environment

None

Attachments

1

Activity

Pothys - MitrahSoft 
16 June 2017 at 05:42

I've added a testcase for this ticket, It's working fine on latest version (5.2.2.48).

Pull Request: https://github.com/lucee/Lucee/pull/249

Igal Sapir 
14 June 2017 at 17:21
(edited)

please add a testcase for this. we need to make sure that we don't break anything here when we work on https://luceeserver.atlassian.net/browse/LDEV-364#icft=LDEV-364 - thanks!

Former user 
25 September 2015 at 11:26

will chase up once he's back from holiday - whilst some of these I would be happy enough to merge, this one was a bit of a pig to fix so it really needs his approval before it's merged in unfortunately. I share your frustration on having to fall back to new Query().

tom chiverton 
25 September 2015 at 11:22

Can we get this into the 4.x release soon ?

I'm a little tired of months later still having to rewrite queryExecute() to new Query() just because we want to change the SQL from '=' to 'in'.

Fixed

Details

Assignee

Reporter

Priority

Fix versions

New Issue warning screen

Before you create a new Issue, please post to the mailing list first https://dev.lucee.org

Once the issue has been verified, one of the Lucee team will ask you to file an issue

Created 12 March 2015 at 10:24
Updated 16 June 2017 at 05:42
Resolved 7 December 2015 at 12:53