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.
Last Modified: 02-OCT-00