Tim

D2006 EDB 1.06

I've just finished, almost, my data transfer program. This stage was adding in full text indexing. I thought I'd test it and I get

ElevateDB Error #700 An error was found in the statement at line 1 and column 28 (Invalid expression "_message" found, CONTAINS or DOES NOT CONTAIN can only be used with a text-indexed column)

According to EDBManager (and my transfer prog) _Message is indexed. The index is fti_Message COLLATE ANSI_CI

Type is Text Index

I have two tables with fti_Message as the full text index name does this screw things up?

I've looked in Information.Indexes and there is an entry for both of them (ps can we have an export/save result set if its not there already). BUT there is a problem - WordGenerator is shown as "Default" - not as specified in my script (below).

Finally what is OwnerConstraint in Information.Indexes?

Roy Lambert

SCRIPT
BEGIN
EXECUTE IMMEDIATE
'CREATE TEXT FILTER "RTFStripper" TYPE ''RTF'' MODULE "StripRTF"';
EXECUTE IMMEDIATE
'CREATE TEXT FILTER "HTMLStripper" TYPE ''RTF'' MODULE "PosExStripHTML"';
EXECUTE IMMEDIATE
'CREATE WORD GENERATOR "wgStandard" MODULE "wgPreFiltered"';
EXECUTE IMMEDIATE
'CREATE WORD GENERATOR "wgLookupList" MODULE "wgLookupList"';
EXECUTE IMMEDIATE
'CREATE TEXT INDEX "fti_Profile" ON "Companies" ("_Profile" COLLATE "ANSI_CI") INDEXED WORD LENGTH 30 WORD GENERATOR "wgLookupList"';
EXECUTE IMMEDIATE
'CREATE TEXT INDEX "fti_Products" ON "Companies" ("_Products" COLLATE "ANSI_CI") INDEXED WORD LENGTH 30 WORD GENERATOR "wgLookupList"';
EXECUTE IMMEDIATE
'CREATE TEXT INDEX "fti_Skills" ON "Contacts" ("_Skills" COLLATE "ANSI_CI") INDEXED WORD LENGTH 30 WORD GENERATOR "wgLookupList"';
EXECUTE IMMEDIATE
'CREATE TEXT INDEX "fti_Attributes" ON "Contacts" ("_Attributes" COLLATE "ANSI_CI") INDEXED WORD LENGTH 30 WORD GENERATOR "wgLookupList"';
EXECUTE IMMEDIATE
'CREATE TEXT INDEX "fti_Keywords" ON "QBase" ("_Keywords" COLLATE "ANSI_CI") INDEXED WORD LENGTH 30 WORD GENERATOR "wgLookupList"';
EXECUTE IMMEDIATE
'CREATE TEXT INDEX "fti_Flags" ON "ELN" ("_Flags" COLLATE "ANSI_CI") INDEXED WORD LENGTH 30 WORD GENERATOR "wgLookupList"';
EXECUTE IMMEDIATE
'CREATE TEXT INDEX "fti_Flags" ON "EMails" ("_Flags" COLLATE "ANSI_CI") INDEXED WORD LENGTH 30 WORD GENERATOR "wgLookupList"';
EXECUTE IMMEDIATE
'CREATE TEXT INDEX "fti_Notes" ON "Contacts" ("_Notes" COLLATE "ANSI_CI") INDEXED WORD LENGTH 30 WORD GENERATOR "wgStandard"';
EXECUTE IMMEDIATE
'CREATE TEXT INDEX "fti_Notes" ON "Companies" ("_Notes" COLLATE "ANSI_CI") INDEXED WORD LENGTH 30 WORD GENERATOR "wgStandard"';
EXECUTE IMMEDIATE
'CREATE TEXT INDEX "fti_Notes" ON "Calls" ("_Notes" COLLATE "ANSI_CI") INDEXED WORD LENGTH 30 WORD GENERATOR "wgStandard"';
EXECUTE IMMEDIATE
'CREATE TEXT INDEX "fti_CandidateNotes" ON "Calls" ("_CandidateNotes" COLLATE "ANSI_CI") INDEXED WORD LENGTH 30 WORD GENERATOR "wgStandard"';
EXECUTE IMMEDIATE
'CREATE TEXT INDEX "fti_Message" ON "ELN" ("_Message" COLLATE "ANSI_CI") INDEXED WORD LENGTH 30 FILTER TYPE COLUMN "_DisplayFormat" WORD GENERATOR "wgStandard"';
EXECUTE IMMEDIATE
'CREATE TEXT INDEX "fti_Subject" ON "ELN" ("_Subject" COLLATE "ANSI_CI") INDEXED WORD LENGTH 30 WORD GENERATOR "wgStandard"';
EXECUTE IMMEDIATE
'CREATE TEXT INDEX "fti_Comments" ON "ELN" ("_Comments" COLLATE "ANSI_CI") INDEXED WORD LENGTH 30 WORD GENERATOR "wgStandard"';
EXECUTE IMMEDIATE
'CREATE TEXT INDEX "fti_Message" ON "EMails" ("_Message" COLLATE "ANSI_CI") INDEXED WORD LENGTH 30 FILTER TYPE COLUMN "_DisplayFormat" WORD GENERATOR "wgStandard"';
EXECUTE IMMEDIATE
'CREATE TEXT INDEX "fti_Subject" ON "EMails" ("_Subject" COLLATE "ANSI_CI") INDEXED WORD LENGTH 30 WORD GENERATOR "wgStandard"';
EXECUTE IMMEDIATE
'CREATE TEXT INDEX "fti_Comments" ON "EMails" ("_Comments" COLLATE "ANSI_CI") INDEXED WORD LENGTH 30 WORD GENERATOR "wgStandard"';
END

Roy,

<< ElevateDB Error #700 An error was found in the statement at line 1 and
column 28 (Invalid expression "_message" found, CONTAINS or DOES NOT CONTAIN
can only be used with a text-indexed column)

According to EDBManager (and my transfer prog) _Message is indexed. The
index is fti_Message COLLATE ANSI_CI >>

What is the SQL SELECT statement that you're using to test this with ?

<< I have two tables with fti_Message as the full text index name does this
screw things up? >>

No.

<< I've looked in Information.Indexes and there is an entry for both of them
(ps can we have an export/save result set if its not there already). BUT
there is a problem - WordGenerator is shown as "Default" - not as specified
in my script (below). >>

Could you send me your catalog file for this database ?  I'll check it out
and see what is going on.

<< Finally what is OwnerConstraint in Information.Indexes? >>

It's the constraint that created the index for its own use in enforcing a
primary key, unique, or foreign key constraint.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim



>What is the SQL SELECT statement that you're using to test this with ?

If I remember correctly SELECT * FROM EMails WHERE _Message CONTAINS 'someword'

Can't actually remember the test word, but it was one that should have been indexed cos I looked in the _Message column to pick one.


>Could you send me your catalog file for this database ? I'll check it out
>and see what is going on.

Sent

><< Finally what is OwnerConstraint in Information.Indexes? >>
>
>It's the constraint that created the index for its own use in enforcing a
>primary key, unique, or foreign key constraint.

OK - thanks

Roy Lambert

Roy,

<< If I remember correctly SELECT * FROM EMails WHERE _Message CONTAINS
'someword' >>

You defined the text index on _Message as using the collation ANSI (notice
the lack of the _CI).  That means that you have to use:

SELECT * FROM EMails WHERE _Message COLLATE ANSI CONTAINS 'someword'

since the CONTAINS operator defaults to looking for a case-insensitive
collation when looking for the matching text index.

You should also be able to cut down on the size of the text index
significantly by using _CI when defining the text index.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

Two points:

1. What you seem to be saying is the error message is wrong since there is a text index there just I HAVE to specify the collation if it isn't ANSI_CI. Is that right?

2. I'll rerun my conversion app (takes about 3 hours) but looking at the code below you'll see I do specify ANSI_CI.

Roy Lambert

procedure MakeAnIndex(const tbl, fld, fltr, wgen: string);
var
 idxStr: string;
begin
 idxStr := 'CREATE TEXT INDEX "fti' + fld + '" ON "' + tbl + '" ("' + fld + '" COLLATE "ANSI_CI") ';
 idxStr := idxStr + 'INDEXED WORD LENGTH ' + MaxWord.Text + ' ';
 if fltr <> blank then idxStr := idxStr + 'FILTER TYPE COLUMN "' + fltr + '" ';
 idxStr := idxStr + 'WORD GENERATOR "';
 if wgen <> blank then idxStr := idxStr + wgen + '"' else idxStr := idxStr + 'Default"';
 IdxMaker.SQL.Add('EXECUTE IMMEDIATE');
 IdxMaker.SQL.Add(QuotedStr(idxStr) + ';');
end;

Tim

I think a major part of the problem is multiple configuration files. Probably the ANSI vs ANSI_CI was something I did, but one thing that was scaring me is explained. I set the word generator for the full text indices and when I looked in EDBManager it was just showing default. I finally figured out that I have to create a separate session, point it at the appropriate configuration file and things are right.

This does raise other issues, more, hopefully, from a developers viewpoint, rather than an end user. What happens to data and indices if I go into the database using the default session which has a different configuration file from the one I'm using?

I know I shouldn't do this, but I'd like the software to stop me rather than allow it. I don't know what, if anything, can be done I'm just hoping you have some ideas.

Roy Lambert

Roy,

<< 1. What you seem to be saying is the error message is wrong since there
is a text index there just I HAVE to specify the collation if it isn't
ANSI_CI. Is that right? >>

Correct.

<< 2. I'll rerun my conversion app (takes about 3 hours) but looking at the
code below you'll see I do specify ANSI_CI. >>

I just tried the following here:

CREATE TEXT INDEX "Notes"
ON "biolife"
("Notes" COLLATE "ANSI_CI")
INDEXED WORD LENGTH 30
WORD GENERATOR "Default"

and it works just fine.  The collation is ANSI_CI as expected.

--
Tim Young
Elevate Software
www.elevatesoft.com

Roy,

<< This does raise other issues, more, hopefully, from a developers
viewpoint, rather than an end user. What happens to data and indices if I go
into the database using the default session which has a different
configuration file from the one I'm using? >>

Then you'll most likely screw something up if you modify data.  In
multi-user situations, however, you'll just simply get a lock error on the
database because your session will try to obtain a lock on the database in
an area that is already locked by another user.

<< I know I shouldn't do this, but I'd like the software to stop me rather
than allow it. I don't know what, if anything, can be done I'm just hoping
you have some ideas. >>

We had databases tied to configuration files early on, but there were
complaints so I removed it:

http://www.elevatesoft.com/scripts/newsgrp.dll?action=openmsg&group=19&msg=75&page=1#msg75

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


I must say a) I'm glad I didn't contribute to that thread and b) having tried to re-read it my brain imploded part way through.

What I've decided to do for now is create a separate session for each project and have only the database(s) for that project in there and make sure they point at the config file I'm actually using.

Roy Lambert

<<"Tim Young [Elevate Software]" wrote:

Roy,

<< If I remember correctly SELECT * FROM EMails WHERE _Message CONTAINS
'someword' >>

You defined the text index on _Message as using the collation ANSI (notice
the lack of the _CI).  That means that you have to use:

SELECT * FROM EMails WHERE _Message COLLATE ANSI CONTAINS 'someword'

since the CONTAINS operator defaults to looking for a case-insensitive
collation when looking for the matching text index.

You should also be able to cut down on the size of the text index
significantly by using _CI when defining the text index.
>>

Having just suffered temporary baldness trying to fix this exact same error (4 years on!) I think it would be really helpful if you could modify the error message to something like,

ElevateDB Error #700 An error was found in the statement at line 1 and
column 28 (Invalid expression "_message" found, CONTAINS or DOES NOT CONTAIN
can only be used with a text-indexed column *with matching collation*

or similar.

The error message is plain wrong when I DO have the column text-indexed.

Thanks :-D

Aaron