Firebird: what changed

Revision 2 to 3. Removed lines have a minus sign; added lines have a plus sign.

Old	New		Line
1	1		This is a guide about using Firebird database in Lazarus/FPC, using Firebird with SQLDB (the FPC/Lazarus built-in database library). Other access methods are described in [[Other Firebird libraries]].
2	2
3	3		## Overview
4	4
5	5		Firebird is an open source, free database server that has been in use and developed for decades (it developed out of the Interbase 6 database that was open sourced by Borland). It includes rich support for SQL statements (e.g. INSERT...RETURNING), stored procedures, triggers, etc. You can write compiled UDFs (User-Defined Functions) libraries for the server in FreePascal if you want to extend the already extensive function list of Firebird.
6	6
7	7		The database requires very little manual DBA work once it is set up, making it ideal for small business use or embedded use. It can grow to terabyte scale given proper tuning, although PostgreSQL may be a better choice for such large environments.
8	8
9	9		Firebird offers both embedded (file-based) and client-server database - usable without having to change a single line of code in FPC/Lazarus. If used as an embedded database, it offers a richer SQL support than [[SQLite]] as well as seamless migration to a client-server database, although SQLite is a quite capable embedded database itself.
10	10
11	11		The latest stable version, Firebird 2.5, runs on Windows (32- and 64-bit), various Linux versions (32- and 64-bit), Solaris (Sparc and Intel), HP-UX (PA-Risc) and OSX.
12	12
13	13		It is being ported to Android at the moment; it is not available on Windows CE/Windows Mobile.
14	14
15	15		Support for Firebird in FPC's SQLDB is quite good, comparable to the level of [[postgres\|PostgreSQL]] support.
16	16
17	17		## Documentation
18	18
19	19		Official documentation is included in FPC 2.6.2+: [SQLDB documentation for IBConnection](http://www.freepascal.org/docs-html/fcl/ibconnection/index.html)
20	20
21	21		## Client/server and embedded installation
22	22
23	23		Firebird can run in client/server and embedded mode.
24	24
25	25		Client/Server means that you have a physical Firebird server somewhere: either on your local machine or another machine reachable over your network. Connections to the server go through TCP/IP; when specifying the connection, the hostname contains a name or IP address. The Firebird DLL you need for this is fbclient.dll (along with its support files).
26	26
27	27		Embedded Firebird means that your application loads the Firebird DLLs to access a Firebird database on the local machine. When specifying the connection, the hostname is always empty. The Firebird DLL you need for this is fbembed.dll (along with its support files). See [[Firebird embedded\|the wiki page on Firebird embedded]] for more details.
28	28
29	29		Note that fbembed.dll can be used both for client/server and embedded use, so installing only this dll may be a smart thing to do.
30	30
31	31		### Windows
32	32
33	33		Win64: please see warning [[Windows Programming Tips\|here]] on not using certain FPC/Lazarus Win64 versions.
34	34
35	35		On Windows: (this applies to all SQLDB database drivers) you must have fbclient.dll (or fbembed.dll) and its support dlls installed in:
36	36
37	37		- the project directory and the executable output directory/the application directory (e.g. lib/something under your project directory)
38	38		- or a directory in your PATH (not your system directory)
39	39		- If you want to use the system directory, please use the official installer and tick "copy fbclient to system directory"
40	40
41	41		As with all (database) DLLs, the bitness of the DLL must match your application: use a 32 bit library for a 32 bit compiled program and a 64 bit library for a 64 bit program.
42	42
43	43		### Unix/Linux/macOS
44	44
45	45		On FreeBSD/Linux/macOS, the Firebird client library should be installed (e.g. by your package manager; install the regular package and the -dev package), or they should be placed in the library search path.
46	46
47	47		FPC searches for the most common library names (e.g. libfbclient.so.2.5, libgds.so, and libfbembed.so.2.5; please check ibase60.inc if your version is different). If you want to, you can explicitly specify the library name. There are 2 ways for this:
48	48
49	49		- use the [[TSQLDBLibraryLoader]] component from sqldblib (FPC 2.7.1). Works for all SQLDB connector components.
50	50		- call
51	51		``` pascal
52	52		function InitialiseIBase60(Const LibraryName : AnsiString) : integer;
53	53		```
54	54
55	55		with the correct library name (you may need to use unit ibase60dyn for this).
56	56
57	57		## Connection examples
58	58
59	59		Example for client/server:
60	60
61		-	~~`Hostname: 192.168.1.1`\~~
62		-	~~`* The database is on the server with IP address 192.168.1.1. `\~~
63		-	~~`DatabaseName: /interdata/example.fdb `\~~
64		-	~~`* The name of the database file is "example.fdb" in the /interdata directory of the server (the machine with IP address 192.168.1.1).`\~~
65		-	~~`Username: SYSDBA`\~~
66		-	~~`Password: masterkey`~~
	61	+	```text
	62	+	Hostname: 192.168.1.1
	63	+	* The database is on the server with IP address 192.168.1.1.
	64	+	DatabaseName: /interdata/example.fdb
	65	+	* The name of the database file is "example.fdb" in the /interdata directory of the server (the machine with IP address 192.168.1.1).
	66	+	Username: SYSDBA
	67	+	Password: masterkey
	68	+	```
67	69
68	70		Another example for client/server:
69	71
70		-	~~`Hostname: dbhost`\~~
71		-	~~`* The database is on the server with the host name dbhost`\~~
72		-	~~`DatabaseName: F:\Program Files\firebird\examples\employee.fdb `\~~
73		-	~~`* The name of the database file is "employee.fdb" in the Program Files\firebird\examples directory on the F: drive of dbhost.`\~~
74		-	~~`Username: SYSDBA`\~~
75		-	~~`Password: masterkey`~~
	72	+	``` pascal
	73	+	Hostname: dbhost
	74	+	* The database is on the server with the host name dbhost
	75	+	DatabaseName: F:\Program Files\firebird\examples\employee.fdb
	76	+	* The name of the database file is "employee.fdb" in the Program Files\firebird\examples directory on the F: drive of dbhost.
	77	+	Username: SYSDBA
	78	+	Password: masterkey
	79	+	```
76	80
77	81		An embedded example:
78	82
79		-	~~`Hostname: <empty string>`\~~
80		-	~~`* Leaving the hostname empty selects embedded use.`\~~
81		-	~~`DatabaseName: test.fdb`\~~
82		-	~~`* The database file is "test.fdb" in the directory where the application runs (make sure fbembed.dll is in the application executable directory)`\~~
83		-	~~`Username: SYSDBA`\~~
84		-	~~`* On embedded, you do have to specify a username...`\~~
85		-	~~`Password: <empty string>`\~~
86		-	~~`* ... but it doesn't matter what password you give.`~~
	83	+	```text
	84	+	Hostname: <empty string>
	85	+	* Leaving the hostname empty selects embedded use.
	86	+	DatabaseName: test.fdb
	87	+	* The database file is "test.fdb" in the directory where the application runs (make sure fbembed.dll is in the application executable directory)
	88	+	Username: SYSDBA
	89	+	* On embedded, you do have to specify a username...
	90	+	Password: <empty string>
	91	+	* ... but it doesn't matter what password you give.
	92	+	```
87	93
88	94		## Troubleshooting client/server access issues
89	95
90	96		Make sure you started the Interbase/Firebird server on the server IP/hostname you specified. You can test connectivity by telnetting to the machine. Firebird usually listens on port 3050:
91	97
92	98		``` bash
93	99		telnet 192.168.1.1 3050
94	100		```
95	101
96	102		You should see something, maybe just a blank screen, but you can type something. This means you can send data to the Firebird database.
97	103
98	104		For further information, please see the Firebird documentation.
99	105
100	106		## Monitoring Events
101	107
102	108		FPC/Lazarus comes with a component to monitor events coming from Firebird databases; see [[TFBEventMonitor]].
103	109
104	110		## Creating objects programmatically
105	111
106	112		While you can use tools such as Flamerobin to create tables etc, you can also create these programmatically/dynamically, which could be handy when you want your programs to update existing database schemas to a new schema.
107	113
108	114		You can use e.g. TSQLQuery.ExecSQL to perform this task:
109	115
110	116		``` pascal
111	117		Query.ExecSQL('CREATE TABLE TEST(ID INTEGER NOT NULL, TESTNAME VARCHAR(800))');
112	118		// You need to commit the transaction after DDL before any DML - SELECT, INSERT etc statements.
113	119		// Otherwise the SQL won't see the created objects
114	120		```
115	121
116	122		To do this, use the TSQLScript object - see [[TSQLScript]].
117	123
118	124		## Database Administration
119	125
120	126		FPC/Lazarus has a component for database adminstration; see [[TFBAdmin]]
121	127
122	128		## Common problems and solutions
123	129
124	130		Sometimes using Firebird in Lazarus seems to be tricky. Please find solutions below.
125	131
126	132		### Attempted update of read-only column / COMPUTED BY fields
127	133
128	134		If you have COMPUTED BY fields (server-side calculated fields) in your Firebird table, SQLDB will not pick up that these are read only fields (for performance reasons).
129	135
130	136		In this case, auto-generated INSERTSQL,UPDATESQL statements can lead to error messages like "attempted update of read-only column". The solution is to manually specify that the field in question may not be updated after setting the TSQLQuery's SQL property, something like:
131	137
132	138		``` pascal
133	139		// Disable updating this field or searching for changed values as user cannot change it
134	140		sqlquery1.fieldbyname('full_name').ProviderFlags:=[];
135	141		```
136	142
137	143		### Bigint: lost precision
138	144
139	145		If you use the bigint datatype (64 bit signed integer) in Firebird, please use .AsLargeInt instead of .AsInteger for parameters:
140	146
141	147		``` pascal
142	148		// Assuming ID is bigint here
143	149		sqlquery1.sql.text := 'insert into ADDRESS (ID) values (:ID)';
144	150		// Use this:
145	151		sqlquery1.params.parambyname('ID').aslargeint := <some qword or 64 bit integer variable>;
146	152		// Do not use this:
147	153		//sqlquery1.params.parambyname('ID').asinteger := <some qword or 64 bit integer variable>;
148	154		...
149	155		```
150	156
151	157		... otherwise you might get errors like duplicate PK (primary key) - if using the bigint as a primary key.
152	158
153	159		### Boolean data types
154	160
155	161		Firebird versions below 3.0 do not support boolean data types.
156	162
157	163		At least on FPC trunk (2.7.1) this datatype can be emulated:
158	164
159	165		Use a DOMAIN that uses a SMALLINT type (other integer types may work as well - please test and adjust text):
160	166
161	167		``` SQL
162	168		CREATE DOMAIN "BOOLEAN"
163	169		AS SMALLINT
164	170		CHECK (VALUE IS NULL OR VALUE IN (-1,0,1))
165	171		/* -1 used for compatibility with FPC SQLDB; 1 is used by many other data access layers */
166	172		;
167	173		```
168	174
169	175		Let your field/column use this domain type e.g.
170	176
171	177		``` SQL
172	178		CREATE TABLE MYTABLE
173	179		(
174	180		...
175	181		MYBOOLEANCOLUMN "BOOLEAN",
176	182		);
177	183		```
178	184
179	185		- Now you can use .AsBoolean for assigning field values etc
180	186
181	187		To do: verify this works with Lazarus grids etc as well.
182	188
183	189		### INSERT INTO...RETURNING problems/Cursor is not open
184	190
185	191		= If you try to select SQL (e.g. Query.Open) with SQL like this:
186	192
187	193		``` SQL
188	194		INSERT INTO PEOPLE (NICKNAME) VALUES ('Superman') RETURNING ID
189	195		```
190	196
191	197		and get something like this error:
192	198
193	199		```dos
194	200		Database error: : Fetch :
195	201		-Dynamic SQL Error
196	202		-SQL error code = -504
197	203		-Invalid cursor reference
198	204		-Cursor is not open(error code: 335544569)
199	205		```
200	206
201	207		while running FPC 2.6.0 (which is supplied with Lazarus 1.0) or lower, then you probably ran into an FPC SQLDB parser bug.
202	208
203	209		SQLDB thinks the statement you're running is a normal INSERT statement, which doesn't return data. Obviously, it should return data. Newer FPC code has fixes for this.
204	210
205	211		If you're using generators/sequences for your primary keys (like many do), a workaround is to first get the next sequence number:
206	212
207	213		``` SQL
208	214		SELECT NEXT VALUE FOR GEN_PEOPLEID FROM RDB$DATABASE /* If your generator name is GEN_PEOPLEID */
209	215		```
210	216
211	217		then use that to do a regular INSERT. see [FAQ entry](http://www.firebirdfaq.org/faq111/%7CFirebird)
212	218
213	219		### Locate does not seem to work
214	220
215	221		Source: [Issue \##21988](https://bugs.freepascal.org/view.php?id=#21988)
216	222
217	223		When running locate on UTF8 (or presumably other multibyte character sets) CHAR fields, locate may not find your record.
218	224
219	225		The problem is related mostly to UTF8 charset used and how Firebird reports column length. In case of UTF8 Firebird reports the maximum column length (in bytes) as 4"character length". So if you have a column defined as char(8), Firebird reports 48=32.
220	226
221	227		Values are right-padded to this length 32. When locating say '57200001' there is no match because the field actually stores '57200001 ........................' (with trailing spaces represented by dots here).
222	228
223	229		Workaround: rewrite your select query:
224	230
225	231		``` sql
226	232		SELECT substring(THEFIELD from 1 for 8) AS THEFIELD ...
227	233		```
228	234
229		-	~~`or`~~
	235	+	```text
	236	+	or
	237	+	```
230	238
231	239		``` sql
232	240		SELECT cast(THEFIELD as varchar(8)) as THEFIELD ...
233	241		```
234	242
235	243		or use VARCHAR fields.
236	244
237	245		Note: this problem may occur for other databases as well depending on their reporting of field length.
238	246
239	247		## Advanced transactions
240	248
241	249		Sources for this information/further reading:
242	250
243	251		- [Understanding Firebird Transactions](http://www.ibphoenix.com/resources/documents/how_to/doc_400) very detailed article
244	252		- [Transactions in Firebird](https://ib-aid.com/en/transactions-in-firebird-acid-isolation-levels-deadlocks-and-update-conflicts-resolution/) another very detailed article
245	253		- Interbase 6 API Guide (valid for Firebird+Interbase), page 63 and further
246	254		- [Overview of transaction settings in Interbase](http://conferences.embarcadero.com/article/32280)
247	255		- [Detailed explanation of parameters in Firebird](http://tech.groups.yahoo.com/group/firebird-support/message/58653)
248	256		- [Overview of settings in Firebird](http://fhasovic.blogspot.com/2005/02/transaction-isolation-levels-in.html)
249	257		- [Transaction isolation levels in FIBPlus](http://www.devrace.com/en/fibplus/articles/3292.php)
250	258		- README.set_transaction.txt in Firebird 2.5 documentation folder.
251	259
252	260		### Transaction isolation levels
253	261
254	262		If you want to, you can change the transaction isolation levels by adding a line in the transaction's Parameters property:
255	263
256	264		- `isc_tpb_read_committed`: you see all changes committed by other transactions
257	265		- `isc_tpb_concurrency`: also called Snapshot: you see database as it was when the transaction started. Has more overhead than isc_tpb_read_committed. Better than ANSI Serializable because it has no phantom reads.
258	266		- `isc_tpb_consistency`: also called Table Stability: stable, serializable view of data, but locks tables. Unlikely you will need this
259	267
260	268		Example:
261	269
262	270		``` pascal
263	271		SQLTransaction1.Params.text:='isc_tpb_read_committed';
264	272		```
265	273
266	274		You can also add additional parameters that have an effect on the transaction (taken from the ibconnection.pp source file and [1](http://conferences.embarcadero.com/article/32280)):
267	275
268	276		### Access mode
269	277
270	278		This allow reads only or read/write
271	279
272	280		- `isc_tpb_read`: read permission
273	281		- `isc_tpb_write`: read+write permission
274	282
275	283		### Lock resolution
276	284
277	285		- `isc_tpb_nowait`: if another transaction is editing the record then don't wait
278	286		- `isc_tpb_wait`: if another transaction is editing the record then wait for it to finish. Can mitigate "live locks" in heavy contention ([2](http://tech.groups.yahoo.com/group/firebird-support/message/58653)). See below for timeout value.
279	287
280	288		### Table reservation
281	289
282	290		Deals with locking entire tables.
283	291
284	292		- `isc_tpb_shared`: first specify this, then either lock_read or lock_write for one or more tables. Shared read or write mode for tables.
285	293		- `isc_tpb_protected`: first specify this, then either lock_read or lock_write for one or more tables. Lock on tables; can allow deadlock-free operation at the cost of delayed transactions
286	294		- `isc_tpb_lock_read`: Set a read lock. Specify which table to lock, e.g. isc_tpb_lock_read=CUSTOMERS
287	295		- `isc_tpb_lock_write`: Set a read/write lock. Specify which table to lock, e.g. isc_tpb_lock_read=CUSTOMERS
288	296
289	297		Combinations:
290	298
291	299		- Shared, lock_write: write transactions with concurrency or read committed isolation
292	300
293	301		can update the table. All transactions can read the table
294	302
295	303		- Shared, lock_read: any transaction can read or update
296	304		- Protected, lock_write: Other transactions cannot update the table. Only concurrency and
297	305
298	306		read committed transactions can read the table
299	307
300	308		- Protected, lock_read: Other transactions cannot update the table. Any transaction can
301	309
302	310		read the table
303	311
304	312		### Record versions
305	313
306	314		This setting is apparently only relevant for isc_tpb_read_committed isolation mode for records being modified by other transactions:
307	315
308	316		- `isc_tpb_no_rec_version`: only newest record version is read. Can be useful for batch/bulk insert operations together with isc_tpb_read_committed)
309	317		- `isc_tpb_rec_version`: the latest committed version is read, even when the other transaction has other uncommitted changes note: verify this. More overhead than isc_tpb_no_rec_version
310	318
311	319		### Various options
312	320
313	321		For completeness, some more options appearing in the Firebird/Interbase FPC code. You will likely only ever use isc_tpb_no_auto_undo to speed up batch inserts/edits.
314	322
315	323		- isc_tpb_exclusive (apparently translates to protected in Firebird, see [3](http://tech.groups.yahoo.com/group/firebird-support/message/58653))
316	324		- isc_tpb_verb_time (Related to deferred constraints, which could execute at verb time or commit time. Firebird: not implemented, always use verb time)
317	325		- isc_tpb_commit_time (Related to deferred constraints, which could execute at verb time or commit time. Firebird: not implemented, always use verb time)
318	326		- isc_tpb_ignore_limbo (ignores the records created by transactions in limbo. Limbo transactions are failing two-phase commits in multi-database transactions. Unlikely that you will need this feature)
319	327		- isc_tpb_autocommit (autocommit this transaction: every statement is a separate transaction. Probably specifically for JayBird JDBC driver.)
320	328		- isc_tpb_restart_requests (apparently looks for requests in the connection which had been active in another transaction, unwinds them, and restarts them under the new transaction.)
321	329		- isc_tpb_no_auto_undo (disable transaction-level undo log, handy for getting max throughput when performing a batch update. Has no effect when only reading data.)
322	330		- isc_tpb_lock_timeout (specify number of seconds to wait for lock release, if you use isc_tpb_wait. If this value is reached without lock release, an error is reported.)
323	331
324	332		### Firebird and ISO transactions
325	333
326	334		Firebird transaction do not map 1 to 1 to ISO/ANSI transaction levels. An approximation is:
327	335
328	336		- ISO Read Committed=READ COMMITTED+RECORD_VERSION
329	337		- ISO Read Committed=READ COMMITTED+NO RECORD_VERSION
330	338		- ISO Repeatable Read=SNAPSHOT (also known as CONCURRENCY)
331	339		- ISO Serializable=SNAPSHOT TABLE STABILITY (also known as CONSISTENCY)
332	340
333	341		### Common combinations
334	342
335	343		Default is (probably, will have to check) read committed.
336	344
337	345		#### Batch/bulk insert
338	346
339	347		isc_tpb_read_committed and isc_tpb_no_rec_version could be a good combination: it allows other transactions to function while the batch is going on.
340	348
341	349		#### Read only transaction
342	350
343	351		If you want to only have read access to the database, you can do so by setting these transaction parameters:
344	352
345	353		- `isc_tpb_read`
346	354		- `isc_tpb_read_committed`
347	355		- `isc_tpb_rec_version`
348	356		- `nowait`
349	357
350	358		This combination will not block garbage collection, which is a good thing. Source: [4](http://tech.groups.yahoo.com/group/firebird-support/message/118748)
351	359
352	360		Note: the [Firebird FAQ](http://www.firebirdfaq.org/faq164/) indicates you will need write access to the database file even if you only read from it, unless you set the database read-only flag (e.g. using gfix).
353	361
354	362		## Links and more information
355	363
356	364		The list below shows links to more information on Firebird and related tools.
357	365
358	366		- [[Firebird embedded\|Using Firebird embedded with FPC/Lazarus]]
359	367
360	368		### Lazarus Firebird samples
361	369
362	370		- Sample Lazarus/Firebird application source code [5](http://lazarus.freepascal.org/index.php/topic,13940.msg73617.html#msg73617)
363	371		- Firebird/SQLDB tutorials:
364	372		- [[SQLdb Tutorial0]]
365	373		- [[SQLdb Tutorial1]]
366	374		- [[SQLdb Tutorial2]]
367	375		- [[SQLdb Tutorial3]]
368	376		- [[SQLdb Tutorial4]]
369	377
370	378		- PP4S tutorials; cover Firebird installation on Windows as well:
371	379		- [Planning a Database](http://www.pp4s.co.uk/main/tu-db-plan.html)
372	380		- [Installing Firebird](http://www.pp4s.co.uk/main/tu-db-installingfirebird01.html)
373	381		- [Creating a Firebird database](http://www.pp4s.co.uk/main/tu-db-firebird-create.html)
374	382		- [Editing a Firebird database](http://www.pp4s.co.uk/main/tu-db-firebird-demo1.html)
375	383		- [Searching a Firebird database](http://www.pp4s.co.uk/main/tu-db-firebird-demo2.html)
376	384		- [Creating and printing a report](http://www.pp4s.co.uk/main/tu-db-firebird-demo3.html)
377	385		- [Creating and using stored procedures](http://www.pp4s.co.uk/main/tu-db-firebird-demo4.html)
378	386
379	387		### Tools
380	388
381	389		- FlameRobin [Flamerobin site](http://www.flamerobin.org/) Open source GUI tool to manage Firebird, available for Linux, Windows and Mac OSX. Highly recommended.
382	390		- Turbobird [Turbobird site](https://github.com/motaz/turbobird) Open source GUI tool to manage Firebird. Written with Lazarus using TIBConnection
383	391		- ibconsole : Tool to manage Firebird an Interbase Databases with a GUI, available for Windows and Linux
384	392		- Lazarus Data Desktop - included in the Lazarus repository (found in the 'tools/lazdatadesktop/' directory)
385	393		- [LazSQLX](http://lazsqlx.wordpress.com/) Multi-database open source database management tool. Written with Lazarus using both SQLDB and Zeos components. Includes support for Firebird.
386	394		- tiSQLEditor - included in the "Support Apps" directory of the [[tiOPF]] repository. It is a tool used to write SQL with some code completion support, can run scripts, execute upgrade scripts for applications from one build to a later build, has various handy copy and paste functions for Object Pascal applications, has many features that are useful to tiOPF (creates Object Pascal code templates from query results, for tiOPF's visitor classes), export query results to CSV etc.
387	395
388	396		### Firebird
389	397
390	398		- The Firebird RDBMS [Firebird site](http://firebirdsql.org/). This site also contains a lot of documentation on Firebird.
391	399		- Firebird FAQ [6](http://firebirdfaq.org/). Handy site that shows e.g. differences with other RDBMS.
392	400		- New site that shows how to use the latest tools and LibreOffice, FPC to be added soon [Firebird](http://www.controlpascal.com/firebird.htm)
393	401
394	402		---
395	403		From the Free Pascal wiki, page "Firebird", as it stood on 2020-02-16 after a change by Trev, out of the wiki's dump of 2024-05-26. Copyright stays with the author who wrote it, according to the old wiki's [[Licensing]] page. If you have concerns about this material, [message the admin](/wiki-contact).

Back to history