Tuesday, February 27, 2024
HomeProductsDelphi DAC5 Simple Tips for PostgreSQL Data Access Components in 2024

5 Simple Tips for PostgreSQL Data Access Components in 2024

In this article, we would like to introduce one of the rock-solid sets of data access components – the PgDAC or PostgreSQL data access component.

PgDAC is a library of components that delivers native connectivity to PostgreSQL from C++ Builder and Delphi including Community Edition as well as Lazarus. PgDAC is designed to help programmers to create swift and clean PostgreSQL database-powered applications without the need to deploy any additional libraries and use libpq.dll to connect. Furthermore, it provides direct access to PostgreSQL without a PostgreSQL client and presents itself as an efficient alternative solution to standard connectivity solutions like the Borland database engine or dbExpress drivers. Additionally, PgDAC provides a further opportunity to work with PostgreSQL directly via TCP and IP without involving the client.

PgDAC is not just about connecting to PostgreSQL databases as it covers a wide range of PostgreSQL features like SSL connection working with large objects, composite or geometric types, intervals, and notices. It also provides complete support for fast record insertion, async notification, PostgreSQL sequences, and more.

Delphi and C++ Builder developers appreciate components and libraries that support cross-platform options. All major GUI frameworks are supported by PgDAC so that you can develop FPC applications for Windows, macOS, Linux, iOS, Android, and FreeBSD for x64 and x86 platforms. PgDAC also allows the creation of cross-platform FMX solutions and LCL applications.

Let’s consider a real-world demo application.

First, you need to download PgDAC Trial and install it for evaluation purposes with some limitations. In this demo, we used Rad Studio 11 Alexandria.

After installing, you will see a menu. If you click PgDAC, you can see the information about it.

PostgreSQL Data Access Components Trial Adition
Figure 1. Information about PgDAC.

Let’s create a new multi-device application with Delphi. Select the type of application. In our example, we have selected Blank Application. Click Ok.

Creation of a new multi-device application
Figure 2. Creating a new multi-device application.

In the list under Palette, you can see PgDAC, TPgConnection, TPgQuery, TPgTable, TPgDataSource, TPgUpdateSQL, and stored product components which are the basic components for the PostgreSQL connection and fetching processes. Several other valuable components like PgScript and PgMetadata make you proactive in obtaining metadata about database objects or other components.

The basic components for the PostgreSQL connection and fetching processes
Figure 3. The basic components for the PostgreSQL connection and fetching processes.

To connect to a PostgreSQL local database server, double-click the TPgConnection component and insert the connection information in the appeared dialog box (see Figure 5).

Let’s consider the example.

In pgAdmin, we previously created a my_db database and a demo table ‘table’.

Create a demo table in pgAdmin
Figure 4. A created demo table “table” in a my_db database in pgAdmin.

To connect to this localhost server, enter its name in the PgConnection dialog box as shown in Figure 5. The username is postgres. Then enter the password and select the name of your database from a dropdown list.

Click Connect and then OK.

Creating a connection to a PostgreSQL database
Figure 5. Creating a connection to a PostgreSQL database.

Now we have connected to our database, so when we run the application, it just connects because we have already selected the connected property.

Now we need to write one line of code on close. This means that when we close the application, it kills the database server connection.

Killing the connection with the database server
Figure 6. Killing the connection with the database server.

Now let’s select a PgQuery. In the Properties, PgConnection is already selected.

In the SQL property here, we’re going to type some SQL code:


After code insertion, click OK and activate. Then mark Activate in the Properties.

SQL query activating
Figure 7. SQL query activating.

To write the SQL query, you can also use Code Editor in the Object Inspector as shown in Figure 8.

Creating an SQL query
Figure 8. Creating an SQL query using the PgQuery component.

Now we have a data source component here. We just need to show the data to the user with our UI controls.

Right-click and open the LiveBindings Designer and as you can see, we already have the fields available.

LiveBindings Designer window
Figure 9. LiveBindings Designer window.

Click the LiveBindings Wizard, and select Link a grid with a data source. Then click Next –> TStringGrid –> Next and as an existing source, select PgQuery. Add a data source navigator if you need it.

Close it.

Now we just need to make some changes. Right-click the grid and select Quick Edit. Choose the alignment and layout and close the window. The navigator should be inside the form, not inside the stream grid component, so drag it to the right place. Make additional changes if you need.

Click Save and then Run.

The result of the connection to the database
Figure 10. The result of the connection to the database.

As you can see, we have connected to our database. You can also add new data by pressing +. In our example, we have added a string with literal values a, b, and c.

Adding new values
Figure 11. Adding new values.

Click Save and Close. If you open the pgAdmin, just select the data and click Run. As you can see, we have the updated information available inside our demo table.

Updated information in the table in pgAdmin
Figure 12. Updated information in the table in pgAdmin.

To learn more about PostgreSQL Data Access Components, please check out its webpage where you can find all the interesting information along with the extensive documentation.