PostgresDAC-2.5.1: Parameters support in TPSQLDirectQuery

Preface

Once again I am to reveal PostgresDAC-2.5.1 “yummies”. Who missed the beginning could easily catch up.

Crux of the matter

TPSQLDirectQuery allows to execute SQL queries and retrieve result sets very efficiently, it provides great performance actually. Meanwhile, it is not TDataset-compatible. This means that it can’t be assigned to TDatasource.Dataset property and you can’t use it with visual DB-controls. TPSQLDirectQuery is usually used in tasks where data requires some processing without displaying it with/within visual DB-controls.

That’s why it is very simple. But we decided that simplicity is not the antonym for flexibility, so we added the parameters support.

A parameterized SQL statement contains parameters or variables. Parameters can replace data values, such as those used in a WHERE clause for comparisons, that appear in an SQL statement. Ordinarily, parameters stand in for data values passed to the statement.

SELECT * FROM "Customers" WHERE "CustID" = :CustParam;
SELECT * FROM "Customers" WHERE "CustID" = ?;

Note that the names of parameters in usual Delphi notation begin with a colon. The colon is required so that the parameter names can be distinguished from literal values. You can also include unnamed parameters by adding a question mark (?) to your query. Unnamed parameters are identified by position, because they do not have unique names.

But instead of usual Delphi parameters representation we decided to use PostgreSQL one:

SELECT * FROM "Customers" WHERE "CustID" = $1;

Why we preferred PostgreSQL syntax over standard Delphi one? Because SQL with parameters will be sent to server without changes, and parameters’ values will be sent separately. But if we use Delphi parameters then client must replace them before sending, that leads us to tedious and error-prone quoting and escaping.

One more thing. So, not to trouble ourselves we didn’t parse SQL for automatic parameters creation. This means that developer should manually create needed parameters.

PSQLDirectQuery1.SQL.Text := 'SELECT * FROM Customer WHERE CustNo > $1';
with PSQLDirectQuery1.Params.CreateParam(ftInteger, '', ptOutput) do 
  Value := 5132;
PSQLDirectQuery1.Open;
ShowMessage(Format('There is %d rows', [PSQLDirectQuery1.RecordCount]));

Stay tuned!😉

2 thoughts on “PostgresDAC-2.5.1: Parameters support in TPSQLDirectQuery

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s