Introducing isql - Firebird

The command-line utility isql (“isql” stands for Interactive SQL) incorporates tools and techniques for using SQL to maintain database objects, manage transactions, display metadata, and manage database definition scripts. A shell interface is available that is consistent across all platforms. This brief introduction will get you started on the basics of connecting (attaching) to a database and creating your first database.

Starting isql

There are several different ways to connect to a database using isql. One way is to start its interactive shell. To begin, in a command shell, go to the /bin directory of your Firebird installation where the isql program is installed, and start isql as follows.

For a POSIX server:

[chick@hotchicken]# ./isql <press Return/Enter]

For a Windows server:

C:\Program Files\Firebird\Firebird_1_5\bin>isql <press Return/Enter]

The isql shell should now open, displaying this message:

Use CONNECT or CREATE DATABASE to specify a database

Using isql

Once connected to a database, you can query its data and metadata by entering regular dynamic SQL statements as well as a special subset of statements that work only in the isql environment.

The CONNECT Statement

The CONNECT statement is a standard SQL command statement for connecting to a database. Here it is assumed you have not changed the sysdba password yet. If you have (this is recommended), then use your own sysdba password.

Each of the command-line statements in the following examples is a single statement.

For connecting to a Linux/UNIX server:

SQL> CONNECT 'hotchicken:/opt/firebird/examples/employee.fdb' user 'sysdba' password 'masterkey';

For connecting to a Windows server:

SQL> CONNECT 'WINSERVER:C:\Program Files\Firebird\Firebird_1_5\examples\employee.fdb' user 'SYSDBA' password 'masterkey';

Make sure you terminate each SQL command with a semicolon (;) character. If you forget to do so, the next thing you will see is isql’s continuation prompt:

CON>

Whenever you see the continuation prompt, simply type a semicolon and press Enter/Return. At this point, isql will inform you that you are connected:

DATABASE 'hotchicken:/opt/firebird/examples/employee.fdb', User: sysdba
SQL>

If the server is on Windows, you will see this:

DATABASE "WINSERVER:C:\Program Files\Firebird\Firebird_1_5\examples\employee.fdb", User: sysdba SQL>

Continue to play about with the employee.fdb database. You can use isql for querying data, getting information about the metadata, creating database objects, running data definition scripts, and much more.

To get back to the command prompt, type

SQL> QUIT;

Creating a Database Using isql

There is more than one way to create a database using isql. Here, you will look at one simple way to create a database interactively—although, for your serious database definition work, you should create and maintain your metadata objects using data definition scripts (also known as DDL scripts, SQL scripts, metadata scripts, and schema scripts). This topic is covered in detail in Chapter From Drawing Board to Database, in the “Schema Scripts” section.

If you are currently logged into a database through the isql shell, leave it now with the following command:

SQL> QUIT;

Next, restart it, without connecting to a database. For a Linux server:

[chick@hotchicken]# ./isql Use CONNECT or CREATE DATABASE to specify a database

For a Windows server:

C:\Program Files\Firebird\Firebird_1_5\bin>isql Use CONNECT or CREATE DATABASE to specify a database

The CREATE DATABASE Statement

Now, you can create your new database interactively. Let’s suppose that you want to create a database named test.fdb on your Windows server and store it in a directory named “data” on your D drive:

SQL> CREATE DATABASE 'D:\data\test.fdb' user 'SYSDBA' password 'masterkey';

The database will be created and, after a few moments, the SQL prompt will reappear. You are now connected to the new database and can proceed to create some test objects in it.

To verify that there really is a database there, type in this query:

SQL> SELECT * FROM RDB$RELATIONS; <press Enter>

The screen will fill up with a large amount of data! This query selects all of the rows in the system table where Firebird stores the metadata for tables. An “empty” database is not empty—it contains a database that will become populated with metadata as you begin creating objects in your database.

To get back to the command prompt, type

SQL> QUIT;

For full information about using isql, refer to Chapter Interactive SQL Utility


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

Firebird Topics