Example full-Text Indexing

Example:

Indexing data from MySQL:

execute dql
begin
open_oledb 'Driver={MySQL ODBC 3.51 Driver}; option=1048576; Server=mysql; Database=debat; Uid=myuser; Pwd=Mypassword,

'select postid, threadid, dateline, title, pagetext from post'

to @Results
project by postid, threadid, title, pagetext

end

option=1048576 – declare forward only cursor, otherwise the MySQL ODBC object will run out of memory if dataset is a big.

Create schema and insert rows from MySQL:

begin tran
--create table and full-text index
create database debate
go;
use debate
create freetext index debate_search
(
postid int unindex,
threadid int,
dateline varchar unstore,
title varchar unstore,
pagetext text unstore
)
go;
create table debate
(
postid int not null primary key,
threadid int,
dateline varchar,
title varchar,
pagetext text,
constraint debate_search freetext(postid,threadid,dateline,title,pagetext)
references debate_search(postid,threadid,dateline,title,pagetext)
)
go;

-- insert and index rows from MySQL
execute dql
begin
open_oledb 'Driver={MySQL ODBC 3.51 Driver}; option=1048576; Server=mysql; Database=debat; Uid=user; Pwd=password,

'select postid,threadid, dateline,title,pagetext from post'

to @T1
project by postid,threadid, title ,pagetext

insert_bulk @T1,'debate.debate'
set postid = @T1.postid,
threadid = @T1.threadid,
dateline = @T1.dateline,
title = @T1.title,
pagetext = @T1.pagetext
end

commit

Searching with full-text index:

use debate
select t2.postid,t2.threadid,t2.dateline,t2.title,t2.pagetext from (
execute freetext
(
select postid from debate_search where
pagetext='int'
restrict 0,10
order by dateline desc
)) t1 inner join debate t2 on (t1.postid=t2.postid)