Locators stopped working on my project in 7.17

[Devan]

Hey all,

Just upgraded to 7.17 today and began work on an old NT6 project.

Got most of the other update issues out of the way, but for some strange reason, the Locator on my main NetWebBrowse just refuses to work.  Worked fine on NT6, but in 7 whenever I type in anything into the Locator field, it returns zero records.

The browse is only looking at about 7 columns from an MSSQL table that has about a thousand rows.

No matter which column I click to locate, it doesn't matter if I type in 1 character or the entire name in perfect case - no rows show up at all.

Any idea what could be causing this?

Thanks,
Devan

[Bruce]

best way to debug it;
a) turn on "send Order/Filter to Debugview" for the browse
b) run the app and type something into the locator
c) cut & paste the filter values here.

Are the SQL fields Strings or Cstrings?

cheers
Bruce

[Devan]

Hey Bruce,

Tested as you requested.  The SQL fields are in my dictionary as STRING fields.

With NO Locator, the filter string is as thus:

[15504] [netTalk][thread=3] BrowseVWProductMaster :: Filter: SQL((PermitReqd=0 OR Code IN (SELECT ProductCode FROM DBRestrictedProducts WHERE CRMGUID='6BFEB8D2-8A10-DF11-8DF5-00215AAC8BCE')) And (Location='B'))


When I enter 'test' into the locator:

[15504] [netTalk][thread=3] BrowseVWProductMaster :: Filter: (SQL((PermitReqd=0 OR Code IN (SELECT ProductCode FROM DBRestrictedProducts WHERE CRMGUID='6BFEB8D2-8A10-DF11-8DF5-00215AAC8BCE')) And (Location='B'))) AND ( INSTRING('TEST',CLIP(UPPER(VPR:Description)),1,1) <> 0)


I seem to remember there was an issue with the test for INSTRING() returning zero for SQL databases in the past??

Cheers,
Devan

[Bruce]

ok, probably the next step to test is to isolate the Locator part of the filter.
Your filter appears to be in 3 parts -
a) the SQL() part
b) the Location='B' part and
c) the locator part.

So start by removing the part (a) and (b) and see if the locator then works "as is". If it does put back b and test again, and then a and test again.

[Devan]

Thanks for the reply Bruce,

I removed the original filter, and the locator began working as it should have.  (Incidentally, the Location="B" bit is part of the whole SQL() filter).

What confuses me is the SQL() filter by itself works fine and filters the records as they should be.  It seems that when we add ' AND ' and a normal Clarion filter that it fails? 

Weird that this browse worked fine in NT6, but since the upgrade to 7 it is failing.  Are there any issues with combining an SQL and Clarion filter?  I didn't think there would be?

EDIT: Just did some more testing, and looks like there IS a problem with combining SQL and non SQL statements on the same line where the non SQL portion returns a numeric result?  I entered this filter in the Browse properties:

'(SQL((PermitReqd=0 OR Code IN (SELECT ProductCode FROM DBRestrictedProducts WHERE CRMGUID=''6BFEB8D2-8A10-DF11-8DF5-00215AAC8BCE'')) And (Location=''B''))) AND (1=1)'

and it worked fine, so I changed the second part to:

'(SQL((PermitReqd=0 OR Code IN (SELECT ProductCode FROM DBRestrictedProducts WHERE CRMGUID=''6BFEB8D2-8A10-DF11-8DF5-00215AAC8BCE'')) And (Location=''B''))) AND (LEN(CLIP(UPPER(VPR:Description))) <> 0)'

to just add a check for the length of the Description column, and I get zero records again. 

Then I changed the second part to:

'(SQL((PermitReqd=0 OR Code IN (SELECT ProductCode FROM DBRestrictedProducts WHERE CRMGUID=''6BFEB8D2-8A10-DF11-8DF5-00215AAC8BCE'')) And (Location=''B''))) AND (SUB(VPR:Description,1,1) ='"B'')'

and it works fine, giving me all products starting with 'B'...

Very odd!

[Bruce]

My guess it doesn't like one, or all, of
UPPER, CLIP, LEN, INSTRING
clearly it likes SUB
so maybe experiment with the use of UPPER, CLIP and LEN in

'(SQL((PermitReqd=0 OR Code IN (SELECT ProductCode FROM DBRestrictedProducts WHERE CRMGUID=''6BFEB8D2-8A10-DF11-8DF5-00215AAC8BCE'')) And (Location=''B''))) AND (LEN(CLIP(UPPER(VPR:Description))) <> 0)'

I'm curious to know if

'(SQL((PermitReqd=0 OR Code IN (SELECT ProductCode FROM DBRestrictedProducts WHERE CRMGUID=''6BFEB8D2-8A10-DF11-8DF5-00215AAC8BCE'')) And (Location=''B''))) AND (instring('a',VPR:Description,1,1))) <> 0)'

returns anything.

[Devan]

Ok, from a process of elimination, it looks like the CLIP() function is what kills the query.  If I remove CLIP() from the function as you suggested, then the query works fine.

Interestingly, UPPER() seems to be IGNORED by the query.  If I try this:

'(SQL((PermitReqd=0 OR Code IN (SELECT ProductCode FROM DBRestrictedProducts WHERE CRMGUID=''6BFEB8D2-8A10-DF11-8DF5-00215AAC8BCE'')) And (Location=''B''))) AND (instring('a',UPPER(VPR:Description),1,1))) <> 0)'

I still get descriptions returned with an 'a' in them, even though logically I SHOULDN'T get anything returned, because 'a' <> UPPER('a'), am I right?

Are these problems because VPR:Descripion is a STRING(40) instead of a CSTRING(41) ??  Or do I need to bind the functions again?

[Devan]

I've been doing some more testing on this.  Running TRACE.EXE shows that just the SQL portion is being sent to the back end regardless of whether the Locator field is filled in or not.  Here is a sample trace log:

Code: [Select]


01D40H(3) 09:25:10.282 Allocating Statement 0A796A0H on Connection 0A793C0H Time Taken:0.00 secs
01D40H(3) 09:25:10.282 Setting Concurrency to Read Only for Statement 0A796A0H Time Taken:0.00 secs
01D40H(3) 09:25:10.282 Setting Cursor Type to Forward Only for Statement 0A796A0H Time Taken:0.00 secs
01D40H(3) 09:25:10.282 Resetting Parameters Statement 0A796A0H Time Taken:0.00 secs
01D40H(3) 09:25:10.282 Preparing Statement 0A796A0H : SELECT  A.LOCATION, A.Code, A.DESCRIPTION, A.INSTOCKUNIT, A.PERMITREQD FROM dbo.VWProductMaster A WHERE ( (PermitReqd=0 OR Code IN (SELECT ProductCode FROM DBRestrictedProducts WHERE CRMGUID='6BFEB8D2-8A10-DF11-8DF5-00215AAC8BCE')) And (Location='B') )  ORDER BY   A.DESCRIPTION,   A.LOCATION,   A.Code Time Taken:0.00 secs
01D40H(3) 09:25:10.282 Setting number of rows to fetch to 1 for Statement 0A796A0H Time Taken:0.00 secs
01D40H(3) 09:25:10.282 Binding Column 1 to C type CHAR(4) for Statement 0A796A0H Time Taken:0.00 secs
01D40H(3) 09:25:10.282 Binding Column 2 to C type CHAR(23) for Statement 0A796A0H Time Taken:0.00 secs
01D40H(3) 09:25:10.282 Binding Column 3 to C type CHAR(41) for Statement 0A796A0H Time Taken:0.00 secs
01D40H(3) 09:25:10.282 Binding Column 4 to C type CHAR(7) for Statement 0A796A0H Time Taken:0.00 secs
01D40H(3) 09:25:10.282 Binding Column 5 to C type UTINYINT for Statement 0A796A0H Time Taken:0.00 secs
01D40H(3) 09:25:10.282 Executing prepared Statement 0A796A0H Time Taken:0.00 secs
01D40H(3) 09:25:10.288 Fetching Row from Statement 0A796A0H Return Code: 0 Time Taken:0.00 secs
01D40H(3) 09:25:10.288 NEXT(VIEW:BB327FC:dbo.VWProductMaster:0BB32484H)   Time Taken:0.00 secs
01D40H(3) 09:25:10.288  VPR:LOCATION: 'B'
01D40H(3) 09:25:10.288  VPR:FLD_CODE: 'CMOUNT00000.5AI'
01D40H(3) 09:25:10.288  VPR:FLD_CODE: 'CMOUNT00000.5AI'
01D40H(3) 09:25:10.288  VPR:DESCRIPTION : '.5 C-Mount RQ E71'
01D40H(3) 09:25:10.288  VPR:PERMITREQD : 00H
01D40H(3) 09:25:10.288  VPR:INSTOCKUNIT : ''
01D40H(3) 09:25:10.288 POSITIONfile(VIEW:BB327FC:dbo.VWProductMaster:0BB32484H)   Time Taken:0.00 secs


It looks like the Clarion part of the filter may be run on the client side?  Any ideas where to look or to trace that side of things?

[Bruce]

>> Ok, from a process of elimination, it looks like the CLIP() function is what kills the query.  If I remove CLIP() from the function as you suggested, then the query works fine.

good work. I'll re-work it to remove the use of Clip.

>> I still get descriptions returned with an 'a' in them, even though logically I SHOULDN'T get anything returned, because 'a' <> UPPER('a'), am I right?

maybe. maybe not. I guess it depends on the way collation is set on the backend. I could see cases where UPPER is ignored.

>> Running TRACE.EXE shows that just the SQL portion is being sent to the back end regardless of whether the Locator field is filled in or not.

That may be a function of TRACE more than the filtering. Can you see the SQL statement as it appears on the server side? I'm not aware of SQL causing the "rest to be client side" but perhaps that's possible. My suspicion though is that this is a red-herring.

>> Are these problems because VPR:Descripion is a STRING(40) instead of a CSTRING(41) ??

no, If anything managing CStrings is worse.

I've tweaked the 7.18 build to remove the CLIP.  (let me know if you can use an interim file to test.)

Cheers
Bruce

[Devan]

Hey Bruce,

Happy to test a 7.18 Beta if you like.  Project is at a standstill until I can resolve this, so it won't hurt to give it a shot.

Feel free to email me at:  devan (at) blaze (dot) com (dot) au with any instructions.

Cheers,
Devan

[Devan]

Hi Bruce,

Just tried 7.18 - same problem.  The CLIP() is still in the filter.

Cheers,
Devan

[Bruce]

hi Devan,
try the attached file
(belongs in \clarion8\accessory\libsrc\win)

cheers
Bruce


[attachment deleted by admin]

[Devan]

YES !!!!!!!!

This patch works - Thank very much for sending it through Bruce.

I assume that these changes will be incorporated in 7.19?

Thanks,
Devan

[bruce2]

>> I assume that these changes will be incorporated in 7.19?

nah... I just sent them to show you I have the power to fix it if I choose <evil laugh mwahahahah>

[Devan]

LOL... I better do a DIFF on the files then, so I can make the requisite changes after I install upgrades...