December 03, 2020, 07:42:15 am
News: GalahTech Community Based Technical Support!
Pages: [1]   Go Down
Author Topic: Lost Topic Title  (Read 1184 times)
Grubby Labrador
Full Member
*

Stars: +0/-0
Offline Offline

Posts: 228



WWW
« on: May 25, 2006, 11:44:49 am »

I've knocked up a function to search for a string in a file

I want to return a list of files whose  name contains "merge" and which contain the string xyz

tblData is a table with a list of files in it; filepath and filename are just two of the columns

query1:
[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]select * from
query2
where searchfile(filepath & "\" & filename,"xyz") = true;[/quote]


query2:
[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]SELECT *
FROM tblData
WHERE filename like "*merge*";[/quote]


This is bollocks though because if I put a break point in searchfile() and run query1, it's obvious that all records in tblData arre being passed to it in turn and not just the ones WHERE filename like "*merge*"

Why is this?


searchfile just opens reads the file and returns the result of an Instr() as true or false
« Last Edit: May 25, 2006, 11:45:58 am by Grubby Labrador » Logged

It's taken many years, but at last:

Dragonfly, "You are probably right Guru."



Martin Wallace
Founders
*

Stars: +1/-0
Offline Offline

Posts: 3602


God is dead, I have surpassed him.


WWW
« Reply #1 on: May 25, 2006, 12:06:36 pm »

Use % instead of * I believe.
Logged

Every time god kills an angel, I masturbate.

Grubby Labrador
Full Member
*

Stars: +0/-0
Offline Offline

Posts: 228



WWW
« Reply #2 on: May 25, 2006, 12:08:21 pm »

No, that's not the answer
Logged

It's taken many years, but at last:

Dragonfly, "You are probably right Guru."



Martin Wallace
Founders
*

Stars: +1/-0
Offline Offline

Posts: 3602


God is dead, I have surpassed him.


WWW
« Reply #3 on: May 25, 2006, 12:38:31 pm »

You must have something else wrong becuase the queries you posted work perfectly for me.

Logged

Every time god kills an angel, I masturbate.

Grubby Labrador
Full Member
*

Stars: +0/-0
Offline Offline

Posts: 228



WWW
« Reply #4 on: May 25, 2006, 12:55:47 pm »

Try this then

I'm using Access 97 which might make a difference...


see what appears in the debug window when you run Query1
Logged

It's taken many years, but at last:

Dragonfly, "You are probably right Guru."



Martin Wallace
Founders
*

Stars: +1/-0
Offline Offline

Posts: 3602


God is dead, I have surpassed him.


WWW
« Reply #5 on: May 25, 2006, 01:20:47 pm »

I get every line printed in the debug window, but only the 2 'The' lines are returned in the results window.

I suspect what is happening is that the query optimiser is recognising that even though you have 2 queries you are in fact performing a single query against the same table with 2 conditional clauses.

So in effect it optimises the 2 queries to :
Code:
SELECT *
FROM Table1
WHERE  f1 like "*the*" AND x(f1)=true;

The result being it performs the function on every line.
« Last Edit: May 25, 2006, 01:21:24 pm by Martin Wallace » Logged

Every time god kills an angel, I masturbate.

Grubby Labrador
Full Member
*

Stars: +0/-0
Offline Offline

Posts: 228



WWW
« Reply #6 on: May 25, 2006, 01:46:51 pm »

I've got one of the old techy dudes here on the case now.

It's due to its use of Rushmore Technology which optomises the sql like you say.

If it handled  the AND like C, it would be fine because it would then fail on the first part and then not bother evaluating the function at all.

« Last Edit: May 25, 2006, 01:51:17 pm by Grubby Labrador » Logged

It's taken many years, but at last:

Dragonfly, "You are probably right Guru."



Pages: [1]   Go Up
Print
 
Jump to: