Unit rdbtool

Description

Uses

Classes, Interfaces, Objects and Records

Functions and Procedures

Types

Constants

Variables

Description

rdbtool unit contains set of usefull database functions used by all other units.

Updated version compatible with Delphi2010+.

Overview

Functions and Procedures

function GetSearchHintText(SearchText, NewKey: string; SearchingType: TSearchingType; Found, AllowFilter, Filtered: boolean): string;

function GetDataSet(GridOrDSet: TObject): TDataSet;

function GetDSStateStr(GridOrDSet: TObject): string;

function IsClientDataset(Dataset: TDataSet): boolean;

function IsAdvantageDatabaseDataset(Dataset: TDataSet): boolean;

procedure AssignGridProps(Source, Dest: TCustomDBGrid);

function IsEditing(GridOrDSet: TObject): boolean;

function IsDataField(Fld: TField): boolean;

function IsSimpleField(Fld: TField): boolean;

function IsApostrField(Fld: TField): boolean;

function IsMemoField(Fld: TField): boolean;

function IsGraphicField(Fld: TField): boolean;

function IsDateTimeField(Fld: TField): boolean;

function IsDateField(Fld: TField): boolean;

function IsTimeField(Fld: TField): boolean;

function GetBooleanFieldTextValue(Fld: TField; State: boolean): string;

function GetBooleanFieldState(Fld: TField; TextValue: string): boolean;

function GetFldDefs(GetLabels, VisibleFieldOnly, SimpleField, MemoField, PictureField, AllField, DataFieldOnly: boolean): TGetFldDefs;

function GetFldListDBGrid(DBGrid: TCustomDBGrid; List: TStrings; FldsDef: TGetFldDefs): integer;

function GetFldListDataset(Dataset: TDataSet; List: TStrings; FldsDef: TGetFldDefs): integer;

function GetFldListRecView(RecView: TrDBRecView; List: TStrings; FldsDef: TGetFldDefs): integer;

function GetFldListStringGrid(StringGrid: TrStringGridEd; List: TStrings; FldsDef: TGetFldDefs): integer;

function GetFldList(O: TObject; List: TStrings; FldsDef: TGetFldDefs): integer;

function GetFldListValues(Dataset: TDataSet; const FldList: string; const ListSeparator: string = ';'; const ConstMarks: string = '"'): string;

function ShowRecordCount(GridOrDSet: TObject): boolean;

function LocateRecord(F: TField; Value: Variant; CaseSensitive: boolean = false): boolean;

function LocateRecordText(F: TField; const Value: string): boolean;

function FillTextMask(const Mask: string; Dataset: TDataSet; const MaskStrStart: string = '<<'; const MaskStrStop: string = '>>'; const ReplaceNullValue: string = ''; SkipNonExistingFields: boolean = false): string;

procedure AddRecordEx(G: TCustomDBGrid; Insert: boolean = false);

procedure DuplicRecord(GridOrDSet: TObject; TagToDuplic: integer; DuplicBeforeAfterInsert: boolean);

function GetSQLLine(SQL: TSQLStrings; const KeyWord: string): string;

function GetSQLWhere(SQL: TSQLStrings): string;

function GetSQLTableName(SQL: TSQLStrings): string;

function SetSQLLine(SQL: TSQLStrings; const KeyWord, NewLine: string): boolean;

function SetSQLOrder(SQL: TSQLStrings; const FieldName: string; Desc: boolean): boolean;

function SetSQLWhere(SQL: TSQLStrings; const Condition: string): boolean; overload;

function SetSQLWhere(SQL: TSQLStrings; const FormatCondition: string; Value: Variant): boolean; overload;

function SetSQLWhere(SQL: TSQLStrings; Field: TField): boolean; overload;

function SetSQLWhere(SQL: TSQLStrings; Field: TField; Value: Variant): boolean; overload;

function AddSQLWhereOR(SQL: TSQLStrings; const Condition: string): boolean;

function AddSQLWhereAND(SQL: TSQLStrings; const Condition: string): boolean;

function MakeSQLCond(Field: TField): string; overload;

function MakeSQLCond(Field: TField; Value: Variant): string; overload;

function MakeSQLCond(FormatCondition: string; Value: Variant): string; overload;

function MakeSQLCondOR(const Params: array of string): string;

function MakeSQLCondAND(const Params: array of string): string;

function MakeSQLCondIsNull(Field: TField): string; overload;

function MakeSQLCondIsNull(Field: TField; Value: Variant): string; overload;

function MakeSQLCondLike(const FieldName, Text: string; CompareType: TrLikeCompareType): string;

function FieldNameToCaption(Grid: TCustomDBGrid; const FieldName: string): string; overload;

function FieldNameToCaption(Dataset: TDataSet; const FieldName: string): string; overload;

function FieldListToCaptionList(Grid: TCustomDBGrid; const FieldList, Separator: string): string; overload;

function FieldListToCaptionList(Dataset: TDataSet; const FieldList, Separator: string): string; overload;

procedure FileToBlob(Fld: TField; const FileName: string);

procedure BlobToFile(Fld: TField; const FileName: string);

function ScanAllRecords(Dataset: TDataSet; CallBack: TScanCallBackProc; DisableControls: boolean = true; BlockEvents: boolean = true; UseBookmark: boolean = true): boolean;

function LoadRecordList(CaptionField, IdField: TField; L: TStrings): integer;

procedure GenerateSQLScript(SqlCmd: TSQLStrings; DS: TDataSet; const TableName: string; KeyFieldList, FieldList: TStrings; GenerateInsert, AllRecords: boolean; AddAfterEachRecord: string = '');

procedure GenerateSaveSQLScript(SqlCmd: TSQLStrings; DS: TDataSet; const TableName: string; KeyFieldList, FieldList: TStrings; GenerateInsert, AllRecords: boolean; FileName: string);

function GetQuotedTableName(TableName: string): string;

function GetQuotedFieldName(FieldName: string): string;

function GetFilterWildCardChar(Dataset: TDataSet; SQLFormat: boolean = false): Char;

function GetFilterLikeOperator(Dataset: TDataSet; SQLFormat: boolean = false): string;

function GetFilterNullEqualStr(Dataset: TDataSet): string;

function SetFilterString(Dataset: TDataSet; const Filter: string; ShowError: boolean): boolean;

procedure ClearFilter(const GridOrDataSet: TObject);

function GetFilterCond(Field: TField; const Value: string; IsNull: boolean; Condition: string = ''): string;

function FilterByField(Field: TField; FilterJoin: TFilterJoin = fjNone): boolean;

function FilterBySel(const Grid: TCustomDBGrid; FilterJoin: TFilterJoin = fjNone): boolean;

function NegateFilter(const GridOrDataSet: TObject): boolean;

function GetNumericFieldFilterValue(Value: extended): string;

function GetDateFieldFilterValue(Value: TDateTime): string;

function MakeFilterCond(FldName, Cond, Value: string; AddSep: string = ''): string;

function MakeFilterBetweenCond(FldName, ValueFrom, ValueTo: string; AddSep: string = ''): string;

Types

TSQLStrings = TStrings;

TFilterJoin = (...);

TrLikeCompareType = (...);

TScanCallBackProc = procedure(Dataset: TDataSet; RecIndex: integer; var Abort: boolean) of object;

Constants

sfSimpleFld: Set of TFieldType = ([ftString, ftWideString, ftSmallint, ftInteger, ftWord, ftBoolean, ftFloat, ftCurrency, ftBCD, ftDate, ftTime, ftDateTime,
    ftAutoInc, ftLargeint, ftTimeStamp, ftFMTBcd, ftLongWord, ftShortint, ftByte, ftExtended, ftSingle, ftGuid]);

Description

Functions and Procedures

function GetSearchHintText(SearchText, NewKey: string; SearchingType: TSearchingType; Found, AllowFilter, Filtered: boolean): string;

return localized hint string

function GetDataSet(GridOrDSet: TObject): TDataSet;

return Dataset from object of type Dataset, Datasource or DBGrid

function GetDSStateStr(GridOrDSet: TObject): string;

return localized text of Dataset state

function IsClientDataset(Dataset: TDataSet): boolean;

indicate that Dataset is TClientDataset

function IsAdvantageDatabaseDataset(Dataset: TDataSet): boolean;

indicate that Dataset is Advantage Database

procedure AssignGridProps(Source, Dest: TCustomDBGrid);

assign colors from one grid to another

function IsEditing(GridOrDSet: TObject): boolean;

return whether object is in editing state

function IsDataField(Fld: TField): boolean;

return whether Field is ftData type

function IsSimpleField(Fld: TField): boolean;

return whether Field is simple field (text, number, date)

function IsApostrField(Fld: TField): boolean;

return whether Field needs appostrophe in Filter

function IsMemoField(Fld: TField): boolean;

return whether Field is Memo field, result depends also on RosiCompConfig.rDBToolCfg.BlobField_ftBlobAsMemo config value

function IsGraphicField(Fld: TField): boolean;

return whether Field is Graphics field

function IsDateTimeField(Fld: TField): boolean;

return whether Field is DateTime type

function IsDateField(Fld: TField): boolean;

return whether Field is Date type

function IsTimeField(Fld: TField): boolean;

return whether Field is Time type

function GetBooleanFieldTextValue(Fld: TField; State: boolean): string;

return defined text value for Boolean Field and defined State

function GetBooleanFieldState(Fld: TField; TextValue: string): boolean;

return state according defined TextValue for Boolean Field

function GetFldDefs(GetLabels, VisibleFieldOnly, SimpleField, MemoField, PictureField, AllField, DataFieldOnly: boolean): TGetFldDefs;

set FldsDef according defined parameters

function GetFldListDBGrid(DBGrid: TCustomDBGrid; List: TStrings; FldsDef: TGetFldDefs): integer;

load list of field names or labels from the object according FldsDef

function GetFldListDataset(Dataset: TDataSet; List: TStrings; FldsDef: TGetFldDefs): integer;

function GetFldListRecView(RecView: TrDBRecView; List: TStrings; FldsDef: TGetFldDefs): integer;

function GetFldListStringGrid(StringGrid: TrStringGridEd; List: TStrings; FldsDef: TGetFldDefs): integer;

function GetFldList(O: TObject; List: TStrings; FldsDef: TGetFldDefs): integer;

load list of field names or labels from the object according FldsDef

function GetFldListValues(Dataset: TDataSet; const FldList: string; const ListSeparator: string = ';'; const ConstMarks: string = '"'): string;

load list of field values from the object according FldsDef

function ShowRecordCount(GridOrDSet: TObject): boolean;

show dialog with number of records

function LocateRecord(F: TField; Value: Variant; CaseSensitive: boolean = false): boolean;

locate record according Field and Value, return if record was found

function LocateRecordText(F: TField; const Value: string): boolean;

locate record according Field and text Value, return if record was found

function FillTextMask(const Mask: string; Dataset: TDataSet; const MaskStrStart: string = '<<'; const MaskStrStop: string = '>>'; const ReplaceNullValue: string = ''; SkipNonExistingFields: boolean = false): string;

replace all Tags in Mask text by Dataset field values; possible tags: <<FieldName>> or <<FieldName:format>> <<FieldName:20>> or <<FieldName:L20>> - aling left to 20 chars <<FieldName:R20>> - aling right to 20 chars <<FieldName:C20>> - aling center to 20 chars <<FieldName:T20>> - limit and aling left to 20 chars <<FieldName:TR20>> - limit and aling right to 20 chars <<FieldName:TC20>> - limit and aling center to 20 chars <<#CRLF>> - replace by #13#10 chars <<#xx>> - replace by char(xx) <<#StrPart(FieldName,From,Count)>>

procedure AddRecordEx(G: TCustomDBGrid; Insert: boolean = false);

add or insert new record and select first visible column

procedure DuplicRecord(GridOrDSet: TObject; TagToDuplic: integer; DuplicBeforeAfterInsert: boolean);

duplicate current record for DBGrid or Dataset, all fields which tag = TagToDuplic is duplicated

function GetSQLLine(SQL: TSQLStrings; const KeyWord: string): string;

return line which starts by defined KeyWord

function GetSQLWhere(SQL: TSQLStrings): string;

find and return condition from line which starts by WHERE

function GetSQLTableName(SQL: TSQLStrings): string;

find and return first table name from sql command

function SetSQLLine(SQL: TSQLStrings; const KeyWord, NewLine: string): boolean;

find line which starts by defined KeyWord and replace it by NewLine

function SetSQLOrder(SQL: TSQLStrings; const FieldName: string; Desc: boolean): boolean;

find line which starts by ORDER and replace it by new sorting from FieldName and Desc

function SetSQLWhere(SQL: TSQLStrings; const Condition: string): boolean; overload;

find line which starts by WHERE and replace it by new Condition

function SetSQLWhere(SQL: TSQLStrings; const FormatCondition: string; Value: Variant): boolean; overload;

find line which starts by WHERE and replace it by new condition according FormatCondition and Value

function SetSQLWhere(SQL: TSQLStrings; Field: TField): boolean; overload;

find line which starts by WHERE and replace it by new condition according Field and its current value

function SetSQLWhere(SQL: TSQLStrings; Field: TField; Value: Variant): boolean; overload;

find line which starts by WHERE and replace it by new condition according Field and Value

function AddSQLWhereOR(SQL: TSQLStrings; const Condition: string): boolean;

find line which starts by WHERE and add another condition with OR

function AddSQLWhereAND(SQL: TSQLStrings; const Condition: string): boolean;

find line which starts by WHERE and add another condition with AND

function MakeSQLCond(Field: TField): string; overload;

make sql condition according Field and its current value

function MakeSQLCond(Field: TField; Value: Variant): string; overload;

make sql condition according Field and Value

function MakeSQLCond(FormatCondition: string; Value: Variant): string; overload;

make sql condition according according FormatCondition and Value

function MakeSQLCondOR(const Params: array of string): string;

join all conditions by OR

function MakeSQLCondAND(const Params: array of string): string;

join all conditions by AND

function MakeSQLCondIsNull(Field: TField): string; overload;

make sql condition according Field and its current value, null field is converted to 0 or empty string

function MakeSQLCondIsNull(Field: TField; Value: Variant): string; overload;

make sql condition according Field and Value, null field is converted to 0 or empty string

function MakeSQLCondLike(const FieldName, Text: string; CompareType: TrLikeCompareType): string;

make sql LIKE condition according FieldName, Text and CompareType

function FieldNameToCaption(Grid: TCustomDBGrid; const FieldName: string): string; overload;

find DBGrid column according FieldName and return Column Title

function FieldNameToCaption(Dataset: TDataSet; const FieldName: string): string; overload;

find Dataset Field according FieldName and return DisplayLabel

function FieldListToCaptionList(Grid: TCustomDBGrid; const FieldList, Separator: string): string; overload;

for each part from FieldList find DBGrid column according FieldName and return ColumnTitle list

function FieldListToCaptionList(Dataset: TDataSet; const FieldList, Separator: string): string; overload;

for each part from FieldList find Dataset Field according FieldName and return DisplayLabel list

procedure FileToBlob(Fld: TField; const FileName: string);

read file content and store to the blob field

procedure BlobToFile(Fld: TField; const FileName: string);

read blob field content and store to the file

function ScanAllRecords(Dataset: TDataSet; CallBack: TScanCallBackProc; DisableControls: boolean = true; BlockEvents: boolean = true; UseBookmark: boolean = true): boolean;

scan all record in Dataset and call CallBack procedure for each one, reset all events before scaning

function LoadRecordList(CaptionField, IdField: TField; L: TStrings): integer;

scan all dataset and load captions to List, List.Object contain IdField integer value converted to Object

procedure GenerateSQLScript(SqlCmd: TSQLStrings; DS: TDataSet; const TableName: string; KeyFieldList, FieldList: TStrings; GenerateInsert, AllRecords: boolean; AddAfterEachRecord: string = '');

generate INSERT or UPDATE sql script for active or all record with defined list of fields

procedure GenerateSaveSQLScript(SqlCmd: TSQLStrings; DS: TDataSet; const TableName: string; KeyFieldList, FieldList: TStrings; GenerateInsert, AllRecords: boolean; FileName: string);

generate and save INSERT or UPDATE sql script for active or all record with defined list of fields

function GetQuotedTableName(TableName: string): string;

return TableName with quotes if needed

function GetQuotedFieldName(FieldName: string): string;

return FieldName with quotes if needed

function GetFilterWildCardChar(Dataset: TDataSet; SQLFormat: boolean = false): Char;

Get filter WildCard char regarding used dataset type and global setting (% or *)

function GetFilterLikeOperator(Dataset: TDataSet; SQLFormat: boolean = false): string;

Get filter LIKE operator string regarding used dataset type and global setting (LIKE or any other value)

function GetFilterNullEqualStr(Dataset: TDataSet): string;

Get filter equal string for null field regarding used dataset type and global setting (IS or =)

function SetFilterString(Dataset: TDataSet; const Filter: string; ShowError: boolean): boolean;

set Filter string for Dataset, try to keep current record if possible

procedure ClearFilter(const GridOrDataSet: TObject);

clear filter string for dataset or grid, try to keep current record if possible

function GetFilterCond(Field: TField; const Value: string; IsNull: boolean; Condition: string = ''): string;

make Filter condition according Field and Value

function FilterByField(Field: TField; FilterJoin: TFilterJoin = fjNone): boolean;

make Filter condition according Field and its current value

function FilterBySel(const Grid: TCustomDBGrid; FilterJoin: TFilterJoin = fjNone): boolean;

make Filter condition according DBGrid selected cell (selected Field and its current value)

function NegateFilter(const GridOrDataSet: TObject): boolean;

negate active filter

function GetNumericFieldFilterValue(Value: extended): string;

return Numeric Field value string prepared for filter

function GetDateFieldFilterValue(Value: TDateTime): string;

return Date Field value string prepared for filter

function MakeFilterCond(FldName, Cond, Value: string; AddSep: string = ''): string;

make filter condition

function MakeFilterBetweenCond(FldName, ValueFrom, ValueTo: string; AddSep: string = ''): string;

make filter condition

Types

TSQLStrings = TStrings;

sBooleanString: array [boolean] of string = ('false', 'true');

TFilterJoin = (...);

list of possible filter join conditions

Values

fjNone
fjAnd
fjOr

TrLikeCompareType = (...);

list of possible filter compare types

Values

lcAnyPos
lcBegin
lcSame
lcAllWords
lcAnyWords

TScanCallBackProc = procedure(Dataset: TDataSet; RecIndex: integer; var Abort: boolean) of object;

callback procedure used by ScanAllRecords

Constants

sfSimpleFld: Set of TFieldType = ([ftString, ftWideString, ftSmallint, ftInteger, ftWord, ftBoolean, ftFloat, ftCurrency, ftBCD, ftDate, ftTime, ftDateTime,
    ftAutoInc, ftLargeint, ftTimeStamp, ftFMTBcd, ftLongWord, ftShortint, ftByte, ftExtended, ftSingle, ftGuid]);

Author

Rosinsky Software - <www.rosinsky.cz/delphi>

Created

Dec 2011

Last Modified

Nov 2021

Generated by PasDoc 0.15.0.

Rosi Components

Unit rdbtool

Description

Overview

Functions and Procedures

Types

Constants

Description

Functions and Procedures

Types

Values

Values

Constants

Author

Created

Last Modified