[fpc-pascal] New local-indexes support for sqldb

Joost van der Sluis joost at cnoc.nl
Tue Feb 12 21:38:20 CET 2008

Hi all,

fpc 2.3.1 and 2.2.1 now have local indexes support for sqldb.

I would like it if you can test if it works ok for you before we release
fpc 2.2.2.

Usage is pretty simple. For example use
"ASQLQuery.AddIndex('indexname','lastname,firstname);" to add an index
named 'indexname' ordered by the fields lastname,firstname.

Then set the active index to this index using: "ASQLQuery.IndexName :=
'indexname';" If you do this and the dataset is active the current
record will stay the same. But the records before and after it can
differ, offcourse.

You can add as many indexes as you want, as long as the TSQLQuery is
closed. When TSQLQuery is opened, you can add up to
TSQLQuery.MaxIndexesCount indexes. You can set the value of this
property when the dataset is closed.
This is because the memory for each indexes is allocated at the moment
that the TSQLQuery is opened. 

The default value of MaxIndexesCount is 2. By default there is always
one default index, which maintains the order in which the records are
loaded into memory. And there is a temporary index. 

To use this temporary index, you can simply set
TSQLQuery.IndexFieldNames to the fields you want to base the index on.
When you do that the dataset is automatically uses a temporary index
based on that field. If you don't want to use this index anymore, set
the property to an empty string, or set the IndexName property to
another index.

After the data is loaded into memory after open, the indexes are build
using a mergesort algorithm.

When you edit (or append/insert) a record, the record is placed into the
right place of all indexes when 'TSQLQuery.Post' is called. (which is
called automatically before you scroll throught the database, or closes

To see which local indexes are defined, you can use 'tsqlquery.indexes'.
Not that the sqldb version from fpc 2.2.0 stored the index-definitions
on the server in that place. Now you can find these index-definitions in
the TSQLQuery.ServerIndexDefs property.

These local indexes are not (yet) used for searching records. And it's
not possible to search case-insensitive or partially. That'll be
implemented soon.

I hope that this is enough information to give a head start... ;)


(for the purists: the local-indexes are implemented in TBufDataset, not

More information about the fpc-pascal mailing list