Difference between revisions of "SQLdb Tutorial1/es"

From Free Pascal wiki
Jump to navigationJump to search
Line 63: Line 63:
 
Creamos una tabla llamada EMPLOYEE que será utilizada en un tutorial posterior.
 
Creamos una tabla llamada EMPLOYEE que será utilizada en un tutorial posterior.
  
−
<syntaxhighlight lang="sql">
+
<syntaxhighlight lang="sql"> CREATE TABLE CUSTOMER
−
CREATE TABLE CUSTOMER
+
(
−
(
+
  CUST_NO INTEGER NOT NULL,
−
  CUST_NO INTEGER NOT NULL,
+
  CUSTOMER VARCHAR(25) NOT NULL,
−
  CUSTOMER VARCHAR(25) NOT NULL,
+
  CITY VARCHAR(25),
−
  CITY VARCHAR(25),
+
  COUNTRY VARCHAR(15),
−
  COUNTRY VARCHAR(15),
+
  CONSTRAINT CT_CUSTOMER_PK PRIMARY KEY (CUST_NO)
−
  CONSTRAINT CT_CUSTOMER_PK PRIMARY KEY (CUST_NO)
+
);</syntaxhighlight>
−
);
 
−
</syntaxhighlight>
 
  
−
<syntaxhighlight lang="sql">
+
<syntaxhighlight lang="sql"> CREATE TABLE EMPLOYEE
−
CREATE TABLE EMPLOYEE
+
(
−
(
+
  EMP_NO INTEGER NOT NULL,
−
  EMP_NO INTEGER NOT NULL,
+
  FIRST_NAME VARCHAR(15) NOT NULL,
−
  FIRST_NAME VARCHAR(15) NOT NULL,
+
  LAST_NAME VARCHAR(20) NOT NULL,
−
  LAST_NAME VARCHAR(20) NOT NULL,
+
  PHONE_EXT VARCHAR(4),
−
  PHONE_EXT VARCHAR(4),
+
  JOB_CODE VARCHAR(5) NOT NULL,
−
  JOB_CODE VARCHAR(5) NOT NULL,
+
  JOB_GRADE INTEGER NOT NULL,
−
  JOB_GRADE INTEGER NOT NULL,
+
  JOB_COUNTRY VARCHAR(15) NOT NULL,
−
  JOB_COUNTRY VARCHAR(15) NOT NULL,
+
  SALARY NUMERIC(10,2) NOT NULL,
−
  SALARY NUMERIC(10,2) NOT NULL,
+
  CONSTRAINT CT_EMPLOYEE_PK PRIMARY KEY (EMP_NO)
−
  CONSTRAINT CT_EMPLOYEE_PK PRIMARY KEY (EMP_NO)
+
);</syntaxhighlight>
−
);
 
−
</syntaxhighlight>
 
  
 
Algunos datos para almenos mostrar algo.... primero algunos clientes:
 
Algunos datos para almenos mostrar algo.... primero algunos clientes:
−
<syntaxhighlight lang="sql">
+
<syntaxhighlight lang="sql"> INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (1, 'Michael Design', 'San Diego', 'USA');
−
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (1, 'Michael Design', 'San Diego', 'USA');
+
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (2, 'VC Technologies', 'Dallas', 'USA');
−
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (2, 'VC Technologies', 'Dallas', 'USA');
+
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (3, 'Klämpfl, Van Canneyt and Co.', 'Boston', 'USA');
−
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (3, 'Klämpfl, Van Canneyt and Co.', 'Boston', 'USA');
+
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (4, 'Felipe Bank', 'Manchester', 'England');
−
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (4, 'Felipe Bank', 'Manchester', 'England');
+
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (5, 'Joost Systems, LTD.', 'Central Hong Kong', 'Hong Kong');
−
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (5, 'Joost Systems, LTD.', 'Central Hong Kong', 'Hong Kong');
+
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (6, 'Van der Voort Int.', 'Ottawa', 'Canada');
−
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (6, 'Van der Voort Int.', 'Ottawa', 'Canada');
+
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (7, 'Mrs. Mauvais', 'Pebble Beach', 'USA');
−
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (7, 'Mrs. Mauvais', 'Pebble Beach', 'USA');
+
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (8, 'Asinine Vacation Rentals', 'Lihue', 'USA');
−
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (8, 'Asinine Vacation Rentals', 'Lihue', 'USA');
+
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (9, 'Fax', 'Turtle Island', 'Fiji');
−
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (9, 'Fax', 'Turtle Island', 'Fiji');
+
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (10, 'FPC Corporation', 'Tokyo', 'Japan');
−
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (10, 'FPC Corporation', 'Tokyo', 'Japan');
+
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (11, 'Dynamic Intelligence Corp', 'Zurich', 'Switzerland');
−
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (11, 'Dynamic Intelligence Corp', 'Zurich', 'Switzerland');
+
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (12, '3D-Pad Corp.', 'Paris', 'France');
−
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (12, '3D-Pad Corp.', 'Paris', 'France');
+
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (13, 'Swen Export, Ltd.', 'Milan', 'Italy');
−
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (13, 'Swen Export, Ltd.', 'Milan', 'Italy');
+
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (14, 'Graeme Consulting', 'Brussels', 'Belgium');
−
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (14, 'Graeme Consulting', 'Brussels', 'Belgium');
+
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (15, 'Klenin Inc.', 'Den Haag', 'Netherlands');</syntaxhighlight>
−
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (15, 'Klenin Inc.', 'Den Haag', 'Netherlands');
 
−
</syntaxhighlight>
 
  
 
Then some employees:
 
Then some employees:
−
<syntaxhighlight lang="sql">
+
<syntaxhighlight lang="sql"> INSERT INTO employee(emp_no, first_name, last_name, phone_ext, job_code, job_grade,  
−
INSERT INTO employee(emp_no, first_name, last_name, phone_ext, job_code, job_grade,  
 
 
   job_country, salary)
 
   job_country, salary)
 
   VALUES (1,'William','Shatner','1702','CEO',1,'USA',48000)
 
   VALUES (1,'William','Shatner','1702','CEO',1,'USA',48000)
−
INSERT INTO employee(emp_no, first_name, last_name, phone_ext, job_code, job_grade,  
+
INSERT INTO employee(emp_no, first_name, last_name, phone_ext, job_code, job_grade,  
 
   job_country, salary)
 
   job_country, salary)
 
   VALUES (2,'Ivan','Rzeszow','9802','Eng',2,'Russia',38000);
 
   VALUES (2,'Ivan','Rzeszow','9802','Eng',2,'Russia',38000);
−
INSERT INTO employee(emp_no, first_name, last_name, phone_ext, job_code, job_grade,  
+
INSERT INTO employee(emp_no, first_name, last_name, phone_ext, job_code, job_grade,  
 
   job_country, salary)
 
   job_country, salary)
−
   VALUES (3,'Erin','Powell','1703','Admin',2,'USA',45368);
+
   VALUES (3,'Erin','Powell','1703','Admin',2,'USA',45368);</syntaxhighlight>
−
</syntaxhighlight>
 
  
 
Por favor, crea la base de datos, la tabla e inserta los datos en el entorno de base de datos.
 
Por favor, crea la base de datos, la tabla e inserta los datos en el entorno de base de datos.
Line 127: Line 119:
 
Si estás utilizando SQLite, entonces puedes crear la mencionada base de datos en el directorio del proyecto ejecutanto sqlite:
 
Si estás utilizando SQLite, entonces puedes crear la mencionada base de datos en el directorio del proyecto ejecutanto sqlite:
  
−
<syntaxhighlight lang="bash">
+
<syntaxhighlight lang="bash"> sqlite employee.sqlite</syntaxhighlight>
−
sqlite employee.sqlite
 
−
</syntaxhighlight>
 
 
Ahora copia y pega lo siguientes sentencias CREATE TABLE e INSERT.
 
Ahora copia y pega lo siguientes sentencias CREATE TABLE e INSERT.
 
Para comprobar que los datos correctos se encuentran presentes entrar la siguiente consulta (query):
 
Para comprobar que los datos correctos se encuentran presentes entrar la siguiente consulta (query):
−
<syntaxhighlight lang="SQL">
+
<syntaxhighlight lang="SQL"> select * from customer;</syntaxhighlight>
−
select * from customer;
 
−
</syntaxhighlight>
 
 
Finaliza la sesión con:
 
Finaliza la sesión con:
−
<syntaxhighlight lang="SQL">
+
<syntaxhighlight lang="SQL"> .quit</syntaxhighlight>
−
.quit
 
−
</syntaxhighlight>
 
  
 
Con todo esto debería haber creado un fichero llamado employee.sqlite dentro del directorio del proyecto.
 
Con todo esto debería haber creado un fichero llamado employee.sqlite dentro del directorio del proyecto.
Line 153: Line 139:
  
 
Log in to your server and switch to the postgres account:
 
Log in to your server and switch to the postgres account:
−
<syntaxhighlight lang=bash>
+
<syntaxhighlight lang=bash> su - postgres -c psql # immediately start up psql SQL interpreter</syntaxhighlight>
−
su - postgres -c psql # immediately start up psql SQL interpreter
 
−
</syntaxhighlight>
 
  
 
Create a user for the database and the tables:
 
Create a user for the database and the tables:
−
<syntaxhighlight lang=SQL>
+
<syntaxhighlight lang=SQL> CREATE USER employee WITH PASSWORD 'hellopassword'; -- of course, adjust password to taste
−
CREATE USER employee WITH PASSWORD 'hellopassword'; -- of course, adjust password to taste
+
-- something like  'CREATE ROLE' should appear indicating success.
−
-- something like  'CREATE ROLE' should appear indicating success.
+
-- to later change the password you can use something like
−
-- to later change the password you can use something like
+
-- alter user employee with password '<newpasswordhere>';
−
-- alter user employee with password '<newpasswordhere>';
+
-- We're going to let the password never expire; if you want more security, you can leave this step out:
−
 
+
ALTER USER employee VALID UNTIL 'infinity'; --password never expires
−
-- We're going to let the password never expire; if you want more security, you can leave this step out:
+
-- Now we're tightening it up a bit again:
−
ALTER USER employee VALID UNTIL 'infinity'; --password never expires
+
-- Don't allow user to create a database or create other users:
−
 
+
ALTER USER employee NOCREATEDB NOCREATEUSER; --restrict object creation
−
-- Now we're tightening it up a bit again:
+
-- something like 'ALTER ROLE' should appear indicating success.
−
-- Don't allow user to create a database or create other users:
+
-- Create our database:
−
ALTER USER employee NOCREATEDB NOCREATEUSER; --restrict object creation
+
CREATE DATABASE employee;
−
-- something like 'ALTER ROLE' should appear indicating success.
+
-- something like CREATE DATABASE should appear indicating success.
−
 
+
-- Assign all privileges on database employee to user employee:
−
-- Create our database:
+
GRANT ALL PRIVILEGES ON DATABASE employee TO employee; -- allow user full permissions to database
−
CREATE DATABASE employee;
+
-- something like GRANT should appear indicating success.
−
-- something like CREATE DATABASE should appear indicating success.
+
-- We create the table using a serial datatype - aka autonumber/autoincrement:
−
 
+
CREATE TABLE customer
−
-- Assign all privileges on database employee to user employee:
+
(
−
GRANT ALL PRIVILEGES ON DATABASE employee TO employee; -- allow user full permissions to database
 
−
-- something like GRANT should appear indicating success.
 
−
 
 
−
-- We create the table using a serial datatype - aka autonumber/autoincrement:
 
−
CREATE TABLE customer
 
−
(
 
 
   cust_no serial NOT NULL,
 
   cust_no serial NOT NULL,
 
   customer character varying(25) NOT NULL,
 
   customer character varying(25) NOT NULL,
Line 188: Line 166:
 
   country character varying(15),
 
   country character varying(15),
 
   CONSTRAINT integ_60 PRIMARY KEY (cust_no )
 
   CONSTRAINT integ_60 PRIMARY KEY (cust_no )
−
);
+
);
  
−
-- Then create the employee table:
+
-- Then create the employee table:
−
CREATE TABLE EMPLOYEE
+
CREATE TABLE EMPLOYEE
−
(
+
(
 
   EMP_NO SERIAL NOT NULL,
 
   EMP_NO SERIAL NOT NULL,
 
   FIRST_NAME VARCHAR(15) NOT NULL,
 
   FIRST_NAME VARCHAR(15) NOT NULL,
Line 202: Line 180:
 
   SALARY NUMERIC(10,2) NOT NULL,
 
   SALARY NUMERIC(10,2) NOT NULL,
 
   CONSTRAINT CT_EMPLOYEE_PK PRIMARY KEY (EMP_NO)
 
   CONSTRAINT CT_EMPLOYEE_PK PRIMARY KEY (EMP_NO)
−
);
+
);
  
−
-- Now copy and paste the INSERT statements from the section above to insert the data.
+
-- Now copy and paste the INSERT statements from the section above to insert the data.
  
−
-- To test if the right data is present, enter this query:  
+
-- To test if the right data is present, enter this query:  
−
SELECT * FROM customer;
+
SELECT * FROM customer;
−
-- You should see some customer data.
+
-- You should see some customer data.
  
−
-- Exit out of psql:
+
-- Exit out of psql:
−
\q  
+
\q </syntaxhighlight>
−
</syntaxhighlight>
 
  
 
Now you should be on a shell logged in as the postgres user.
 
Now you should be on a shell logged in as the postgres user.
  
 
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:
 
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:
−
<syntaxhighlight lang=bash>
+
<syntaxhighlight lang=bash> # please adjust nano (e.g. use vim,emacs,joe...) and the postgres version number depending on situation
−
# please adjust nano (e.g. use vim,emacs,joe...) and the postgres version number depending on situation
+
nano /etc/postgresql/8.4/main/pg_hba.conf
−
nano /etc/postgresql/8.4/main/pg_hba.conf
+
</syntaxhighlight>
−
</syntaxhighlight>
 
  
 
Verify if there is a line like - NOTE: replace 192.168.0.1 with your own LAN ip address range
 
Verify if there is a line like - NOTE: replace 192.168.0.1 with your own LAN ip address range
  
−
<nowiki>
+
<nowiki> #allow access from local network using md5 hashed passwords:
−
#allow access from local network using md5 hashed passwords:
+
host    all        all        192.168.0.1/24      md5</nowiki>
−
 
 
−
host    all        all        192.168.0.1/24      md5
 
−
</nowiki>
 
  
 
or more restrictive:
 
or more restrictive:
  
−
<nowiki>
+
<nowiki> # only allow network access to the employee database by the employee user
−
# only allow network access to the employee database by the employee user
+
host    employee        employee        192.168.0.1/24      md5</nowiki>
−
 
 
−
host    employee        employee        192.168.0.1/24      md5
 
−
</nowiki>
 
  
 
If there isn't such a line, add the line at the end, save and close your editor.
 
If there isn't such a line, add the line at the end, save and close your editor.
Line 242: Line 212:
  
 
Reload PostgreSQL settings:
 
Reload PostgreSQL settings:
−
<syntaxhighlight lang=bash>  
+
<syntaxhighlight lang=bash> psql</syntaxhighlight>
−
psql
 
−
</syntaxhighlight>
 
 
then
 
then
−
<syntaxhighlight lang=SQL>  
+
<syntaxhighlight lang=SQL> SELECT pg_reload_conf(); --reload settings...
−
SELECT pg_reload_conf(); --reload settings...
+
-- ...and exit back to shell:
−
-- ...and exit back to shell:
+
\q</syntaxhighlight>
−
\q
 
−
</syntaxhighlight>
 
  
 
Test logging in to PostgreSQL.  
 
Test logging in to PostgreSQL.  
  
 
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:
 
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:
−
<syntaxhighlight lang=bash>
+
<syntaxhighlight lang=bash> psql -h 127.0.0.1 -d employee -U employee -W #Log in via tcp/ip. Enter your db password.</syntaxhighlight>
−
psql -h 127.0.0.1 -d employee -U employee -W #Log in via tcp/ip. Enter your db password.
 
−
</syntaxhighlight>
 
  
 
Make sure the required PostgreSQL dll/so and any required other libraries is installed - e.g. on Windows, they should be either:
 
Make sure the required PostgreSQL dll/so and any required other libraries is installed - e.g. on Windows, they should be either:

Revision as of 13:10, 10 September 2013

Template:SQLdb Tutorial

Introducción

Este tutorial muestra como programar con bases de datos (BBDD) utilizando un ejemplo práctico basado en el paquete SQLdb. Está primordialmente orientado a principiantes. Para quien esté buscando sobre fundamentos de bases de datos SQL debería leer libros y documentación relevante. Para este tutorial he utilizado Firebird con el fichero de bases de dato ejempo llamado employee.fdb. Se pueden utilizar otras bases de datos, para lo cual se requieren algunos ajustes que se mencionan en este texto.

Mientras que este tutorial puede parecer muy extenso, sin embargo es simplemente una explicación ajustada de lo que hay que teclear y porque. Como podrás deducir al final, la cantidad de código actual que se necesitará para tener una aplicación funcional no es tan grande. La mayor parte de los desarrolladores experimentados serán felizmente capaces de recorrer el código y entender de que trata.

Además, cuando finalices el capítulo te darás cuenta de que tienes en este ejemplo básico un programa que funciona.

Este tutorial está basado en [[SQLdb Tutorial1/de|el tutorial en alemán] realizado por el usuario Swen, pero su contenido ha sido extendido después del ejemplo básico. Swen necesita la versión en alemán como base. Si esto supone un problema entonces se puede renombrar esta versión y basar una nueva versión traducida.

De parte de Swen: gracias a Joost y Michael. sin su ayuda probablemente nunca se habría escrito este tutorial.

Requerimientos

Este tutorial se ha escrito para usarse con versiones recientes de Lazarus (Laz 1.0); debería también funcionar en versiones antiguas como Lazarus 0.9.30.

Además se necesita una base de datos relacional SQL, tal como Firebird (a ser posible una versión 2.0 o superior). Es sencillo si utilizas configuraciones estandar (e.g. por ejemplo el nombre de usuario SYSDBA y contraseña masterkey) y tienes instalada la base de datos employee.

Se puede utilizar cualquier otra base de datos (e.g. MySQL, PostgreSQL, Oracle, SQLite o de otro tipo utilizando ODBC): deberías tener la base de datos apropiada con su estructura de tablas (ver más abajo), y utilizar el correspondiente conector TSQLConnector (descendiente (también indicado abajo). Si tu base de datos tiene diferencias significativas en cuanto a transacciones etc., entonces haz una anotación en esta sección.

El tutorial ha sido actualizado con notas detalladas para SQLite y PostgreSQL.

Librerías de bases de datos Firebird en Windows

Finalmente, en Windows se va a necesitar tener presentes las librerías DLL de Firebird:

  • Deben estar en el directorio system (de esta forma estarían disponibles para todos los programas).
  • Además también pueden estar en el directorio de Lazarus (para tenerlo en tiempo de diseño en el IDE) y en el directorio de salida donde se obtiene el ejecutable (para arrancar el programa compilado).

Un modo fácil de obtener las librerías cliente es descargarse Firebird Embebido 2.5 desde [1] Extraer estos ficheros en el directorio de la aplicación: 

 fbembed.dll
 firebird.msg
 ib_util.dll
 icudt30.dll
 icuin30.dll
 icuuc30.dll
 Microsoft.VC80.CRT.manifest
 msvcp80.dll
 msvcr80.dll

Renombrar fbembed.dll a fbclient.dll (el nombre regular para el cliente de Firebird cliente-servidor - esto ayuda a utilizarlo en versiones antiguas de Lazarus/FPC). La librería DLL embebida de Firebird puede actuar como un cliente regular de Firebird.

Asegúrate de que el fichero de la base de datos employee se encuentra en el directorio del proyecto. Se puede copiar desde el directorio examples/empbuild/ del servidor de Firebird 2.5.

Finalmente, compila el proyecto (aunque esté vacío) una vez para crear el directorio de salida, y copia las dlls, además del fichero de base de datos employee.fdb en el mismo directorio.

Librerias de base de datos Firebird en otros sistemas

En Linxu/OSX se necesitan también las librerías compartidas de cliente Firebird. En Linux se puede utilizar los médios de obtener programas, de la distribución (distro) en concreto de las muchas existentes, para obtener los paquetes de cliente de Firebird, e.g, en la distro debian sería así: 

 aptitude install libfbclient2 firebird-dev #necesitamos la versión de desarrollo (dev) porque FPC 2.6 y anteriores bucan el fichero libfbclient.so

No Firebird or employee.fdb installed?

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).

Creamos una tabla llamada EMPLOYEE que será utilizada en un tutorial posterior. 

 CREATE TABLE CUSTOMER
 (
   CUST_NO INTEGER NOT NULL,
   CUSTOMER VARCHAR(25) NOT NULL,
   CITY VARCHAR(25),
   COUNTRY VARCHAR(15),
   CONSTRAINT CT_CUSTOMER_PK PRIMARY KEY (CUST_NO)
 );

 CREATE TABLE EMPLOYEE
 (
   EMP_NO INTEGER NOT NULL,
   FIRST_NAME VARCHAR(15) NOT NULL,
   LAST_NAME VARCHAR(20) NOT NULL,
   PHONE_EXT VARCHAR(4),
   JOB_CODE VARCHAR(5) NOT NULL,
   JOB_GRADE INTEGER NOT NULL,
   JOB_COUNTRY VARCHAR(15) NOT NULL,
   SALARY NUMERIC(10,2) NOT NULL,
   CONSTRAINT CT_EMPLOYEE_PK PRIMARY KEY (EMP_NO)
 );

Algunos datos para almenos mostrar algo.... primero algunos clientes: 

 INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (1, 'Michael Design', 'San Diego', 'USA');
 INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (2, 'VC Technologies', 'Dallas', 'USA');
 INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (3, 'Klämpfl, Van Canneyt and Co.', 'Boston', 'USA');
 INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (4, 'Felipe Bank', 'Manchester', 'England');
 INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (5, 'Joost Systems, LTD.', 'Central Hong Kong', 'Hong Kong');
 INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (6, 'Van der Voort Int.', 'Ottawa', 'Canada');
 INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (7, 'Mrs. Mauvais', 'Pebble Beach', 'USA');
 INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (8, 'Asinine Vacation Rentals', 'Lihue', 'USA');
 INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (9, 'Fax', 'Turtle Island', 'Fiji');
 INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (10, 'FPC Corporation', 'Tokyo', 'Japan');
 INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (11, 'Dynamic Intelligence Corp', 'Zurich', 'Switzerland');
 INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (12, '3D-Pad Corp.', 'Paris', 'France');
 INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (13, 'Swen Export, Ltd.', 'Milan', 'Italy');
 INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (14, 'Graeme Consulting', 'Brussels', 'Belgium');
 INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (15, 'Klenin Inc.', 'Den Haag', 'Netherlands');

Then some employees: 

 INSERT INTO employee(emp_no, first_name, last_name, phone_ext, job_code, job_grade, 
  job_country, salary)
  VALUES (1,'William','Shatner','1702','CEO',1,'USA',48000)
 INSERT INTO employee(emp_no, first_name, last_name, phone_ext, job_code, job_grade, 
  job_country, salary)
  VALUES (2,'Ivan','Rzeszow','9802','Eng',2,'Russia',38000);
 INSERT INTO employee(emp_no, first_name, last_name, phone_ext, job_code, job_grade, 
  job_country, salary)
  VALUES (3,'Erin','Powell','1703','Admin',2,'USA',45368);

Por favor, crea la base de datos, la tabla e inserta los datos en el entorno de base de datos.

SQLite

Si estás utilizando SQLite, entonces puedes crear la mencionada base de datos en el directorio del proyecto ejecutanto sqlite: 

 sqlite employee.sqlite

Ahora copia y pega lo siguientes sentencias CREATE TABLE e INSERT. Para comprobar que los datos correctos se encuentran presentes entrar la siguiente consulta (query): 

 select * from customer;

Finaliza la sesión con: 

 .quit

Con todo esto debería haber creado un fichero llamado employee.sqlite dentro del directorio del proyecto.

Hay que asegurarse de tener instaladas las librerías cliente de sqlite dll/so - e.g. en windows la librería sqlite3.dll debería ubicarse en:

  • el directorio de salida de proyecto de Lazarus.
  • el directorio system (accesible a todas las aplicaciones).

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.

PostgreSQL

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.

Log in to your server and switch to the postgres account: 

 su - postgres -c psql # immediately start up psql SQL interpreter

Create a user for the database and the tables: 

 CREATE USER employee WITH PASSWORD 'hellopassword'; -- of course, adjust password to taste
 -- something like  'CREATE ROLE' should appear indicating success.
 -- to later change the password you can use something like
 -- alter user employee with password '<newpasswordhere>';
 -- We're going to let the password never expire; if you want more security, you can leave this step out:
 ALTER USER employee VALID UNTIL 'infinity'; --password never expires
 -- Now we're tightening it up a bit again:
 -- Don't allow user to create a database or create other users:
 ALTER USER employee NOCREATEDB NOCREATEUSER; --restrict object creation
 -- something like 'ALTER ROLE' should appear indicating success.
 -- Create our database:
 CREATE DATABASE employee;
 -- something like CREATE DATABASE should appear indicating success.
 -- Assign all privileges on database employee to user employee:
 GRANT ALL PRIVILEGES ON DATABASE employee TO employee; -- allow user full permissions to database
 -- something like GRANT should appear indicating success.
 -- We create the table using a serial datatype - aka autonumber/autoincrement:
 CREATE TABLE customer
 (
  cust_no serial NOT NULL,
  customer character varying(25) NOT NULL,
  city character varying(25),
  country character varying(15),
  CONSTRAINT integ_60 PRIMARY KEY (cust_no )
 );

 -- Then create the employee table:
 CREATE TABLE EMPLOYEE
 (
  EMP_NO SERIAL NOT NULL,
  FIRST_NAME VARCHAR(15) NOT NULL,
  LAST_NAME VARCHAR(20) NOT NULL,
  PHONE_EXT VARCHAR(4),
  JOB_CODE VARCHAR(5) NOT NULL,
  JOB_GRADE INTEGER NOT NULL,
  JOB_COUNTRY VARCHAR(15) NOT NULL,
  SALARY NUMERIC(10,2) NOT NULL,
  CONSTRAINT CT_EMPLOYEE_PK PRIMARY KEY (EMP_NO)
 );

 -- Now copy and paste the INSERT statements from the section above to insert the data.

 -- To test if the right data is present, enter this query: 
 SELECT * FROM customer;
 -- You should see some customer data.

 -- Exit out of psql:
 \q

Now you should be on a shell logged in as the postgres user.

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: 

 # please adjust nano (e.g. use vim,emacs,joe...) and the postgres version number depending on situation
 nano /etc/postgresql/8.4/main/pg_hba.conf

Verify if there is a line like - NOTE: replace 192.168.0.1 with your own LAN ip address range

#allow access from local network using md5 hashed passwords: host all all 192.168.0.1/24 md5

or more restrictive:

# only allow network access to the employee database by the employee user host employee employee 192.168.0.1/24 md5

If there isn't such a line, add the line at the end, save and close your editor. See PostgreSQL documentation for more details.

Reload PostgreSQL settings: 

 psql

then 

 SELECT pg_reload_conf(); --reload settings...
 -- ...and exit back to shell:
 \q

Test logging in to PostgreSQL.

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: 

 psql -h 127.0.0.1 -d employee -U employee -W #Log in via tcp/ip. Enter your db password.

Make sure the required PostgreSQL dll/so and any required other libraries is installed - e.g. on Windows, they should be either:

  • in your Lazarus + project output directory or
  • in your system directory

Compile your project (even if it is empty) once to create the output directory, and (on Windows) copy the dllsinto that directory.

Retrieved from "http://wiki.freepascal.org/index.php?title=SQLdb_Tutorial1/es&oldid=72320"