Firebird

If your databases are on-line, i.e. visible to each other via network, then you can use some data pump tool like freeware IB Pump or some of advanced commercial administration tools which have this option integrated.

If your databases are offline, you should first export the data and then import it on the other end. Most admin. tools can do export to CVS,XML or INSERT statements. If efficiency is important, or your have data with BLOB column, you can use the open source FBExport tool.

If you are looking for a way to easily import CSV or XML data into Firebird, take a look at XMLWizard tool. You can also use it to import data from Microsoft Excel or OpenOffice by saving the sheet to .csv format and then importing via XMLWizard

Deleting all foreign keys can be done by querying the system tables and droping them one by one. If you use Firebird 2 or higher, it can be done with a single SQL statement:

set term !! ;

EXECUTE BLOCK RETURNS (stmt VARCHAR(1000)) AS

BEGIN

FOR

select 'alter table '||r.rdb$relation_name ||' drop constraint '||r.rdb$constraint_name||';'

from rdb$relation_constraints r

where (r.rdb$constraint_type='FOREIGN KEY')

into :stmt

DO begin suspend; execute statement :stmt; end

END!!

set term ; !!

If you use Firebird 1.x, you can run the following query to get statements to execute and then copy/paste the result and execute:

select 'ALTER TABLE '||r.rdb$relation_name

||' DROP CONSTRAINT '||r.rdb$constraint_name||';'

from rdb$relation_constraints r

where (r.rdb$constraint_type='FOREIGN KEY')

Here are some tips:

a) create a scheduled task or cron job that will truncate or rotate the log file. By rotation, we mean renaming the files in such way that you always have a number of previous logs available. Example:

delete firebird.log.5

rename firebird.log.4 firebird.log.5

rename firebird.log.3 firebird.log.4

rename firebird.log.2 firebird.log.3

rename firebird.log.1 firebird.log.2

rename firebird.log firebird.log.1

This way you'll always have last five logs available, and those too old get deleted. You can also use zip, rar, bzip2 or some other packer to compress the old log files. Since they are plain text, they compress very well.

b) redirect logging to void. For example, on Linux, you can do it by creating a symlink to /dev/null instead of the regular log file:

# cd /opt/firebird

# rm -f firebird.log

# ln -s /dev/null firebird.log

Please note that you really shouldn't be doing this, as you will lose all valuable diagnostic information. It's better to try to find what are the errors, what's causing them and fix the problem at the source.

Here's a short step-by-step walkthrough:

* disconnect users and disable incoming connections to the database

* make a copy of database file (or two copies) and work on that

* use GFIX with -v option to validate the database file

* use GFIX with -v and -f to do full validation

If problem is not too serious, you can try to backup the broken db and restore under a new name:

* use GFIX -mend to prepare corrupt database for backup

* use GBAK -b -g to backup the database. -g disables garbage collection (FAQ #41)

* use GBAK -c to restore backup to a new database.

If you succeed, you have fixed the problem and have a functional database. If not, you can try to create an empty database with the same structure and pump the data to it.

One of the reasons why backup or restore can fail is if some broken database triggers exist, and prevent connection to the database. For example, a database trigger might use some table which has a broken index, etc. To work around this, connect to database with isql tool using -nodbtriggers option and then disable those triggers. You can enable them later when you fix other problems and get a working database again.

There is no such feature in Firebird, but you can use some tricks. The following example requires that you have a unique integer column (primary key is usually used):

SELECT ...field_list...

FROM table t1

WHERE conditions

ORDER BY (t1.int_col + seed)*4294967291-((t1.int_col + seed)*4294967291/49157)*49157;

If you just need one random record, limit the result set using FIRST or ROWS clause. This query will give consistent records for the same seed. If you wish to be completely random, you need to change the seed. You could use the value of int_col from previous run, or simply fetch a new value from a generator (just make sure the same value for seed is used in both places in expression).

The server is started and stopped by 'fbmgr' executable from 'bin' directory of your Firebird installation. It is called 'ibmgr' in Firebird 1.0. To start the server type:

/opt/firebird/bin/fbmgr -start

To start the server with Guardian (Guardian watches the server and restarts it if it crashes) type:

/opt/firebird/bin/fbmgr -start -forever

To stop a running server, type:

/opt/firebird/bin/fbmgr -shut -user SYSDBA -pass *****

To force a shutdown, type:

/opt/firebird/bin/fbmgr -shut -force -user SYSDBA -pass *****

If you use Firebird 2 or higher, you can also use the regular 'kill' command to shutdown the server, as it handles the signals properly. Make sure you first kill the guardian and then the server (otherwise guardian would restart the server).

It's quite simple, the only thing you need to remember is that you must always use ib_util_malloc() to allocate memory if your UDF returns string result. The UDF must be declared as FREE_IT, so that Firebird releases the memory after it reads the string.

To use ib_util_malloc(), you need to import it from ib_util.dll into your program - and make sure you use it instead of regular memory alocating functions. Here's a simple example of Delphi UDF:

function ib_util_malloc(l: integer): pointer; cdecl; external 'ib_util.dll';

function ChangeMyString(const p: PChar): PChar; cdecl;

var

s: string;

begin

s := DoSomething(string(p));

Result := ib_util_malloc(Length(s) + 1);

StrPCopy(Result, s);

end;

Declaration in Firebird:

DECLARE EXTERNAL FUNCTION ChangeMyString

CString(255)

RETURNS CString(255) FREE_IT

ENTRY_POINT 'ChangeMyString' MODULE_NAME '......'

There are some ways to detect it:

- check the size of DLL file

- if you are using different versions of Firebird (for example 1.5.4 and 2.0.1, you can query the server version via Services API)

You should understand that fbembed can be used as a regular Firebird client. Checking whether embedded or fbclient is loaded for licensing or similar needs is really not useful. You could use the connection string as guide, but super server can establish direct local connections without localhost prefix.

If you combine all this information, you could get some conclusions:

- if DLL size matches fbembed and connection string doesn't have hostname, you are using embedded

- if DLL size matches fbembed and connection string does have hostname, you are using either super server or classic

- if DDL size matches fbclient and connection string doesn't have hostname, you are using super server via local connection (IPC, XNET)

- if DLL size matches fbclient and connection string does have hostname, you are using either super server or classic

Firebird's config file (firebird.conf) does have descriptions inside that explain everything, but sometimes they are confusing and hard to understand what should you do exactly if you don't have examples. One of such settings is ExternalFileAccess. Some people are even tempted to put Full as it is much easier than trying to guess what's the correct format. Here are the basic settings ('None' to disallow external tables and 'Full' to allow them anywhere) which you probably understood yourself:

ExternalFileAccess = None

ExternalFileAccess = Full

And here are those tricky Restrict settings:

ExternalFileAccess = Restrict C:somedirectory

For multiple directories, use something like this:

ExternalFileAccess = Restrict C:somedirectory;C:someotherdirectory

For Linux users:

ExternalFileAccess = Restrict /some/directory