postgres: what changed

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

Old	New		Line
1	1		## Overview
2	2
3	3		You can use Free Pascal/Lazarus to access a PostgreSQL database server. If you are looking for information on the postgres package in FPC, please see [[postgres\|postgres#PostgreSQL_package:_the_low_level_units]] below.
4	4
5	5		Advantages of PostgreSQL:
6	6
7	7		- It is very widely used and available
8	8		- Very stable and has a complete feature set
9	9		- Liberal license (no costs) in comparison with MySQL
10	10
11	11		Disadvantage of PostgreSQL:
12	12
13	13		- Some hosters may not offer PostgreSQL)
14	14		- No embedded version
15	15
16	16		Win64: please see warning [[Windows Programming Tips\|here]] on not using certain FPC/Lazarus Win64 versions.
17	17
18	18		## Direct access to PostgreSQL
19	19
20	20		You can connect Lazarus with PostgreSQL by using PostgreSQL Data Access Components (PgDAC). It is a library of components that provides native connectivity to PostgreSQL from Lazarus (and Free Pascal) on Windows, Mac OS X, iOS, Android, Linux, and FreeBSD for both 32-bit and 64-bit platforms. PgDAC is designed to help programmers develop really lightweight, faster and cleaner PostgreSQL database applications without deploying any additional libraries.
21	21
22	22		You can download this [Lazarus component](https://www.devart.com/pgdac/download.html) for free.
23	23
24	24		## Zeos
25	25
26	26		Zeos supports PostgreSQL; please see [[ZeosDBO]]
27	27
28	28		## SQLDB
29	29
30	30		FPC/Lazarus supports PostgreSQL out of the box with a PostgreSQL connection component/class. If you are using FPC only or want to manually add PostgreSQL support, add pqconnection to your uses clause. Otherwise, Lazarus provides a component:
31	31
32	32		![](/wiki-images/c/c5/Sqldbcomponents.png%E2%80%8E)
33	33
34	34		Note: The libpq C client contains some memory leaks (at least up till version 9.3 of Postgres) when a library is repeatedly loaded/unloaded. SQLDB loads the library when the first connection is made, and unloads it when the last connection closes. This means that whenever the last connection is closed, a small memory leak is created. To prevent this from happening (and speed up the application), you can load the library once at the start of the process with the InitialisePostgres3 call.
35	35
36	36		The CharSet property is used for client encoding.
37	37
38	38		The TPQConnection component does not directly support a Port property, but one can pass the port into the component via the Params parameter:
39	39
40	40		``` pascal
41	41		PQConnection.Params.Add('port=' + VariableContainingPort);
42	42		```
43	43
44	44		Also other PostgreSQL specific connection parameters can be specified using the Params property:
45	45
46	46		``` pascal
47	47		PQConnection.Params.Add('application_name=''yourappname''')
48	48		```
49	49
50	50		For all supported connection parameters see: [Connection Parameter Key Words](https://www.postgresql.org/docs/current/static/libpq-connect.html#LIBPQ-PARAMKEYWORDS)
51	51
52	52		### Example
53	53
54	54		See [[SQLdb Tutorial1]] for a tutorial on creating a GUI database-enabled program that is written for PostgreSQL/SQLDB, as well as SQLite/SQLDB, Firebird/SQLDB, basically any RDBMS SQLDB supports).
55	55
56	56		### Event monitoring
57	57
58	58		If you have FPC2.6.2+ and a recent version of Lazarus, you can use the TPQTEventMonitor component to monitor events coming from PostgreSQL.
59	59
60	60		It is a thin wrapper around FPC PQEventMonitor; please see the FPC pqeventstest.pp example programs for details.
61	61
62	62		### Installation and errors
63	63
64	64		As with all sqldb units, you need to add your driver libraries (all required PostgreSQL .dll/.manifest files)
65	65
66	66		- to a directory in the (library search) path (e.g. c:\windows\system32 for Windows)
67	67		- or (Windows) to the program output directory (e.g. lib/something/ in your project directory, and the project directory
68	68
69	69		#### Windows 64 bit driver
70	70
71	71		If you are developing 64 bit applications, you must use a 64 bit DLL.
72	72
73	73		> Note: FPC 2.6.0 (therefore Lazarus 1.0.4 or lower) does not yet support PostgreSQL on Windows 64 bit (unless you patch and recompile it).
74	74
75	75		A Windows 64 driver is fairly hard to find but can be downloaded here: [1](http://www.enterprisedb.com/products-services-training/pgdownload#windows). The driver library can be installed in c:\windows\system32; 32 bit driver libraries can be installed in the confusingly named c:\windows\syswow64
76	76
77	77		#### Error: "Can not load PostgreSQL client library "libpq.dll""
78	78
79	79		The program cannot find your PostgreSQL driver files.
80	80
81	81		See above on instructions where to install the libraries.
82	82
83	83		A good example that demonstrates how to include drive DLL files when connecting Lazarus with PostgreSQL under Windows is [easyDB](http://paginas.fe.up.pt/~asousa/wiki/doku.php?id=proj:lazarus_projects#easy_db).
84	84
85	85		On Linux/Unix/OSX: make sure the PostgreSQL libraries are in your library search path, e.g.:
86	86
87	87		- On Linux add the path to the libpq.so file to the libraries section in your /etc/fpc.cfg file. For example : -Fl/usr/local/pgsql/lib
88	88		- It may be necessary to create a symbolic link from a specific library version to a general library name:
89	89		``` bash
90	90		ln -s /usr/lib/pqsql.so.5 /usr/lib/pqsql.so
91	91		```
92	92
93	93		. Alternatively, install the postgresql client -dev package using your distribution's package manager
94	94
95	95		#### Problems clearing parameters
96	96
97	97		> Note: Verify this: is .paramtype really necessary?
98	98
99	99		At least in FPC \<= 2.6.2: if you .Clear a parameter (i.e. set it to NULL), PostgreSQL may have difficulty recognizing the parameter type.
100	100
101	101		In that case, explicitly specify the type, e.g.:
102	102
103	103		``` pascal
104	104		FWriteQuery.Params.ParamByName('LONGITUDE').ParamType:=ptInput; //required for postgresql
105	105		FWriteQuery.Params.ParamByName('LONGITUDE').Clear
106	106		```
107	107
108	108		## How To
109	109
110	110		### Use keyword RETURNING for values
111	111
112	112		#### Get Last Inserted ID
113	113
114	114		With PostGres there is no need to run a second query to get the last inserted ID.
115	115
116	116		Use INSERT RETURNING and read the value:
117	117
118	118		``` pascal
119	119		var
120	120		ID: Integer
121	121		..
122	122		Query.SQL.Text:= 'INSERT INTO myschema.films(film_name)' +
123	123		'VALUES(:film_name) RETURNING film_id;';
124	124		Query.Open;
125	125		ID:= Query.FieldByName('film_id').AsInteger;
126	126		```
127	127
128	128		#### Get multiple fields and expressions
129	129
130	130		RETURNING in PostgreSQL is more flexible than most database engines. It works with INSERT, UPDATE, and DELETE statements and can be any list of fields, constants, or expressions that would be found in a SELECT list.
131	131
132	132		``` SQL
133	133		INSERT INTO films (film_name) VALUES ('val') RETURNING film_id; -- Returns id's for newly created rows.
134	134		INSERT INTO films (film_name) VALUES ('val') RETURNING film_id, kind; -- Returns id and kind fields in newly created rows.
135	135		INSERT INTO films (film_name) VALUES ('val') RETURNING *; -- Returns all fields in newly created rows.
136	136
137	137		UPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama' RETURNING film_id; -- Returns id's of updated rows.
138	138		UPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama' RETURNING film_id, film_name; -- Returns id and film names of updated rows.
139	139		UPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama' RETURNING *; -- Returns all fields of updated rows.
140	140
141	141		DELETE FROM films RETURNING film_id; --Returns id's of deleted rows.
142	142		DELETE FROM films RETURNING film_id, film_name; --Returns id's and film names of deleted rows.
143	143		DELETE FROM films RETURNING *; -- Returns all fields of deleted rows.
144	144		```
145	145
146	146		RETURNING using fields, constants, and expressions:
147	147
148	148		``` SQL
149	149		INSERT INTO films (film_name) VALUES ('val') RETURNING id, 'a' AS a, id*2 AS doubled_id, CASE WHEN id > 100 THEN 'a' ELSE 'b' END AS foo;
150	150		```
151	151
152	152		## PostgreSQL package: the low level units
153	153
154	154		As with all databases, the SQLDB code depends on a lower level PostgreSQL specific unit that wraps around the PostgreSQL driver library (.so/.dll/.dylib). Normally, you would use the higher-level SQLDB code as it allows you to code more quickly, easily switch databases etc.
155	155
156	156		Using this is very easy, all you need to do is compile some units, and use these units in your program. You need to specify the place of the PostgreSQL client Library (libpq) when compiling, and that is it.
157	157
158	158		The main unit is called postgres, normally this is the only unit you must include in your uses clause.
159	159
160	160		### Requirements
161	161
162	162		You need at least version 0.99.5 of Free Pascal (basically any version of FPC except extremely old ones). The headers are translated from PostgreSQL version 6.3.1.
163	163
164	164		### Installation
165	165
166	166		The postgres unit comes with the Free Pascal packages, and is distributed together with the compiler. This contains a directory postgres with the units, a test program and a makefile. cd to the directory and edit the Makefile to set the variables for your system. You must provide only 1 thing:
167	167
168	168		1. The directory where the libpq library resides, usually /usr/local/pgsql/lib
169	169
170	170		Typing
171	171
172		-	~~`make`~~
	172	+	```text
	173	+	make
	174	+	```
173	175
174	176		Should compile the units and the program. If compilation was succesfull, you can install with
175	177
176		-	~~`make install`~~
	178	+	```text
	179	+	make install
	180	+	```
177	181
178	182		(Remember to set the directory where the units should be installed.)
179	183
180	184		You can then test the program by running
181	185
182		-	~~`make test`~~
	186	+	```text
	187	+	make test
	188	+	```
183	189
184	190		This will:
185	191
186	192		- Run the test program testpg. It is a straightforward pascal translation of the example program in the PostGreSQL programmers' guide.
187	193		- Run a script to create a table in a database, and fill it with some data. (the psql program should be in your PATH for this) . By default, the used database is testdb.
188	194		- Run the testprogram testemail
189	195		- Run a shell script again to remove the created table.
190	196
191	197		You will see a lot of messages on your screen, giving you feedback and results. If something went wrong, make will inform you of this.
192	198
193	199		Go back to [[Package List\|Packages List]]
194	200
195	201		## See also
196	202
197	203		- [[TPSQL]] - A different set of PostgreSQL dataset/database components
198	204		- [[Lazarus DB Faq]] - More about database programming
199	205		- [[Lazarus Database Tutorial]] - Getting Lazarus working with various databases
200	206
201	207		---
202	208		From the Free Pascal wiki, page "postgres", as it stood on 2020-02-23 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