Understanding static SQL statements

This is an introduction to static SQL statements.

Static SQL statements are SQL instructions that are a part of the Genero BDL language syntax. Static SQL statements can be used directly in the source code as a normal procedural instruction. The static SQL statements are parsed and validated at compile time. At runtime, these SQL statements are automatically prepared and executed by the runtime system.

Program variables can be used inside static SQL statements. Variables are detected by the compiler and handled as SQL parameters at runtime.

The following example defines two variables that are directly used in an INSERT statement:

MAIN
  DEFINE iref INTEGER, name CHAR(10)
  DATABASE stock 
  LET iref = 65345
  LET name = "Kartopia"
  INSERT INTO item ( item_ref, item_name ) VALUES ( iref, name )
  SELECT item_name INTO name 
    FROM item WHERE item_ref = iref 
END MAIN

As it is integrated in the language syntax, static SQL statement usage clarifies the source code, but the SQL text is hard-coded and cannot be modified at runtime as is possible with PREPARE / EXECUTE instructions of dynamic SQL.

Limited SQL syntax is part of the language, only common SQL statements such as INSERT, UPDATE, DELETE, SELECT are supported.

The compiler supports also SQL ... END SQL blocks to write free SQL text in your programs. The SQL syntax in SQL blocks is not limited to the static SQL syntax.