1

Topic: To convert the table with FTS from MSSQL in SQLITE

In MSSQL is available  table1 c fields
docum_id:integer autoincrement
publishDate:datetime;
kod:int;
mytext:Nvarchar (max);
Across the field mytext it is installed FTS.
It is necessary to copy the table in sqlite.
At creation fts tables in sqlte with the help
CREATE VIRTUAL TABLE table1 USING fts4 (mytext);
However, it was clarified that in fts to the table it is impossible to create type fields datetime or int.
As in sqlite to create corresponding table1 the table with fts
And as
In sqlite to write request
select publishDate, mytext from table1 where Contains (mytext, ' searchstr ') and (publishDate> d1) and (publishDate <d2)

2

Re: To convert the table with FTS from MSSQL in SQLITE

AVRob;
In the documentation SQLite FTS3 and FTS4 Extensions there is no convergence that there is function Contains in an engine. But such it is googled that is in Oracle. In SQLite there is operator MATCH and restrictions on creation of the table typeful datetime are not present.
p.s. Actually the internal type of storage not DATETTIME, and TEXT, but administration application, can visually show that it is "date" and to work as with date (sorting can correctly work display in a national format date etc.), anyway problems at adding of such columns did not reveal

CREATE VIRTUAL TABLE table1 USING fts4 (mytext text, n integer, d1 datetime, d2 datetime);
select * from table1 where table1.mytext match ' 456 ' and d1> = strftime (' %Y- % M - % d %H: % M: % S', ' 2016-01-01 0:00:00 PM ')

3

Re: To convert the table with FTS from MSSQL in SQLITE

VSVLAD, Thanks.
Now other problem with Encoding.
The code there is a normal table
CREATE TABLE table0 (mytext Ntext, publishdate date);
INSERT INTO (mytext, publishdate) VALUES (' greetings ', ' 1998-12-07 ')
All is normal with value mytext =''
And at creation fts tables
CREATE VIRTUAL TABLE table0 USING FTS4 (mytext, publishdate);
INSERT INTO (mytext, publishdate) VALUES (' greetings ', ' 1998-12-07 ')
At reading instead of ' greetings ' are had '?????? '.
I read from basis by means of AnyDac (FireDac) in Delphi xe3 with ADQUERY.
NTEXT, NVARCHAR near to mytext at table creation do not help.

4

Re: To convert the table with FTS from MSSQL in SQLITE

AVRob;
Check up by means of any administrative tool, whether correctly in basis write down values to be convinced, whether that reading is correctly fulfilled. Or a problem with adjustment AnyDac or with application. AnyDac it is not installed, was more specific I can not prompt

5

Re: To convert the table with FTS from MSSQL in SQLITE

VSVLAD, in basis all is written down truly - opened basis with SQLITEEXPERT.
Problem in FireDAC c SQLITE.
In the first case FireDAC reads the data from basis as Ntext as  it is specified at table creation.
In the second case at table creation the column type (and at instructions the type is ignored) is not underlined.
In this case FireDAC reads the data from basis as ANSISTRING.

6

Re: To convert the table with FTS from MSSQL in SQLITE

More likely it is bug FireDac
In a file uADPhysSQLite.pas
In procedure ADSQLiteTypeName2ADDataType
For case ABaseTypeName = ' VARCHAR'
Lines
SetLen (AOptions. FormatOptions. MaxStringSize, False);
AType: = dtAnsiString;
Replaced on
SetLen (AOptions. FormatOptions. MaxStringSize, True);
AType: = dtWideString;
ADQUERY and ADTABLE only in runtime return ' greetings '.