Using Parameters - Firebird

A “non-parameterized” query uses constants in expressions for search conditions. For example, in this query

SELECT MAX(COST * QUANTITY) AS BEST_SALE FROM SALES WHERE SALES_DATE > '31.12.2003' ; the query is asked to operate on all of the rows in SALES that have a SALES_DATE later than the last day of 2003.

Data access interfaces that implement the Firebird API have the capability to process the constants in search conditions as replaceable parameters. The API allows a statement to be submitted to the server as a kind of template that represents these parameters as placeholders. The client request asks for the statement to be prepared— by obtaining syntax and metadata validation—without actually executing it.

A DSQL application can set up a statement with dynamic search conditions in the WHERE clause, have it prepared once, and then assign values to the parameters one or many times, just before each execution. This capability is sometimes referred to as late binding. Application interfaces vary in the way they surface late parameter binding to the host language. Depending on the interface you use for application development, a parameterized version of the last example may look something like the following:

SELECT MAX(COST * QUANTITY) AS BEST_SALE FROM SALES WHERE SALES_DATE > ? ;

The replaceable parameter in this example allows the application to prepare the statement and capture from the user (or some other part of the application) the actual date on which the query is to be filtered. The same query can be run repeatedly, within the same transaction or in successive transactions, with a variety of dates, without needing to be prepared again.

The API “knows” the order and format of multiple parameters because the application interface passes descriptive structures, XSQLDAs, that contain an array of SQLVARs, the variable descriptors that describe each parameter, and other data describing the array as a whole.

Delphi and some other object-oriented API implementations use the methods and properties of classes to hide the machinery used for creating and managing the raw statement and the descriptors internally. Other interfaces code the structures closer to the surface. The statement is passed to the server with a format like the following:

INSERT INTO DATATABLE(DATA1, DATA2, DATA3, DATA4, DATA5,....more columns) VALUES (?, '?', '?', ?, ?, ....more values);

If parameters are implemented in your application programming language, or can be, then it is highly recommended to make use of them.

Note for Delphi Users

Delphi, having been “made in Borland” like Firebird’s InterBase ancestors, implements a format that mimics that used in PSQL to refer to the values of local variables in SQL statements and in ESQL to pass host variables. It requires all parameters to be explicitly named and prefixed with the colon symbol. In Delphi, the preceding simple example would be expressed in the SQL property of a data access object as follows:

SELECT MAX(COST * QUANTITY) AS BEST_SALE FROM SALES WHERE SALES_DATE > :SALES_DATE ;

Once the Prepare call has validated the statement and passed the metadata description back to the client application, the data access object then lets the latest value be assigned to the parameter using a local method that converts the value to the format required by Firebird:

aQuery.ParamByName ('SALES_DATE').AsDate := ALocalDateVariable;

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Firebird Topics