Class TSqlitePassDataset

DescriptionHierarchyFieldsMethodsProperties

Unit

Declaration

type TSqlitePassDataset = class(TDataSet)

Description

The TSqlitePassDataset is a link between your application and the database content. TSqlitePassDataset enables you to access tables, queries, views or even to create direct SQL queries to read and write data from/to your database. It supports almost any kind of fields, including blobs, memo, datetime...etc, and has extended capabilities to quickly sort, filter records (even on calculated or lookup fields). Locate and Lookup is also implemented with some additional methods.

Hierarchy

Overview

Methods

Public Constructor Create(AOwner: TComponent); override;
Public Destructor Destroy; override;
Public Function BookmarkValid(Bookmark: TBookmark): Boolean; override;
Public Function CompareBookmarks(Bookmark1, Bookmark2: TBookmark): Integer; override;
Public Function CreateBlobStream(Field: TField; Mode: TBlobStreamMode): TStream; override;
Public Function GetFieldData(Field: TField; Buffer: Pointer): Boolean; override;
Public function Locate(const LocateStmt: String; Options: TLocateOptions): Boolean; reintroduce; overload;
Public function Locate(const KeyFields: String; const KeyValues: Variant; Options: TLocateOptions): Boolean; overload; override;
Public function LocateFirst: Boolean;
Public function LocateLast: Boolean;
Public function LocateNext: Boolean;
Public function LocatePrior: Boolean;
Public function Lookup(const KeyFields: String; const KeyValues: Variant; const ResultFields: String): Variant; override;
Public function LookupEx(const KeyFields: String; const KeyValues: Variant; const ResultFields: String; const LookUpResultMaxRecordCount: Integer = 0): Variant; overload;
Public function LookupEx(const LookupStmt: String; const ResultFields: String; const LookUpResultMaxRecordCount: integer = 0): Variant; overload;
Public function LookupFirst(var LookupResult: Variant): Boolean;
Public function LookUpLast(var LookupResult: Variant): Boolean;
Public function LookupNext(var LookupResult: Variant): Boolean;
Public function LookupPrior(var LookupResult: Variant): Boolean;
Protected Function AllocRecordBuffer: PRecBuffer; override;
Protected function FindRecord(Restart, GoForward: Boolean): Boolean; override;
Protected Function GetBookmarkFlag(Buffer: PRecBuffer): TBookmarkFlag; override;
Protected Function GetCanModify: Boolean; override;
Protected function GetFieldClass(FieldType: TFieldType): TFieldClass; override;
Protected Function GetRecNo: Integer; override;
Protected Function GetRecord(Buffer: PRecBuffer; GetMode: TGetMode; DoCheck: Boolean): TGetResult; override;
Protected Function GetRecordCount: Integer; override;
Protected Function GetRecordSize: Word; override;
Protected Function IsCursorOpen: Boolean; override;
Public Procedure ApplyChanges;
Public procedure CloseBlob(Field: TField); override;
Public Procedure EmptyTable;
Public Procedure ExecSQL;
Public procedure GetDetailLinkFields(MasterFields, DetailFields: TList);
Public procedure GetFieldList(List: TList; const FieldNames: string);
Public Procedure Index;
Public Procedure Prepare;
Public Procedure Refresh;
Public procedure RefreshLocate;
Public Procedure SetFieldData(Field: TField; Buffer: Pointer); override;
Public Procedure Sort;
Public Procedure UnPrepare;
Public Procedure UpdateParamsList;
Protected procedure AllocateBLOBPointers(Buffer: pAnsiChar);
Protected Procedure ClearCalcFields(Buffer: PRecBuffer); override;
Protected procedure DefineProperties(Filer: TFiler); override;
Protected procedure DoOnNewRecord; override;
Protected procedure FreeBlobPointers(Buffer: pAnsiChar);
Protected Procedure FreeRecordBuffer(var Buffer: PRecBuffer); override;
Protected procedure FreeRecordPointers(Buffer: pAnsiChar);
Protected Procedure GetBookmarkData(Buffer: pAnsiChar; Data: Pointer); override;
Protected Procedure InternalAddRecord(Buffer: Pointer; Append: Boolean); override;
Protected Procedure InternalCancel; override;
Protected Procedure InternalClearFieldDefs;
Protected Procedure InternalClose; override;
Protected Procedure InternalDelete; override;
Protected Procedure InternalEdit; override;
Protected Procedure InternalFirst; override;
Protected Procedure InternalGotoBookmark(ABookmark: Pointer); override;
Protected Procedure InternalHandleException; override;
Protected Procedure InternalInitFieldDefs; override;
Protected Procedure InternalInitIndexDefs;
Protected Procedure InternalInitRecord(Buffer: PRecBuffer); override;
Protected procedure InternalInsert; override;
Protected Procedure InternalLast; override;
Protected Procedure InternalOpen; override;
Protected Procedure InternalPost; override;
Protected Procedure InternalRefresh; override;
Protected Procedure InternalSetToRecord(Buffer: PRecBuffer); override;
Protected Procedure Loaded; override;
Protected Procedure Notification(AComponent: TComponent; Operation: TOperation); Override;
Protected procedure ProcessFilterText;
Protected Procedure RefreshActive;
Protected Procedure RefreshFilteredRecords;
Protected Procedure SetBookmarkData(Buffer: pAnsiChar; Data: Pointer); override;
Protected Procedure SetBookmarkFlag(Buffer: PRecBuffer; Value: TBookmarkFlag); override;
Protected procedure SetFiltered(Value: Boolean); override;
Protected procedure SetFilterOptions(Value: TFilterOptions); override;
Protected procedure SetFilterText(Const Value: String); override;
Protected Procedure SetRecNo(Value: Integer); override;
Protected Procedure UpdateIndexDefs; override;
Protected Procedure UpdateInternalFieldsInfos;

Properties

Published property Active;
Published property AfterCancel;
Published property AfterClose;
Published property AfterDelete;
Published property AfterEdit;
Published property AfterInsert;
Published property AfterOpen;
Published property AfterPost;
Published property AfterScroll;
Published property AutoCalcFields;
Published property BeforeCancel;
Published property BeforeClose;
Published property BeforeDelete;
Published property BeforeEdit;
Published property BeforeInsert;
Published property BeforeOpen;
Published property BeforePost;
Published property BeforeScroll;
Published property CalcDisplayedRecordsOnly: Boolean Read FCalcDisplayedRecordsOnly Write SetFCalcDisplayedRecordsOnly;
Published property Database: TSqlitePassDatabase Read GetFDatabase Write SetFDatabase;
Published property DatabaseAutoActivate: Boolean Read FDatabaseAutoActivate Write FDatabaseAutoActivate;
Published property DatasetName: String Read FDatasetName Write SetFDatasetName;
Public property DatasetType: TSqlitePassDatasetType Read FDatasetType;
Published property Filter;
Published property Filtered;
Published property FilterMode: TSqlitePassFilterMode Read FFilterMode Write FFilterMode;
Published property FilterOptions;
Published property FilterRecordLowerLimit: Integer Read FFilterRecordLowerLimit Write SetFFilterRecordLowerLimit;
Published property FilterRecordUpperLimit: Integer Read FFilterRecordUpperLimit Write setFFilterRecordUpperLimit;
Public property Filters: TSqlitePassFieldFilters Read FFieldFilters Write FFieldFilters;
Published property IndexDefs: TSqlitePassDatasetIndexDefs Read FIndexDefs Write FIndexDefs;
Published property Indexed: Boolean Read FInMemoryIndexed Write SetFInMemoryIndexed;
Published property IndexedBy: String Read FInMemoryIndexedBy Write SetFInMemoryIndexedBy;
Public property Indexes: TSqlitePassInMemoryIndexes Read FInMemoryIndexes Write FInMemoryIndexes;
Public property LocateFilters: TSqlitePassFieldFilters read FLocateFieldFilters write FLocateFieldFilters;
Public property LocateMoveState: TGetResult read GetLocateMoveState;
Published property LocateSmartRefresh: Boolean Read FLocateSmartRefresh Write FLocateSmartRefresh;
Published property LookUpCache: Boolean Read FLookUpCache Write SetFLookUpCache;
Published property LookUpDisplayedRecordsOnly: Boolean Read FLookUpFieldsDisplayedRecordsOnly Write SetFLookUpFieldsDisplayedRecordsOnly;
Public property LookupFilters: TSqlitePassFieldFilters read FLookupKeyFieldFilters write FLookupKeyFieldFilters;
Public property LookupMoveState: TGetResult read GetLookupMoveState;
Published property LookUpSmartRefresh: Boolean Read FLookUpSmartRefresh Write FLookUpSmartRefresh;
Published property MasterFields: String read GetMasterFields write SetMasterFields;
Published property MasterSource: TDataSource read GetMasterDataSource write SetMasterDataSource;
Published property MasterSourceAutoActivate: Boolean read FMasterAutoActivate write FMasterAutoActivate;
Published property OnCalcFields;
Published property OnDeleteError;
Published property OnEditError;
Published property OnFilterRecord : TSqlitePassDatasetFilterRecordEvent read FOnFilterRecord write FOnFilterRecord;
Published property OnMasterChanged: TFieldNotifyEvent read FOnMasterChanged write FOnMasterChanged;
Published property OnNewRecord;
Published property OnPostError;
Published property ParamCheck: Boolean Read FParamCheck Write FParamCheck;
Published property Params: TSqlitePassParams read FParams Write FParams;
Public property Prepared: Boolean read GetFPrepared write SetFPrepared;
Published property ReadOnly: Boolean Read GetFReadOnly Write SetFReadOnly default False;
Published property RecordsCacheCapacity: TSqlitePassRecordsCacheCapacity Read FRecordsCacheCapacity Write FRecordsCacheCapacity;
Published property Sorted: Boolean Read FSorted Write SetFSorted;
Published property SortedBy: String Read FSortedBy Write SetFSortedBy;
Public property SortMode: TSqlitePassSortMode Read FSortMode Write SetFSortMode;
Published property SQL: TStringList Read FSQL Write SetFSQL;
Public property SQLSelectStmt: TSqlitePassSelectStmt Read FSQLSelectStmt;
Published property VersionInfo: TSqlitePassDatasetVersionInfo Read FVersionInfo Write FVersionInfo;
Published property WriteMode: TSqlitePassWriteMode Read FWriteMode Write SetFWriteMode;

Description

Methods

Public Constructor Create(AOwner: TComponent); override;

—– Constructor - Destructor —–

Public Destructor Destroy; override;
 
Public Function BookmarkValid(Bookmark: TBookmark): Boolean; override;

Call the BookmarkValide method to check if a given bookmark is still valid This function tests bookmarks for validity and indicate when a bookmark is valid by returning True.

Public Function CompareBookmarks(Bookmark1, Bookmark2: TBookmark): Integer; override;

Compares two bookmark and returns

  • -1 if Bookmark1 < Bookmark2

  • 0 if Bookmark1 = Bookmark2

  • 1 if Bookmark1 > Bookmark2

Public Function CreateBlobStream(Field: TField; Mode: TBlobStreamMode): TStream; override;

Classic TDataset behavior

Public Function GetFieldData(Field: TField; Buffer: Pointer): Boolean; override;

GetFieldData Puts the data for field 'Field' from the active buffer into Buffer. This is called whenever a field value is demanded by a TField, so it must be efficient. the TDataset class doesnt know anything about how our record buffer is organized. The next question then becomes that if the TDataset class doesnt know about our record structure, how it does it pull out field values from this structure? How it does it put them back in when the user is editing a record? The answer is that it uses the GetFieldData and SetFieldData methods which a custom dataset class must override. These methods are called by the Tdataset.fields when it needs to retrieve or set a specific field value from the current record buffer. GetFieldData is prototyped as follows:

function TDataset.GetFieldData(Field: TField; Buffer: Pointer): Boolean;

The Field parameter is the field for which the value needs to be retrieved We send back the needed value in the buffer. Returns False if a null value was retrieved

Public function Locate(const LocateStmt: String; Options: TLocateOptions): Boolean; reintroduce; overload;
 
Public function Locate(const KeyFields: String; const KeyValues: Variant; Options: TLocateOptions): Boolean; overload; override;

The Locate function works as the classic one, with the same parameters, but has extended features to provide navigation in Located records with LocateFirst, LocateNext, LocatePrior, LocateLast, LocateRecordCount

Public function LocateFirst: Boolean;

Moves to the first record matching Locate parameters

Public function LocateLast: Boolean;

Moves to the last record matching Locate parameters

Public function LocateNext: Boolean;

Moves to the next record matching Locate parameters.

Public function LocatePrior: Boolean;

Moves to the prior record matching Locate parameters

Public function Lookup(const KeyFields: String; const KeyValues: Variant; const ResultFields: String): Variant; override;

Implementation of the classic TDataset.Lookup function

Public function LookupEx(const KeyFields: String; const KeyValues: Variant; const ResultFields: String; const LookUpResultMaxRecordCount: Integer = 0): Variant; overload;

LookupEx function is a extension of classic lookup function in order to provide 'multiple rows' lookup results. It has got two syntaxes : The first syntaxe works as quite the sames parameters as the TDataset.Lookup classic one, but has extended features to provide navigation in LookedUp records with LookupFirst, LookupNext, LookupPrior, LookupLast. Additional Parameters : LookUpResultMaxRecordCount is used to limit the number of records returned by the Lookup function in order to improve speed with large tables. Note : Strings case sensitive lookup is set by the lookupFilters.Options property

Public function LookupEx(const LookupStmt: String; const ResultFields: String; const LookUpResultMaxRecordCount: integer = 0): Variant; overload;

The second LookupEx function takes a complete Lookup Statement as first parameter. The others parameters remains the same

Public function LookupFirst(var LookupResult: Variant): Boolean;

Moves to the first record matching lookup parameters

Public function LookUpLast(var LookupResult: Variant): Boolean;

Moves to the last record matching lookup parameters

Public function LookupNext(var LookupResult: Variant): Boolean;

Moves to the next record matching lookup parameters

Public function LookupPrior(var LookupResult: Variant): Boolean;

Moves to the prior record matching lookup parameters

Protected Function AllocRecordBuffer: PRecBuffer; override;
 
Protected function FindRecord(Restart, GoForward: Boolean): Boolean; override;
 
Protected Function GetBookmarkFlag(Buffer: PRecBuffer): TBookmarkFlag; override;

Procedure InternalMoveToBookmark(Bookmark: Pointer);

Protected Function GetCanModify: Boolean; override;
 
Protected function GetFieldClass(FieldType: TFieldType): TFieldClass; override;

—— ——-

Protected Function GetRecNo: Integer; override;
 
Protected Function GetRecord(Buffer: PRecBuffer; GetMode: TGetMode; DoCheck: Boolean): TGetResult; override;
 
Protected Function GetRecordCount: Integer; override;

—– Records infos —–

Protected Function GetRecordSize: Word; override;
 
Protected Function IsCursorOpen: Boolean; override;
 
Public Procedure ApplyChanges;

—– Apply or Cancel records modification if WriteMode is wmPostpone —–

Public procedure CloseBlob(Field: TField); override;

Classic TDataset behavior

Public Procedure EmptyTable;
 
Public Procedure ExecSQL;

Classic TQuery behavior.

Public procedure GetDetailLinkFields(MasterFields, DetailFields: TList);

—– Master - Detail —–

Public procedure GetFieldList(List: TList; const FieldNames: string);

Override the TDataset.GetFieldList method to accept quoted field names

Public Procedure Index;

Indexes the dataset in memory, using the fields and index order defined in the IndexedBy property

Public Procedure Prepare;

Classic TQuery behavior.

Public Procedure Refresh;

—– —–

Public procedure RefreshLocate;
 
Public Procedure SetFieldData(Field: TField; Buffer: Pointer); override;

seealso(GetFieldData)

Public Procedure Sort;

Sorts the dataset using the fields and sort order defined in the SortedBy property

Public Procedure UnPrepare;

Classic TQuery behavior.

Public Procedure UpdateParamsList;

Retrieve the params list from the current SQL statement

Protected procedure AllocateBLOBPointers(Buffer: pAnsiChar);

—– Blobs —–

Protected Procedure ClearCalcFields(Buffer: PRecBuffer); override;

—– —–

Protected procedure DefineProperties(Filer: TFiler); override;

—— Read and Write Params properties ——-

Protected procedure DoOnNewRecord; override;
 
Protected procedure FreeBlobPointers(Buffer: pAnsiChar);
 
Protected Procedure FreeRecordBuffer(var Buffer: PRecBuffer); override;
 
Protected procedure FreeRecordPointers(Buffer: pAnsiChar);
 
Protected Procedure GetBookmarkData(Buffer: pAnsiChar; Data: Pointer); override;

Procedure SetBookmarkStr(const Value: TBookmarkStr); override; -> Keep the inherited

Protected Procedure InternalAddRecord(Buffer: Pointer; Append: Boolean); override;

—– Navigation and Editing —–

Protected Procedure InternalCancel; override;
 
Protected Procedure InternalClearFieldDefs;
 
Protected Procedure InternalClose; override;
 
Protected Procedure InternalDelete; override;
 
Protected Procedure InternalEdit; override;
 
Protected Procedure InternalFirst; override;
 
Protected Procedure InternalGotoBookmark(ABookmark: Pointer); override;

—– Bookmarks —–

Protected Procedure InternalHandleException; override;

—– Miscalleous —–

Protected Procedure InternalInitFieldDefs; override;
 
Protected Procedure InternalInitIndexDefs;
 
Protected Procedure InternalInitRecord(Buffer: PRecBuffer); override;
 
Protected procedure InternalInsert; override;
 
Protected Procedure InternalLast; override;
 
Protected Procedure InternalOpen; override;
 
Protected Procedure InternalPost; override;
 
Protected Procedure InternalRefresh; override;
 
Protected Procedure InternalSetToRecord(Buffer: PRecBuffer); override;
 
Protected Procedure Loaded; override;

—– Override methods from TDataset —–

Protected Procedure Notification(AComponent: TComponent; Operation: TOperation); Override;
 
Protected procedure ProcessFilterText;
 
Protected Procedure RefreshActive;

Fetch data from physical Database

Protected Procedure RefreshFilteredRecords;

—– Filtering —–

Protected Procedure SetBookmarkData(Buffer: pAnsiChar; Data: Pointer); override;
 
Protected Procedure SetBookmarkFlag(Buffer: PRecBuffer; Value: TBookmarkFlag); override;

From TDataset

Protected procedure SetFiltered(Value: Boolean); override;
 
Protected procedure SetFilterOptions(Value: TFilterOptions); override;
 
Protected procedure SetFilterText(Const Value: String); override;
 
Protected Procedure SetRecNo(Value: Integer); override;
 
Protected Procedure UpdateIndexDefs; override;
 
Protected Procedure UpdateInternalFieldsInfos;
 

Properties

Published property Active;

Classic dataset behavior. When set to True, opens the dataset and displays data if dataware components are linked to the datasource. When set to False, closes the dataset and frees the memory used to store dataset records.

Published property AfterCancel;
 
Published property AfterClose;
 
Published property AfterDelete;
 
Published property AfterEdit;
 
Published property AfterInsert;
 
Published property AfterOpen;
 
Published property AfterPost;
 
Published property AfterScroll;
 
Published property AutoCalcFields;

Classic dataset behavior

Published property BeforeCancel;
 
Published property BeforeClose;
 
Published property BeforeDelete;
 
Published property BeforeEdit;
 
Published property BeforeInsert;
 
Published property BeforeOpen;

Published property BeforePost;
 
Published property BeforeScroll;
 
Published property CalcDisplayedRecordsOnly: Boolean Read FCalcDisplayedRecordsOnly Write SetFCalcDisplayedRecordsOnly;

Enable or Disable the persistent lookupfields

Published property Database: TSqlitePassDatabase Read GetFDatabase Write SetFDatabase;

Selects the TsqlitePassDatabase component you want to depend on.

Published property DatabaseAutoActivate: Boolean Read FDatabaseAutoActivate Write FDatabaseAutoActivate;

When set to True, opens automatically the database if needed.

Published property DatasetName: String Read FDatasetName Write SetFDatasetName;

Once you are connected to a database, enters a table name or a query name. At design time, a dialog will let you choose your dataset among all the available database datasets. Any modification done to the SQL property will empty the DatasetName

Public property DatasetType: TSqlitePassDatasetType Read FDatasetType;

This property is read only. It gives you information about the currently selected dataset and can be one of the following values :

dtTable : The dataset is a table.

dtQuery : The dataset is a query.

dtView : The dataset is a view.

dtSQLSelect : The SQL property has been modified or you entered a new SQL statement. The DatasetName is automatically set to '' assuming that the DatasetName and SQL text don't match anymore. Note : The dtSQLSelect DatasetType is changed to dtSQLDirect if the SQL statement cannot be properly analyzed by the internal interpreter.

dtSQLDirect : The SQL staement cannot be properly analyzed by the internal interpreter and operations on dataset are limited : The data returned by the query cannot be modified, sorting and filtering are only supported with in-memory operations (FilterMode = fmDirect and SortMode = smDirect), Master-Detail is only supported when using params as detail(s) field(s).

dtUnkown : The dataset type or SQL statement could not be recognized

Published property Filter;

This dataset allows many kinds of filtering features that will be applied in this priority to the retrieved data :

  1. Original SQL statement using a WHERE or a LIMIT clause to filter records.

  2. Additional WHERE clause defined in the 1st part of the Filter property - when FilterMode in [fmSQL, fmSQLDirect].

  3. FilterRecordLowerLimit applies after the SQL statement is executed.

  4. FilterRecordUpperLimit applies after the SQL statement is executed.

  5. Additional filter defined in the 2nd part of the Filter property - when FilterMode is in [fmDirect, fmSQLDirect].

  6. Master - Detail relationship.

As described, the filter statement itsef can be divided in two parts that are activated depending on the FilterMode property. [1st part = SQL syntax][;][2nd part = Direct syntax]

The goal of the 2nd part is to provide filtering on calculated fields

The 1st part : follows the classic dataset filter behavior and takes a SQL WHERE clause but without the WHERE word at the begining. You can also use wildcard characters as discribed in the Sqlite help. Example #1 : country = 'France' Example #2 : customer like '%cur%'

The ';' semi-colon is used to separate the 1st and the 2nd part

The 2nd part : it uses a custom syntax close to SQL but limited For numeric fields : ComparisonOp : '=, <>, >, >=, <=, <, Null, NotNull' Value = numeric or empty (with Null and NotNull) Example 1 : MyDataset.Filter := MyField1 > 1 and < 10 or = 20; MyField7 <> 5; MyField3 Null;

Example 2 : '=' operator has a special syntax supporting multiple values separated with comma MyDataset.Filter := MyField1 = 1, 10, 20, 30; MyField2 <> 5; MyField7 NotNull;

For Boolean fields ComparisonOp : '=' Value : True or False

Example 1 : MyDataset.Filter := MyField1 = True; MyField2 = False;

For Text fields (including Memo) ComparisonOp : '=, <>, >, >=, <=, <, Null, NotNull' '=' and '<>' accepts '%' or '*' wildcard char. You can use *YourText or %YourText* or YourText% as TextPattern

Example 1 : MyDataset.Filter := MyField1 = %Pasc%; MyField3 NotNull;

For Date, Time, DateTime fields ComparisonOp : '=, <>, >, >=, <=, <, Null, NotNull' Value = numeric or empty (with Null and NotNull) DateFormat : #DD-MM-YYYY# TimeFormat : #hh:mm:ss[.zzz]# DateTimeFormat : #DD-MM-YYYY hh:mm:ss[.zzz]#

Note : '#' can be replaced by single or double quote

Example 1 : MyDataset.Filter := MyField1 = #22-02-2008#; MyField3 NotNull;

Published property Filtered;

Determines whether or not the different filters are activated. The TsqlitePassDataset component can handle three filter levels that will be applied in this priority order :

1 : MasterFields/DetailFields property 2 : Filter property 3 : RecordLowerLimit/RecordUpperLimit properties

Published property FilterMode: TSqlitePassFilterMode Read FFilterMode Write FFilterMode;

Can take one of the following values :

  • fmDirect

  • fmSQL

  • fmSQLDirect - Default

See also
Filter
This dataset allows many kinds of filtering features that will be applied in this priority to the retrieved data :

  1. Original SQL statement using a WHERE or a LIMIT clause to filter records.

  2. Additional WHERE clause defined in the 1st part of the Filter property - when FilterMode in [fmSQL, fmSQLDirect].

  3. FilterRecordLowerLimit applies after the SQL statement is executed.

  4. FilterRecordUpperLimit applies after the SQL statement is executed.

  5. Additional filter defined in the 2nd part of the Filter property - when FilterMode is in [fmDirect, fmSQLDirect].

  6. Master - Detail relationship.

As described, the filter statement itsef can be divided in two parts that are activated depending on the FilterMode property.

Published property FilterOptions;

foCaseInsensitive and foNoPartialCompare applies both to SQL and Internal Filters. You can also override the general setting, if you change the LocateFilters.Options, LookupFilters.Options, LookupFieldFilters.Options or Filters.Options properties

Published property FilterRecordLowerLimit: Integer Read FFilterRecordLowerLimit Write SetFFilterRecordLowerLimit;

FilterRecordLowerLimit is the lower limit of the range filter. If greater than 1, the -nth first records will not be retrieved. In other words, if FilterLowerLimit = 4, the fifth record will be the first one retrieved from the query.

Published property FilterRecordUpperLimit: Integer Read FFilterRecordUpperLimit Write setFFilterRecordUpperLimit;

FilterRecordUpperLimit is the upper limit of the range filter.

If greater than 1, the -nth first records will be retrieved. In other words, if FilterUpperLimit = 4 and FilterLowerLimit = 0 then only the four first records will be retrieved from the query.

If lesser than 0, the -nth last records will be retrieved. In other words, if FilterUpperLimit = -9 and FilterLowerLimit = 0 then only the nine last records will be retrieved from the query.

Public property Filters: TSqlitePassFieldFilters Read FFieldFilters Write FFieldFilters;

A list of internal filters applying to a record

Published property IndexDefs: TSqlitePassDatasetIndexDefs Read FIndexDefs Write FIndexDefs;

The IndexDefs property gives you access to the indexes definitions for the selected table. Indexes are only available if the DatasetType is a 'dtTable' type.

Published property Indexed: Boolean Read FInMemoryIndexed Write SetFInMemoryIndexed;

Determines whether or not the IndexedBy property is activated.

Published property IndexedBy: String Read FInMemoryIndexedBy Write SetFInMemoryIndexedBy;

The IndexedBy property lets you define an internal index used to speed up locate, lookup operations. When a proper index is set, the dataset is directly Indexed in memory, using quicksort. and a fast binary search routine is used to find the requested records. The IndexedBy property always takes a SQL 'ORDER BY' clause but without the 'ORDER BY' expression at the begining
Example : 'car_names ASC, car_types DESC'. Field names with space must be quoted (see TSqlitePassDatabaseOptions.QuoteStyle).


At design time, a dialog will let you create or modify the index.

Public property Indexes: TSqlitePassInMemoryIndexes Read FInMemoryIndexes Write FInMemoryIndexes;

A list of In-Memory Indexes

Public property LocateFilters: TSqlitePassFieldFilters read FLocateFieldFilters write FLocateFieldFilters;
 
Public property LocateMoveState: TGetResult read GetLocateMoveState;

Returns the state of the last move when locating a record

Published property LocateSmartRefresh: Boolean Read FLocateSmartRefresh Write FLocateSmartRefresh;

When set to TRUE, Located records list is updated every time data is refetched from database. If FALSE, the located records list is not synchronized : Located records are lost when data is refetched

Published property LookUpCache: Boolean Read FLookUpCache Write SetFLookUpCache;

Activate or disactivate the LookUpCache

Published property LookUpDisplayedRecordsOnly: Boolean Read FLookUpFieldsDisplayedRecordsOnly Write SetFLookUpFieldsDisplayedRecordsOnly;

Enable or Disable the persistent lookupfields. You should turn on thos option if you need to sort lookupfields

Public property LookupFilters: TSqlitePassFieldFilters read FLookupKeyFieldFilters write FLookupKeyFieldFilters;

Collection of lookup Filters

Public property LookupMoveState: TGetResult read GetLookupMoveState;

Returns the state of the last lookup move

Published property LookUpSmartRefresh: Boolean Read FLookUpSmartRefresh Write FLookUpSmartRefresh;

Automatically Resfreshes the Lookup Results when dataset is changed or refetch from database

Published property MasterFields: String read GetMasterFields write SetMasterFields;

Classic table MasterFields behavior.

At design time, a dialog will let you create or modify the relation between MasterFields and DetailFields.

A relation is defined like this : MasterFieldName=DetailFieldName If you want set several relations, they must be separated by a ';' MasterFieldName1=DetailFieldName1;MasterFieldName2=DetailFieldName2

Published property MasterSource: TDataSource read GetMasterDataSource write SetMasterDataSource;

Classic table MasterSource behavior.

Published property MasterSourceAutoActivate: Boolean read FMasterAutoActivate write FMasterAutoActivate;

When set to TRUE, the dataset will try to automatically open the master dataource if necessary

Published property OnCalcFields;
 
Published property OnDeleteError;
 
Published property OnEditError;
 
Published property OnFilterRecord : TSqlitePassDatasetFilterRecordEvent read FOnFilterRecord write FOnFilterRecord;

Property OnExportProgress: TSqlitePassDatasetExportProgressEvent Read FOnExportProgress Write FOnExportProgress; Property OnImportProgress: TSqlitePassDatasetImportProgressEvent Read FOnImportProgress Write FOnImportProgress;

Published property OnMasterChanged: TFieldNotifyEvent read FOnMasterChanged write FOnMasterChanged;

OnMasterChanged is triggered when MasterSource records changes (scroll, data changes, etc...). The concerned field is send as parameter to the event handler.

Published property OnNewRecord;
 
Published property OnPostError;
 
Published property ParamCheck: Boolean Read FParamCheck Write FParamCheck;

When set to True or at DesignTime, Params are automatically updated from SQL Statement. Once your params are properly defined, you should turn this property to false, especially when params value are linked to master fields

Published property Params: TSqlitePassParams read FParams Write FParams;

Params give you access to the queries or sql statements parameters. They are very similar to classic TParams. You can easily retrieve and automatically bind your params to the appropriate field datatype if you suffixe your params names (in the sql statement) like this : br MyParamName + As + Datatype. br For example, "select * from MyQuerie where MyFieldDate = :MyParam1AsDate and MyFieldCode :MyParam2AsInteger;". br Supported datatype are AsWidestring, AsString, AsSmallint, AsLargeint, AsInteger, AsWord, AsFloat, AsBoolean, AsCurrency, AsBcd, AsDatetime, AsDate, AsTime, AsVarbytes, AsBytes, AsAutoinc, AsFmtmemo, AsMemo, AsBlob, AsGraphic, AsTypedbinary, AsFixedchar. br Note : Params values are always filled in as litteral text. Date, DateTime, Time...etc values must follow the corresponding Database.DatatypeOptions.DateFormat, DateTimeFormat, TimeFormat template. br Here is a small example : DatasetDetail.Close; DatasetDetail.SQL.Text := 'SELECT * FROM [DateTime] where Date = :ParamDateAsDate;'; DatasetDetail.Params.ParamByName('ParamDateAsDate').Value := '2010/12/10'; DatasetDetail.Open; br You can also define a master/detail relation with parameters. At design time, once your datasetname is selected, set the MasterSource Property and then set the params property to show up the parameters dialog. The Value dropdown list allows you to choose the master field for each param you want to link with. ,

See also
ParamCheck
When set to True or at DesignTime, Params are automatically updated from SQL Statement.
TSqlitePassParam
Param
Public property Prepared: Boolean read GetFPrepared write SetFPrepared;

Classic TQuery behavior.

Published property ReadOnly: Boolean Read GetFReadOnly Write SetFReadOnly default False;

Classic Dataset behavior

Published property RecordsCacheCapacity: TSqlitePassRecordsCacheCapacity Read FRecordsCacheCapacity Write FRecordsCacheCapacity;

Set the number of records that can fit within one memory bloc before the need to alLookup a new records memory bloc. Using a larger RecordsCacheCapacity with large tables or queries should improve loading speed

Published property Sorted: Boolean Read FSorted Write SetFSorted;

Determines whether or not the IndexedBy property is activated.

Published property SortedBy: String Read FSortedBy Write SetFSortedBy;

The IndexedBy property always takes a SQL 'ORDER BY' clause but without the 'ORDER BY' expression at the begining
Example : 'car_names ASC, car_types DESC'. Field names with space must be quoted (see TSqlitePassDatabaseOptions.QuoteStyle).
It has two different sort modes :
When the SortMode property is set to smDirect, (this is the default value) the dataset is directly Indexed in memory, using quicksort. This is fast and handy to sort dates, times, and calculated fields since you don't have to think about date or time format.
When the SortMode property is set to smSQL, the dataset is Indexed using a SQL ORDER BY statement. Calculated fields cannot be Indexed this way.
At design time, a dialog will let you create or modify the sort order.

Public property SortMode: TSqlitePassSortMode Read FSortMode Write SetFSortMode;

Can take one of the following values :

  • smDirect - Default

  • smSQL

See also
SortedBy
The IndexedBy property always takes a SQL 'ORDER BY' clause but without the 'ORDER BY' expression at the begining
Example : 'car_names ASC, car_types DESC'. Field names with space must be quoted (see TSqlitePassDatabaseOptions.QuoteStyle).
Published property SQL: TStringList Read FSQL Write SetFSQL;

SQL represents the SQL statement used to retrieve data from the database.

For tables, it will automatically be set to :

SELECT * FROM TableName; if all fields need to be retrieved from the table, or to :

SELECT field1, field2... FROM TableName; if only some fields need to be retrieved from the table.

For queries, it will reflect the query SQL statement. You can also directly write your own SQL statement to fit your needs or to interact directly with the database. In this case, the datasetname propery will be set to '' (empty) and the datasetType will be set to dtDirectSql.

Public property SQLSelectStmt: TSqlitePassSelectStmt Read FSQLSelectStmt;

The internal schema of the current SQL statement. It gives you access to advanced information - read only

Published property VersionInfo: TSqlitePassDatasetVersionInfo Read FVersionInfo Write FVersionInfo;

Returns the dataset component version number

Published property WriteMode: TSqlitePassWriteMode Read FWriteMode Write SetFWriteMode;

need to write doc...


Generated by PasDoc 0.11.0 on 2010-09-10 15:54:38