Start a new topic

VDB 4.2 DDA SetScope...

Trying to emulate a LIKE value% type of search in DDA with SetScope.


What I do is take the input from as TextBox and use it as the beginning of the range, and for the end of the range, I add ASCII 1 to the value.  


Works great until I have someone searching on lower case Z.


I get a SetScope of AN_REG_NAME: 'z', AN_REG_NAME: '{'


This does not crash but it returns no results.  The same thing happens if the search term is a word like 'Boyz'  , where the values to look for are 'Boyz' and 'Boy{'  


Since { is a valid character in a nvarchar(100) column, one would sort of hope that this kind of a thing would work.  


I cannot use a LIKE and a SQL call because the result set is way too large and you don't yet have the equivalent of the SQLCEResultSet, which is really necessary for desktop programs.  So to get scrollability, I have to use DDA and attempt to emulate.

Right now my customers cannot type Z in the search and show all animals with names that begin with Z, but SetRange should work.


What am I doing incorrectly?


Yes, the index is set to one over the proper column.


Thanks.  In VB.NET, I changed the last character of the search string for the high range to ChrW(65500) and all is well!


Solved!

Incrementing the last character in the prefix isn't quite right because you wouldn't want it to actually match on that value (say, if it was another letter) and more importantly the culture-based case-insensitive order (of the index) may not match the ordinal ASCII order assumed by the increment--especially between letters and non-letters; that could be why it fails when appending '{'.


The appending trick from that old thread is close, and it would work for strictly alphanumeric data values, but appending a 'z' won't account for non-letter characters if they are greater than a 'z' (in a case-insensitive comparison, assuming your database is configured the normal way).


Internally, the LIKE operator appends a (char)65500 character to the prefix to get the upper bound of the scope for a prefix comparison (LIKE 'prefix%').  In theory, this is the highest-valued character but should not actually be in use; hopefully, those assumptions are both true for all possible cultures.  The un-appended prefix is used as the lower bound--as you had already figured.

I wasn't able to get it to work using the "{" symbol. However, after reading from the old help forums, I found this. (http://community.gibraltarsoftware.com/forums/yaf_postsm18733_Using--LIKE-xxx-in-a-DDA-SetFilter-Method.aspx)


AN_REG_NAME:'z', AN_REG_NAME:'zz'


I tested it using databuilder and it appeared to provide the expected results.


I hope that helps.

Login to post a comment