TDBF - Locate - more than one field?
lazarus / 16-Databases

----------------------------------------------------------------------
timcs · 2012-11-28 08:13

Hi

  Is it possible to search on more that one field using Locate via a
TDBF component? I am using the index which holds these fields . I have
tried the following attempts name and dateon are the field names:

       if
dbf1.Locate('name,'+'dateon','Smith'+'30/08/2012',[loCaseInsensitive,
loPartialKey]) = false then
          messagedlg('No record found.',mtinformation,[mbok],0);

       if dbf1.Locate('customer'+'dateon','Manuli
'+'30/08/2012',[loCaseInsensitive, loPartialKey]) = false then
          messagedlg('No record found.',mtinformation,[mbok],0);

The first attempt does not error when used but does not find the
record even though the values are there in a record

Any ideas on this please

Thanks

Tim

----------------------------------------------------------------------
timcs · 2012-11-28 12:07

I assume that the loPartialKey is like a wildcard search?


Thanks

Tim

----------------------------------------------------------------------
timcs · 2012-11-29 08:01

I have split the search up to see if I can figure out what is causing
the problem. So far I am struggling to search just by the date with an
index that just includes the date.

I have tried:

dbf1.IndexName:='dateind';
       if
dbf1.Locate('dateon',strtodate('30/08/2012'),[loCaseInsensitive,
loPartialKey]) = false then
          messagedlg('No record found.',mtinformation,[mbok],0)

dbf1.IndexName:='dateind';
       if dbf1.Locate('dateon','30/08/2012',[loCaseInsensitive,
loPartialKey]) = false then
          messagedlg('No record found.',mtinformation,[mbok],0)

and all I get is the result of no record found. As the date field also
contains the time, I have even tried this:

dbf1.IndexName:='dateind';
       if dbf1.Locate('dateon',strtodate('30/08/2012
17:15:09'),[loCaseInsensitive, loPartialKey]) = false then
          messagedlg('No record found.',mtinformation,[mbok],0)

Which gave a datetime format error and this:

dbf1.IndexName:='dateind';
       if dbf1.Locate('dateon','30/08/2012
17:15:09',[loCaseInsensitive, loPartialKey]) = false then
          messagedlg('No record found.',mtinformation,[mbok],0)

I have even tried to use DTOS as follows:

dbf1.IndexName:='dateind';
       if dbf1.Locate('DTOS(dateon)','30/08/2012',[loCaseInsensitive,
loPartialKey]) = false then
          messagedlg('No record found.',mtinformation,[mbok],0)

But I get an error stating DTOS(dateon) field not found.


Thanks

Tim

----------------------------------------------------------------------
BigChimp · 2012-11-29 10:08

Hi Tim,

Thanks for posting your findings. Hope you get it to work (or somebody
else has a good suggestion) so
http://wiki.lazarus.freepascal.org/Lazarus_Tdbf_Tutorial can be
updated.

Any tdbf gurus out there?

----------------------------------------------------------------------
timcs · 2012-11-29 10:59

Quote from: BigChimp on November 29, 2012, 11:08:56 amHi Tim,

Thanks for posting your findings. Hope you get it to work (or somebody
else has a good suggestion) so
http://wiki.lazarus.freepascal.org/Lazarus_Tdbf_Tutorial can be
updated.

Any tdbf gurus out there?

BigChimp I think the problem is down to the index as even though I had
written code to add the index it does not appear to add it. I have
also read the TDBF PDF from the author and the keyvalues for the
locate needs to be an array of variant, I created this as follows:

var
 keysearch : array of variant;

if dbf1.Active = true then
  begin
       dbf1.IndexName:='datename';
       keysearch[1] := '30/08/2012';
       keysearch[2] := 'Smith';

       if
dbf1.Locate('date;name',keysearch,[loCaseInsensitive,loPartialKey]) =
false then
          messagedlg('No record found.',mtinformation,[mbok],0);
  end;                        

When I did this the program crashed completely even though the PDF
status to create a variant of keyvalues to store the information.

the date index is created as follows:

   dbf1.Active:=false;
   dbf1.Exclusive:=true;
   dbf1.Open;

   dbf1.AddIndex('dateind.ndx','DTOS(dateon)',[ixCaseInsensitive]);
   dbf1.Close;
   dbf1.Exclusive:=false;
   dbf1.Active:=true;      

and also tried it as follows:

dbf1.Active:=false;
   dbf1.Exclusive:=true;
   dbf1.Open;

   dbf1.AddIndex('dateind','DTOS(dateon)',[ixCaseInsensitive]);
   dbf1.Close;
   dbf1.Exclusive:=false;
   dbf1.Active:=true;      


But when I run this :
if dbf1.Active = true then
    begin
         dbf1.GetIndexNames(listbox1.Items);
    end;     

It does not list this index


HELP!!!

Thanks


Tim

----------------------------------------------------------------------
taazz · 2012-11-29 11:14

var
 keysearch : array of variant;
..
Setlength(keysearch, 2)
if dbf1.Active = true then
  begin
       dbf1.IndexName:='datename';
       keysearch[0] := '30/08/2012';
       keysearch[1] := 'Smith';

       if
dbf1.Locate('date;name',keysearch,[loCaseInsensitive,loPartialKey]) =
false then
          messagedlg('No record found.',mtinformation,[mbok],0);
  end;                       


Those are the errors I see I don't know anything about tdbf so I can't
really help any more than code or logic errors from the code you post.

----------------------------------------------------------------------
timcs · 2012-11-29 11:30

Quote from: taazz on November 29, 2012, 12:14:55 pmvar
 keysearch : array of variant;
..
Setlength(keysearch, 2)
if dbf1.Active = true then
  begin
       dbf1.IndexName:='datename';
       keysearch[0] := '30/08/2012';
       keysearch[1] := 'Smith';

       if
dbf1.Locate('date;name',keysearch,[loCaseInsensitive,loPartialKey]) =
false then
          messagedlg('No record found.',mtinformation,[mbok],0);
  end;                       


Those are the errors I see I don't know anything about tdbf so I can't
really help any more than code or logic errors from the code you post.

Tazz thanks for that, it has stopped the error I got but still not
finding the record. I really think this is to do with the index not
appearing to be created but I am struggling to create it.

Thanks

Tim

----------------------------------------------------------------------
timcs · 2012-11-29 12:58

Right got the index created by doing it as follows:

  dbf1.Active:=false;
   dbf1.Exclusive:=true;
   dbf1.Open;
   dbf1.Active:=true;
   dbf1.AddIndex('dateind','DTOS(date)',[]);
   dbf1.Close;
   dbf1.Active:=false;
   dbf1.Exclusive:=false;
   dbf1.Active:=true;

Which I am sure I tried before but never mind but even after setting
this index and searching I still cannot search on the date. I can
search on the customer on its own but not the date  aaaaahhh

Thanks

Tim

----------------------------------------------------------------------
taazz · 2012-11-29 13:52

try the following

keysearch[0] := StrtoDate('30/08/2012');
instead of
keysearch[0] := '30/08/2012';

and see if that gives you better results. Keep in mind that you might
need to be specific ee you might need to declare a time as well
depends heavely on how the search is done internally and if it smart
enough to locate on date regardless of the time part. To put it in sql
terms

Where MydatetimeField >= '30/08/2012' and MydatetimeField <
'31/08/2012'
instead of
Where MydatetimeField = '30/08/2012'

----------------------------------------------------------------------
timcs · 2012-11-29 14:09

Quote from: taazz on November 29, 2012, 02:52:17 pmtry the following

keysearch[0] := StrtoDate('30/08/2012');
instead of
keysearch[0] := '30/08/2012';

and see if that gives you better results. Keep in mind that you might
need to be specific ee you might need to declare a time as well
depends heavely on how the search is done internally and if it smart
enough to locate on date regardless of the time part. To put it in sql
terms

Where MydatetimeField >= '30/08/2012' and MydatetimeField <
'31/08/2012'
instead of
Where MydatetimeField = '30/08/2012'

Tazz just tried this as you suggested but no joy  .  I was thinking of
using the setrange but this looks like it works for only one field .


I have also tried:
keysearch[0] := StrtoDateTime('30/08/2012 17:15:09');

Which still did not work. My reason for trying this all is that once
the dbf database gets to around 250k the filters on the records that I
am doing takes much longer even if the index is also applied

Thanks

Tim

----------------------------------------------------------------------
timcs · 2012-11-30 08:40

Further to this I have also tried to use the searchkey method from
dbf1 using just the date again with little success and also I have
used the setrange for a low and high setting and still no joy.

I am starting to think that the date does not work with these searches

Thanks

Tim

----------------------------------------------------------------------
BigChimp · 2012-11-30 09:01

Assuming you've looked into the tdbf source code?

If you could create a small demonstration project I'm willing to have
a look (and more importantly, others may as well). Don't know much
about the dbf code but it does sound like it could be improved - or at
least the documentation!

----------------------------------------------------------------------
timcs · 2012-11-30 11:06

Quote from: BigChimp on November 30, 2012, 10:01:53 amAssuming you've
looked into the tdbf source code?

If you could create a small demonstration project I'm willing to have
a look (and more importantly, others may as well). Don't know much
about the dbf code but it does sound like it could be improved - or at
least the documentation!

Actually I have not, not sure if I would understand it!! When you say
a small demonstration project, I assume if I put together a basic
program for you to use?

Thanks

Tim

----------------------------------------------------------------------
BigChimp · 2012-11-30 12:39

Exactly: a small program that demonstrates the problems you're having.

As for not understanding the source code... well, who knows. Sometimes
a little browse does wonders...

----------------------------------------------------------------------
timcs · 2012-11-30 13:50

Quote from: BigChimp on November 30, 2012, 01:39:05 pmExactly: a small
program that demonstrates the problems you're having.

As for not understanding the source code... well, who knows. Sometimes
a little browse does wonders...

Okay will try and get a small project set up and sent over , do you
need a dbase file as well? (I would presume you would but I'd thought
I'd ask) . Stupid question, where is the source for TDBF ?

Thanks

Tim

----------------------------------------------------------------------
BigChimp · 2012-11-30 14:01

You can attach it to a post in a zip file. Yes, a DBF file would be
nice: if you have code that is ready to run it can be quickly checked
by anybody who's interested... and if necessary attached to a
bugtracker issue as demonstration of a problem (and from there on the
code could be used in test code as well).

The source code is present in the FPC source code. Try rightclicking
on the dbf reference in you uses clause, then Find declaration. If you
have regular Lazarus (installed from the installer) the source may not
be present... in which case you can find it in the SVN repository,
e.g. here for FPC 2.6.0 which is used in Lazarus 1.0.0/1.0.2:
http://svn.freepascal.org/svn/fpc/tags/release_2_6_0/packages/fcl-
db/src/dbase/

----------------------------------------------------------------------
timcs · 2012-12-02 22:36

BigChimp

  Something here to ponder over, I have just installed Lazarus on a
Linux Virtual PC to put together the code you want to look at . When I
have been coding this program I have been using Windows as this is
what the program platform is aimed at.

 However using Linux I have noticed that the date display seems to be
determined on how the O/S requires it to show. So for example, on
Windows it has been showing it has DD-MM-YYYY HH:MM . Whereas with
Linux it has been showing it as DD-M-YY HH:MM so I have originally
been searching in the format on Windows and I am now wondering if the
database holds the date and time differently to how it displays it and
when searching for it, the format used it that of what is in the
database.

Perhaps the reason?

Thanks

TimCS

----------------------------------------------------------------------
BigChimp · 2012-12-03 07:23

It would be a stupid design decision to change the way dates are
stored based on the user's date preferences... but worse things have
happened.

Rational beings would store the date as a numeric value regardless of
formatting. Perhaps DBASE does this, perhaps they store it in US
format (it's a US developed format). I would however be very surprised
if the tdbf code did not convert between the TDateTime used in FPC and
the storage format, so I don't think storage could be a problem.

What could of course be a problem - and what you probably are getting
at - is the way you should specify date filters. I have no idea how
these work - that's why I suggested to have a look at the TDBF
sources. Once again, a sensible way would be to specify a TDateTime
which would eliminate display conversion issues... or perhaps there is
some set format which you have to use (I'd suspect US format mmddyyy
again)... should be in the source

If it is about ready, please feel free to attach the project anyway -
whether it works or fails - it could always be turned into an example:
you (and I) are not the only one wondering about how to deal with
filters in tdbf.

Regards,
BigChimp

----------------------------------------------------------------------
timcs · 2012-12-04 18:02

Quote from: BigChimp on December 03, 2012, 08:23:27 amIt would be a
stupid design decision to change the way dates are stored based on the
user's date preferences... but worse things have happened.

Rational beings would store the date as a numeric value regardless of
formatting. Perhaps DBASE does this, perhaps they store it in US
format (it's a US developed format). I would however be very surprised
if the tdbf code did not convert between the TDateTime used in FPC and
the storage format, so I don't think storage could be a problem.

What could of course be a problem - and what you probably are getting
at - is the way you should specify date filters. I have no idea how
these work - that's why I suggested to have a look at the TDBF
sources. Once again, a sensible way would be to specify a TDateTime
which would eliminate display conversion issues... or perhaps there is
some set format which you have to use (I'd suspect US format mmddyyy
again)... should be in the source

If it is about ready, please feel free to attach the project anyway -
whether it works or fails - it could always be turned into an example:
you (and I) are not the only one wondering about how to deal with
filters in tdbf.

Regards,
BigChimp

Now it gets strangers , working on a Windows PC again which is what I
have been developing the program on before, when I cleared the
existing table and created manual new records with dates and times, if
I searched using the full date and time format it would find the
record , however my problem still lies with searching just on the date
part of the field instead of the whole date and time format. I do not
think that the loPartialKey works with dates.

 I think it is best for me to take my other route over this matter,
clear down all previous records and just keep the one days worth.

Thanks

TimCS

----------------------------------------------------------------------
BigChimp · 2012-12-09 14:28

@timcs: I've been going through the dbf code a bit but what helped me
was the documentation on the tdbf sourceforge site and another post in
the forum that gave the answer.

Edit: well, that doesn't work, see code snippet below for locate code
with 1 field
See http://wiki.lazarus.freepascal.org/Lazarus_Tdbf_Tutorial#Index.2Ff
ilter_on_TDateTimes for some working examples for filtering using
date/time.

Hope that translates to your locate problem...

----------------------------------------------------------------------
BigChimp · 2012-12-18 18:17

Misusing this thread for reporting some progress on my investigations:
at least on foxpro tables (I'm looking at a visual foxpro one),
milliseconds are also stored, so you have to take these into account:
Code: [Select]          SomeDate:=ScanDateTime('yyyymmdd hhnnss
zzz','20051216 154425 999');
          writeln('trying to find modified at date
'+FormatDateTime('yyyymmdd hhnnss zzz',SomeDate));
          if MyDBF.Locate('MODIFIED',SomeDate,[]) then
          begin
            writeln('Recno is now: '+inttostr(MyDBF.RecNo));
            ....

----------------------------------------------------------------------
BigChimp · 2012-12-18 19:26

Quote from: timcs on December 04, 2012, 07:02:15 pmhowever my problem
still lies with searching just on the date part of the field instead
of the whole date and time format. I do not think that the
loPartialKey works with dates.
Two things:
1. I found out that datetime fields in at least visual foxpro tables
are stored with milliseconds. This will likely throw off exact
searches (I saw a record with milliseconds 999, perhaps an artifact of
floating point arithmetic)
2. At least on unindexed fields, locating with loPartialKey on dates
indeed does not work: it searches for equal fields (as if you didn't
specify loPartialKey)

Edit: this seems actually like the proper behaviour and matches other
datasets (sqldb, bufdataset). Fixed the dbf code that searches for
indexes to match this behaviour (which it didn't) see
http://bugs.freepascal.org/view.php?id=23509

As there is no way to indicate precision in the required date/time,
the results might not be what you want. Taazz' hunch was correct:
you'd probably need to find using a filter...