SQLdb Tutorial0: what changed

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

Title: SQLdb Tutorial0

New		Line
1	+	## Introduction
2	+
3	+	This article will help you set up your database environment with sample tables and data to use in SQLdb Tutorial 1, 2 and 3.
4	+
5	+	Although this page is quite long, you will only go through a few of the sections as this article contains instructions for various database systems.
6	+
7	+	If you want to get up and running quickly, I'd suggest installing the Firebird server and example database as described below.
8	+
9	+	## Requirements
10	+
11	+	This tutorial is written for use with recent Lazarus versions (Laz 1.0); it should also work on older versions Lazarus 0.9.30.
12	+
13	+	Furthermore you need an SQL/relational database, such as [Firebird](http://sourceforge.net/project/showfiles.php?group_id=9028) (if possible version 2.0 or newer). It's easiest if you use standard settings (e.g user name SYSDBA and password masterkey), and that you have the employee sample database installed.
14	+
15	+	You can use another database (e.g. Microsoft SQL Server, MySQL, PostgreSQL, Oracle, SQLite, Sybase ASE or another database using ODBC). Please make sure you have the required database client libraries installed (see the various wiki articles on databases) Please see below (section No Firebird or employee.fdb installed?) for a way to set up your tables
16	+
17	+	## Firebird installation
18	+
19	+	In case you haven't yet installed Firebird or the sample database that is used with the tutorials, please find some instructions below.
20	+
21	+	### Install Firebird client/server
22	+
23	+	If you haven't installed anything, you can download and run the installer from www.firebirdsql.org and install the server (e.g. "32-bit Classic, Superclassic & Superserver").
24	+
25	+	### Firebird database libraries on Windows
26	+
27	+	After installation, on Windows you will need to have the Firebird client DLLs present:
28	+
29	+	- they could be in your system directory (available for all programs)
30	+	- they can also be in your Lazarus directory (for design time support in the IDE) and in the output directory where the executable is (to run the compiled program).
31	+
32	+	If you haven't installed the Firebird server, an easy way to get the client DLLs is: download Firebird Embedded 2.5 from [1](http://www.firebirdsql.org/en/firebird-2-5/) Extract these files to your application directory:
33	+
34	+	``` bash
35	+	fbclient.dll #only if using the server
36	+	#or
37	+	fbembed.dll #only if using embedded
38	+	firebird.msg
39	+	ib_util.dll
40	+	icudt30.dll
41	+	icuin30.dll
42	+	icuuc30.dll
43	+	Microsoft.VC80.CRT.manifest
44	+	msvcp80.dll
45	+	msvcr80.dll
46	+	```
47	+
48	+	Rename fbembed.dll to fbclient.dll (the name for a regular, client-server Firebird client - this helps use on older Lazarus/FPC versions). The embedded Firebird DLL can also act as a regular Firebird client.
49	+
50	+	Make sure the employee.fdb database is in your project directory. You can copy it from the examples/empbuild/ directory of the firebird 2.5 server.
51	+
52	+	Finally, compile your project (even if it empty) once to create the output directory, and copy the dlls, as well as the employee.fdb database, into that directory.
53	+
54	+	### Firebird database libraries on other systems
55	+
56	+	On Linux/OSX, you will also need the Firebird client shared libraries. On Linux you can use your distribution's method of getting programs to get the Firebird client packages, e.g. on Debian:
57	+
58	+	``` bash
59	+	aptitude install libfbclient2 firebird-dev #we need the dev version because FPC 2.6 and lower will look for libfbclient.so
60	+	```
61	+
62	+	## No Firebird or employee.fdb installed?
63	+
64	+	If you don't have the employee sample database installed or are using a different database, here is a minimal version of the table we'll be using (note: directions for some specific databases can be found below).
65	+
66	+	### Automatic creation
67	+
68	+	The easiest way of setting this up is to create a new empty database on your system, then connect to it with the TSQLScriptSample sample/utility program. This program is included with current Lazarus development releases in the `./examples/database/tsqlscript` directory, but can also be downloaded from
69	+
70	+
71	+	<http://svn.freepascal.org/svn/lazarus/trunk/examples/database/tsqlscript/>
72	+
73	+	Compile and run the program, then connect to the database:
74	+
75	+	<figure>
76	+	<img src="tsqlscriptsample_connect.png" title="tsqlscriptsample_connect.png" />
77	+	<figcaption>tsqlscriptsample_connect.png</figcaption>
78	+	</figure>
79	+
80	+	then click the button Copy table creation script, and Run script:
81	+
82	+	<figure>
83	+	<img src="tsqlscriptsample_runfirstscript.png" title="tsqlscriptsample_runfirstscript.png" />
84	+	<figcaption>tsqlscriptsample_runfirstscript.png</figcaption>
85	+	</figure>
86	+
87	+	The program should indicate success. Now do the same for Copy sample data script, and Run script
88	+
89	+	In case of problems, you can try the manual steps below.
90	+
91	+	### Manual creation
92	+
93	+	We will create CUSTOMER and EMPLOYEE tables that will be used in a later tutorial.
94	+
95	+	Run these SQL commands in your database editor/tool:
96	+
97	+	``` sql
98	+	CREATE TABLE CUSTOMER
99	+	(
100	+	CUST_NO INTEGER NOT NULL,
101	+	CUSTOMER VARCHAR(25) NOT NULL,
102	+	CITY VARCHAR(25),
103	+	COUNTRY VARCHAR(15),
104	+	CONSTRAINT CT_CUSTOMER_PK PRIMARY KEY (CUST_NO)
105	+	);
106	+	```
107	+
108	+	``` sql
109	+	CREATE TABLE EMPLOYEE
110	+	(
111	+	EMP_NO INTEGER NOT NULL,
112	+	FIRST_NAME VARCHAR(15) NOT NULL,
113	+	LAST_NAME VARCHAR(20) NOT NULL,
114	+	PHONE_EXT VARCHAR(4),
115	+	JOB_CODE VARCHAR(5) NOT NULL,
116	+	JOB_GRADE INTEGER NOT NULL,
117	+	JOB_COUNTRY VARCHAR(15) NOT NULL,
118	+	SALARY NUMERIC(10,2) NOT NULL,
119	+	CONSTRAINT CT_EMPLOYEE_PK PRIMARY KEY (EMP_NO)
120	+	);
121	+	```
122	+
123	+	Some data so you can at least show something.... first some clients:
124	+
125	+	``` sql
126	+	INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (1, 'Michael Design', 'San Diego', 'USA');
127	+	INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (2, 'VC Technologies', 'Dallas', 'USA');
128	+	INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (3, 'Klämpfl, Van Canneyt', 'Boston', 'USA');
129	+	INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (4, 'Felipe Bank', 'Manchester', 'England');
130	+	INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (5, 'Joost Systems, LTD.', 'Central Hong Kong', 'Hong Kong');
131	+	INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (6, 'Van der Voort Int.', 'Ottawa', 'Canada');
132	+	INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (7, 'Mrs. Mauvais', 'Pebble Beach', 'USA');
133	+	INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (8, 'Asinine Vacation Rentals', 'Lihue', 'USA');
134	+	INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (9, 'Fax', 'Turtle Island', 'Fiji');
135	+	INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (10, 'FPC Corporation', 'Tokyo', 'Japan');
136	+	INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (11, 'Dynamic Intelligence Corp', 'Zurich', 'Switzerland');
137	+	INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (12, '3D-Pad Corp.', 'Paris', 'France');
138	+	INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (13, 'Swen Export, Ltd.', 'Milan', 'Italy');
139	+	INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (14, 'Graeme Consulting', 'Brussels', 'Belgium');
140	+	INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (15, 'Klenin Inc.', 'Den Haag', 'Netherlands');
141	+	```
142	+
143	+	Then some employees:
144	+
145	+	``` sql
146	+	INSERT INTO EMPLOYEE (emp_no, first_name, last_name, phone_ext, job_code, job_grade,
147	+	job_country, salary)
148	+	VALUES (1,'William','Shatner','1702','CEO',1,'USA',48000);
149	+	INSERT INTO EMPLOYEE (emp_no, first_name, last_name, phone_ext, job_code, job_grade,
150	+	job_country, salary)
151	+	VALUES (2,'Ivan','Rzeszow','9802','Eng',2,'Russia',38000);
152	+	INSERT INTO EMPLOYEE (emp_no, first_name, last_name, phone_ext, job_code, job_grade,
153	+	job_country, salary)
154	+	VALUES (3,'Erin','Powell','1703','Admin',2,'USA',45368);
155	+	```
156	+
157	+	Please create the database, table and insert the data in your database environment.
158	+
159	+	### SQLite
160	+
161	+	If you are using SQLite, you can create the database mentioned above in your project directory by running the sqlite executable:
162	+
163	+	``` bash
164	+	sqlite employee.sqlite
165	+	```
166	+
167	+	Now copy and paste the above CREATE TABLE and INSERT statements. To test if the right data is present, enter this query:
168	+
169	+	``` SQL
170	+	select * from customer;
171	+	```
172	+
173	+	End your session with
174	+
175	+	``` SQL
176	+	.quit
177	+	```
178	+
179	+	A file called employee.sqlite should now be created in your project directory.
180	+
181	+	Make sure the required sqlite dll/so is installed - e.g. on Windows, sqlite3.dll should be present in
182	+
183	+	- your Lazarus + project output directory or
184	+	- your system directory
185	+
186	+	Also, if you have a 32-bit sqlite dll, make sure you have a 32-bit lazarus.
187	+
188	+	Compile your project (even if it is empty) once to create the output directory, and (on Windows) copy the dll, as well as the employee.sqlite database, into that directory.
189	+
190	+	### PostgreSQL
191	+
192	+	This section assumes you're using a Linux server and the shell; comparable steps can be done using Windows and GUI tools such as pgadmin.
193	+
194	+	Log in to your server and switch to the postgres account:
195	+
196	+	``` bash
197	+	su - postgres -c psql # immediately start up psql SQL interpreter
198	+	```
199	+
200	+	Create a user for the database and the tables:
201	+
202	+	``` SQL
203	+	CREATE USER employee WITH PASSWORD 'hellopassword'; -- of course, adjust password to taste
204	+	-- something like 'CREATE ROLE' should appear indicating success.
205	+	-- to later change the password you can use something like
206	+	-- alter user employee with password '<newpasswordhere>';
207	+
208	+	-- We're going to let the password never expire; if you want more security, you can leave this step out:
209	+	ALTER USER employee VALID UNTIL 'infinity'; --password never expires
210	+
211	+	-- Now we're tightening it up a bit again:
212	+	-- Don't allow user to create a database or create other users:
213	+	ALTER USER employee NOCREATEDB NOCREATEROLE; --restrict object creation
214	+	-- something like 'ALTER ROLE' should appear indicating success.
215	+
216	+	-- Create our database:
217	+	CREATE DATABASE employee;
218	+	-- something like CREATE DATABASE should appear indicating success.
219	+
220	+	-- Assign all privileges on database employee to user employee:
221	+	GRANT ALL PRIVILEGES ON DATABASE employee TO employee; -- allow user full permissions to database
222	+	-- something like GRANT should appear indicating success.
223	+
224	+	-- We create the table using a serial datatype - aka autonumber/autoincrement:
225	+	CREATE TABLE customer
226	+	(
227	+	cust_no serial NOT NULL,
228	+	customer character varying(25) NOT NULL,
229	+	city character varying(25),
230	+	country character varying(15),
231	+	CONSTRAINT integ_60 PRIMARY KEY (cust_no )
232	+	);
233	+
234	+	-- Then create the employee table:
235	+	CREATE TABLE EMPLOYEE
236	+	(
237	+	EMP_NO SERIAL NOT NULL,
238	+	FIRST_NAME VARCHAR(15) NOT NULL,
239	+	LAST_NAME VARCHAR(20) NOT NULL,
240	+	PHONE_EXT VARCHAR(4),
241	+	JOB_CODE VARCHAR(5) NOT NULL,
242	+	JOB_GRADE INTEGER NOT NULL,
243	+	JOB_COUNTRY VARCHAR(15) NOT NULL,
244	+	SALARY NUMERIC(10,2) NOT NULL,
245	+	CONSTRAINT CT_EMPLOYEE_PK PRIMARY KEY (EMP_NO)
246	+	);
247	+
248	+	-- Now copy and paste the INSERT statements from the section above to insert the data.
249	+
250	+	-- To test if the right data is present, enter this query:
251	+	SELECT * FROM customer;
252	+	-- You should see some customer data.
253	+
254	+	-- Exit out of psql:
255	+	\q
256	+	```
257	+
258	+	Now you should be on a shell logged in as the postgres user.
259	+
260	+	If your server is on another machine than your development machine, make sure you allow network access to the database. See your postgresql documentation for details, but something like this should work:
261	+
262	+	``` bash
263	+	# please adjust nano (e.g. use vim,emacs,joe...) and the postgres version number depending on situation
264	+	nano /etc/postgresql/8.4/main/pg_hba.conf
265	+	```
266	+
267	+	Verify if there is a line like - NOTE: replace 192.168.0.1 with your own LAN ip address range
268	+
269	+	\#allow access from local network using md5 hashed passwords: host all all 192.168.0.1/24 md5
270	+
271	+	or more restrictive:
272	+
273	+	\# only allow network access to the employee database by the employee user host employee employee 192.168.0.1/24 md5
274	+
275	+	If there isn't such a line, add the line at the end, save and close your editor. See PostgreSQL documentation for more details.
276	+
277	+	Reload PostgreSQL settings:
278	+
279	+	``` bash
280	+
281	+	psql
282	+	```
283	+
284	+	then
285	+
286	+	``` SQL
287	+
288	+	SELECT pg_reload_conf(); --reload settings...
289	+	-- ...and exit back to shell:
290	+	\q
291	+	```
292	+
293	+	Test logging in to PostgreSQL.
294	+
295	+	Note: by default PostgreSQL tries an ident/unix domain socket login which doesn't allow passwords. So we specify a host to force TCP/IP login:
296	+
297	+	``` bash
298	+	psql -h 127.0.0.1 -d employee -U employee -W #Log in via tcp/ip. Enter your db password.
299	+	```
300	+
301	+	Make sure the required PostgreSQL dll/so and any required other libraries is installed - e.g. on Windows, they should be either:
302	+
303	+	- in your Lazarus + project output directory or
304	+	- in your system directory
305	+
306	+	Compile your project (even if it is empty) once to create the output directory, and (on Windows) copy the dlls into that directory.
307	+
308	+	## Finish
309	+
310	+	Now you've set up your database, you can continue to [[SQLdb Tutorial1\|the first "real" tutorial]].
311	+
312	+	## See also
313	+
314	+	- [[SQLdb Tutorial1]]: First part of the DB tutorial series, showing how to set up a grid that shows database data
315	+	- [[SQLdb Tutorial2]]: Second part of the DB tutorial series, showing editing, inserting etc.
316	+	- [[SQLdb Tutorial3]]: Third part of the DB tutorial series, showing how to program for multiple databases and use a login form
317	+	- [[SQLdb Tutorial4]]: Fourth part of the DB tutorial series, showing how to use data modules
318	+	- [[Lazarus Database Overview]]: Information about the databases that Lazarus supports. Links to database-specific notes.
319	+	- [[SQLdb Package]]: information about the SQLdb package
320	+	- [[SQLdb Programming Reference]]: an overview of the interaction of the SQLdb database components
321	+	- [[SqlDBHowto]]: information about using the SQLdb package
322	+	- [[Working With TSQLQuery]]: information about TSQLQuery
323	+
324	+	\
325	+
326	+	---
327	+	From the Free Pascal wiki, page "SQLdb Tutorial0", as it stood on 2019-08-29 after a change by Ottod, 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