[4.2] - SQL query too long - Forum - Rollbase - Progress Community

[4.2] - SQL query too long

 Forum

[4.2] - SQL query too long

This question is not answered

Hello,

I have a rbf_selectQuery2() api call and its failing because the query is too long. The query is given below. How can I effectively get query length in order to:

1) Make sure that I get the query length less than the highest length and get the records returned from that

2) Make another query api call (or calls) to cover the remaining records till all the records have been fetched

3) Add these returned records to a global array and then use that as my kendo datasource in order to display it in a grid

Query:

// API call - selectQuery2()
        rbf_selectQuery2("SELECT city, name, email, mobilePhone, id FROM contact WHERE id IN (" + contactID + ")", 0, 10000, function contactValues_callback(returnedValues) {
...
});

contactID: contains the list of ID's in a comma separated manner: eg: '12345', '45678', '45666'. This can grow and have as many contact ID's as it can.

What is the best way to achieve this?

Cheers.

Iram

All Replies
  • 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 ,

    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 for the detailed answer, I shall try this out and get back to you.

    Cheers.

    Iram

  • Hello ,

    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 , if I have multiple queries (using deferred objects like provided an example of over here), how can I use your approach and optimize the results?

    Cheers.

    Iram