INSERT INTO tblFileRepository(Type, objFile, txtFileName, txtTitle, txtDescription, txtPOC, txtFormat, txtSize, dateUploadDate, txtUploadedBy, intFileAreaID) VALUES('#txtType#', , '#txtNewFileName#', '#attributes.txtTitle#', '#attributes.txtDescription#', '#attributes.txtPOC#', '#attributes.txtFormat#', '#txtSize#', #Now()#, '#USERINFO.txtUserName#', #qryAreaInfo.intFileAreaID#) SELECT @@IDENTITY AS intNewID 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! [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => fillae [ip] => webforumsuser@m [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 1 [islastshown] => [isfirstshown] => 1 [attachments] => [allattachments] => ) --> > VARIABLE="binary_file"> > > > INSERT INTO tblFileRepository(Type, objFile, txtFileName, txtTitle, > txtDescription, txtPOC, txtFormat, > txtSize, dateUploadDate, txtUploadedBy, intFileAreaID) > VALUES('#txtType#', value="#binary_file#">, '#txtNewFileName#', '#attributes.txtTitle#', > '#attributes.txtDescription#', '#attributes.txtPOC#', > '#attributes.txtFormat#', > '#txtSize#', #Now()#, '#USERINFO.txtUserName#', > #qryAreaInfo.intFileAreaID#) > SELECT @@IDENTITY AS intNewID > > > 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! >[/quote] [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => ctrl+alt+delete [ip] => thenetwerx@REMO [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 2 [islastshown] => [isfirstshown] => [attachments] => [allattachments] => ) --> sql server full-text search - Coldfusion - Advanced Techniques

sql server full-text search - Coldfusion - Advanced Techniques

Does anyone have experience with storing doents (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 ...

  1. #1

    Default sql server full-text search

    Does anyone have experience with storing doents (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 (*, ' "doent" ')

    The word 'doent' 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 Guest

  2. #2

    Default Re: sql server full-text search

    Did you repopulate the index after uploading your files?

    "fillae" <webforumsusermacromedia.com> wrote in message
    news:d5r9ar$2mf$1forums.macromedia.com...
    > Does anyone have experience with storing doents (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 (*, ' "doent" ')
    >
    > The word 'doent' 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 Guest

  3. #3

    Default Re: sql server full-text search

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

  4. #4

    Default Re: sql server full-text search

    "fillae" <webforumsusermacromedia.com> wrote in message
    news:d5t3v0$o6e$1forums.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, Doent)
    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.)


    ctrl+alt+delete Guest

  5. #5

    Default Re: sql server full-text search

    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.

    fillae Guest

  6. #6

    Default Re: sql server full-text search

    "fillae" <webforumsusermacromedia.com> wrote in message
    news:d5tv04$59j$1forums.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?


    ctrl+alt+delete Guest

  7. #7

    Default Re: sql server full-text search

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

Similar Threads

  1. mySQL and Full Text Search
    By Bill in forum MySQL
    Replies: 1
    Last Post: August 4th, 09:43 PM
  2. Basic problem installing TSearch2 (full text search)
    By Jon Asher in forum PostgreSQL / PGSQL
    Replies: 0
    Last Post: December 23rd, 06:34 AM
  3. bilingual Full text search
    By uzzu webforumsuser@macromedia.com in forum Macromedia Director Lingo
    Replies: 7
    Last Post: September 2nd, 01:59 PM
  4. Regular Expression for SQL Server full-text search
    By Ray Dixon [MVP] in forum ASP.NET General
    Replies: 0
    Last Post: July 21st, 06:27 PM
  5. full text search
    By Tommy in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 9th, 01:36 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •