Full-text index image column, all Raiders!

Introduction | Index


today, "a once-in-a-century" blackout, read a day book. The evening made a full text index, decided to paste out the experience, I try to write in detail, we learn together, welcome to correct me!





1, start Microsoft Search service


Start Menu-->sql program Group--> Service Manager--> dropdown basket-->microsoft Search service--> launch it





2,


  .. \microsoft SQL Server\mssql\ftdata Qlserver\config\ directory to build a non-empty noise.chs file


not empty Noise.chs file, also some people say is empty noise.chs file, but I write a few useless letters every time.





3, establish the environment


Open Query Analyzer--> execute the following script:


--------------------------------------------


CREATE DATABASE Test---creating test databases


Use test---Select the test database


CREATE TABLE Dali (ID int not null primary key,myimage image,filetype varchar (255), Filenmae varchar (255))---creating a Dali table

The three columns in the
--dali table must be id,myimage,filetype, because to index the image column, you must have a primary key column, an image column, a column that holds the file type


--we know that file types are differentiated by extension in Windows systems, so the filetype column is the extension used to place the file


--------------------------------------------





sp_fulltext_database ' Enable '-Enables the database for Full-text indexing


sp_fulltext_catalog ' my_fulldir ', ' create '---Create a full-text catalog called My_fulldif





declare @Key sysname; Select @Key =c.name from syscolumns a,sysconstraints b,sysobjects C where a.id=object_id (' Dali ') and a.name= ' id ' and a.id=b . ID and B.constid=c.id and c.name like ' pk% '


exec sp_fulltext_table ' Dali ', ' Create ', ' my_fulldir ', @Key----These two sentences are for full-text indexing, marking tables





sp_fulltext_column ' Dali ', ' myimage ', ' Add ', 0x0804, ' FileType '---this is the specified myimage is listed as a full-text indexed column, FileType is a type column


------------------------------------------------


4, a Word file with an extension of doc on the C disk, an Excel file with an extension of XLS, a Web page file with an extension of HTM, a picture with an extension of BMP


a total of 4, you can put it according to the actual situation!





5, insert data


establishes the following stored procedure


--------------------------------------------------


CREATE PROCEDURE sp_textcopy


@srvname varchar (30),


@login varchar (30),


@password varchar (30),


@dbname varchar (30),


@tbname varchar (30),


@colname varchar (30),


@filename varchar (30),


@whereclause varchar (40),


@direction char (1)


as


/* This is the use of the Textcopy tool to insert files into the database, if there is a foreground tool can use the foreground development tool to insert the file, here for demonstration/


DECLARE @exec_str varchar (255)


SELECT @exec_str = ' textcopy/s ' + @srvname + '/u ' + @login + '/p ' + @password + '/d ' + @dbname + '/T ' + @tbname + '/C ' + @colname + '/w ' ' + @whereclause + ' "/F" ' + @filename + ' "/' + @direction


EXEC Master. xp_cmdshell @exec_str


----------------------------------------------------





Insert Dali values (1,0x, ' Doc ', ' vigorously Doc ')---where the second column is 0x it is a 16 binary number corresponding to the image column, is required, do not write null, the third column is a file type, both extension





sp_textcopy ' Your server name ', ' sa ', ' Your password ', ' Test ', ' Dali ', ' myimage ', ' c:\ vigorously doc.doc ', ' where id=1 ', ' I '


-------The following parameters are: Instance name, username, password, database name, table name, image column name, path and filename, condition (you must make sure it selects only one line), I


----------------------------------------------------------------------------------------------------------- ----------


Insert Dali values (2,0x, ' bmp ', ' picture ')


sp_textcopy ' Your server name ', ' sa ', ' Your password ', ' Test ', ' Dali ', ' myimage ', ' c:\ picture. bmp ', ' where id=2 ', ' I '--note the condition is id=2





Insert Dali values (3,0x, ' xls ', ' Excel file ')


Sp_textcopy ' Your server name ', ' sa ', ' Your password ', ' Test ', ' Dali ', ' myimage ', ' c:\Excel file. xls ', ' where id=3 ', ' I '--note that the condition is id=3





Insert Dali values (4,0x, ' htm ', ' web ')


sp_textcopy ' Your server name ', ' sa ', ' Your password ', ' Test ', ' Dali ', ' myimage ', ' c:\ Web page. htm ', ' where id=4 ', ' I '--note that the condition is id=4





----------The above statement, to ensure the same type, the path is correct, the only correct conditions should be able to





6, populating Full-text indexing





sp_fulltext_table ' Dali ', ' start_full '---the first argument is the table name, and the second parameter is the full population of the Full-text index of the startup table





7, you can start your experiment





select * from Dali where contains (MyImage, ' J teacher ')





select * from Dali where contains (MyImage, ' Sea teacher ')





------End----------


-Debugging Environment: SQLSERVER2000 Enterprise Edition, Windows2000 Advanced Server