The Firebird 5.0 Language Reference

This section describes how to create a database, connect to an existing database, alter the file structure of a database and how to drop a database.It also shows two methods to back up a database and how to switch the database to the “copy-safe” mode for performing an external backup safely.

`CREATE DATABASE`

Creates a new database

Available in

DSQL, ESQL

Syntax

CREATE {DATABASE | SCHEMA} <filespec>
  [<db_initial_option> [<db_initial_option> ...]]
  [<db_config_option> [<db_config_option> ...]]

<db_initial_option> ::=
    USER username
  | PASSWORD 'password'
  | ROLE rolename
  | PAGE_SIZE [=] size
  | LENGTH [=] num [PAGE[S]]
  | SET NAMES 'charset'

<db_config_option> ::=
    DEFAULT CHARACTER SET default_charset
      [COLLATION collation] -- not supported in ESQL
  | <sec_file>
  | DIFFERENCE FILE 'diff_file' -- not supported in ESQL

<filespec> ::= "'" [server_spec]{filepath | db_alias} "'"

<server_spec> ::=
    host[/{port | service}]:
  | <protocol>://[host[:{port | service}]/]

<protocol> ::= inet | inet4 | inet6 | xnet

<sec_file> ::=
  FILE 'filepath'
  [LENGTH [=] num [PAGE[S]]
  [STARTING [AT [PAGE]] pagenum]

Note	Each db_initial_option and db_config_option can occur at most once, except sec_file, which can occur zero or more times.

Table 1. `CREATE DATABASE` Statement Parameters
Parameter	Description
filespec	File specification for primary database file
server_spec	Remote server specification.Some protocols require specifying a hostname.Optionally includes a port number or service name.Required if the database is created on a remote server.
filepath	Full path and file name including its extension.The file name must be specified according to the rules of the platform file system being used.
db_alias	Database alias previously created in the `databases.conf` file
host	Host name or IP address of the server where the database is to be created
port	The port number where the remote server is listening (parameter RemoteServicePort in `firebird.conf` file)
service	Service name.Must match the parameter value of RemoteServiceName in `firebird.conf` file)
username	Username of the owner of the new database.The maximum length is 63 characters.The username can optionally be enclosed in single or double quotes.When a username is enclosed in double quotes, it is case-sensitive following the rules for quoted identifiers.When enclosed in single quotes, it behaves as if the value was specified without quotes.The user must be an administrator or have the `CREATE DATABASE` privilege.
password	Password of the user as the database owner.When using the `Legacy_Auth` authentication plugin, only the first 8 characters are used.Case-sensitive
rolename	The name of the role whose rights should be taken into account when creating a database.The role name can be enclosed in single or double quotes.When the role name is enclosed in double quotes, it is case-sensitive following the rules for quoted identifiers.When enclosed in single quotes, it behaves as if the value was specified without quotes.
size	Page size for the database, in bytes.Possible values are 4096, 8192, 16384 and 32768.The default page size is 8192.
num	Maximum size of the primary database file, or a secondary file, in pages
charset	Specifies the character set of the connection available to a client connecting after the database is successfully created.Single quotes are required.
default_charset	Specifies the default character set for string data types
collation	Default collation for the default character set
sec_file	File specification for a secondary file
pagenum	Starting page number for a secondary database file
diff_file	File path and name for DIFFERENCE files (.delta files) for backup mode

The CREATE DATABASE statement creates a new database.You can use CREATE DATABASE or CREATE SCHEMA.They are synonymous, but we recommend to always use CREATE DATABASE as this may change in a future version of Firebird.

A database consists of one or more files.The first (main) file is called the primary file, subsequent files are called secondary file(s).

Note	Multi-file Databases Nowadays, multi-file databases are considered an anachronism.It made sense to use multi-file databases on old file systems where the size of any file is limited.For instance, you could not create a file larger than 4 GB on FAT32.

The primary file specification is the name of the database file and its extension with the full path to it according to the rules of the OS platform file system being used.The database file must not exist at the moment the database is being created.If it does exist, you will get an error message, and the database will not be created.

If the full path to the database is not specified, the database will be created in one of the system directories.The particular directory depends on the operating system.For this reason, unless you have a strong reason to prefer that situation, always specify either the absolute path or an alias, when creating a database.

Using a Database Alias

You can use aliases instead of the full path to the primary database file.Aliases are defined in the databases.conf file in the following format:

alias = filepath

Note

Executing a CREATE DATABASE statement requires special consideration in the client application or database driver.As a result, it is not always possible to execute a CREATE DATABASE statement.Some drivers provide other ways to create databases.For example, Jaybird provides the class org.firebirdsql.management.FBManager to programmatically create a database.

If necessary, you can always fall back to isql to create a database.

Creating a Database on a Remote Server

If you create a database on a remote server, you need to specify the remote server specification.The remote server specification depends on the protocol being used.If you use the TCP/IP protocol to create a database, the primary file specification should look like this:

host[/{port|service}]:{filepath | db_alias}

Firebird also has a unified URL-like syntax for the remote server specification.In this syntax, the first part specifies the name of the protocol, then a host name or IP address, port number, and path of the primary database file, or an alias.

The following values can be specified as the protocol:

INET: TCP/IP (first tries to connect using the IPv6 protocol, if it fails, then IPv4)
INET4: TCP/IP v4
INET6: TCP/IP v6
XNET: local protocol (does not include a host, port and service name)

<protocol>://[host[:{port | service}]/]{filepath | db_alias}