Subject Re: Character ' and applylocate some opinion would be welcome
From Mervyn Bick <invalid@invalid.invalid>
Date Wed, 6 Apr 2022 17:55:16 +0200
Newsgroups dbase.getting-started
Attachment(s) locate_apostrophe.wfm

On 2022/04/06 16:48, Ken Mayer wrote:

>> so i think the last function is doing the same
 >> once by requering and once by applylocate
 >> the point is how to use applylocate without param in the query
 >> and finding words starting with ' ( all others characters are accepted)
 >>
 >> thanks for some additional informatien,
 >
> The applyLocate() (and filter) options are limited to what SQL they
> understand (the limits are really based on the BDE itself, not on
> anything in dBASE).
 >
> You need to look at the locateOptions (and if you use filter,
> filterOptions), although it seems that using 3 (Match partial length and
> ignore case) as you are above, should do the trick. One problem might be
> if you are looking for words starting with an apostrophe ('), is that
> apostrophes or single-quotes are delimiters in dBASE and SQL, as are
> double-quotes. It might help you to build your commands using the square
> brackets. The other thing is if you do " ' " dBASE is going to be
> looking for the spaces as well ...
The applyLocate() and filter() methods of a rowset object require SQL
expressions as the argument.  Unfortunately localSQL as used in dBASE is
VERY limited.

dBASE will accept single quotes, double quotes or square brackets to
delimit a string.  localSQL wants single quotes.

oRef.applyLocate([name = 'whatever'])
  and
oRef.applyLocate("name = 'whatever'")

are both acceptable.

oRef.applyLocate([name = "whatever"])
  and
oRef.applyLocate('name = "whatever"')

are not acceptable.

To use the applyLocate() method to locate a record where the name
includes an apostrophe it is necessary to "escape" the apostrophe by
using a \ in front of it/


oRef.applyLocate("name = '\'t veer'")

A little example is attached.

Mervyn.






if file('locate_apostrophe.dbf')
// drop table locate_apostrophe
endif

if not file('locate_apostrophe.dbf')
   create table locate_apostrophe  (id autoinc,name character(15))

   insert into locate_apostrophe  (name) values ("Abel")
   insert into locate_apostrophe  (name) values ("O'Neil")
   insert into locate_apostrophe  (name) values ("'t Veer")
   insert into locate_apostrophe  (name) values ("Baker")
endif
** END HEADER -- do not remove this line
//
// Generated on 2022-04-06
//
parameter bModal
local f
f = new locate_apostropheForm()
if (bModal)
   f.mdi = false // ensure not MDI
   f.readModal()
else
   f.open()
endif

class locate_apostropheForm of FORM
   with (this)
      height = 16.0
      left = 32.5714
      top = 5.0909
      width = 53.1429
      text = ""
   endwith

   this.LOCATE_APOSTROPHE1 = new QUERY(this)
   with (this.LOCATE_APOSTROPHE1)
      left = 4.0
      top = 1.0
      width = 14.0
      height = 1.0
      sql = "select * from locate_apostrophe"
      active = true
   endwith

   with (this.LOCATE_APOSTROPHE1.rowset)
      locateOptions = 3        // Match Partial Length and Ignore Case
   endwith

   this.GRID1 = new GRID(this)
   with (this.GRID1)
      dataLink = form.locate_apostrophe1.rowset
      height = 8.0
      left = 6.4286
      top = 2.8182
      width = 39.2857
   endwith

   this.ENTRYFIELD1 = new ENTRYFIELD(this)
   with (this.ENTRYFIELD1)
      height = 1.0
      left = 11.2857
      top = 13.0
      width = 10.5714
      value = "'t Veer"
   endwith

   this.PUSHBUTTON1 = new PUSHBUTTON(this)
   with (this.PUSHBUTTON1)
      onClick = class::PUSHBUTTON1_ONCLICK
      height = 1.0909
      left = 28.5714
      top = 12.9091
      width = 15.2857
      text = "Locate"
   endwith

   this.rowset = this.locate_apostrophe1.rowset

  
   function escape_apostrophe(cString)
      // Add  \ as an escape charater before '
       for n = len(cString) to 1 step -1
            if substr(cString,n,1) = [']
               cString = stuff(cString,at(['],cString),0,[\])
            endif
         next
      return cString

   function PUSHBUTTON1_onClick()
      cEsc = class::escape_apostrophe((form.entryfield1.value))
      form.rowset.applyLocate([name = ']+cEsc+['])
      return

endclass