1. Why does reading require write privileges on database file?
In order to run the SELECT statmement, it still needs to start a transaction.
If you wish to build a read-only database to place on some read-only media like CD or DVD ROM, you can do it with:
gfix -mode read_only database.fdb
...or within your favorite administration tool. It is also available via ServicesAPI, so you may do it from your application as well. Please note that you can only make this change while preparing the database, because the read-only flag needs to be written in the database file.
When the database becomes read-only, the only thing you can write to is the read_only flag (to reset it back to read-write).
2. What is the best way to determine whether Firebird server is running?
If you want to do it from an application, a simple try to connect should suffice. Otherwise you have various options:
a) check if firebird server is in the list of running programs (use task manager on Windows, or 'ps ax' command on Linux). Please note that Classic won't be running until there is a connection established.
b) check whether the port 3050 is open on the machine. First, you can check with netstat command, and if it is open, you can test whether it accepts connections by telnet-ing to the port. Just type:
telnet [hostname|IPaddress] 3050
Example:
telnet localhost 3050
If you use Linux, you can also check the open port with 'lsof' command. It outputs a lot, so you might want to 'grep' for 3050 or gds_db strings:
# lsof | grep gds_db
# lsof | grep 3050
c) if all of this fails, perhaps you should check whether the remote server is reachable at all. You can use 'ping' command:
ping [hostname|IPaddress]
Example:
ping 192.168.0.22
Please note that ping can still give you 'host unreachable' message even if host is up. This is because the firewall software can drop the ICMP (ping) packets (it's done to prevent some viruses from spreading, or network scans).
3. Is there some bulk load or other way to import a lot of data fast?
Currently there is only one way to quickly load a lot of data into database. That is by using external tables. You should read the manual for details, but here's a short explanation. You create a binary or textual file using the external table format and then hook it up in the database using a statement like this:
CREATE TABLE ext1 EXTERNAL 'c:myfile.txt'
(
field1 char(20),
field2 smallint
);
To do quick import into regular table, do something like this:
INSERT INTO realtable1 (field1, field2)
SELECT field1, field2 FROM ext1;
This insert would still check constraints, foreign keys, fire triggers and build indexes. If you can, it is wise to deactivate indexes and triggers while loading and activate them when done.
Make sure you drop the external table when done, in order to release the lock on the file.
The main problem with external tables is handling of NULLs and BLOBs. If you need to deal with those, you're better off using some tool like FBExport. However, please note that external tables are much faster.
4. Is there an example how to configure UdfAccess setting in firebird.conf?
Well, there's one right there in the firebird.conf, but perhaps it isn't obvious enough. Here are the basic settings ('None' to disallow UDFs completely and 'Full' to allow them anywhere) which you probably understood yourself:
UdfAccess = None
UdfAccess = Full
And here is that tricky Restrict setting:
UdfAccess = Restrict C:somedirectory
For multiple directories, use something like this:
UdfAccess = Restrict C:somedirectory;C:someotherdirectory
For Linux users:
UdfAccess = Restrict /some/directory
In the default setting 'Restrict UDF', 'UDF' is a directory relative to root directory of Firebird installation.
5. Is there an example how to configure ExternalFileAccess setting in firebird.conf?
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
6. Is there a way to detect whether fbclient.dll or fbembed.dll is loaded?
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
7. Is there a way to automate SQL execution from the command-line, batch job or shell script?
Yes. You can use isql for this. It is located in the 'bin' directory of your Firebird installation. If you wish to try it interactively, run isql and then type:
isql localhost:my_database -user sysdba -pass ******
SQL> input my_script.sql;
SQL> commit;
SQL>
To run it from a batch (.bat) file or a shell script, use -i switch:
isql -i my_script.sql localhost:my_database -user sysdba -pass ******
If you have some DML statements in your script, make sure you put the COMMIT command at the end of the file. Also, make sure the file ends with a newline, as isql executes the commands on the line only after it gets the newline character.
8. Is it possible to determine clients IP address?
To get it from SQL, you need to use Firebird 2.0 (own address), or Firebird 2.1 (anyone's):
If you use Firebird 2.0 or higher, use the GET_RDB$Context function with ('SYSTEM', 'CLIENT_ADDRESS') parameters.
If you use Firebird 2.1 or higher, you can get address of any client by selecting from the monitoring tables.
With Firebird 1.x you can try to get the information from TCP/IP stack, using netstat or lsof commands from the command-prompt. Just search for Firebird's port (3050 or gds_db).
9. How to write UDF s in Delphi?
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 '......'
10. How to use events with ZeBeDee, SSH or stunnel?
You have to use SuperServer, set up RemoteAuxPort setting in firebird.conf and create two tunnels (one for data, other for events).
