Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 9 of 9 total
Thread Problem with locate
Wed, Feb 28 2007 8:25 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Tim

I'm trying to clean up documents left around over the last 10 or so years in my app.

I've created two tables (TechniquesDocs and DiskDocs). TechniquesDocs holds the path and file details for all linked documents  (4725) DiskDocs for all documents actually on the disk (4908). I then ran a loop to flag which documents in DiskDocs were also in TechniquesDocs

procedure TForm2.Button3Click(Sender: TObject);
begin
TechniquesDocs.First;
while not TechniquesDocs.Eof do begin
 if DiskDocs.Locate('_Path', LowerCase(TechniquesDocs.FieldByName('_Path').AsString), [loCaseInsensitive]) then begin
  DiskDocs.Edit;
  DiskDocs.FieldByName('_OnTechniques').AsBoolean := True;
  DiskDocs.Post;
  TechniquesDocs.Edit;
  TechniquesDocs.FieldByName('_OnDisk').AsBoolean := True;
  TechniquesDocs.Post;
 end;
 TechniquesDocs.Next;
end;
end;

Problem is Locate doesn't seem to be finding them eg

TechniquesDocs
\\Base\work\NLHEMail\Selling Stuff\\Attached\SalesCallGuide05.pdf
DiskDocs
\\Base\Work\NLHEMail\Selling Stuff\Attached\SalesCallGuide05.pdf

Flag not set.

Roy Lambert


/************************************************************
*
* ElevateDB Reverse-Engineered SQL for the DocCheck database
*
* Generated on 28/02/2007 13:17:22
* By the user Administrator
*
************************************************************/


/************************************************************
* Tables
************************************************************/

CREATE TABLE "TechniquesDocs"
(
"_Path" VARCHAR(512) COLLATE "ANSI_CI",
"_OnDisk" BOOLEAN,
"_Source" VARCHAR(10) COLLATE "ANSI_CI"
)
VERSION 1
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
CREATE INDEX "Source" ON "TechniquesDocs"
("_Source" COLLATE "ANSI_CI")
CREATE INDEX "Path" ON "TechniquesDocs"
("_Path" COLLATE "ANSI_CI")
CREATE TABLE "DiskDocs"
(
"_Path" VARCHAR(512) COLLATE "ANSI_CI",
"_OnTechniques" BOOLEAN,
CONSTRAINT "PK" PRIMARY KEY ("_Path")
)
VERSION 1
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768

/************************************************************
* Views
************************************************************/


/************************************************************
* Functions
************************************************************/


/************************************************************
* Procedures
************************************************************/

/************************************************************
* End of generated SQL
************************************************************/

Wed, Feb 28 2007 8:52 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Roy,

<< I've created two tables (TechniquesDocs and DiskDocs). TechniquesDocs
holds the path and file details for all linked documents (4725) DiskDocs for
all documents actually on the disk (4908). I then ran a loop to flag which
documents in DiskDocs were also in TechniquesDocs >>

Could you send me the tables and catalog ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Wed, Feb 28 2007 10:17 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Tim


In binaries - forgive me if I haven't sent the right stuff - I'm still practicing

Roy Lambert

Wed, Feb 28 2007 11:23 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Roy,

<<  In binaries - forgive me if I haven't sent the right stuff - I'm still
practicing >>

No, you got everything just fine.  However, the Locate is working just fine
here and the Edit/Post sequences are firing for every row.

--
Tim Young
Elevate Software
www.elevatesoft.com

Wed, Feb 28 2007 1:35 PMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Tim


If you look at the last row in each table are the booleans both true?

In my case they're both still null.

Roy Lambert
Thu, Mar 1 2007 5:41 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Roy,

<< If you look at the last row in each table are the booleans both true? In
my case they're both still null. >>

Check the back-slashes.

You owe me 20 minutes. Smiley

--
Tim Young
Elevate Software
www.elevatesoft.com

Thu, Mar 1 2007 8:07 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Tim


My eyeballs only just managed to make the same connection. What can I do for your 20 mins oh great one. In fact to make it worth while I hereby place a standard working day at your disposal.

Here's part payment Smiley

update techniquesdocs T set _onDisk = True
where T._Path=(SELECT D._Path FROM diskdocs D WHERE D._Path=T._Path)

Doesn't work in b1

Error #700

expected SET but instead found T

update techniquesdocs set _onDisk = True
where _Path=(SELECT D._Path FROM diskdocs D WHERE D._Path=_Path)

results in only 1 row being processed

update techniquesdocs set _onDisk = True
where techniquesdocs._Path=(SELECT D._Path FROM diskdocs D WHERE D._Path=techniquesdocs._Path)

works

Roy Lambert
Fri, Mar 2 2007 7:30 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Roy,

<< My eyeballs only just managed to make the same connection. What can I do
for your 20 mins oh great one. In fact to make it worth while I hereby place
a standard working day at your disposal.

Here's part payment Smiley

update techniquesdocs T set _onDisk = True
where T._Path=(SELECT D._Path FROM diskdocs D WHERE D._Path=T._Path)

Doesn't work in b1 >>

You might need to use the actual table name instead of a correlation name.
I didn't test the query and just banged it up quickly.  The reason for not
allowing table correlation names in an UPDATE statement is that an UPDATE
statement only affects one table, hence it shouldn't need correlation names.
However, I'll see about adding it as some sugar. Smiley

<< update techniquesdocs set _onDisk = True
where _Path=(SELECT D._Path FROM diskdocs D WHERE D._Path=_Path)

results in only 1 row being processed >>

This is correct.  EDB has no way of knowing that the _Path column that
you're using in the sub-query is from the techniquedocs table instead of the
diskdocs table.

--
Tim Young
Elevate Software
www.elevatesoft.com

Fri, Mar 2 2007 8:08 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Tim


Thanks

Roy Lambert
Image