Uploaded image for project: 'Lucee Development'
  1. LDEV-224

QueryExecute throws error when specifying list of numerics

    Details

    • Type: Bug
    • Status: Deployed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 4.5.1.008, 4.5.1.000, 4.5.2.010, 5.0.0.81
    • Fix Version/s: 4.5.3.002
    • Labels:
      None

      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 )
      		"
      	);
      
      

        Attachments

          Issue links

            Activity

              People

              • Assignee:
                michaeloffner Michael Offner
                Reporter:
                SimonHooker Simon Hooker
              • Votes:
                11 Vote for this issue
                Watchers:
                12 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: