How To Create Database In Firebird

<< | IBExpert | >>

Tutorial

A new database can be created by simply using the IBExpert menu item Database / Create Database... or using the respective icon in the Database toolbar. The Create Database dialog appears:

(1) Server: first the server which is to store the database needs to be specified. This can be local or remote.

Remote: the remote connection needs to be defined by specifying (2) Server name and (3) Protocol. The drop-down list shows all servers previously connected to/from this workstation/PC.
Local: LOCALHOST (own Server). To create a new database on the same machine where IBExpert is in use, you do not need to enter a server name.

We recommend always referencing a server, even if your database is sitting locally on your machine. Going directly using the local specification can cause problems (refer to (3) Protocol below), particularly with Windows Vista, so always use the Remote and LOCALHOST options.

The DOS PING LOCAL HOST or PING SRVNAME command shows the path if unknown (it is not necessary to know which operating system is running or where this server is). By specifying a local server, fields (2) and (3) are automatically blended out, as they are in this case irrelevant.

(2) Server name: must be known when accessing remotely. The following syntax should be used:

Windows SERVER_NAME:C:\path\database.gdb
Linux SERVER_NAME:/path/database.gdb

The standard port for Firebird and InterBase^� is 3050. However this is sometimes altered for obvious reasons of security, or when other databases/Firebird versions are already using this port. If a different port is to be used for the Firebird/InterBase^� connection, the port number needs to be included as part of the server name. For example, if port number 3055 is to be used, the server name is SERVER/3055. If you use multiple Firebird versions and have a database, db1, sitting locally on C:\ root using the Firebird version on port 3052 (which has been specified in the firebird.config), the database connection path would be:

          localhost3052:C:\db1.fdb

(3) Protocol: a drop-down list of three options: TCP/IP, NetBEUI or SPX. As a rule we recommend you always use TCP/IP (worldwide standard).

SPX used to be used by Novell; now even Novell supports TCP/IP.
NetBEUI (aka WNET) - is not really a network protocol, it simply accesses the line. It is slow as it makes everything available everywhere and anyone can access the information. This is also purely a Windows protocol. Since Firebird 2.0 this protocol no longer performs client impersonation (see Change to WNET ("NetBEUI") Protocol).

Note: in DOS the TRACERT command lists the protocol route. TCP/IP intelligently takes another direction, if one or part of the lines on the quickest route is blocked or down.

As the local protocol should only be used if really necessary on machines that are isolated and not part of any network, specify the database server connection if possible using Remote and LOCALHOST and selecting one of the above protocols. Although the introduction of the new local Firebird protocol, XNET, in Firebird 2.0 has solved many of the former problems of the previous local transport protocol (often referred to as IPC or IPServer) - please refer to Local protocol--XNET for further information.

(4) Database: by clicking on the folder icon to the right of this field, the path can easily found and specified, the database name entered, and the suffix selected from the pull-down list. The database name must always be specified with the drive and path when creating a database. Please note that the database file for a Windows server must be on a physical drive on the server, because Firebird/InterBase^� does not support databases on mapped drive letters. The database suffixes do not have to adhere to the forms offered in the list.

(5) Client Library File: This displays the path and client library file name, as specified in the Default Client Library option, found in the IBExpert Options menu item, Environment Options / Preferences. This can, of course, be overwritten if wished.

(6) User Name: Only those names may be entered when creating a database, which already exist in the server security database ISC4.GDB, security.fdb or since Firebird 2.0 the new security2.fdb (which stores server rights; user rights for the database objects are stored in the database itself). The person creating the database becomes the database owner. Only the database owner and the SYSDBA (System Database Administrator) are allowed to perform certain operations upon the database (such as a database shutdown). Therefore if the database owner is defined as the SYSDBA, this is the only person entitled to perform these operations. Note: when a role with the name SYSDBA is created, no other users (not even the SYSDBA) can access the database. Therefore ensure the database is created by another user already registered in the security database and not the SYSDBA. This way there are at least two users able to perform key administrative tasks.

(8) Password: The passwords are encrypted in the ISC4.GDB, security.fdb or security2.fdb. If you insist upon using the SYSDBA name as the database owner, at least change the standard password (masterkey) to ensure at least some degree of security! The masterkey password should be changed as soon as possible after creating the database.

Firebird/InterBase^� verifies only the first 8 characters of a password, even if a longer word is entered, i.e. in the case of the masterkey password only "masterke" is verified. All characters following the 8th are ignored.

(7) SQL Dialect: Here Dialect 1 (up to and including InterBase^� 5) or 3 (InterBase^� 6/Firebird) needs to be specified. For more information regarding this subject, please refer to SQL Dialect.

(9) Role: Here you can specify the SQL role name while creating the database.

(10) Page size: Specifies the database page size in bytes. For more information regarding this subject, please refer to Page size.

(11) Charset: Here the default character set can be defined for the database. (A default character set can be specified as default for all new databases in the IBExpert Options menu item, Environment Options, under Default character set.) This character set is useful, when the database created is to be used for foreign languages as it is applicable for all areas of the database unless overridden by the domain or field definition. If not specified, the parameter defaults to NONE, i.e. values are stored exactly as typed. For more information regarding this subject, please refer to Default character set.

(12) Collation (FB 2.5): This field allows you to specify a default collation for Firebird 2.5 databases.

(13) Register Database After Creating: This checkbox automatically generates the Database Registration dialog so that the database can be registered. Registration is necessary, so that IBExpert recognizes that a database is present. The Register Database dialog however offers many further options. We recommend clicking this checkbox (the default setting), so that the database is registered immediately after creation. If the database is not registered at the time of creation, it cannot be seen in the DB Explorer of the left of the IBExpert screen. This means that the user must know exactly where the new database can be found (i.e. which server, path, name etc.) when registering at a later date.

Tip: IBExpert recommends creating a User Database - please refer to Environment Options / IBExpert User Database for further information.

For those preferring SQL, the syntax is as follows:

          CREATE {DATABASE | SCHEMA}          filespec          [USER          username          [PASSWORD          password]]  [PAGE_SIZE ] int]  [LENGTH [ int [PAGE[S]]]  [DEFAULT CHARACTER SET charset]  [secondary_file];   <secondary_file> = FILE          filespec          [fileinfo] [secondary_file]   <fileinfo> = [LENGTH [=] int [PAGE[S]] | STARTING [AT [PAGE]] int }  [fileinfo]

For example:

          CREATE DATABASE          C:\DATABASEFILES\employee.gdb          DEFAULT CHARACTER SET ISO8859_1  FILE          employee2.gdb          STARTING AT PAGE 10001;

See also:
Default character set
Page size
CREATE statement
Register Database
Database Designer
Memory configuration
CREATE DATABASE
Creating your first database
Firebird Internals