At 08:26 AM 10/29/2004, Peter wrote:

>I have been told is sql an interpreted language?

My, there are a lot of experts in the world. Such a simple
statement. So many fallacies... There is a world of possible
language manipulation between interpreted and compiled. A
language is not inherently "interpreted" or "compiled". The
handling of SQL is different in different engines, and different
for different uses of SQL in most engines. ISQL is a (feeble)
interpreter of SQL. The Firebird engine runs compiled SQL.

In Firebird, SQL statements are initially translated to a
machine-friendly format called BLR (binary language representation).
That transformation is sometimes called compilation, though it
represents only the first two phases of compilation - lexical
analysis and parsing for syntax.

The SQL->BLR transformation happens when a stored procedure or
trigger is defined, when a program is preprocessed if the program
used the embedded SQL language, when a DSQL statement is prepared,
or as the first phase of a DSQL execute immediate statement.

After the SQL-BLR transformation, a Firebird SQL statement undergoes
a second transformation, which takes it from BLR to an executable
format. This transformation includes semantic analysis - validating
the data references, determining data types, etc., access right
verification, query optimization, and the generation of an execution
structure.

The BLR->execution structure transformation happens as the second
phase of a DSQL prepare or execute immediate. It happens the first
time a stored procedure or trigger is invoked after server startup.

>Is this the case and if so does this mean that the sql script is read
>every time it is applied to a record?

No and no.

>Does it mean that the sql script is read in once and converted to a
>"pcode" (like vb used to be) format and then applied to all records
>specified?

Closer, but not exactly. If you interpret pcode broadly to mean an
execution structure that moves data through a sequence of predefined
routines, yes, that's what it is. The engine does not generate
machine code, so purists will say that statements are not compiled.
I'm not nearly that purist.

>Can the sql scripts in FB be "compiled" for either better performance
>or improved security?

Can and are. If you're using DSQL, prepare your statements rather than
using execute immediate.

Regards,


Ann