Subject Re: Problems with requery() in dbase 12 and dbase 19 (in windows 11) From HERNAN Campos <hfcro@yahoo.com> Date Sat, 21 Jan 2023 21:16:28 -0500 Newsgroups dbase.getting-started Thank you very much, Mervyn Bick Wrote, with your indications we have managed to reduce the dbase drops to almost zero. Mervyn Bick Wrote: > On 2023/01/19 01:17, Hernan Campos wrote: > > Question and help, the Requery() in dbase 12 or Dbase 2019 has some kind of problem that causes it to close the program without giving any kind of warning or message (we use windows 11). We have a simple form, where we consult a list of products, when we search we give the data we want to locate, it works fine but after carrying out several searches, the dbase closes the program completely without giving any notice > > > > The statement we use is > > > > [Select * from ']+[produc_file]+[' where name like "%dataseek%" OR name like "%dataseek" OR name like "dataseek%" ] > > > > then in combination with the "requery()" so that it executes the "CanOpen" again > > > > Can someone give me an idea of what it could be, we get the impression that when using it it destabilizes the application in a very severe way, previously we didn't use this function, we just completely regenerated the object, it was a bit slower but it didn't give you problems, then I read an email in this group, where they recommended to do faster searches, we also noticed that a temporary file called _sqlXXX.dbf is created, is there a way to delete it or that it is generated in a folder where it can be deleted safely, since even though the application is closed, the file is not removed by itself, thanks > > Instead of using requery(), if you make the query's active property > false before you change the SELECT statement and then make the active > property true the instability should go away.  You may, however need to > reassign the datasource property of any object which accesses the rowset. > > That said, this isn't the way to do this.  You should be using a > parameter driven query.  The parameter can be changed at any time and a > requery() will fetch a new set of records without any problems at all. > >     .... >     sql = 'select * from whatever where name like :name' >     params['name'] = '%'  //fetch all records when query opens. >                           // '' will open with an empty rowset. >     .... > > To fetch a new set of records, in your code > >     form.queryname.params['name'] = '%'+dataseek+'%' >     form.queryname.requery() > > > '%'+dataseek+'%' returns the records that both '%'+dataseek and > dataseek+'%' would return. > > Be aware that this query is just about the most onerous task one can set > for an SQL engine as every character of every record in the table must > be checked for a match.  On large tables this can affect response times. > > In localSQL the LIKE predicate is case-sensitive.  You may need to use > >    '... where lower(name) like :name' > > and > >    form.queryname.params['name'] = '%'+lower(dataseek)+'%' > > > If you need to change tables for a query on an open form, create a > separate query for each table. > > Assign the "live" rowset to the form's rowset property (or a new user > defined property of the form) and use form.rowset (or the new property) > as the datasource for objects on the form. > >     form.querynameX.params['name'] = '%'+dataseek+'%' >     form.querynameX.requery() >     form.rowset = form.querynameX.rowset > > If you do have separate product tables you should probably rethink your > database design. > > > Mervyn.