Firebird embedded: what changed

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

Old	New		Line
1	1		# What is Firebird?
2	2
3	3		Firebird embedded is a special version of the [[Firebird]] database server. It lets you run your programs without a dedicated database server. Instead, you add a library (DLL/.so/.dylib) to your application) and let your application access your Firebird .fdb database file using that library.
4	4
5	5		This is similar to the way programs like Microsoft Access and LibreOffice Base can work.
6	6
7	7		## Advantages
8	8
9	9		- runs on many architectures (Linux, macOS, Windows, FreeBSD, Solaris)
10	10		- no separate server setup necessary, reducing complexity
11	11		- you can easily switch from embedded to full client/server - without changing a line of code (except your connection string). This is probably a nice advantage compared to sqlite.
12	12
13	13		## Disadvantages
14	14
15	15		- only single user access
16	16		- need to distribute some libraries along with your program when distributing it (unless you can statically link them)
17	17
18	18		# Installation
19	19
20	20		On Windows, Firebird embedded and regular Firebird client libraries are different files. Firebird embedded can always serve as a regular client, making it a logical choice for inclusion in your installation.
21	21
22	22		## Windows
23	23
24	24		Download and unzip the Firebird embedded kit. Make sure the .dll and .manifest files are either:
25	25
26	26		- in your project directory and executable output directory (where the .exe is generated) or
27	27		- in a directory in your PATH (not your system directory) (useful if you don't want to keep copying dlls)
28	28
29	29		Note that bitness must match: you must use the 64 bit embedded version if you compile your program for 64 bit, and 32 if you're writing 32 bit programs. Also make sure the the .dlls and .manifest files are placed in your project output (executable) directory, and distribute them with your applications, along with the license files.
30	30
31	31		As an indication, the minimum necessary files for Firebird 2.5 are:
32	32
33	33		- fbembed.dll
34	34		- firebird.msg
35	35		- ib_util.dll
36	36		- icudt30.dll
37	37		- icuin30.dll
38	38		- icuuc30.dll
39	39		- IDPLicense.txt
40	40		- IPLicense.txt
41	41		- Microsoft.VC80.CRT.manifest
42	42		- msvcp80.dll
43	43		- msvcr80.dll
44	44
45	45		## Linux
46	46
47	47		For Debian, probably similar for Ubuntu:
48	48
49	49		Get library files as well as the symlink: e.g. /usr/lib/x86_64-linux-gnu/libfbembed.so linking to /user/lib/x86_64-linux-gnu/libfbembed.so.bla.x.y
50	50
51	51		``` bash
52	52		aptitude install libfbembed2.5 firebird-dev
53	53		```
54	54
55	55		For at least Firebird 2.5+: Do not specify a username or password as that will force a lookup to security.fdb which may fail if system-wide security.fdb cannot be accessed. Do specify the role RDB\$ADMIN on Firebird 2.5+ as that should give you full control over the database file.
56	56
57	57		Put these files in your project directory (to do: test if this is all needed):
58	58
59		-	~~`firebird/libfbembed.so (or perhaps libfbembed.so.2.5)`\~~
60		-	~~`firebird.conf`\~~
61		-	~~`firebird/security.fdb #perhaps not necessary`\~~
62		-	~~`firebird/intl/fbintl`\~~
63		-	~~`firebird/udf/fbudf.so`~~
	59	+	```text
	60	+	firebird/libfbembed.so (or perhaps libfbembed.so.2.5)
	61	+	firebird.conf
	62	+	firebird/security.fdb #perhaps not necessary
	63	+	firebird/intl/fbintl
	64	+	firebird/udf/fbudf.so
	65	+	```
64	66
65	67		For Firebird 3 you will need:
66	68
67		-	~~`firebird/libfbclient.so`\~~
68		-	~~`plugins/libEngine12.so`~~
	69	+	```text
	70	+	firebird/libfbclient.so
	71	+	plugins/libEngine12.so
	72	+	```
69	73
70	74		You need to change plugins directory variable. If you cannot do that you need libEngine into its original directory.
71	75
72	76		Had set this in firebird.conf (to do: check if this is all needed)
73	77
74	78		``` ini
75	79		RootDirectory=./firebird #replace with your directory
76	80		DatabaseAccess = Full #probably useful
77	81		ExternalFileAccess = Full #probably useful
78	82		UdfAccess = Full #probably useful
79	83		#disable
80	84		#TempDirectories = /tmp #=> didn't fix /tmp/firebird lock issues
81	85		#TempDirectories = ~/tmp #=> didn't fix /tmp/firebird lock issues
82	86		```
83	87
84	88		2 environment variables need to be set before the application is started. Supposing the application is installed in /home/pascaldev/embed:
85	89
86	90		``` bash
87	91		# tell dynamic loader where to find embedded lib:
88	92		LD_LIBRARY_PATH=/home/pascaldev/embed/firebird
89	93		# tell server where to find files (messages, config etc)
90	94		FIREBIRD=. #or use absolute path: /home/pascaldev/embed
91	95		```
92	96
93	97		If you hit errors like
94	98
95		-	~~`EIBDatabaseError/ : DoInternalConnect :`\~~
96		-	~~` -Can't access lock files' directory /tmp/firebird`~~
	99	+	```text
	100	+	EIBDatabaseError/ : DoInternalConnect :
	101	+	-Can't access lock files' directory /tmp/firebird
	102	+	```
97	103
98	104		you probably have stale lock files lying around that are owned by another user Deleting the /tmp/firebird directory works, but the problem is that it will be recreated under your own local permissions
99	105
100	106		See [How to setup Firebird embedded on Linux or Mac?](http://www.firebirdfaq.org/faq51/) for hints on working with embedded Firebird on Linux or Apple macOS. Somebody used Firebird embedded with FPC: [Earlier version of Firebird embedded on Linux](http://www.mwasoftware.co.uk/index.php?option=com_content&task=view&id=105)
101	107
102	108		## macOS
103	109
104	110		- todo: write this, perhaps adapt [Earlier version of Firebird embedded on Linux](http://www.mwasoftware.co.uk/index.php?option=com_content&task=view&id=105) and especially [1](http://paulbeachsblog.blogspot.mx/2012/09/firebird-embedded-on-macosx.html)
105	111
106	112		## FreeBSD
107	113
108	114		- to do: write this, perhaps adapt [Earlier version of Firebird embedded on Linux](http://www.mwasoftware.co.uk/index.php?option=com_content&task=view&id=105)
109	115
110	116		# Connecting to Firebird servers
111	117
112	118		As mentioned, Firebird embedded can work like a regular Firebird client. Therefore, using the embedded Firebird library instead of the client/server Firebird library can help make your application more flexible.
113	119
114	120		Note: there is a bug at least in Firebird embedded 2.5.3 together with FPC 2.6.4 that crashes when closing a connection to a remote server. This bug has been fixed in FPC trunk.
115	121
116	122		# Use
117	123
118	124		Once you have the proper Firebird embedded libraries in the right path (see above), you need to specify that you are connecting to an embedded instead of a client server environment. Leave the hostname property of the IBConnection object empty. The rest of the properties work like in the client/server version, e.g. the DatabaseName is the full path to your .fdb file - or just the database name if it is in the same directory as fbembed.dll. Additionally, older versions of FPC (FPC 2.6.2+ certainly don't need this) may require that you set the UseEmbeddedFirebird property to true. The property is not present in the SQLDB units, but you need to add ibase60dyn to your uses clause.
119	125
120	126		## Creating databases
121	127
122	128		### Using Firebird ISQL
123	129
124	130		You can create databases by using the Firebird isql application (e.g. as provided in the regular Firebird (client) package). On Windows, this executable should be in the same directory as your fbembed.dll. Note: isql expects the fbclient library, not fbembed; you can copy and rename fbembed, e.g. into fbclient.dll on Windows, to fix this. Run isql, then do:
125	131
126	132		``` sql
127	133		create database 'employee.fdb' user 'SYSDBA' password 'masterkey' page_size 16384 default character set UTF8;
128	134		exit;
129	135		```
130	136
131	137		Dropping/deleting a database would basically mean deleting the file, but you can also run: isql
132	138
133	139		``` sql
134	140		connect 'employee.fdb' user 'sysdba' password 'masterkey';
135	141		drop database;
136	142		commit;
137	143		exit;
138	144		```
139	145
140	146		### Programmatically creating databases
141	147
142	148		You can also create a database from within your program. The program in the Creating the test database section below shows how you can detect missing databases and create them on the fly. A similar procedure could be called in Lazarus applications when initializing your form (FormShow event).
143	149
144	150		### Creating objects like tables, views, triggers...
145	151
146	152		Creating the objects - as most things - is the same in Firebird and Firebird embedded. Please see [[Firebird\|Firebird#Creating_objects_programmatically]]
147	153
148	154		# Running FPC database test suite
149	155
150	156		It can be useful to run the FPC database test framework (in packages\fcl-db\tests) when you are hunting bugs, trying to find out how something works, or improving the database code.
151	157
152	158		## Telling the framework which database to use
153	159
154	160		You will need to edit the db test framework .ini file to tell the test executable that it needs to use your embedded database. See [[Databases\|Databases#Running_FPC_database_tests]] for instructions on how to do this.
155	161
156	162		## Creating the test database
157	163
158	164		If you have set up the framework to use Firebird, you will need to prepare the environment for testing.
159	165
160	166		Copy over the Firebird embedded libraries and other files for your architecture into the test directory.
161	167
162	168		Now create the test database - manually or programmatically
163	169
164	170		### Manually creating a database
165	171
166	172		isql
167	173
168	174		``` sql
169	175		create database 'test.fdb' user 'SYSDBA' password 'masterkey' page_size 16384 default character set UTF8;
170	176		exit;
171	177		```
172	178
173	179		### Programmatically creating a database
174	180
175	181		You can programmatically create a database. The example below creates a local, embedded database, but it can be easily changed to a remote database (adjust hostname and if necessary path):
176	182
177	183		``` pascal
178	184		program firebirdembeddedtest;
179	185		{$mode objfpc}{$H+}
180	186
181	187		uses
182	188		{$IFDEF UNIX}{$IFDEF UseCThreads}
183	189		cthreads,
184	190		{$ENDIF}{$ENDIF}
185	191		Classes, SysUtils,
186	192		sqldb, IBConnection {for Firebird};
187	193
188	194		const
189	195		// Name for the database. Be sure to specify the same name in your database.ini
190	196		DatabaseFile = 'test.fdb'; //Sensible name for a test database, right?
191	197		var
192	198		Fire: TIBConnection;
193	199
194	200		begin
195	201		//Connection to Firebird database
196	202		// The next line is needed for quite old FPC versions
197	203		// Newer versions will first look for fbembed.dll in the application directory automatically
198	204		//UseEmbeddedFirebird:=true; // Using embedded (and fbembed.dll) or regular client/server (fbclient.dll), requires ibase60dyn in uses
199	205		Fire:=TIBConnection.Create(nil);
200	206		try
201	207		Fire.HostName := ''; //must be empty for embedded Firebird; must be filled for client/server Firebird
202	208		Fire.DatabaseName := DatabaseFile; //(path and) filename
203	209		// Username and password do not matter for authentication, but you do get authorizations in the database
204	210		// based on the name (and optionally role) you give.
205	211		Fire.Username := 'SYSDBA';
206	212		Fire.Password := 'masterkey'; //default password for SYSDBA
207	213		Fire.Charset := 'UTF8'; //Send and receive string data in UTF8 encoding
208	214		Fire.Dialect := 3; //Nobody uses 1 or 2 anymore.
209	215		Fire.Params.Add('PAGE_SIZE=16384'); //I like a large page size (used when creating a database). Useful for larger indexes=>larger possible column sizes
210	216
211	217		// Find out if there is a database in the application directory.
212	218		// If not, create it. Note: this may fail if you don't have enough permissions.
213	219
214	220		// If you use client/server, you obviously don't need this part of the code.
215	221		if (FileExists(DatabaseFile)=false) then
216	222		begin
217	223		writeln('File '+DatabaseFile+' does not exist.');
218	224		writeln('Creating a Firebird embedded database...');
219	225		// Create the database as it doesn't exist
220	226		try
221	227		Fire.CreateDB; //Create the database file.
222	228		except
223	229		on E: Exception do
224	230		begin
225	231		writeln('ERROR creating database. Probably problems loading embedded library:');
226	232		writeln('- not all files present');
227	233		writeln('- wrong architecture (e.g. 32 bit instead of 64 bit)');
228	234		writeln('Exception message:');
229	235		writeln(E.ClassName+'/'+E.Message);
230	236		end;
231	237		end;
232	238		Fire.Close;
233	239		end;
234	240		finally
235	241		Fire.Free;
236	242		end;
237	243		end.
238	244		```
239	245
240	246		## Running the test suite
241	247
242	248		Finally compile and run the dbtestframework.pas database test program. It should generate status output on the console and XML output in files with test results that can be analysed and sent to developers.
243	249
244	250		# Windows x64 and FPC pre-March 2013
245	251
246	252		> Warning: In versions of FPC below FPC 2.7.1 r23738 (March 2013), FPC x64 on Windows does not work well with the Firebird embedded dll - or other DLLs. The section below contains a workaround if you have an old FPC version
247	253
248	254		On versions of FPC below 2.7.1 r23738 you can get errors/exceptions from the IBConnection object even if you write correct code (see e.g. [Create database bug report](http://bugs.freepascal.org/view.php?id=21581).
249	255
250	256		Workarounds:
251	257
252	258		1. move to a development snapshot/FPC version after FPC 2.7.1 r23738; see [[http://wiki.lazarus.freepascal.org/User Changes 3.0\|http://wiki.lazarus.freepascal.org/User_Changes_3.0#Exception_handling_has_been_changed_to_be_ABI-conformant]]
253	259		2. if on FPC 2.7.1 lower than r23738, compile FPC with -dTEST_WIN64_SEH in order to use the new Structured Exception Handling (SEH) code on Win64.
254	260		3. use a 32 bit FPC and Lazarus on Windows 64
255	261
256	262		# External references
257	263
258	264		- [How to setup Firebird embedded on Linux or Mac?](http://www.firebirdfaq.org/faq51/)
259	265		- [Earlier version of Firebird embedded on Linux](http://www.mwasoftware.co.uk/index.php?option=com_content&task=view&id=105)
260	266
261	267		---
262	268		From the Free Pascal wiki, page "Firebird embedded", 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