[4.2] - SQL query too long

the query max length is controlled by the shared property "MaxQueryLength". You will have to break the query into multiple calls when you hit the upper limit.

Hello [mention:1b78ce1c2c2a4204bd296c4a20853160:e9ed411860ed4f2ba0265705b8793d05] ,

Yes, I'm aware of that but could you please provide examples on how I can achieve that with the query mentioned above?

Cheers.

Iram

Hi Iram,

Can you do something like this? The following code will determine when to have multiple query calls based on ID count which is used as validation.Just for a test I gave 1500

var ids="24189,24190,24191";
var arrayOfIds = ids.split(',');
if(arrayOfIds.length<1500){
rbf_selectQuery2("SELECT name FROM ObjectA WHERE ID IN("+arrayOfIds+")", 0, 1000, function call(arr){
for(var i=0;i<arr.length;i++){
console.log(arr[i][0]);
}
});
}else {
for(var i=0;i<arrayOfIds.length;i++){
rbf_selectQuery2("SELECT name FROM ObjectA WHERE ID IN("+arrayOfIds[i]+")", 0, 1000, function call(arr){
for(var i=0;i<arr.length;i++){
console.log(arr[i][0]);
}
});
}
}

Regards,

Shiva

Hi Iram,

The solution which I gave earlier would work but its not a recommended approach because if we have 1000 id's then it will make 1000 query calls.

I have modified above code in such a way that original ids will be divided as per the limit that we give and it will process each array to get values from select query.

function sliceIdsIntoChunks(){

 var ids="1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,24189,24190,24191";

 var arrayOfIds=ids.split(",");

 var arrayOfarrays=[];

 var limit=1500;

 while(arrayOfIds.length>0){

   arrayOfarrays.push(arrayOfIds.splice(0, limit));

 }

 for(var i=0;i<arrayOfarrays.length;i++){

         calculatetListOfValuesFromQuery(arrayOfarrays[i]);

}

}

function calculatetListOfValuesFromQuery(arrayOfIds){

   for(var i=0;i<arrayOfIds.length;i++){

     rbf_selectQuery2("SELECT city, name, email, mobilePhone, id FROM Contact WHERE ID IN("+arrayOfIds[i]+")", 0, 1000, function call(arr){

      for(var i=0;i<arr.length;i++){

        console.log(arr[i][0]);

      }

     });

   }

 }

Please let me know if this works for you.

Regards,

Shiva

Thanks [mention:efd18cd3210243c3a2645cbe9409d974:e9ed411860ed4f2ba0265705b8793d05] for the detailed answer, I shall try this out and get back to you.

Cheers.

Iram

Hello [mention:efd18cd3210243c3a2645cbe9409d974:e9ed411860ed4f2ba0265705b8793d05] ,

I have been trying to achieve this solution and been facing issues with the SQL query length. I have successfully divided the query into multiple queries and my query length limit (set in the backend) is 2000 characters. When I try to make a selectQuery with just 1000 characters, it throws an error for SQL Query too long. I have checked this a lot of times and the query still fails. Any ideas on why the query length of 2000 characters is not being taken into account? The query works fine if I change the limit to 500 characters and then perform the selectQuery but I don't understand why it wouldn't use the 2000 character limit?

It also works for queries when the character limit is between 800 and 900 characters but as soon as I change the limit between 900 and 1000 characters, it throws the error for "SQL Query too long".

Cheers.

Iram

Also [mention:efd18cd3210243c3a2645cbe9409d974:e9ed411860ed4f2ba0265705b8793d05] , if I have multiple queries (using deferred objects like [mention:78c86023544844079dc6455a4a7a4d57:e9ed411860ed4f2ba0265705b8793d05] provided an example of over here), how can I use your approach and optimize the results?

Cheers.

Iram