eSearch SQL query issue

1476
9
Jump to solution
08-24-2017 10:41 AM
EricMcAvoy
New Contributor III

Good morning!

 

I am starting a new thread here at the request of Robert Scheitlin, GISP to attempt to solve this issue.

 

I'm having a weird issue and I'm hoping you might be able to help me out. We've been using the eSearch for more than a year now and it has worked flawlessly the entire time. Our 2.4 Web App is using eSearch Version 2.3. Early last week, the Search by Value ceased working as it had and started returning no results. This search for Survey numbers containing "CS1" should return hundreds of results.

The query it sends to the service should be

Upper(DocumentNa) LIKE Upper('%cs1%')

but instead it is sending the entire query regardless of the entered parameters. 

Upper(DocumentNa) LIKE Upper('%cs1%') AND Upper(SurveyorKe) LIKE Upper('%%') AND Upper(Subdivisio) LIKE Upper('%%') AND Upper(ClerkNumbe) LIKE Upper('%%')

The issue exists in IE, Firefox, and Chrome versions 59 and 60. No errors show in the DEV Console.

0 Kudos
1 Solution

Accepted Solutions
RobertScheitlin__GISP
MVP Emeritus

Eric,

  In the eSearch Widget folder open the Widget.js and find the buildWhereClause function and add lines 38-40 as shown below:

      buildWhereClause: function (layerIndex, expressIndex, /* optional */ theValue) {
        var myPattern = /\[value\]/g;
        var myPattern1 = /\[value1\]/g;
        var myPattern2 = /\[value2\]/g;
        var myPattern3 = /\[value\]/;
        var expr = "";
        var eVal;
        var eVal1;
        var eVal2;
        var criteriaFromValue;
        var content = theValue || this.paramsDijit.getSingleParamValues();
        if (!content || content.length === 0 || !this.config.layers.length) {
          return;
        }
        //loop though the SPs and assemble the where clause
        for (var s = 0; s < content.length; s++) {
          var tOperator = (this.config.layers[layerIndex].expressions.expression[expressIndex].values.value[s] &&
            typeof this.config.layers[layerIndex].expressions.expression[expressIndex].values.value[s].operator !== 'undefined') ? this.config.layers[layerIndex].expressions.expression[expressIndex].values.value[s].operator : 'OR';
          var tOperation = this.config.layers[layerIndex].expressions.expression[expressIndex].values.value[s].operation;
          var queryExpr = this.config.layers[layerIndex].expressions.expression[expressIndex].values.value[s].sqltext;
          if (!content[s].hasOwnProperty('value') || content[s].value === null) {
            if(!content[s].hasOwnProperty('value1') || content[s].value1 === null){
              continue;
            }
            if (content[s].value1.toString() !== "NaN" && content[s].value2.toString() !== "NaN") {
              eVal1 = content[s].value1.toString();
              eVal2 = content[s].value2.toString();
              criteriaFromValue = queryExpr.replace(myPattern1, eVal1);
              criteriaFromValue = criteriaFromValue.replace(myPattern2, eVal2);
              expr = this.AppendTo(expr, criteriaFromValue, tOperator);
              continue;
            } else {
              continue;
            }
          }

          if (tOperation === 'stringOperatorContains') {
            if(content[s].value.toString() === ""){
              continue;
            }
            var sa = content[s].value.toString().split(" "), word;
            for(w=0; w < sa.length; w++){
              word = sa[w];
              criteriaFromValue = queryExpr.replace(myPattern, word);
              expr = this.AppendTo(expr, criteriaFromValue, "AND");
            }
            continue;
          }

And let me know if this solves your issue.

View solution in original post

9 Replies
RobertScheitlin__GISP
MVP Emeritus

Eric,

  In the eSearch Widget folder open the Widget.js and find the buildWhereClause function and add lines 38-40 as shown below:

      buildWhereClause: function (layerIndex, expressIndex, /* optional */ theValue) {
        var myPattern = /\[value\]/g;
        var myPattern1 = /\[value1\]/g;
        var myPattern2 = /\[value2\]/g;
        var myPattern3 = /\[value\]/;
        var expr = "";
        var eVal;
        var eVal1;
        var eVal2;
        var criteriaFromValue;
        var content = theValue || this.paramsDijit.getSingleParamValues();
        if (!content || content.length === 0 || !this.config.layers.length) {
          return;
        }
        //loop though the SPs and assemble the where clause
        for (var s = 0; s < content.length; s++) {
          var tOperator = (this.config.layers[layerIndex].expressions.expression[expressIndex].values.value[s] &&
            typeof this.config.layers[layerIndex].expressions.expression[expressIndex].values.value[s].operator !== 'undefined') ? this.config.layers[layerIndex].expressions.expression[expressIndex].values.value[s].operator : 'OR';
          var tOperation = this.config.layers[layerIndex].expressions.expression[expressIndex].values.value[s].operation;
          var queryExpr = this.config.layers[layerIndex].expressions.expression[expressIndex].values.value[s].sqltext;
          if (!content[s].hasOwnProperty('value') || content[s].value === null) {
            if(!content[s].hasOwnProperty('value1') || content[s].value1 === null){
              continue;
            }
            if (content[s].value1.toString() !== "NaN" && content[s].value2.toString() !== "NaN") {
              eVal1 = content[s].value1.toString();
              eVal2 = content[s].value2.toString();
              criteriaFromValue = queryExpr.replace(myPattern1, eVal1);
              criteriaFromValue = criteriaFromValue.replace(myPattern2, eVal2);
              expr = this.AppendTo(expr, criteriaFromValue, tOperator);
              continue;
            } else {
              continue;
            }
          }

          if (tOperation === 'stringOperatorContains') {
            if(content[s].value.toString() === ""){
              continue;
            }
            var sa = content[s].value.toString().split(" "), word;
            for(w=0; w < sa.length; w++){
              word = sa[w];
              criteriaFromValue = queryExpr.replace(myPattern, word);
              expr = this.AppendTo(expr, criteriaFromValue, "AND");
            }
            continue;
          }

And let me know if this solves your issue.

EricMcAvoy
New Contributor III

Robert,

It worked!!

Thank You So Much!

0 Kudos
RobertScheitlin__GISP
MVP Emeritus

It's just strange that you did not see this issue before or anyone else for that matter. I will get this added to the next release.

EricMcAvoy
New Contributor III

Yeah, it is super weird. Thanks again!

0 Kudos
Keith-Bornhorst
New Contributor III

Hey, Robert, just implemented the code you posted which helped us fix an issue with a big AND query, but it also highlights a hard-coded "AND" in line 45

expr = this.AppendTo(expr, criteriaFromValue, "AND");

I replaced the "AND" with tOperator and or OR queries are working.

As always, thanks for all your time developing eSearch!

RobertScheitlin__GISP
MVP Emeritus

Keith,

   I now have this in the next release of the eSearch thanks for the help.

Keith-Bornhorst
New Contributor III

Just a friendly nudge; this didn't make it into 2.5.

In addition, I'd consider adding an initial check for empty strings at the beginning of that same IF block so the resulting query 

if (tOperation === 'stringOperatorContains') {

 

if (content.value.toString() === "") {
continue; // don't add empty fields to the search query
}


var sa = content.value.toString().split(" "), word;
for(w=0; w < sa.length; w++){
word = sa;
criteriaFromValue = queryExpr.replace(myPattern, word);
expr = this.AppendTo(expr, criteriaFromValue, tOperator);   // replaced "AND" with tOperator
}
continue;
}

0 Kudos
RobertScheitlin__GISP
MVP Emeritus

Keith,

  I have not made a new release in the last month. It will be in the 2.6 version.

RobertScheitlin__GISP
MVP Emeritus

Version 2.6 is now released with this fix.

0 Kudos