An SQL statement is used to submit a query to the database. The language of the query is expressed in statements that specify purpose: what is to be done (an operation), the objects to which it is to be done, and the details of how it is to be done. In theory, every possible interaction between the outside world and a database is surfaced through a statement syntax.
Statement syntaxes are grouped according to two broad purposes:
The Firebird implementation of the SQL language falls into a number of overlapping subsets, each used for a specific purpose and incorporating its own language extensions:
Data Definition Language (DDL)
When defining metadata for use in a Firebird database, we use a lexicon of standard SQL statements and parameters that provide for the creation of an object by its type and name—or identifier—and for specifying and modifying its attributes. Also in this lexicon are statements for removing objects.
Queries using DDL are reserved for the purpose of metadata definition, so
Firebird’s DDL is described in Parts Three and Four. View definitions and the granting and revoking of SQL permissions are also DDL. Views, which incorporate both DDL and DML, are discussed in Chapter Ordered and Aggregated Sets. Defining and manipulating SQL permissions is described in Chapter Database-Level Security.
Data Manipulation Language (DML)
The DML statements, syntaxes, and expressions for retrieving and manipulating sets of data form the content of this part of the book.
Embedded Language Features (ESQL)
Some SQL database management systems, including Firebird, provide the capability to embed SQL statements directly inside 3GL host programming language modules. The standard provides conceptual algorithms by which embedded application programming is to be accomplished, but it does not make any implementation rules.
Firebird’s embedded application programming capabilities include a subset of SQL-like statements and constructs that can be incorporated into the source code of a program for pre-processing before the code goes to the compiler. The embedded SQL language constructs are known as embedded SQL (ESQL). ESQL statements cannot be generated dynamically.
ESQL is not valid in stored procedures and triggers, and procedure language (PSQL) is not valid in embedded SQL. ESQL can execute stored procedures. ESQL is used in programs written in traditional languages such as C and COBOL, prefaced by the EXEC SQL statement. The pre-processor, gpre, converts ESQL statements into host language data structures and calls to the Firebird server.
Dynamic vs. Static SQL
SQL statements embedded and pre-compiled in code are sometimes referred to as static SQL. By contrast, statements that are generated by a client program and submitted to the server for execution during runtime are known as dynamic SQL (DSQL).
Unless you are writing code for ESQL applications, you are using DSQL. Statements executed by the interactive SQL utility (isql) or other interactive desktop utility programs are DSQL, as are those processed through client applications that use the API directly or indirectly (through database access drivers such as ODBC, JDBC, and the BDE).
In embedded applications, static SQL allows queries to bypass the Firebird API, instead being pre-compiled to use macro calls to the API structures. Because the whole query process is pre-compiled, it can execute faster than dynamic statements, which are submitted, parsed, and prepared at runtime.
Language Subset Variations
By design, or by accident of history, some minor variations exist between the subsets of Firebird’s SQL language:
Interactive SQL (ISQL)
The interactive query tool isql uses DSQL statements, along with two subsets of extension commands (the SET XXX and SHOW XXX groups), which allow certain settings and schema queries, respectively, to be performed interactively. Certain SET commands can also be included in data definition scripts (DDL scripts for batch execution In isql) and in embedded SQL.
Procedural Language (PSQL)
The standard does not prescribe procedural language features since, in principle, it assumes that general programming tasks will be accomplished using the host language. There is no specification for language constructs to manipulate, calculate, or create data programmatically inside the database management system.
Those RDBMS engines that support server-based programming usually provide SQL-like statement formats and syntaxes to extend SQL. Each vendor’s implementation freely provides its own variants of these constructs. Typically, such code modules in the database are called stored procedures.
Firebird provides them as procedure language (sometimes referred to as PSQL), a set of SQL extensions that programmers use, along with a variant of the ESQL language set, to write the source code for stored procedures and triggers. PSQL is extended to include flow control, conditional expressions, and error handling. It has the unique ability to generate multi-row output sets that can be directly accessed using SELECT statements.
Certain SQL constructs, including all DDL statements, are excluded. However, from Firebird 1.5 onward, the EXECUTE STATEMENT syntax is supported in PSQL to enable the execution of DSQL commands, including some DDL.
PSQL for stored procedures and triggers is described in detail in Part Seven.
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|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.