Friday, August 23, 2013

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 25Page
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.

Sunday, August 11, 2013

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.