The One Manual Every Delphi Programmer Should Have!

I have always stayed away from using Delphi's DBGrid because it gave me the feeling of being in the cockpit of an airplane without a clue, as to what all the controls are, or even where to look first. I've decided to roll up my sleeves and get over my fear and anxiety of Delphi's DBGrid.

I'm currently using XE4 and I've been struggling with data validation within a DBGrid as you can see from a recent SO post How to get the value that caused the TDBGridInplaceEdit error?

After reading the answer I got from @bummi, I started googling related topics. Then I remembered I have a copy of Borland's Delphi 7 Developer's Guide.




Delphi 7 Developer's Guide

I quickly turned to Chapter 25 and found all the information I need to know, written in a friendly and very informative way. It's perfect. Each topic builds upon the previous topic giving a full treatment of the subject matter. Here is the table of contents for Chapter 25 of Borland's Delphi 7 Developer Guide:

Chapter 25	Page
Working with field components	25-1
Dynamic field components	25-2
Persistent field components	25-3
Creating persistent fields	25-4
Arranging persistent fields	25-5
Defining new persistent fields	25-5
Defining a data field	25-6
Defining a calculated field	25-7
Programming a calculated field	25-8
Defining a lookup field	25-9
Defining an aggregate field	25-10
Deleting persistent field components	25-11
Setting persistent field properties and events	25-11
Setting display and edit properties at design time	25-11
Setting field component properties at runtime	25-13
Creating attribute sets for field components	25-13
Associating attribute sets with field components	25-14
Removing attribute associations	25-14
Controlling and masking user input	25-15
Using default formatting for numeric, date, and time fields	25-15
Handling events	25-16
Working with field component methods at runtime	25-17
Displaying, converting, and accessing field values	25-18
Displaying field component values in standard controls	25-18
Converting field values	25-19
Accessing field values with the default dataset property	25-20
Accessing field values with a dataset’s Fields property	25-21
Accessing field values with a dataset’s FieldByName method	25-21
Setting a default value for a field	25-22
Working with constraints	25-22
Creating a custom constraint	25-22
Using server constraints	25-23
Using object fields	25-23
Displaying ADT and array fields	25-24
Working with ADT fields	25-25
Using persistent field components	25-25
Using the dataset’s FieldByName method	25-25
Using the dateset’s FieldValues property	25-25
Using the ADT field’s FieldValues property	25-26
Using the ADT field’s Fields property	25-26
Working with array fields	25-26
Using persistent fields	25-26
Using the array field’s FieldValues property	25-27
Using the array field’s Fields property	25-27
Working with dataset fields	25-27
Displaying dataset fields	25-27
Accessing data in a nested dataset	25-28
Working with reference fields	25-28
Displaying reference fields	25-28
Accessing data in a reference field	25-29

I've already printed out Chapter 25 and plan to study it this weekend. The manual is 1,106 pages long. I plan on printing the chapters I need as I go. And yes I did buy paper that's already punched with 3-holes.

I don't care what version of Delphi you are using this manual is relevant and will make your life easier. Download your copy of Borland's Delphi 7 Developer Guide now!

If we could only get EMBT to create an updated version of this Delphi classic!

Semper Fi,
Gunny Mike

end.

Format Numbers with an ElevateDB Function

There are times when I want numeric data coming out of a database formatted with a thousands separator and set number of decimal places. I'd rather see large numbers formatted as 123,740.00 rather than 123740. To me formatted numbers are just easier to read. Unfortunately, ElevateDB does not have a built in function similar to Microsoft's CONVERT.

However, you can create your own CONVERT function within ElevateDB quite easily. ElevateDB has two statements that make the job of formating numbers fairly easy. The first is the LABEL statement and the second is the LEAVE statement. The LABEL statement lets you set up a block of code. For example:

MyFistLabel:
BEGIN
  Do Something1;
  Do Something2;
  Do Something3;
  Do Something4;
  Do Something5;
END; --MyFistLabel

The LEAVE statement lets you exit a block of code. For Example:

MyFistLabel:
BEGIN
  Do Something1;
  Do Something2;
  Do Something3;
  IF Something3 = Done THEN
    LEAVE MyFistLabel
  END IF;
  Do Something4;
  Do Something5;
END; --MyFistLabel

So let's set out to create a function that formats numbers. This function will have two input parameters Value and Decimals. Value is the number to be formatted and Decimals is the number of decimal places. I built this function using two labels. One label that handles zero decimal places and one label that handles non zero decimal places. Here is the stubbed out concept:

----------------------------------------------------------------
ProcessZeroDecimals:
----------------------------------------------------------------
BEGIN
  -- Exit if Decimals parameter is other than 0 (zero)
  IF DECIMALS <> 0 THEN LEAVE ProcessZeroDecimals; END IF;
  -- Perform if Decimals parameter is equal to 0 (zero)
  Code Goes Here
  Code Goes Here
  Code Goes Here
----------------------------------------------------------------
END;--ProcessZeroDecimals:
----------------------------------------------------------------

----------------------------------------------------------------
ProcessNonZeroDecimals:
----------------------------------------------------------------
BEGIN
  -- Exit if Decimals parameter is equal to 0 (zero)
  IF DECIMALS = 0 THEN LEAVE ProcessNonZeroDecimals; END IF;
  -- Perform if Decimals parameter is other than 0 (zero)
  Code Goes Here
  Code Goes Here
  Code Goes Here
----------------------------------------------------------------
END; --ProcessNonZeroDecimals:
----------------------------------------------------------------

By testing the value of Decimals first we will either decide to LEAVE the code block or perform the code within that code block. The use of LABEL combined with the LEAVE statement makes for a very elegant solution. Here is the complete ElevateDB funtion:

CREATE FUNCTION "fFormatThousands"
(
 INOUT "Value" DECIMAL(19,4)
,INOUT "Decimals" INTEGER
)
RETURNS VARCHAR(20) COLLATE UNI_WI
BEGIN

DECLARE Separator VARCHAR(1);
DECLARE Str1      VARCHAR(30);
DECLARE Str2      VARCHAR(30);

DECLARE StrLength  INTEGER;
DECLARE ZeroPad    INTEGER;
DECLARE DecimalPos INTEGER;

DECLARE i INTEGER;
DECLARE j INTEGER;

SET Separator = ',';
SET Str1      = '';
SET Str2      = '';
SET ZeroPad   = 0;

CASE DECIMALS
  WHEN 0 THEN SET Value = ROUND(Value to 0);
  WHEN 1 THEN SET Value = ROUND(Value to 1);
  WHEN 2 THEN SET Value = ROUND(Value to 2);
  WHEN 3 THEN SET Value = ROUND(Value to 3);
  WHEN 4 THEN SET Value = ROUND(Value to 4);
  ELSE
    BEGIN
      SET Value = ROUND(Value to 2);
      SET Decimals = 2;
    END;
END CASE;
SET Str1 = CAST(Value as VARCHAR);

----------------------------------------------------------------
ProcessZeroDecimals:
----------------------------------------------------------------
BEGIN
  -- Exit if Decimals parameter is other than 0 (zero)
  IF DECIMALS <> 0 THEN LEAVE ProcessZeroDecimals; END IF;
  -- Perform if Decimals parameter is equal to 0 (zero)
  SET StrLength = LENGTH(Str1);
  SET Str2 = '';
  SET i = 0;
  SET j = StrLength;
  WHILE j > 0 DO
    SET Str2 = SUBSTRING(Str1, j for 1) + Str2;
    SET i = i + 1;
    SET j = j - 1;
    IF (i MOD 3 = 0) AND (j > 0) THEN
      SET Str2 = Separator + Str2;
    END IF;
  END WHILE;
----------------------------------------------------------------
END;--ProcessZeroDecimals:
----------------------------------------------------------------

----------------------------------------------------------------
ProcessNonZeroDecimals:
----------------------------------------------------------------
BEGIN
  -- Exit if Decimals parameter is equal to 0 (zero)
  IF DECIMALS = 0 THEN LEAVE ProcessNonZeroDecimals; END IF;
  -- Perform if Decimals parameter is other than 0 (zero)
  SET DecimalPos = POSITION('.' IN Str1);
  IF DecimalPos = 0 then
    CASE Decimals
      WHEN 1 THEN SET Str1 = Str1 + '.0';
      WHEN 2 THEN SET Str1 = Str1 + '.00';
      WHEN 3 THEN SET Str1 = Str1 + '.000';
      WHEN 4 THEN SET Str1 = Str1 + '.00000';
    END Case;
  END IF;
  SET StrLength = LENGTH(Str1);
  SET DecimalPos = POSITION('.' IN Str1);
  SET ZeroPad = Decimals - (StrLength - DecimalPos);
  SET Str2 = SUBSTRING(Str1, DecimalPos for Decimals+1);
  SET i = 0;
  SET j = DecimalPos -1;
  WHILE j > 0 DO
    SET Str2 = SUBSTRING(Str1, j for 1) + Str2;
    SET i = i + 1;
    SET j = j - 1;
    IF (i MOD 3 = 0) AND (j > 0) THEN
      SET Str2 = Separator + Str2;
    END IF;
  END WHILE;
  CASE ZeroPad
    WHEN 1 THEN SET Str2 = Str2 + '0';
    WHEN 2 THEN SET Str2 = Str2 + '00';
    WHEN 3 THEN SET Str2 = Str2 + '000';
    WHEN 4 THEN SET Str2 = Str2 + '0000';
  END CASE;
----------------------------------------------------------------
END; --ProcessNonZeroDecimals:
----------------------------------------------------------------

RETURN str2; 

END
VERSION 1.00!

Here is the fFormatThousands function at work against the DBDemos database.

SELECT
 OrderNo
,CustNo
,fFormatThousands(ItemsTotal,2) as "Items Total"
,fFormatThousands(AmountPaid,2) as "Amount Paid"
,fFormatThousands(ItemsTotal - AmountPaid,2) as "Balance Due"
FROM orders
ORDER BY
ItemsTotal - AmountPaid DESC;

 

Semper Fi,
Gunny Mike

end.