sql server full-text search

[fillae]

Does anyone have experience with storing documents (word, excel, pdf, etc) in
sql server as a binary object, and then using the full-text catalogs to search
for data within the binary object (just like verity)? I am able to insert a
binary object into a database table, and retrieve the object, but I am unable
to search the contents of the file using the full-text capabilities of sql
server.

If anyone has been able to do what I've described, could you please offer some
advice?

Here is the code I use to upload/insert a record into the database:

<!--- Get binary file. --->
<CFFILE ACTION="readbinary" FILE="#txtTempUploadDir#\#txtNewFileName#"
VARIABLE="binary_file">

<CFQUERY NAME="qryNewFile" DATASOURCE="#Request.dsnSQLDB#">
INSERT INTO tblFileRepository(Type, objFile, txtFileName, txtTitle,
txtDescription, txtPOC, txtFormat,
txtSize, dateUploadDate, txtUploadedBy, intFileAreaID)
VALUES('#txtType#', <cfqueryparam cfsqltype="cf_sql_blob"
value="#binary_file#">, '#txtNewFileName#', '#attributes.txtTitle#',
'#attributes.txtDescription#', '#attributes.txtPOC#',
'#attributes.txtFormat#',
'#txtSize#', #Now()#, '#USERINFO.txtUserName#', #qryAreaInfo.intFileAreaID#)
SELECT @@IDENTITY AS intNewID
</CFQUERY>

I then use the following query to try and retrieve information:

SELECT *
FROM tblFileRepository
WHERE CONTAINS (*, ' "document" ')

The word 'document' is within the file that is uploaded/inserted.

I followed the instructions in the following article when setting up my
catalog:

[url]http://www.databasejournal.com/features/mssql/article.php/3486331[/url]

The article has a .net slant, so I am starting to think that the problem is
how I am inserting the object into the database. Perhaps it is not in the
correct format?

Any help would be GREATLY appreciated.

Thanks!

[ctrl+alt+delete]

Did you repopulate the index after uploading your files?

"fillae" <webforumsuser@macromedia.com> wrote in message
news:d5r9ar$2mf$1@forums.macromedia.com...

> Does anyone have experience with storing documents (word, excel, pdf, etc)
> in
> sql server as a binary object, and then using the full-text catalogs to
> search
> for data within the binary object (just like verity)? I am able to insert
> a
> binary object into a database table, and retrieve the object, but I am
> unable
> to search the contents of the file using the full-text capabilities of sql
> server.
>
> If anyone has been able to do what I've described, could you please offer
> some
> advice?
>
> Here is the code I use to upload/insert a record into the database:
>
> <!--- Get binary file. --->
> <CFFILE ACTION="readbinary" FILE="#txtTempUploadDir#\#txtNewFileName#"
> VARIABLE="binary_file">
>
> <CFQUERY NAME="qryNewFile" DATASOURCE="#Request.dsnSQLDB#">
> INSERT INTO tblFileRepository(Type, objFile, txtFileName, txtTitle,
> txtDescription, txtPOC, txtFormat,
> txtSize, dateUploadDate, txtUploadedBy, intFileAreaID)
> VALUES('#txtType#', <cfqueryparam cfsqltype="cf_sql_blob"
> value="#binary_file#">, '#txtNewFileName#', '#attributes.txtTitle#',
> '#attributes.txtDescription#', '#attributes.txtPOC#',
> '#attributes.txtFormat#',
> '#txtSize#', #Now()#, '#USERINFO.txtUserName#',
> #qryAreaInfo.intFileAreaID#)
> SELECT @@IDENTITY AS intNewID
> </CFQUERY>
>
> I then use the following query to try and retrieve information:
>
> SELECT *
> FROM tblFileRepository
> WHERE CONTAINS (*, ' "document" ')
>
> The word 'document' is within the file that is uploaded/inserted.
>
> I followed the instructions in the following article when setting up my
> catalog:
>
> [url]http://www.databasejournal.com/features/mssql/article.php/3486331[/url]
>
> The article has a .net slant, so I am starting to think that the problem
> is
> how I am inserting the object into the database. Perhaps it is not in the
> correct format?
>
> Any help would be GREATLY appreciated.
>
> Thanks!
>

[fillae]

Yes I did. I also have Change Tracking turned on so it is updated any time there is a change to the table.

[ctrl+alt+delete]

"fillae" <webforumsuser@macromedia.com> wrote in message
news:d5t3v0$o6e$1@forums.macromedia.com...

> Yes I did. I also have Change Tracking turned on so it is updated any
> time there is a change to the table.

Well, I don't use the Full Text search capability of MS SQL Server often,
but I tried to mimic your code on my test servers and had no trouble. My
simplified code is as follows:

<CFFILE action="readbinary"
file="#Request.repository#\GFX_Textron031505.doc" variable="binary_file">

<CFQUERY NAME="qryNewFile" DATASOURCE="#Request.DSN#">
INSERT INTO Docs(Type, Title, Document)
VALUES('doc', 'GFX_Textron031505.doc', <cfqueryparam
cfsqltype="cf_sql_blob" value="#binary_file#">)
</CFQUERY>

<CFQUERY name="rsFiles" datasource="#Request.DSN#">
SELECT f.*, d.*
FROM Docs d INNER JOIN FREETEXTTABLE(Docs, *, ' "$45/hour" ') f ON d.DocID =
f.[Key]
</CFQUERY>

<CFDUMP var="#rsFiles#">

On the SQL Server, I used the script exactly as provided in the web article
you referenced (so my table contains the bare minimum.)

[fillae]

After some trial and error, I was able to get it to work. The problem was with
the 'Type' field in the database. The only way I could get it to work is if I
defined it as a char(3).

As a side note. The Full-Text capability works a ton better than verity for
my application. I've been using verity for over 5 years now with success, but
the Full-Text search in SQL server is the nuts. It's faster, plus there are no
more collections to hassle over. JMHO.

Thanks all.

[ctrl+alt+delete]

"fillae" <webforumsuser@macromedia.com> wrote in message
news:d5tv04$59j$1@forums.macromedia.com...

> After some trial and error, I was able to get it to work. The problem was
> with
> the 'Type' field in the database. The only way I could get it to work is
> if I
> defined it as a char(3).
>
> As a side note. The Full-Text capability works a ton better than verity
> for
> my application. I've been using verity for over 5 years now with success,
> but
> the Full-Text search in SQL server is the nuts. It's faster, plus there
> are no
> more collections to hassle over. JMHO.
>
> Thanks all.
>

Have you played with ranking yet?

[fillae]

No, I have not played with ranking. It might be a future revision.