Gary's Database Tools

• gaDBTools.pas - general database functions such BLOB handling, changing database connections
• gaSQLTools.pas - functions to make life easier in using TQuery with cached updates
• gaCDSTools.pas - functions to make life easier in using TClientDatasets
• gaSysInfo.pas - get info regarding system/user; access registry items;
• gaTools.pas - general tools
• gaCom.pas - export data to Excel97 or MSWord97
• FMXUtils.pas - file management routines
• BSUtils.pas - various string, maths, sysinfo functions
• see also:
  • Delphi Programming Main Index

gaDBTools.pas:

• BDE installation/registration functions:
  • procedure CheckBDE(rslt: DBIResult);
  • function CanBDERun:Boolean;
• Change database connection:
  • function ga_ChangeDatabase(var DB:TDatabase;Alias,Title,Password:String):Boolean;
• Force physical saving of posted edits to disk to avoid possible data loss if crash before BDE closed normally:
  • procedure ga_SaveDatasetToDisk(DBdataset:TDBDataset);
• BLOB field handling:
  • function ga_LoadFileToBLOBField(Adataset:TDataset;Afield, Filename:string):Boolean;
  • function ga_SaveBLOBFieldToFile(Adataset:TDataset;Afield, Filename:string):Boolean;
  • function ga_ShowBLOBImageFromDB(dataset:TDataset;Afield:string;Image:TImage):Boolean;
  • function ga_SaveFormToBLOB(FField:TBlobField;Form:TComponent):Boolean;
  • function ga_LoadFormFromBLOB(FField:TBlobField;Form:TComponent):Boolean;
  • function ga_MemoFieldToList(Table:TDataset;MemoFldName:String;DestList:TStringList):Boolean;
  • function ga_ListToMemoField(Table:TDataset;MemoFldName:String;SourceList:TStringList):Boolean;
• determine if table is password protected:
  • function TableProtected (tblSource: TTable): boolean;
• copying tables:
  • TBatchMove does not copy indices thus to copy indices:
    • function gaCopyTableIndices(Source,Target:TTable):Boolean; //must be able to set target.exclusive := true!!
• copying data from same column of prior record (eg. Ctrl-D functionality):
  • function ga_DBFldDitto(Dataset1: TDataset; Field: TField):boolean;

gaSQLTools.pas:

• Sybase date formatting for SQLs:
  • function DateToYYYYMMDD(cdate:TDateTime):String;
• TQuery cached updates - updating SQL database +/- using audit trail:
  • see actually pas file for usage.
  • function ga_qryApplyUpdates(DataSet: TDataSet):Boolean;
  • function ga_qryApplyUpdatesWithAudit(DataSet,AuditDataset: TDataSet):Boolean;
  • procedure ga_UpdateQuery(Dataset:TDataset;UpdateKind: TUpdateKind; var UpdateAction: TUpdateAction);
  • procedure ga_UpdateQueryWithAudit(Dataset,AuditDataset,AuditMax:TDataset;UpdateKind: TUpdateKind; var UpdateAction: TUpdateAction;Filename,Machine,User:String);
  • procedure ga_qryUpdateRecord(DataSet,DatasetMax: TDataSet;UpdateKind: TUpdateKind; var UpdateAction: TUpdateAction; PrimKey:String);
  • procedure ga_qryUpdateRecordWithAudit(DataSet,DatasetMax,AuditDataset,AuditMax: TDataSet;UpdateKind: TUpdateKind; var UpdateAction: TUpdateAction; PrimKey,Filename,Machine,User:String);
  • function ga_UpdateAuditTrail(Dataset,AuditDataset,AuditMax: TDataset;Filename,Machine,User:String):Boolean;

gaCDSTools.pas:

• these functions are to assist in use of TClientDatasets connected to TDatasetProvider which connects to a TTable:
  • procedure ga_cdsBeforeDelete(DataSet: TDataSet);
  • procedure ga_prvdrUpdateError(Sender: TObject;DataSet: TClientDataSet; E: EUpdateError; UpdateKind: TUpdateKind;var Response: TResolverResponse);
  • procedure ga_prvdrBeforeUpdateRecord(Sender: TObject;SourceDS: TDataSet; DeltaDS: TClientDataSet; UpdateKind: TUpdateKind;var Applied: Boolean);
  • procedure ga_CDSChangeIndexByDbGridTitleButtonClick(Sender: TObject;AFieldName: String);
  • procedure ga_CDSSaveAllChanges(form:TForm;ServerDir,DB:String);
  • procedure ga_CDSApplyToServer(cds:TClientDataset;ServerDir,DB:String);
  • procedure ga_OpenCDSTable(cds:TClientDataset;DB:String);
  • function ga_GetCDSFilename(cds:TClientDataset;DB:String):String;
  • procedure ga_CDSSaveToDisk(cds:TClientDataset;DB:String);
  • procedure ga_GetAndWriteDataDate(ServerDir,db,tbl:String);
  • function ga_IsLocalDataOutOfDate(ServerDir,db,tbl:String):Boolean;

gaSysInfo.pas:

• these functions help get info about system & user:
  • function GetWinUserName :String;
  • function GetWinUserPassword :String; //doesn't work yet!
  • function GetWinMachineName :String;
  • function GetWinPercentMemUsed:DWord;
  • function GetWinTotalPhysicalMemory:DWord;
  • function GetWinAvailablePhysicalMemory:DWord;
  • function GetWinTotalVirtualMemory:DWord;
  • function GetWinAvailableVirtualMemory:DWord;
  • function ShowDriveType(DriveLetter: char): string;
  • procedure MapNetworkDrive(Password, LocalDriveLetter,RemoteDriveName:String; Permanent:Boolean);
  • procedure CreateShortcut(Dest,ShortcutName,ExeFile,StartDir,Params:String);
  • function IsNetworkConnected:Boolean;
  • function IsBDEInstalled :Boolean;
  • function GetBDEConfigFile :String;
  • function GetBDEPath :String;

gaTools.pas:

• miscellaneous tools:
  • function ga_LoadForm(Form:TForm;FormClass:TFormClass;FormName:String):Boolean;
  • function ga_GetNoDOWInMonth(Month,Year,DOW:Word):Byte;
  • Procedure ga_HideTitlebar(Form:TForm);
  • Procedure ga_ShowTitlebar(Form:TForm);
  • Function ga_GetShortFileName(Const FileName : String) : String;
  • Function ga_GetLongFileName(Const FileName : String) : String;
  • function Encrypt(const S: String; Key: Word): String;
  • function Decrypt(const S: String; Key: Word): String;

gaCom.pas:

• this unit contains COM interface tools for manipulating other programs such as Excel & MS Word 97
• export a dataset to Excel97:
  • procedure ExportDbToExcel(aDataset:TDataset);
• MS Word97 functions:
  • one must create the WordApp variable BEFORE calling these
    • eg. add comObj, ComCtrls, Word_tlb to uses clause
    • then declare variable: WordApp: Word_TLB.Application_;
    • then WordApp := CoApplication_.Create;
    • then call functions as needed
      • function ga_OpenMSWordDocument(var WordApp: Word_TLB.Application_;filename:String;Show:Boolean):Boolean;
      • function ga_SetOutlineOn(var WordApp: Word_TLB.Application_;ItemNo:Integer):Boolean;
      • function ga_CreateMSWordPageHeader(var WordApp:Word_TLB.Application_; heading, subheading:String):Boolean;
      • function ga_InsertAndFormatInWord(var WordApp:Word_TLB.Application_;s:String):Boolean;
      • function ga_InsertImageInMSWord(var WordApp:Word_TLB.Application_;ImageFile:String):Boolean;
      • function ga_SaveMSWordDocument(var WordApp:Word_TLB.Application_;Filename,UserPassword:String):Boolean;

FMXUtils.pas:

• this file is distributed by Borland in its demos folder, but I modify it as I feel fit:
• function CopyTable(const FileName, DestName: TFileName):Boolean;
• procedure CopyFile(const FileName, DestName: string);
• procedure MoveFile(const FileName, DestName: string);
• function GetFileSize(const FileName: string): LongInt;
• function FileDateTime(const FileName: string): TDateTime;
• function HasAttr(const FileName: string; Attr: Word): Boolean;
• function ExecuteFile(const FileName, Params, DefaultDir: string;ShowCmd: Integer): THandle;
• function GetOperatingSystem: string;

BSUtils.pas:

• freeware functions:
• string routines:
  • function slash(value:string):string; {ensures that value has '\' as last character (for directory strings)}
  • function capfirst(value:string):string; {Capitalise first character of each word, lowercase remaining chars}
    • {example: capfirst('bOrLANd delPHi FOR windOWs') = 'Borland Delphi For Windows'}
  • function striptags(value:string):string; {strip HTML tags from value}
    • {example: striptags('<TR><TD Align="center">Hello World</TD>') = 'Hello World'}
  • function replace(str,s1,s2:string;casesensitive:boolean):string; {replace all incidences of s1 in str with s2}
    • {example: replace('We know what we want','we','I',false) = 'I Know what I want'}
  • function CopyFromChar(s:string;c:char;l:integer):string; {copy l characters from string s starting at first incidence of c}
    • {example: Copyfromchar('Borland Delphi','a',3) = 'and'}
  • procedure splitstring(s:string;delim:char;ts:tstrings); {splits a compound string (s) delimited by (delim) into TStrings (ts)}
    • {example SplitString('Tony,Blair,Downing Street,London',',',ts) returns:
      • ts.count=4 ts[0]='Tony' ts[1]='Blair' ts[2]='Downing Street' ts[3]='London'
• System Utils:
  • function getwinsysdir:string; {returns Windows System Path (inc drive)}
    • {example: getwinsysdir = 'C:\WINDOWS\SYSTEM\'}
  • function getwindir:string; {returns windows directory path (inc Drive)}
    • {example: getwindir = 'C:\WINDOWS\'}
  • function getinstalldir:string; {returns install directory of EXE using this library}
    • {example: getinstalldir = 'C:\PROGRAM FILES\BORLAND\DELPHI\DEMOS\'}
  • function getcurrentdir:string; {returns current directory}
    • {example: getcurrentdir = 'D:\DELPHI PROJECTS\CLASSES\UTILS\'}
  • function getregvalue(root:integer;key,value:string):string; {reads a registry value}
    • {example: getregvalue(HKEY_LOCAL_MACHINE,'network\logon\','username') = 'Eddie Bond'}
  • function getaliaspath(dbset:Tdataset):string; {returns DOS path of an ACTIVE dataset's (TTable or TQuery) database alias}
    • {example getaliaspath(Table1) = 'C:\Program Files\Borland\Delphi\Demos\Data\'}
  • function getfiledate(filename:string):Tdatetime; {returns a file's date in TDateTime format}
• File Management:
  • function filecopy(f,t:string;NoOverwrite:boolean):boolean; {returns success of copy from file f to t with overwrite protection}
    • {example: filecopy('C:\WINDOWS\WIN.INI','C:\BACKUPS\WIN.INI',true)=true}
• Arithmetic Utils:
  • function StrToFloatDef(const s:string;def:Extended):Extended; {converts S into a number. If S is invalid, returns the number passed in Def.}
    • {example: strtofloatdef('$10.25',0) = 0}
  • function VolSphere(radius:single):extended; {volume of sphere of given radius}
  • function AreaSphere(radius:single):extended; {surface area of sphere of given radius}
  • function VolCylinder(radius,height:single):extended; {volume of cylinder of given radius and height}
  • function AreaCylinder(radius,height:single):extended; {surface area of cylinder of given radius and height}
  • function MinExt(const A:array of Extended):Extended; {returns minimum value of an array of extended}
  • function MaxExt(const A:array of Extended):Extended; {returns maximum value of an array of extended}
  • function MinInteger(const A:array of Integer):Integer; {returns minimum value of an array of integers}
  • function MaxInteger(const A:array of integer):Integer; {returns maximum value of an array of integers}
  • function InverseSum(const a:array of single):single; {solves formulae of type 1/r = 1/a + 1/b +...1/n (eg electrical resistance in parallel)}
• Financial Utils:
  • function MarkUp(profit:single):single; {returns markup percentage required to return a profit of profit percent}
    • {example: MarkUp(25) = 20 }
  • function SellingPrice(net:double;markup:single):double; {returns selling price after adding markup percent to net}
    • {example: SellingPrice(199.50,22.5) = 244.3875}
  • function NetPrice(gross:double;taxrate:single):double; {returns the net value of an item of gross value containing tax at taxrate percent}
    • {example: NetPrice(199.99,17.5) = 170.204255319149}