Howto search blob-, memo- fields 
Author Message
 Howto search blob-, memo- fields

IT student requires expert opinion for thesis project.

My project requires me to store texts and search them for keywords.
The texts are currently stored in Ms Access 97 memo-fields.
The content is law articles.

Jet SQL, nor the SQL-92 standard, permit searches in memo-fields at all.
I think Oracle supports it (all in database philosophy) but am not sure.
I don't know about SQL Server.

The solution I tested embodies the storage of the texts in text- or
html-files.
Microsoft Indexing Service (Win2k) or Index Server (WinNt4) would allow me
to search.

Because texts would change only once in an interval of two months this seems
ok to me.

Tested for word "tea" with Indexing Service with

  strSearch = "SELECT DocTitle, Path, FileName, Characterization, Size" & _
              " FROM SCOPE()" & _
              " WHERE CONTAINS(CONTENTS,'" & Request.Form("txtSearchFor") &
"%') "

Indexing Server is not able to find "tea" in "teacup".
Matches predicate as an alternative is slow and difficult to write.

Searching in memo/blob fields with VB/VBScript would - I think - would
provide very poor performance while searching in a lot of memo-fields

Another alternative is to provide an index ,to search in, myself, based on
previous methods.

All suggestions and hints are welcome.

Bart Debersaques
Subsidia R & D
Belgium



Sun, 22 Sep 2002 03:00:00 GMT  
 Howto search blob-, memo- fields
With DAO, from VB on a FoxPRO database, we use
1   instr(UCase(MEMO1),ucase('" & Text3.Text & "'))>0
or
2    the like% function in SQL's

Nico

Quote:
> IT student requires expert opinion for thesis project.

> My project requires me to store texts and search them for keywords.
> The texts are currently stored in Ms Access 97 memo-fields.
> The content is law articles.

> Jet SQL, nor the SQL-92 standard, permit searches in memo-fields at all.
> I think Oracle supports it (all in database philosophy) but am not sure.
> I don't know about SQL Server.

> The solution I tested embodies the storage of the texts in text- or
> html-files.
> Microsoft Indexing Service (Win2k) or Index Server (WinNt4) would allow me
> to search.

> Because texts would change only once in an interval of two months this
seems
> ok to me.

> Tested for word "tea" with Indexing Service with

>   strSearch = "SELECT DocTitle, Path, FileName, Characterization, Size" &
_
>               " FROM SCOPE()" & _
>               " WHERE CONTAINS(CONTENTS,'" & Request.Form("txtSearchFor")
&
> "%') "

> Indexing Server is not able to find "tea" in "teacup".
> Matches predicate as an alternative is slow and difficult to write.

> Searching in memo/blob fields with VB/VbScript would - I think - would
> provide very poor performance while searching in a lot of memo-fields

> Another alternative is to provide an index ,to search in, myself, based on
> previous methods.

> All suggestions and hints are welcome.

> Bart Debersaques
> Subsidia R & D
> Belgium



Sun, 22 Sep 2002 03:00:00 GMT  
 Howto search blob-, memo- fields
If you set up the text files and Index Service correctly, you should have no
problems searching the text and the performance should be very good. If you
are not finding substrings with IS, you are not formulating your query
correctly. Unfortunately I am not too familiar with actually coding for IS,
but I do know that it can find substrings.

SQL Server 7 provides full-text indexing capabilities, but, like Oracle's
solution it's performance breaks down under heavy load, and needs to be
indexed on a schedule to pick up new additions (it won't add them
automatically). It also does not function on SQL Server Enterprise Edition.
Given those restrictions, it may still be a good solution for you.

The LIKE command with wildcards will work but cannot use indexes.

Colin


Quote:
> IT student requires expert opinion for thesis project.

> My project requires me to store texts and search them for keywords.
> The texts are currently stored in Ms Access 97 memo-fields.
> The content is law articles.

> Jet SQL, nor the SQL-92 standard, permit searches in memo-fields at all.
> I think Oracle supports it (all in database philosophy) but am not sure.
> I don't know about SQL Server.

> The solution I tested embodies the storage of the texts in text- or
> html-files.
> Microsoft Indexing Service (Win2k) or Index Server (WinNt4) would allow me
> to search.

> Because texts would change only once in an interval of two months this
seems
> ok to me.

> Tested for word "tea" with Indexing Service with

>   strSearch = "SELECT DocTitle, Path, FileName, Characterization, Size" &
_
>               " FROM SCOPE()" & _
>               " WHERE CONTAINS(CONTENTS,'" & Request.Form("txtSearchFor")
&
> "%') "

> Indexing Server is not able to find "tea" in "teacup".
> Matches predicate as an alternative is slow and difficult to write.

> Searching in memo/blob fields with VB/VbScript would - I think - would
> provide very poor performance while searching in a lot of memo-fields

> Another alternative is to provide an index ,to search in, myself, based on
> previous methods.

> All suggestions and hints are welcome.

> Bart Debersaques
> Subsidia R & D
> Belgium



Sun, 22 Sep 2002 03:00:00 GMT  
 Howto search blob-, memo- fields
Some experiments

------------- MSDE & Full Text Querying

Got MSDE for cheap testing.

Full text Querying of SQL Server Data
 1> exec sp_fulltext_database 'enable'
 2> go
 (1 row affected)
 Msg 7609, Level 17, State 2, Server BART, Procedure sp_fulltext_database,
Line 37
 [Microsoft][ODBC SQL Server Driver][SQL Server]Could not load the Full-Text
 Search component (SQLFTQRY.DLL).
does not work in MSDE :-)

Full text Querying of File Data (via Indexing Service) worked
I dumped the memo/blob fields in a files [primarykey.txt]
The primary key is 5 digits long stored in a text field.

Indexed them and looked in Platform SDK for Documentation

/* Setting up link to Indexing Service */


go

/* my database */
use wib
go

/* select sample: join of WIB & Indexing service */
SELECT SID, STEXT
FROM WIBNL as W,
 OpenQuery( BARTWIB,
 "SELECT Filename
  FROM SCOPE()
  WHERE CONTAINS('lijfrente') "
 ) as Q
WHERE W.SID = SUBSTRING( Q.FileName, 1, 5 )
go

/* drop de link to the indexing server */
EXEC sp_dropserver 'BARTWIB'
go

This works

Next I would have liked to make a stored procedure like

1> CREATE PROCEDURE searchIDX


4> AS


strToSea
rch + "')"
7>      SELECT W.SID, W.STEXT

9>      WHERE W.SID = SUBSTRING( Q.FileName, 1, 5 )
10>
11> go
Msg 170, Level 15, State 1, Server BART, Procedure searchIDX, Line 8

If I fix it

1> CREATE PROCEDURE searchIDX


4> AS


strToSearch + "')"
7>      SELECT W.SID, W.STEXT

9>      WHERE W.SID = SUBSTRING( Q.FileName, 1, 5 )
10>
11>  go
Msg 170, Level 15, State 1, Server BART, Procedure searchIDX, Line 8
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 8: Incorrect syntax near

Does OpenQuery require a constant expression for the 'query' argument?
What am I doing wrong?

Same problem with

1> CREATE PROCEDURE searchIDX


4> AS


strToSea
rch + "')"
7>      SELECT W.SID, W.STEXT

9>      WHERE W.SID = SUBSTRING( Q.FileName, 1, 5 )
10>
11> go
Msg 170, Level 15, State 1, Server BART, Procedure searchIDX, Line 8

The idea is to provide a server + a word to search for.
Or is this impossible with OpenQuery & OpenRowSet?

------------- MSDE & SQL Server DML/SQL

An other, totaly different, approach is

select SID
from artnl
where CHARINDEX('administratie', artikel ) <> 0
go

select SID
from artnl
where patindex('%administratie%', artikel) <> 0
go

where the artikel column is of the text-datatype

seems very effective

------------- Thanks

Thank You for your suggestions,
they are most helpfull and stimulating

Bart Debersaques
Subsidia R & D
Belgium



Quote:
> If you set up the text files and Index Service correctly, you should have
no
> problems searching the text and the performance should be very good. If
you
> are not finding substrings with IS, you are not formulating your query
> correctly. Unfortunately I am not too familiar with actually coding for
IS,
> but I do know that it can find substrings.

> SQL Server 7 provides full-text indexing capabilities, but, like Oracle's
> solution it's performance breaks down under heavy load, and needs to be
> indexed on a schedule to pick up new additions (it won't add them
> automatically). It also does not function on SQL Server Enterprise
Edition.
> Given those restrictions, it may still be a good solution for you.

> The LIKE command with wildcards will work but cannot use indexes.

> Colin



> > IT student requires expert opinion for thesis project.

> > My project requires me to store texts and search them for keywords.
> > The texts are currently stored in Ms Access 97 memo-fields.
> > The content is law articles.

> > Jet SQL, nor the SQL-92 standard, permit searches in memo-fields at all.
> > I think Oracle supports it (all in database philosophy) but am not sure.
> > I don't know about SQL Server.

> > The solution I tested embodies the storage of the texts in text- or
> > html-files.
> > Microsoft Indexing Service (Win2k) or Index Server (WinNt4) would allow
me
> > to search.

> > Because texts would change only once in an interval of two months this
> seems
> > ok to me.

> > Tested for word "tea" with Indexing Service with

> >   strSearch = "SELECT DocTitle, Path, FileName, Characterization, Size"
&
> _
> >               " FROM SCOPE()" & _
> >               " WHERE CONTAINS(CONTENTS,'" &

Request.Form("txtSearchFor")

- Show quoted text -

Quote:
> &
> > "%') "

> > Indexing Server is not able to find "tea" in "teacup".
> > Matches predicate as an alternative is slow and difficult to write.

> > Searching in memo/blob fields with VB/VbScript would - I think - would
> > provide very poor performance while searching in a lot of memo-fields

> > Another alternative is to provide an index ,to search in, myself, based
on
> > previous methods.

> > All suggestions and hints are welcome.

> > Bart Debersaques
> > Subsidia R & D
> > Belgium



Mon, 23 Sep 2002 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. memo/blob fields from access and/or oracle, access in VBScript under ASP

2. Blob or Memo field not allowed in formula

3. Append memo field to a different memo field

4. Memo fields - problems reporting 2 memo fields

5. How can I search a Memo Field in code

6. Help - Searching Memo Fields For Multiple Strings

7. Searching Memo Field For Text Strings

8. Need Help - Searching Memo Fields For Strings

9. Search an replace in memo fields

10. Large Memo field search and replace error

11. Searching memo fields??

12. Large Memo Field search fix from Microsoft

 

 
Powered by phpBB® Forum Software