topic Re: eSearch SQL query issue in Web AppBuilder Custom Widgets Questions

eSearch SQL query issue

EricMcAvoy — Thu, 24 Aug 2017 17:41:22 GMT

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.

Re: eSearch SQL query issue

RobertScheitlin__GISP — Sun, 12 Dec 2021 10:30:21 GMT

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.

Re: eSearch SQL query issue

EricMcAvoy — Thu, 24 Aug 2017 19:35:16 GMT

Robert,

It worked!!

Thank You So Much!

Re: eSearch SQL query issue

RobertScheitlin__GISP — Thu, 24 Aug 2017 19:38:43 GMT

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.

Re: eSearch SQL query issue

EricMcAvoy — Thu, 24 Aug 2017 19:44:35 GMT

Yeah, it is super weird. Thanks again!

Re: eSearch SQL query issue

Keith-Bornhorst — Fri, 13 Oct 2017 17:10:38 GMT

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!

Re: eSearch SQL query issue

RobertScheitlin__GISP — Fri, 13 Oct 2017 23:42:46 GMT

Keith,

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

Re: eSearch SQL query issue

Keith-Bornhorst — Fri, 17 Nov 2017 14:39:27 GMT

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;
}

Re: eSearch SQL query issue

RobertScheitlin__GISP — Fri, 17 Nov 2017 14:45:59 GMT

Keith,

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

Re: eSearch SQL query issue

RobertScheitlin__GISP — Fri, 17 Nov 2017 15:12:47 GMT

Version 2.6 is now released with this fix.