FireDAC.PostgreSQL Ranges Sample

This sample demonstrates how to use range types on a PostgreSQL database using FireDAC.

Location

You can find the Ranges project at:

• Start | Programs | Embarcadero RAD Studio Athens | Samples and then navigate to:
  • Object Pascal\Database\FireDAC\Samples\DBMS Specific\PostgreSQL\Ranges
• Subversion Repository:
  • You can find Delphi code samples in GitHub Repositories. Search by name into the samples repositories according to your RAD Studio version.

Description

The Ranges sample shows you how to use range types on a PostgreSQL database to represent many element values in a single range value. FireDAC represents a range type column as a ftADT field with 3 subfields:

• lbound - lower range bound.
• hbound - upper range bound.
• flags - range flags.

Note: Columns of range type are read-only. This sample updates them using SQL commands.

To get range column attributes, this sample uses the following code:

var
 l: Xxxx;
 // ...
 l := FDQuery1.FieldByName('<range column name>.<subfield>').AsXxxx;

Notes:

• <subfield> can be set to lbound, hbound or flags.
• This sample uses integer ranges. Therefore, on the above code, Xxxx is replaced by Integer. However, you can use other range types such as time and date ranges for scheduling purposes.

How to Use the Sample

1. Navigate to the location given above and open PGRanges.dproj.
2. Press F9 or choose Run > Run.

Files

File in Delphi	Contains
PGRanges.dproj PGRanges.dpr	The project itself.
fMain.pas fMain.fmx	The main form.

Implementation

Before running the sample, the main components are configured at design time using the Object Inspector as follows:

• A TFDConnection object named FDConnection1. This is the FireDAC connection object that the sample uses to connect to a DBMS. The sample sets the ConnectionDefName property to PG_Demo.

Note: You can change the ConnectionDefName property to connect to a different PostgreSQL server using a valid user name and password.

• A TFDQuery object named FDQuery1. This component implements a dataset capable of executing SQL queries. The sample sets:
  • The Connection property to FDConnection1 in order to specify the FireDAC connection object.
  • The SQL property with the following SQL SELECT statements that define different integer ranges:

SELECT int4range(null,2000, '(]') rng
UNION ALL
SELECT int4range(3000,4000, '[)') rng
UNION ALL
SELECT int4range(5000,6000, '[]') rng
UNION ALL
SELECT int4range(7000,null, '()') rng
UNION ALL
SELECT int4range(20,20, '()') rng

Notes:

• The parentheses or brackets of the third argument indicate whether the lower and upper bounds are exclusive or inclusive. "[" represents an inclusive lower bound while "(" represents an exclusive lower bound. The same way, "]" represents an inclusive upper bound while ")" represents an exclusive upper bound.
• If you omit the lower bound of a range, it means that all points less than the upper bound are included in the range. Likewise, if you omit the upper bound of the range, then all points greater than the lower bound are included in the range. If both lower and upper bounds are omitted, all values of the element type are considered to be in the range.

• A TDataSource object named DataSource1. This component provides an interface between a dataset component and data-aware controls on a form. In this sample, it is used to provide communication between the dataset and the grid where the dataset is displayed. To this end, the sample sets the following properties:

• The DataSet property of DataSource is set to FDQuery1.
• The DataSource property of DBGrid1 is set to DataSource1.

When you run the application, you see:

• A TDBGrid that is used to display the different data ranges defined on the SQL command.
• Three TDBEdit components that are used to display the lbound,lbound and flags fields of the range type.
• Two TLabel components that are used to display the type of the range bounds.

Uses

• FireDAC.Comp.Client.TFDConnection
• FireDAC.Comp.Client.TFDQuery
• Data.DB.TDataSource
• Vcl.DBGrids.TDBGrid
• Vcl.DBCtrls.TDBEdit
• Vcl.StdCtrls.TLabel

See Also

• FireDAC
• Connect to PostgreSQL (FireDAC)