Unit rdbtool

DescriptionUsesClasses, Interfaces, Objects and RecordsFunctions and ProceduresTypesConstantsVariables

Description

rdbtool contains set of usefull database functions.

Updated version compatible with Delphi2010+.

Overview

Functions and Procedures

function GetDataSet(GridOrDSet: TObject): TDataSet;
function GetDSStateStr(GridOrDSet: TObject): string;
function IsClientDataset(Dataset: TDataSet): boolean;
function IsAdvantageDatabaseDataset(Dataset: TDataSet): boolean;
procedure AssignGridProps(Source, Dest: TDBGrid);
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, AllField, DataFieldOnly: boolean): TGetFldDefs;
function GetFldList(GridOrDSet: 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: TDBGrid; 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: TDBGrid; const FieldName: string): string; overload;
function FieldNameToCaption(Dataset: TDataSet; const FieldName: string): string; overload;
function FieldListToCaptionList(Grid: TDBGrid; 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 GetFilterLikeChar(Dataset: TDataSet): Char;
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): string;
function FilterByField(Field: TField; FilterJoin: TFilterJoin = fjNone): boolean;
function FilterBySel(const Grid: TDBGrid; 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;
TGetFldDef = (...);
TGetFldDefs = set of TGetFldDef;
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, ftLongWord, ftShortint, ftByte, ftExtended, ftSingle, ftGuid]);

Description

Functions and Procedures

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: TDBGrid);

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, AllField, DataFieldOnly: boolean): TGetFldDefs;

set FldsDef according defined parameters

function GetFldList(GridOrDSet: 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: TDBGrid; 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: TDBGrid; 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: TDBGrid; 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 GetFilterLikeChar(Dataset: TDataSet): Char;

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

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): 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: TDBGrid; 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');

TGetFldDef = (...);

list of possible fields group

Values
  • fdGetLabels:  
  • fdVisibleFieldOnly:  
  • fdSimpleField:  
  • fdMemoField:  
  • fdAllField:  
  • fdDataFieldOnly:  
TGetFldDefs = set of TGetFldDef;

set of possible fields group

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, ftLongWord, ftShortint, ftByte, ftExtended, ftSingle, ftGuid]);
 

Author

Created

Dec 2011

Last Modified

Oct 2016


Generated by PasDoc 0.13.0 on 2017-06-17 14:57:06