SQL Statements - Firebird

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:

  • Those that CREATE ALTER, or DROP metadata objects (also known as schema objects or schema elements). Such queries are referred to as data definition language , or DDL.
  • Those that operate on data. They provide language for defining sets of data in columns and rows and specifying operations to
  • Retrieve and transform (SELECT) images of those sets from storage for reading by applications.
  • Change the state of the database by adding, modifying, or deleting the specified sets (INSERT, UPDATE, and DELETE operations). These statements that operate on data are referred to as data manipulation language , or DML.

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:

  • Embedded SQL (ESQL): This is the “base” SQL implementation, consisting of DDL and DML syntax that the other subsets incorporate, where possible. It was the original implementation of SQL in the InterBase predecessors, designed to be embedded in client applications and pre-compiled.
  • Dynamic SQL (DSQL): This is the subset in most common use today. It is used by all database interface layers that communicate with the server through the application programming interface (API). Some DDL commands available to ESQL that cannot be implemented in DSQL are replaced by API function call structures.
  • Interactive (ISQL): This is the language implemented for the command-line isql utility. It is based on DSQL with extensions for viewing metadata and some system statistics and for controlling isql sessions.
  • Procedural SQL (PSQL): This is the language for writing stored procedures and triggers. This consists of all DML statements, with the addition of a number of procedural 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

  • Control them carefully if you are implementing them in end-user applications.
  • Expect compiler exceptions if you attempt to use them in stored procedures.

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.

  • Chapter DML Queries introduces the concept of sets, and the structure and syntax of DML queries. It includes an “Optimization Topic” section that covers working with the query optimizer.
  • Chapter Expressions and Predicates describes functions, operations, and expressions, and how to use them.
  • Chapter Querying Multiple Tables looks at queries that operate on multiple tables using joins, subqueries, and unions.
  • Chapter Ordered and Aggregated Sets examines the syntax and issues for specifying sets that require sorting or grouping and those that retrieve rows from multiple tables without joining.
  • Chapter Viewscovers the definition and use of views and other derived, table-like objects.

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:

  • The formats of certain regular SQL statements may vary slightly between the static and dynamic SQL variants.
  • Statement terminators can vary according to the language subset:
  • ESQL and PSQL statements are terminated with semicolons.
  • The terminator is omitted in DSQL statements passed through the API structures.
  • DSQL passed through the isql interactive query utility requires terminators, which can be set, using SET TERM, to any printable character from the first 127-character ASCII subset.

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.

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

Firebird Topics