Interactive Commands - Firebird

You can enter three kinds of commands or statements interactively at the SQL> prompt:

• SQL data definition (DDL) statements, such as CREATE, ALTER, DROP, GRANT, and REVOKE. These statements create, modify, or remove metadata and objects or control user access permission (privileges) to the database.
• SQL data manipulation (DML) statements such as SELECT, INSERT, UPDATE, and DELETE. The output of SELECT statements can be displayed or directed to a file (see the OUTPUT command).
• isql commands, which fall into three main categories:
• General commands (for example, commands to read an input file, write to an output file, or end an isql session)
• SHOW commands (to display metadata or other database information)
• SET commands (to modify the isql environment)

Creating and Altering Database Objects

In an isql session you can submit DDL statements one by one to CREATE or DROP databases, domains, generators, tables, indexes, triggers, and stored procedures. With the exception of generators, you can also issue ALTER statements for any of these objects.

While it is possible to build a database by submitting and committing a series of DDL statements during an interactive isql session, this approach is ad hoc. It leaves you with no documentation of the process and potential holes in your QA review process.

Scripts

It is very good practice to use a script to create your database and its objects. A script for creating and altering database objects is sometimes known as a schema script, a data definition file, or just a DDL script. The topic of schema scripting is covered in detail in Chapter From Drawing Board to Database.

Scripting ongoing changes in a DDL script gives you a permanent record of the evolution of your database and the ability to review and reverse any imprudent changes. Isql can create a script during an interactive session by passing your keyboard input to a named file. To find out how to do this, read the notes on the OUTPUT command in the next section.

You can run scripts—whether created in isql or by another text editor—using the INPUT command. INPUT can be used inside scripts, too, to build nested scripts.