Locator problems since upgrading to 6.2x?

[Devan]

Hey all,

I have a web project that has been under development for some months now.  There are a few browses with Locators on them using 'Contains', and they were working fine for the client previously.

Recently I recompiled the app using the latest templates (6.19 and now 6.24) and the client has let me know that the Locators now no longer work.  No matter what they type in the Search box, it comes up with 'No records found'.  I've tested here and reproduced it.  I've changed the locator Type to 'Starts-With' or 'Search' and still no results are coming up.

We are using an MS-SQL back end - any reason why the Locators would suddenly stop working?  Anyone else seen an issue?

Cheers,
Devan

[Bruce]

nothing springs to mind, but try;

a) see if you can duplicate in the Locators example.
if not
b) put a p_web._trace statement in, just before the LOOP in that browse, and have a look at the filter. ie
p_web._trace('filter: ' & thisview{prop:filter})

I'm guessing the filter is constructed wrong, but I'd need to see what's in it to be more specific.

the locator did change a few builds back, with the addition of the SEARCH type - so it's possible somethingis bust for SQL specifically.

cheers
Bruce

[Devan]

Ok, I slapped a trace on it as requested, and I think the problem here is that there is a default query in place already using the SQL() qualifier, but the query appended onto the end when I select a locator, is NOT wrapped in SQL(), so I am not sure if that is causing the SQL back end to fail?

Original Query:

Code: [Select]

[10396] [netTrace][thread=3] Filter: (SQL((PermitReqd=0 OR Code IN (SELECT ProductCode FROM DBRestrictedProducts WHERE CRMGUID='0CAD3FF0-C710-DD11-ADEF-0013206363F7')) And (Location='Q')))

When locator is applied:

Code: [Select]

[10396] [netTrace][thread=3] Filter: (SQL((PermitReqd=0 OR Code IN (SELECT ProductCode FROM DBRestrictedProducts WHERE CRMGUID='0CAD3FF0-C710-DD11-ADEF-0013206363F7')) And (Location='Q'))) AND ( INSTRING('BAY',UPPER(VPR:Description),1,1) > 0)

I am assuming because INSTRING() is not a valid SQL function, it is failing when getting passed to the back end?

Cheers,
Devan

[Bruce]

Instring is ok in a filter, the driver converts it to SQL.
And it should be ok to mix the sql, and non-sql parts.

cheers
Bruce

[Devan]

Okay, this is REALLY weird!

I've been cracking my head against this for hours now.  Ended up MANUALLY recreating that filter each time to see what was happening.

I've found that if I append the following to the end of the filter:

( INSTRING('BAY',UPPER(VPR:Description),1,1) > 0)

Then it fails, and does not return any records, HOWEVER, if I change it slightly to:

( INSTRING('BAY',UPPER(VPR:Description),1,1) <> 0)

Then it works perfectly.  Just changed the '>' (greater than) to '<>' (not equals).  I even put an extra field in the browse with the value 'INSTRING('BAY',UPPER(VPR:Description),1,1)' and it is returning 1, 6, 9 etc. as it should.

SO - the question is: LOGICALLY it should work because if the returned value is '1' then '>0' is the same as '<>0' ?!?!  Yet changing this ONE character is the difference between the query working or failing.

Is this a Clarion or MS-SQL driver bug???

Cheers,
Devan

[Bruce]

I've no idea Devan, but I guess it's an easy enough change for me to make, and I can't see that it does any harm. When I'm back in the office I'll tweak netweb.clw with this change.

cheers
Bruce

[linas@debetas.lt]

I can join here

After each nettalk update I tweak netweb.clw  as Deva described ssituation.

Linas

[Bruce]

done for the next build.