Database Search

User avatar
Jeff Barnes
Posts: 912
Joined: Sun Oct 09, 2005 1:05 pm
Location: Ontario, Canada
Contact:

Database Search

Post by Jeff Barnes »

Hi,

Is there a fast way to search all fields in a database for some text.

Thanks,

Jeff
Colin Haig
Posts: 310
Joined: Mon Oct 10, 2005 5:10 am

Search

Post by Colin Haig »

Jeff

There is a third party function called wildseek - never
used it though - I have used the rat() and at() functions to find part
of names in a cash record database, but this checks every record ,
unless you can set a scope on a subset of records.


Cheers

Colin
User avatar
Antonio Linares
Site Admin
Posts: 37481
Joined: Thu Oct 06, 2005 5:47 pm
Location: Spain
Contact:

Post by Antonio Linares »

Jeff,

Some years ago we helped a company to test several third party tools for such purpouse and finally implemented our own solution to make the fastest search on all fields of a DBF.

We found with great surprise that the solution was to open the DBF as a standard file with FOpen(), read a bunch of bytes in memory and perform a simple At() to locate a string. Once found, you substract the DBF header length, then divide the offset by the record size and you get the record number. At() is an extremelly fast function as it is directly performed by the processor.

These days that we use 32 bits flat memory, I guess there is no need to use a bunch of bytes, so the entire DBF may be loaded in memory doing a MemoRead() of the DBF file, or several bunchs if it is too large, so the code may get simpler.

We compared this way with other available third party tools, and we found that ours was the fastest one :)

Its worth to try it.
regards, saludos

Antonio Linares
www.fivetechsoft.com
User avatar
Eugeniusz Owsiak
Posts: 60
Joined: Fri Oct 07, 2005 5:38 am
Location: Poland

Post by Eugeniusz Owsiak »

Antonio,

This is very interesting solution.

thx Eugeniusz
User avatar
Jeff Barnes
Posts: 912
Joined: Sun Oct 09, 2005 1:05 pm
Location: Ontario, Canada
Contact:

Post by Jeff Barnes »

Antonio,

I tried your idea but I have 2 problems...

1. It returns the record number -1
example: I know the record is #5 but it return #4

2. I need to search for ALL occurences of "sometext". At() will only find the first (and RAt() the last)

Can you think of any other way of doing it?

Thanks,
Jeff


P.S. This is the Code I tried:

Function SearchFile( cDBF, cAlias )
Local cSearch := Space(20), nLocation, cData

if ! MsgGet("Search...","Enter Search String ",@cSearch)
Return nil
endif

SELECT &cAlias
cData := MemoRead( cDBF )

if Len(cData ) < 1
MsgInfo("Not Data to Search","File Error")
Return Nil
endif

nLocation := (AT( cSearch, cData ) - Header() ) / RecSize()
if nLocation < 1
Exit
else
MsgInfo("Record: "+str(nLocation ))
endif
Return Nil
User avatar
Antonio Linares
Site Admin
Posts: 37481
Joined: Thu Oct 06, 2005 5:47 pm
Location: Spain
Contact:

Post by Antonio Linares »

Jeff,

> 1. It returns the record number -1

Add 1 to it :)

> 2. I need to search for ALL occurences of "sometext". At() will only find the first (and RAt() the last)

Modify your function, so when you find the text, then you modify the cData:

cData = SubStr( cData, nLocation + Len( cSearch ) )

and then perform At() again until there are no more occurrences. Store the previous nLocation on a variable to properly calculate the offset.
regards, saludos

Antonio Linares
www.fivetechsoft.com
User avatar
Antonio Linares
Site Admin
Posts: 37481
Joined: Thu Oct 06, 2005 5:47 pm
Location: Spain
Contact:

Post by Antonio Linares »

Jeff,

Both Harbour/xHarbour lets you specify a third parameter when calling At() to select the starting offset into the text.

This is a better way so you don't have to call SubStr() which manages a lot of memory for copying the previous string into the new one, so it will result into an even faster function :)

At( cSearch, cData, nOffset )
regards, saludos

Antonio Linares
www.fivetechsoft.com
User avatar
Jeff Barnes
Posts: 912
Joined: Sun Oct 09, 2005 1:05 pm
Location: Ontario, Canada
Contact:

Post by Jeff Barnes »

Thanks Antonio ... I now have a good start ... one more question:

In the new Search function I store the record number into an array (aRecords).

How can I display a listbox with only those record numbers that are in the array?

Thanks
Jeff
User avatar
Antonio Linares
Site Admin
Posts: 37481
Joined: Thu Oct 06, 2005 5:47 pm
Location: Spain
Contact:

Post by Antonio Linares »

Jeff,

If the amount of found records is not too big then you could use a standard listbox (not a browse). If the amount is big then you could create a temporary DBF, copy the selected records and show them in a browse.
regards, saludos

Antonio Linares
www.fivetechsoft.com
jacquet philippe
Posts: 22
Joined: Fri Nov 04, 2005 9:05 pm
Location: LIEGE Belgium

searching records

Post by jacquet philippe »

In other to show only the records you have found,
place the record number in a data base (in place of an array) and after that use the instruction SET RELATION TO
sildata
Posts: 38
Joined: Thu May 11, 2006 7:41 pm

Re: Database Search

Post by sildata »

Amigo: si no has solucionado todavia tu problema enviame un mensajea.
RODOLFO SILVA
email: sildata@cantv.net
User avatar
Otto
Posts: 4470
Joined: Fri Oct 07, 2005 7:07 pm
Contact:

Post by Otto »

Hello Jeff,
do you have your "Search Function" ready?
If yes, would you be so kind to share it?

Regards
Otto
Gale FORd
Posts: 663
Joined: Mon Dec 05, 2005 11:22 pm
Location: Houston
Contact:

Post by Gale FORd »

Are you using xHarbour.com. The FTS functions would allow you to create a FTS (Fast Text Search) index. I have done this with Six driver's FTS and the xHarbour.com version is supposed to work the same.
User avatar
Otto
Posts: 4470
Joined: Fri Oct 07, 2005 7:07 pm
Contact:

Post by Otto »

Although I have all the FWH releases the main part of my program is still FW 1.9.
I need the search function for that part.

Regards
Otto
Gale FORd
Posts: 663
Joined: Mon Dec 05, 2005 11:22 pm
Location: Houston
Contact:

Post by Gale FORd »

You could use the Six driver that includes the Fast Text Search. I still have an aplication that keeps an FTS indexe on some history files that get updated once a month.

I think Fast Text Search was sold as a seperate product at one time also.
Post Reply