|
|
| Howto ... Create a Full Text index at runtime, using CreateTable()? |
The fulltext indexing is not supported for Unicode fields for TurboDB version 4.19 (and lower) and following
workaround is needed. To create a fulltext index at runtime you have to perform the following steps:
1. Create the keyword table
The structure has to be: string, smallint, autoinc:
FIndexTable := TTdbTable.Create(nil);
FIndexTable.DatabaseName := 'DB1';
FIndexTable.TableName := 'FTIndex';
FIndexTable.FieldDefsTdb.Add('Keyword', dtString, 30);
FIndexTable.FieldDefsTdb.Add('Frequency', dtSmallInt);
FieldDef := FIndexTable.FieldDefsTdb.Add('RecordId', dtAutoInc);
FieldDef.Specification := 'Keyword';
FIndexTable.CreateTable;
2. Create the main table containing a relation field, the linked table is the keyword table:
FMainTable := TTdbTable.Create(nil);
FMainTable.DatabaseName := 'DB1';
FMainTable.TableName := 'FTMaster';
FieldDef := FMainTable.FieldDefsTdb.Add('RecordId', dtAutoInc);
FieldDef.Specification := 'RecordId';
FMainTable.FieldDefsTdb.Add('Alpha1', dtString, 30);
FMainTable.FieldDefsTdb.Add('Alpha2', dtString, 255);
FMainTable.FieldDefsTdb.Add('Memo1', dtMemo);
FMainTable.FieldDefsTdb.Add('Memo2', dtMemo);
FieldDef := FMainTable.FieldDefsTdb.Add('Keywords', dtRelation);
FieldDef.Specification := 'FTIndex.dat';
FMainTable.CreateTable;
// tell the main table that there is a fulltext table
FMainTable.FullTextTable := FIndexTable;
3. Call UpdateFulltextIndex method:
FMainTable.UpdateFulltextIndex('Alpha1,Alpha2,Memo1,Memo2', 'Keywords', '', 1000);
Tested with TurboDB 4 für VCL.
| | Howto ... Change the TurboDB generated errormessages? |
Make the appropriate changes in **tdbmessages.pas**. See also TurboDb helpfile.
| | Howto ... define the AutoInc Expression using the SQL Create Table command> |
Example: Feld: MyName Char(20) and Feld: MyTown Char(20)
CREATE TABLE MyTable (
MyName CHAR(20),
MyTown CHAR(20),
RecordId AUTOINC("MyName:10,MyTown:10")
)
Tested with TurboDB 4 für VCL.
| | Howto ... use within Turbo-SQL the Option UNIQUE to avoid duplicate entries> |
Use the keyword UNIQUE
CREATE UNIQUE INDEX MyIndex ON MyTable (MyName)
Tested with TurboDB 4 für VCL.
| | Howto ... define a filter on an empty datefield and the same for a blobfield? |
Filter = 'not DateField' / Filter = 'not BlobField'
Note: following filter do not work: Table1.Filter := 'Datefield = 0' or Table1.Filter := 'Datefield = '''
| | Howto ... use NOT NULL in AutoInc fields? |
This is NOT allowed, since autoinc fields can not be zero
Note: use for example unique to avoid duplicate entries ALTER TABLE bs MODIFY MyAutoIncField AUTOINC UNIQUE
Tested with TurboDB 4 für VCL.
| | Howto ... optimize SQL statements? |
Parsing of long queries take their time. To optimize use parameter.
Example: TdbQuery.SQL.Text :
INSERT INTO MyTable (Field1, MemoField1) VALUES("XXX", :MyMemo)
During SQL statement execution the memocontent can be set using
TdbQuery.ParamByName('MyMemo').Value := 'this is the 50K memocontent...';
This statement will not be parsed everytime, because it is clear which data needs to be filled in.
The data will be written directly.
Tested with TurboDB 4 für VCL.
| | Howto ... change the specification of a field at runtime? |
Use (table1.FieldDefsTdb.Add('MyRealField', dtFloat)).Precision := 1;
Tested with TurboDB 4 für VCL.
| | Howto ... make sure entries are not set as null? |
SQL : ALTER TABLE Table MODIFY MyField BOOLEAN NOT NULL
TDBFieldDef : set property TdbFieldDef.Required to true
| | Howto ... EDatabaseError exception and Table closure? |
Call the new database method CloseCachedTables after setting Active to False.
Active := False
CloseCachedTables
Tested with TurboDB 4 für VCL.
| | Howto ... repair all indices in a single file database? |
Use
for d := 0 to TdbDatabase1.DataSetCount - 1 do begin
with TdbDatabase1.DataSets[d] as TTdbTable do begin
for i := 0 to IndexDefs.Count - 1 do begin
UpdateIndex(TdbTable1.IndexDefs[i].Name);
end;
end;
end;
Tested with TurboDB 4 für VCL.
|
Komponenten TurboDB und TMS Component Pack
»
| 