A “non-parameterized” query uses constants in expressions for search conditions. For example, in this querySELECT 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;
Firebird Related Interview Questions
|RDBMS Interview Questions||MySQL Interview Questions|
|Linux Interview Questions||Mac OS X Deployment Interview Questions|
|Windows Administration Interview Questions||Windows Server 2003 Interview Questions|
|SQL Interview Questions||NoSQL Interview Questions|
|Advanced C++ Interview Questions|
Introduction To Client/server Architecture
About Firebird Data Types
Date And Time Types
Blobs And Arrays
From Drawing Board To Database
Creating And Maintaining A Database
Firebird’s Sql Language
Expressions And Predicates
Querying Multiple Tables
Ordered And Aggregated Sets
Overview Of Firebird Transactions In
Programming With Transactions
Introduction To Firebird Programming
Developing Psql Modules
Error Handling And Events
Security In The Operating Environment
Configuration And Special Features
Interactive Sql Utility (isql)
Database Backup And Restore (gbak)
Housekeeping Tool (gfix)
Understanding The Lock Manager
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.