Automatically Generating Data to Create Large Tables Using Delphi

By: Borland Staff

Abstract: Contains Delphi example to randomly fill a table

Problem:
In testing applications, sometimes a large table is desired for a variety of reasons.  
Some of the reasons might be to test performance, backing up and restoring sets of data, 
and reporting statistical data.  Creating a large table by hand, or by several persons can 
be a time-consuming task that can be automated and performed quickly by using the 
approach given below.


Solution:
The approach given below uses Delphi to connect to the InterBase server, however it could easily be modified for any database.  

Before starting to construct the program that will generate records, if they are to be viewed in the 
Delphi generated application,  run the BDE Configuration Utility.  On the InterBase alias 
that will be used change the Enable BCD property to TRUE.  This step not required but is 
recommended for tables with NUMERIC and DECIMAL columns.  BDE will treat NUMERIC and 
DECIMAL columns differently based upon the setting of this property, the chart below will 
illustrate how BDE treats these columns:


				ENABE BCD is FALSE	ENABLE BCD is TRUE
	NUMERIC(4)		TSmallIntField		TSmallIntField
	NUMERIC(9)		TIntegerField		TBCDField
	NUMERIC(10)		TFloatField		TBCDField

	DECIMAL(4)		TIntegerField		TBCDField
	DECIMAL(9)		TIntegerField		TBCDField
	DECIMAL(10)		TFloatField		TBCDField

The ENABLE BCD setting affects how the BDE retrieves data from InterBase by use of a SELECT 
statement.   When the columns are translated into the TSmallIntField and TIntegerField types the 
decimal data is truncated, otherwise if the columns are translated to TFloatField or TBCDField types
then the whole and decimal values are retained.   The ENABLE BCD setting has no bearing on the 
INSERT statement that will be used later on to insert data into the table. 

In the application the Interface section of the .pas will look similar to:

unit gendata1;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  StdCtrls, DB, DBTables;

type
  TForm1 = class(TForm)
    Query1: TQuery;
    Button1: TButton;
    Edit1: TEdit;
    Edit2: TEdit;
    procedure Button1Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
  private
    procedure gen_alpha    (var in_string:   String; in_length: Word);

    procedure gen_smallint (var in_smallint: Smallint);
    procedure gen_integer  (var in_integer:  Integer);
    procedure gen_float    (var in_float:    Single);
    procedure gen_double   (var in_double:   Double);
    procedure gen_decimal  (var in_decimal:  Double; precision, 
      scale: Word);
    procedure gen_integer_range (var in_integer: Integer; in_low, 
      in_high: Integer);
    procedure gen_date_1900     (var in_date: TDateTime);

    function  gen_rand: Extended;
  public
  end;

var
  Form1: TForm1;


The application is divided up into several functions that perform the generation of fictitious data.
The functions are as follows:


  // Generate CHAR and VARCHAR data for strings that are from 1 to 
  // 255 characters in width
  procedure gen_alpha    (var in_string:   String; in_length: Word);

  // Generate a SMALLINT value
  procedure gen_smallint (var in_smallint: Smallint);

  // Generate an INTEGER value
  procedure gen_integer  (var in_integer:  Integer);

  // Generate a FLOAT value
  procedure gen_float    (var in_float:    Single);

  // Generate a DOUBLE PRECISION value
  procedure gen_double   (var in_double:   Double);

  // Generate a date in the 1900's range (i.e 20th Century)
  procedure gen_date_1900(var in_date: TDateTime);

  // Generate a DECIMAL or a NUMERIC value
  procedure gen_decimal  (var in_decimal:  Double; precision, 
    scale: Word);

  // Generate a value used by the procedures gen_integer, gen_float,
  // gen_double, and gen_decimal
  function  gen_rand: Extended;

  // Generate a value used by gen_decimal, and gen_1900_date
  procedure gen_integer_range (var in_integer: Integer; in_low, 
    in_high: Integer);

The functions used in the interface of this application and the TQuery component that is used to 
insert the data into the table will be covered later.   The code for each procedure is shown below:


In this procedure an array of possible characters to include in the character string is constructed 
private to this procedure.  In this array the blank space character is available twice, at the beginning
and the end, and the comma character is available.   What is not included here is the period 
character and other punctuation character, include these punctuation characters as desired.  
Please note that in Delphi 1.0 (16-bit) strings are limted to 255 characters.  In Delphi v3.0 and higher,
(32-bit) strings can be up to 4Gig in size,  though InterBase supports CHAR and VARCHAR types 
only up 32,767 (32k) bytes in length.

procedure TForm1.gen_alpha(var in_string: String; in_length: Word);
const
  char_array_size: Word = 55;
  char_array: Array[0..54] of Char =
    (' ', ',', 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 
     'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 
     'Y', 'Z', 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 
     'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 
     'y', 'z', ' ' );
var
  i, j: Word;
begin
  // CHAR and VARCHAR types cannot exceed 32767, these lines of 
  // code ensure this limit is not exceeded.
  if (in_length > 32767) then
    in_length := 32767;

  j := Random(in_length - 1) + 1;
  SetLength(in_string, j);
  for i := 1 to j do
    in_string[i] := char_array[Random(char_array_size)];
end;

procedure TForm1.gen_smallint(var in_smallint: Smallint);
begin
  // This calculation provides a range of
  // -32,768 to 32,767, the range of a smallint
  in_smallint := 32767 - Trunc(65536 * gen_rand);
end;


In generating a random integer value and extended variable is used internally in the procedure.  
An extended variable is used because the internal algorithm uses a value that ranges from 
0 to 4,294,967,295 and integer values above 2147483647 are not supported in 
Delphi (i.e. Delphi does not support unsigned long values).

procedure TForm1.gen_integer(var in_integer: Integer);
var
  temp_extended: Extended;
begin
  // This calculation provides a range of
  // -2,147,483,648 to 2,147,483,647, the range of a integer

  temp_extended := 4294967295.0 * gen_rand;
  if temp_extended <= 2147483647 then
    in_integer := Integer(2147483647 - Trunc(temp_extended))
  else
    in_integer := -1 * Integer(Trunc(temp_extended - 2147483647));
end;


In generating a random float value the format of the float value as defined in the Object Pascal 
Language Guide is used to construct the float value.  Specifically, the s, e, and f components are 
separately obtained and then placed in an integer variable (which is of the same size as a float), 
and then once this is done the value is copied into the float.  Please note in constructing a float 
value it is possible to create a NAN (not a number), infinity, and minus infinity values.  
The procedure below does not create these values, if it did this would raise an exception in Delphi.

procedure TForm1.gen_float(var in_float: Single);
var
  s, e:         Byte;
  f:            Longint;
  temp_integer: Integer;
begin
  temp_integer := 0;

  // Obtain values for the component elements of in_float
  s := Trunc(2* gen_rand);
  e := Trunc(255 * gen_rand);

  f := Trunc(8388608 * gen_rand);

  // Place the components in the proper location in in_float
  temp_integer := temp_integer or  s;
  temp_integer := temp_integer shl 8;
  temp_integer := temp_integer or  e;
  temp_integer := temp_integer shl 23;
  temp_integer := temp_integer or  f;

  // Move the data into the float variable
  Move(temp_integer, in_float, sizeof(temp_integer));
end;


In generating random double precision values the format double precision type that is defined in 
the Object Pascal Language Guide is used.  Specifically the s, e, and f components are separately
generated.  Since a double precision value is 64-bits wide two long integers, which are 32-bits in 
length, are used to hold the values during computation of the double precision value.  To this end 
the s, e, and 20 bits of the f components are held in one integer, and the remaining 32-bits of f are 
held in the other integer.  In the final stages the contents of the two integers are copied to the correct
locations inside the double precision number to produce a random value.  Please note in 
constructing a double precision value it is possible to create a NAN (not a number), infinity, and 
minus infinity values.   The procedure below does not create these values, if it did this would 
raise an exception in Delphi.


procedure TForm1.gen_double(var in_double: Double);
var
  e, s: Word;
  f_low, low_integer, hi_integer: Integer;
  temp_char_array: array[0..7] of char;
begin

  low_integer := 0;
  hi_integer := 0;

  // Get s component of double
  s := Trunc(2 * gen_rand);
  low_integer := low_integer or s;

  // Get e component of double
  low_integer := low_integer shl 11;
  e := Trunc(2046 * gen_rand);
  low_integer := low_integer or e;

  // Low 20 bits of f component of double
  low_integer := low_integer shl 20;
  f_low := Trunc(2097152 * gen_rand);
  low_integer := low_integer or f_low;

  // High 32 bits of f component of double
  gen_integer(hi_integer);

  Move(hi_integer,  temp_char_array[0], 4);
  Move(low_integer, temp_char_array[4], 4);
  Move(temp_char_array, in_double, 8);
end;


procedure TForm1.gen_date_1900     (var in_date: TDateTime);
var
  days, millisecs: Integer;
begin
  gen_integer_range(days, Integer(0), Integer(36524));
  gen_integer_range(millisecs, 1, 99999);

  in_date := StrToFloat(FormatFloat('00000', days) + '.' + 
    FormatFloat('00000', millisecs));
end;


In this procedure the range array is used to signify the range of numbers to generate using the 
gen_integer_range procedure when generating integers within a certain range.  The double_format
array is used to indicate a specific string formatting used in the algorithm used to generate the
DECIMAL or NUMERIC value.

procedure TForm1.gen_decimal  (var in_decimal:  Double; precision, scale: Word);
const
  range: array[1..9] of Integer  = (9, 99, 999, 9999, 99999, 999999, 9999999,
                                    99999999, 999999999);
  double_format: array[1..16] of String =
    ('0', '00', '000', '0000', '00000', '000000', '0000000', '00000000',
     '000000000', '0000000000', '00000000000', '000000000000', '0000000000000',
     '00000000000000', '000000000000000', '0000000000000000');
var
  whole, decimal:               Word;
  whole_int, decimal_int:       Integer;
  whole_double, decimal_double: Double;
begin
  if ((precision > 15) or (precision < 1) or (scale > 15) or (scale < 1)) then begin
    in_decimal := 0.0;
    exit;
  end;

  if (scale > precision) then begin
    in_decimal := 0.0;
    exit;
  end;

  whole   := precision - scale;
  decimal := scale;

  // generate whole number portion
  if (whole = 0) then
    whole_int := 0
  else begin
    if (whole <= 9) then begin
      gen_integer_range(whole_int, -1 * range[whole], range[whole]);
      whole_double := whole_int;
    end
    else begin
      gen_integer_range(whole_int, -1 * range[9], range[9]);
      whole_double := Int(whole_int *  exp((whole - 9)*ln(10)));
      gen_integer_range(whole_int, 0, (whole - 9));
      whole_double := whole_double + whole_int;
    end;
  end;

  // generate decimal number portion
  if (decimal = 0) then
    decimal_int := 0
  else begin
    if (decimal <= 9) then begin
      gen_integer_range(decimal_int, 0, range[decimal]);
      decimal_double := decimal_int;
    end
    else begin
      gen_integer_range(decimal_int, 0, range[9]);
      decimal_double := Int(decimal_int *  exp((decimal - 9)*ln(10)));
      gen_integer_range(decimal_int, 0, (decimal - 9));
      decimal_double := decimal_double + decimal_int;
    end;
  end;

  in_decimal := StrToFloat(FormatFloat(double_format[whole+1],whole_double) +
    '.' + FormatFloat(double_format[decimal], decimal_double));
end;


This procedure is used internally by other procedures that require generating a random integer
value within a certain range.

procedure TForm1.gen_integer_range (var in_integer: Integer; in_low, in_high: Integer);
var
  low, hi, delta: Integer;
begin
  if (in_low <= in_high) then begin
    low := in_low;
    hi  := in_high;
  end
  else begin
    low := in_high;
    hi  := in_low;
  end;

  if ((hi - low) > 2147483647) then begin
    in_integer := 0;
    Exit;
  end;

  delta := hi - low + 1;
  in_integer := Trunc(delta * gen_rand) + low;
end;

Place each procedure and function shown above in the Implementation section of the .pas file.  Next,
place a TQuery, two TEdit, and a TButton Component on the form.  Give the TQuery component focus 
on the form and in the Object Inspector make the entry in the SQL property similar to the following
entry:

  INSERT INTO InputTble
  (Col1, Col2, Col3)
  VALUES
  (:parm1, :parm2, :parm3)

The names of the columns and their data type in the table depend on the structure of the table.  Next,
set data type of the parm variables in the Params property.  The InterBase data types will match
up with the following BDE data types:

	InterBase Type:		BDE Type:
	============		==========
	CHAR			String
	VARCHAR		String
	SMALLINT		Smallint
	INTEGER			Integer
	FLOAT			Float
	DOUBLE PRECISION	Float
	NUMERIC		Float
	DECIMAL			Float
	DATE			Date

One data type is missing here, the BLOb data type.  BDE v3.5 and previous does not accept blob 
parameters to pass to InterBase BLOb columns.  This is also why there is no procedure the 
generate random BLOb data.

For the TButton component set up an OnClick event and have the OnClick event look like:

procedure TForm1.Button1Click(Sender: TObject);
var
  i:              		Longint;
  in_string:	String;
  in_integer:    	Integer;
  in_decimal86:   Double;
begin
  in_integer  := 0;
  in_decimal  := 0;
  Database1.StartTransaction;
  try
    for i := 0 to StrToInt(Edit1.Text) - 1 do begin
      gen_alpha(in_string, 128);
      gen_integer(in_integer);
      gen_decimal(in_decimal86, 8, 6);
      Query1.ParamByName('parm1').AsString   := in_string;
      Query1.ParamByName('parm2').AsInteger  := in_integer;
      Query1.ParamByName('parm3').AsFloat    := in_decimal86;
      Query1.ExecSQL;
      Edit2.Text := IntToStr(i);
      Application.ProcessMessages;
    end;
  except
      else Database1.Rollback;
  end;
  if Database1.InTransaction then
    Database1.Commit;
end;

Using the Object Inspector, set up an OnCreate and an OnClose event for the form and have 
them look similar to:

procedure TForm1.FormCreate(Sender: TObject);
begin
  // Start up the random number generator in the runtime library
  Randomize;
end;

procedure TForm1.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  // Free the form upon closing
  Action := caFree;
end;

When running the application type the number of records that are desired to be generated in the 
Edit1 control and click on the Button12 control to start the process of adding records.
The Edit2 control will display the number of records added.

Server Response from: ETNASC04