FirebirdSQL logo

Creating a database using isql

There is more than one way to create a database with isql.Here, we 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.

Starting isql

To create a database interactively using the isql command shell, type isql (Windows) or ./isql (Linux) at the command prompt in the directory where the Firebird tools are.

Note

In the following examples, [chevron circle left] means “hit kbd:[Enter]”

C:\Programmas\Firebird\Firebird_3_0>isql[chevron circle left]
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 and store it in a directory named data on your D drive:

SQL>create database 'D:\data\test.fdb' page_size 8192[chevron circle left]
CON>user 'sysdba' password 'masterkey';[chevron circle left]
Important
  • In the CREATE DATABASE statement it is mandatory to place quote characters (single or double) around path and password.This is different from the CONNECT statement.Quoting the username is optional, unless it is case-sensitive or contains spaces, international characters or any other character that is not allowed in a regular (unquoted) identifier.

  • If the connection string doesn’t start with a host or protocol name, creation of the database file is attempted with your OS login as the owner.This may or may not be what you want (think of access rights if you want others to be able to connect).If you prepend localhost: or a protocol to the path or alias, the server process will create and own the file.

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, let’s first type in this query:

SQL>select * from rdb$relations;[chevron circle left]

Although you haven’t created any tables yet, the screen will fill up with a large amount of data!This query selects all rows in the system table RDB$RELATIONS, where Firebird stores the metadata for tables.An “empty” database is not really empty: it contains a number of system tables and other objects.The system tables will grow as you add more user objects to your database.

To get back to the command prompt type QUIT or EXIT, as explained in the section on connecting.

Creating a database as a non-privileged user

In Firebird 5, if you try to create a database other than in embedded mode as someone who is not a Firebird admin (i.e. SYSDBA or an account with equal rights), you may be in for a surprise:

SQL>create database 'xnet://D:\data\mydb.fdb' user 'john' password 'lennon';[chevron circle left]
Statement failed, SQLSTATE = 28000
no permission for CREATE access to DATABASE D:\DATA\MYDB.FDB

Non-admin users must explicitly be granted the right to create databases by a Firebird admin:

SQL>grant create database to user john;[chevron circle left]

After that, they can create databases.

Notice that with a serverless connection, i.e. without specifying a host name or protocol before the database name (and Engine13 enabled!), Firebird won’t deny any CREATE DATABASE statement.It will only fail if the client process doesn’t have sufficient rights in the directory where the database is to be created.