segunda-feira, 26 de fevereiro de 2007

ISQL - Firebird Interactive SQL Tool

ISQL is a command-line tool for interactive SQL access to databases. It can also be used to perform administrative tasks in command-line environments (like (metadata) updates via a batch file).

General Syntax

isql <options> [<database>]

isql -?


Extract metadata incl. legacy non-SQL tables
Bail on errors (set bail on)
-c(ache) <num>
Number of cache buffers
-ch(arset) <charset>
Connection Character Set (set names)
-d(atabase) <database>
Database name to put in script creation
Echo commands (set echo on)
Extract metadata
-i(nput) <filename>
Process SQL script file (set input)
Merge standard error
Merge diagnostic
No autocommit DDL (set autoddl off)
Do not show warnings
-o(utput) <filename>
Output file (set output)
-pag(elength) <size>
Page Length
-p(assword) <password>
Connection Password
Do not show the message "Use CONNECT..."
-r(ole) <role>
Role Name
-r2 <role>
Role (uses quoted identifier)
-s(qldialect) <dialect>
SQL Dialect (set sql dialect)
-t(erminator) <term>
Command Terminator (set term)
-u(ser) <user>
User Name
Extract Metadata
Show Program and Server Version

Interactive Mode

ISQL enters an interactive mode. Command lines start with a SQL> prompt, continuing lines with a CON> prompt.
NOTE that every command has to be terminated by a semi-colon (;).

Use CONNECT or CREATE DATABASE to specify a database
SQL> connect elias:apqp user sysdba password masterkey;
Database: elias:apqp, User: sysdba
SQL> _

You can now enter DDL, DML or special ISQL commands.

You can also specify the database directly in the isql call:
C:ProgrammeFirebird2bin>isql -user SYSDBA -password masterkey elias:apqp

Database: elias:apqp, User: SYSDBA
SQL> exit;

ISQL Commands

You can get a list of commands by calling the HELP; command.

BLOBDUMP <blobid> <file> 
Dump BLOB to a file
BLOBVIEW <blobid> 
View BLOB in text editor
EDIT [<filename>] 
Edit SQL script file and execute
Edit current command buffer and execute
Display Help
Display Help for the SET command
INput <filename> 
Take input from the named SQL file
OUTput [<filename>] 
Write output to named file
Return output to stdout
SET <option> 
Set option. Use HELP SET for a complete list
SHELL <command> 
Execute Operating System command in sub-shell
SHOW <object> [<name>] 
Exit and Commit changes
Exit and Roll back changes

SET Options

There are several options that influence the behaviour of ISQL (this list can be retrieved by calling HELP SET;)

Display current SET options
Toggle autocommit of DDL statements
Toggle bailing out on errors in non-interactive mode
Display BLOBS of subtype <n> or ALL
Turn off BLOB display
Toggle count of selected rows on/off
Toggle command echo on/off
Toggle display of query column titles
Toggle column or table display format
SET NAMES <csname>
Set name of runtime character set
Toggle display of query access plan
Toggle display of query plan without executing
Set SQL Dialect to <n>
Toggle display of performance statistics
Toggle display of timestamp with DATE values
SET TERM <string>
Change statement terminator string
SET WIDTH <col> [<n>]
Set/unset print width to <n> for column <col>

By just calling SET without parameters you can get a list of all settings.


SET TERM is special in that it changes the statement termination character. The default statement termination character is a semi-colon. However, if you want to create or alter a Stored Procedure or Trigger, the semi-colons contained in the body would also terminate the command that define the procedure. So to define a stored procedure:

  • Use SET TERM to change the statement termination to something that does not occur in your procedure
  • Create or alter your Stored Procedure or Trigger
  • Terminate this command with the new termination character
  • Change the termination character back to a semi-colon using SET TERM


SQL> set term ^ ;
SQL> create procedure Mul (a integer, b integer)
CON> returns (Result integer)
CON> as begin
CON> Result = a * b;
CON> suspend;
CON> end ^
SQL> set term ; ^

Note that the end statement of the procedure is terminated with a caron (^) instead of a semi-colon (;). The caron has been defined as the new termination character by the first SET TERM statement.

Running SQL Scripts

You can run an SQL script by using the -i command line option. To further suppress the "Use CONNECT or CREATE DATABASE to specify a database" message that appears everytime when ISQL starts, use the -q option.

C:ProgrammeFirebird2bin>isql -q -i c:ScriptsCreateScript.sql


In this case, the script must contain a CONNECT or CREATE DATABASE command.

Scripts should also use SET NAMES to define the Client Character Set they use.

quinta-feira, 22 de fevereiro de 2007

GFIX - Firebird Administration

GFIX is Firebird's command line tool for administration issues like data repair, sweeping, etc.

General Syntax

gfix [options] -user <username> -password <password> <database> [options]

Database Shutdown

When a database has been shut down, only SYSDBA and the database owner are able to connect to the database in order to perform administrative tasks.


-at[tach] <seconds>
Used with the -shut option. Waits <seconds> seconds for all current connections to end. If after <seconds> seconds there are still connections open, the shutdown will be cancelled and return an error.
-f[orce] <seconds>
Used with the -shut option. Waits <seconds> seconds for all connections and transactions to end. After this time, all connections and transactions are cancelled and the database is shut down. Use with caution.
If a -shut operation is pending, it is cancelled. Otherwise, takes a database back online
Shut down database. Must be used together with -attach, -force or -tran
-shut {normal | multi |
single | full}
Firebird 2.0 and later: New shutdown modes

NORMAL: Database is active and online
MULTI: Only connection from SYSDBA and the Database Owner will be allowed (compatible mode with Firebird 1.0/1.5)
SINGLE: Only one SYSDBA connection will be allowed
FULL: Exclusive shutdown: Database is completely offline, no connections will be allowed (it is now possible to access the database file safely on a file basis, e.g. for backups)

-tr[an] <seconds>
Used with the -shut option. Waits <seconds> seconds for all running transactions to end. If after <seconds> seconds there are still running transactions, the shutdown will be cancelled.


Shut down database, wait 60 seconds until all connections are closed

gfix -user SYSDBA -password "masterkey" dbserver:/db/mydb.fdb -shut -attach 60

Note that GFIX will terminate with an error if there are still connections open after 60 seconds.

Shut down database, force shutdown after 60 seconds

gfix -user SYSDBA -password masterkey dbserver:/db/mydb.fdb -shut -force 60

Shut down database, force shutdown NOW

gfix -user SYSDBA -password masterkey dbserver:/db/mydb.fdb -shut -force 0

Put database online again

gfix -user SYSDBA -password masterkey dbserver:/db/mydb.fdb -online

Shut down database to single user mode (Firebird 2.0)

gfix -user SYSDBA -password masterkey dbserver:/db/mydb.fdb -shut single -force 60

Put database online again (Firebird 2.0)

gfix -user SYSDBA -password masterkey dbserver:/db/mydb.fdb -shut normal

Shut down database, force shutdown NOW, allow no subsequent connections, even from SYSDBA or Owner (Firebird 2.0)

gfix -user SYSDBA -password masterkey dbserver:/db/mydb.fdb -shut full -force 0

Database Repair, Sweeping


Use with the -v option. Checks all records and pages and releases unassigned record fragments
-h[ousekeeping] 0
Switch off automatic sweeping
-h[ousekeeping] <n>
Set Sweep Interval to <n> transactions (default is 20000)
Ignores checksum errors during a validate or sweep
Marks corrupt records as unavailable so they are skipped on a subsequent backup
Use with the -v option. Checks all records and pages and reports errors but does not repair them
Forces an immediate sweep
Check database for validity. At the same time, errors are reported and repaired


Validate Database

gfix -user SYSDBA -password masterkey dbserver:/db/mydb.fdb -v -f

Sweep Database now

gfix -user SYSDBA -password masterkey dbserver:/db/mydb.fdb -s

Set Sweep Interval to 50000 transactions

gfix -user SYSDBA -password masterkey dbserver:/db/mydb.fdb -h 50000

Switch off Automatic Sweeping

gfix -user SYSDBA -password masterkey dbserver:/db/mydb.fdb -h 0



-b[uffers] <pages>
Default cache buffers for the database will be set to <pages> pages
-c[ommit] <id>
Commits limbo transaction specified by the given <id>
-c[ommit] all
Commits all limbo transactions
Display IDs of all limbo transactions and what would happen to each transaction if you would use -t on it
-mo[de] read_write
Set mode of database to read/write (default). Requires exclusive access to database (shutdown)
-mo[de] read_only
Set mode of database to read-only. Requires exclusive access to database (shutdown)
-pa[ssword] <password>
Database password
Use with -l. Prompts for action.
-r[ollback] <id>
Rolls back limbo transaction specified by the given <id>
-r[ollback] all
Rolls back all limbo transactions
-s[ql_dialect] 1
Sets SQL dialect 1 for the database
-s[ql_dialect] 3
Sets SQL dialect 3 for the database
-t[wo_phase] <id>
Performs automated two-phase recovery for limbo transaction with the given <id>
-t[wo_phase] all
Performs automated two-phase recovery for all limbo transactions
-user <name>
Database username
-w[rite] sync
Enables Forced Writes
-w[rite] async
Disabled Forced Writes
Show GFIX and server version


Set Database to Read-Only

gfix -user SYSDBA -password masterkey dbserver:/db/mydb.fdb -shut -attach 60
gfix -user SYSDBA -password masterkey dbserver:/db/mydb.fdb -shut -force 0
gfix -user SYSDBA -password masterkey dbserver:/db/mydb.fdb -mode read_only
gfix -user SYSDBA -password masterkey dbserver:/db/mydb.fdb -online

Set Database to SQL Dialect 3

gfix -user SYSDBA -password masterkey dbserver:/db/mydb.fdb -sql_dialect 3

Enable Forced Writes

gfix -user SYSDBA -password masterkey dbserver:/db/mydb.fdb -write sync

Disable Forced Writes

gfix -user SYSDBA -password masterkey dbserver:/db/mydb.fdb -write async

GSEC - Firebird User Administration

GSEC is Firebird's command line tool for user administration issues.

All database users are stored in the security database named isc4.gdb (Firebird 1.0), security.fdb (Firebird 1.5) or security2.fdb (Firebird 2.0) in the Firebird directory. There is always at least one user, the system database administrator, SYSDBA.

After installation, the SYSDBA password is "masterkey". (Exception: Firebird 1.5 for Linux)

Only the first 8 characters of a password are significant. The password should not contain space characters.

Invoking GSEC

GSEC can only be run by the SYSDBA.

To use GSEC for the local machine, use:

gsec -user sysdba -password <password> [options]

To use GSEC for a remote machine, use:

gsec -user sysdba -password <password> -database <databasename>

where <databasename> is the database name of the remote security.fdb or security2.fdb database.

You can use GSEC has an interactive command line tool or give all commands on one command line.


Displays all users
di[splay] <username>
Displays all information for the given user
a[dd] <username> -pw <password> [options]
Add a new user
mo[dify] <username> [options]
Modify user
de[lete] <username>
Delete user
Display help
Display help
Quit interactive mode
Display GSEC version number

Wenn you don't want to invoke the interactive mode, you can give all commands directly in the command line. To do that, precede the commands with a dash.


-pa[ssword] <password>
Password of the user who is performing the change
-user <username>
User name of the user who is performing the change
-pw <password>
Password of target user (or new password)
-fname <first name>
Target user's first name
-mname <middle name>
Target user's middle name
-lname <last name>
Target user's last name


Add user Elvis Presley as user ELVIS, password is "Aaron"

gsec -user SYSDBA -password masterkey

GSEC> add elvis -pw Aaron -fname Elvis -lname Presley

GSEC> quit

Change password of user ELVIS to "chuck"

gsec -user SYSDBA -password masterkey

GSEC> modify elvis -pw chuck

GSEC> quit

Change password of SYSDBA from "masterkey" to "MyKey37"

gsec -user SYSDBA -password masterkey -modify sysdba -pw MyKey37

Change password of SYSDBA to "hamburg" on remote Linux server "harry"

gsec -user SYSDBA -password masterkey -database harry:/opt/firebird/security.fdb -modify sysdba -pw hamburg

Change password of SYSDBA on remote Windows server "sally" to "hannover"

gsec -user SYSDBA -password masterkey -database sally:"C:Program FilesFirebirdsecurity.fdb" -modify sysdba -pw hannover

Change password of SYSDBA on remote server "jake" on TCP port 3051 to "london"

gsec -user SYSDBA -password masterkey -database jake/3051:/opt/firebird/security.fdb" -modify sysdba -pw london

Delete user Joe on local server

gsec -user SYSDBA -password masterkey -delete joe


On Borland InterBase systems, the security database is named isc4.gdb.
There will be a warning when a new password is longer than 8 characters.

GBAK - Firebird Backup and Restore

GBAK is Firebird's command line tool for online backup and restore of a complete database.

GBAK is able to perform a backup while the database is running. There is no need to shut down the database during a GBAK backup. GBAK will create a consistent snapshot of the database at the time it starts running. You will, however, notice a perfomance degradation during the backup, so it is a good idea to backup at night. As GBAK visits all pages of the database, so it will also perform a garbage collection on the database.

General Syntax

gbak <options> -user <username> -password <password> <source> <destination>


For backups, <source> is the database you want to back up, <destination> is the file name of the backup file. The usual extension for the backup file is .fbk for Firebird and .gbk for InterBase.

Only SYSDBA or the database owner can perform a backup. For multi-file databases, specify only the name of the first file as the database name.


For restores, <source> is the backup file and <destination> is the name of the database that is to be built up from the backup file. You will have to specify the -C option for restore.

General Options

-pas[sword] <password>
Database password
-role <role>
Connect as role [OBSOLETE]
Backup: Creates the backup file on the database server, using the Service Manager.
Restore: Creates the database from a backup file on the server, using the Service Manager.
-u[ser] <username>
Database user name
Verbose output of what GBAK is doing
-y <filename>
Redirect all output messages to <filename>
The file must not exist before running GBAK!
-y suppress_output
Quiet mode
Show GBAK version and server version

Backup Options

Back up. This switch is optional.
Converts external tables to internal tables
Creates an uncompressed backup
-fa[ctor] n
Blocking factor for tape device
-g[arbage collect]
Does not perform garbage collection (sweeping) during backup
Ignores checksum errors while backing up
Ignores limbo transactions while backing up
Only backs up metadata (schema). No table data will be stored.
Non-transportable format (use only when you know you will restore on same platform and database version)
Creates a transportable backup (transportable between platforms and server versions)

Restore Options

Set cache size for restored database
Restore to a new database (the target database file MUST NOT exist)
All indexes will be restored as INACTIVE
Does not create shadows that are defined in the backup
-mo[de] read_write
Restores to a read/write database (This is the default)
-mo[de] read_only
Restores to a read-only database
Does not restore validity constraints. So you can restore data that does not meet these constraints and could not be restored otherwise.
Restores one table at a time. You can use this to partially restore databases with corrupt table data
-p[age_size] <size>
Sets page size of new database. <size> can be one of 1024, 2048, 4096, 8192. Default is 1024.
Restores over an existing database. This can only be performed by SYSDBA or the owner of the database that is overwritten. Do NOT restore over a database that is in use! [Firebird 1.0, 1.5]
New abbreviation for the old -replace_database [Firebird 2.0]
-r[ecreate_database] o[verwrite]
[Firebird 2.0] Restores over an existing database. This can only be performed by SYSDBA or the owner of the database that is overwritten. Do NOT restore over a database that is in use!

-r is equivalent to -c. Only the "overwrite" option will restore over an existing database.

Normally, on restore, database pages will be filled to about 80 %. With the use_all_space option, database pages will be filled to 100 %. (Useful for read-only databases which will see no more modifications.)


A "normal" Backup

gbak -v -t -user SYSDBA -password "masterkey" dbserver:/db/warehouse.fdb c:backupswarehouse.fbk

Backup with output to a logfile

del c:backupswarehouse.log
gbak -v -t -user SYSDBA -password masterkey -y c:backupswarehouse.log dbserver:/db/warehouse.fdb c:backupswarehouse.fbk

A "normal" Restore

gbak -c -v -user SYSDBA -password masterkey c:backupswarehouse.fbk dbserver:/db/warehouse2.fdb

Restore to an already existing database (Firebird 1.0, 1.5)

gbak -c -r -v -user SYSDBA -password masterkey c:backupswarehouse.fbk dbserver:/db/warehouse.fdb

Restore to an already existing database (Firebird 2.0)

gbak -r o -v -user SYSDBA -password masterkey c:backupswarehouse.fbk dbserver:/db/warehouse.fdb

Create a read-only database

gbak -c -v -mode read_only -use_all_space -user SYSDBA -password masterkey c:backupswarehouse.fbk c:fileswarehousedb.fdb

Multi-file backups

Syntax for backup

gbak [options] <database> <target file 1> <size 1> <target file 2> <size 2> ... <target file n>

NOTE: Do not specify a size for the last file. It will always be filled to take up what is left over, no matter how large.

Size can be given in bytes (8192), kilobytes (1024k), megabytes (5m), or gigabytes (2g)

Syntax for restore

gbak -c [options] <source file 1> <source file 2> ... <source file n> <database>

Restoring to a multi-file database

gbak -c [options] <source file> <db file 1> <size 1> <db file 2> <size 2> ... <db file n>

NOTE: Do not specify a size for the last database file. It can always grow unlimited to take up the rest.

Size can be given in bytes (8192), kilobytes (1024k), megabytes (5m), or gigabytes (2g)

Restoring from a multi-file backup to a multi-file database

gbak -c [options] <source file 1> <source file 2> ... <source file n> <db file 1> <size 1> <db file 2> <size 2> ... <db file n>

Backing up and Restoring the Security Database

Firebird 1.0, 1.5

You can perform a "normal" backup of the security database. The security database resides in the Firebird directory. It is named

  • ISC4.gdb in Firebird 1.0 and
  • security.fdb in Firebird 1.5.

Firebird 2.0

Firebird 2.0 does not allow normal database access to the security database. Its name is now security2.fdb

The only way to access the security database is via the Service Manager. As GBAK can also use the Service Manager (Option -se), you can run a backup using this option. However, the backup file will also be written to the server machine.

General Syntax:

gbak <options> -user <username> -password <password> -se <servername>:service_mgr <sec-db-name> <backup-filename>


gbak -v -t -user sysdba -password masterkey -se dbserver:service_mgr c:ProgrammeFirebird2security2.fdb C:BackupsSecurity2.fbk
  (in this case, Security2.fbk will be written to the C:Backups folder of dbserver)

When your database server listens on a non-default port:

gbak -v -t -user sysdba -password masterkey -se dbserver/3051:service_mgr c:ProgrammeFirebird2security2.fdb C:BackupsSecurity2.fbk

Restoring the Security Database

It is not possible to restore the security database while Firebird is running. In case your security database gets destroyed, this is what you can do:

  • Stop the Firebird service/daemon
  • Replace the current security database with a new one. If anything else fails, re-install the Firebird server
  • You should now have a working SYSDBA login and password. If not, re-install the Firebird server ...
  • Start the Firebird service/daemon
  • Using GBAK, restore your backup of the security database to a temporary place (like C:Tempsecurity.fdb)
  • Stop the Firebird service/daemon again
  • Now copy the file from the temporary place to the correct place in the Firebird folder
  • Start the Firebird service/daemon
  • Now you should have your "old" security database back.
  • Good Luck! :-)

Upgrading a Security Database to Firebird 2.0

There is a special chapter "Dealing with the New Security Database" about this in the Firebird 2.0 Release Notes, which get installed to the doc subdirectory of your Firebird directory. You should also take a look at the miscupgradesecuritysecurity_database.txt file, which explains in detail how to do it.

terça-feira, 6 de fevereiro de 2007

Códigos de erro do Winsock

Códigos de erro do Winsock

Quando Firebird em Windows tem um problema com o subsistema da rede um erro Win32 desconhecido volta com um número (como por exemplo 10060). O número é um número do erro do Winsock. Esta é uma lista de todos os números do erro do Winsock:

Error# Error Code Error Text
10004 WSAEINTR Interrupted function call
10014 WSAEFAULT Bad address
10022 WSAEINVAL Invalid argument
10024 WSAEMFILE Too many open files
10035 WSAEWOULDBLOCK Operation would block
10036 WSAEINPROGRESS Operation now in progress
10037 WSAEALREADY Operation already in progress
10038 WSAENOTSOCK Socket operation on non-socket
10039 WSAEDESTADDRREQ Destination address required
10040 WSAEMSGSIZE Message too long
10041 WSAEPROTOTYPE Protocol wrong type for socket
10042 WSAENOPROTOOPT Bad protocol option
10043 WSAEPROTONOSUPPORT Protocol not supported
10044 WSAESOCKTNOSUPPORT Socket type not supported
10045 WSAEOPNOTSUPP Operation not supported
10046 WSAEPFNOSUPPORT Protocol family not supported
10047 WSAEAFNOSUPPORT Address family not supported by protocol family
10048 WSAEADDRINUSE Address already in use
10049 WSAEADDRNOTAVAIL Cannot assign requested address
10050 WSAENETDOWN Network is down
10051 WSAENETUNREACH Network is unreachable
10052 WSAENETRESET Network dropped connection on reset
10053 WSAECONNABORTED Software caused connection abort
10054 WSAECONNRESET Connection reset by peer
10055 WSAENOBUFS No buffer space available
10056 WSAEISCONN Socket is already connected
10057 WSAENOTCONN Socket is not connected
10058 WSAESHUTDOWN Cannot send after socket shutdown
10060 WSAETIMEDOUT Connection timed out
10061 WSAECONNREFUSED Connection refused
10064 WSAEHOSTDOWN Host is down
10065 WSAEHOSTUNREACH No route to host
10067 WSAEPROCLIM Too many processes
10091 WSASYSNOTREADY Network subsystem is unavailable
10092 WSAVERNOTSUPPORTED WINSOCK.DLL version out of range
10093 WSANOTINITIALISED Successful WSAStartup() not yet performed
11001 WSAHOST_NOT_FOUND Host not found
11002 WSATRY_AGAIN Non-authoritative host not found
11003 WSANO_RECOVERY This is a non-recoverable error
11004 WSANO_DATA Valid name, no data record of requested type