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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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
Status
Assignee
Michael Offner
Reporter
Simon Hooker
Fix versions
Affects versions
4.5.1.008
4.5.1.000
4.5.2.010
5.0.0.81
Priority
Major