DESARROLLO DE APLICACIONES EN BASES DE DATOS

SQL embebido en C

Objetivos

Presentar los fundamentos de la manipulación de bases de datos relacionales desde programas.
Presentar las instrucciones básicas del lenguaje SQL embebido.
Presentar la estructura de un programa en C con SQL embebido.
Realizar programas sencillos de manipulación sobre la base de datos Docencia cuyo esquema relacional se puede encontrar en el anexo.

Contenidos

1. Manipulación de bases de datos relacionales desde programas

Los lenguajes definidos para el modelo relacional de datos son lenguajes orientados a la manipulación de conjuntos de tuplas. En ellos los operadores seleccionan tuplas con la información requerida en una consulta, o bien actualizan grupos de tuplas de la forma especificada en un requerimiento de actualización.

Esta característica, que representa una mejora respecto a los lenguajes de modelos de datos anteriores (red y jerárquico), introduce sin embargo un problema a la hora de integrar estos lenguajes con los lenguajes de programación clásicos, ya que estos últimos no contemplan la manipulación de estructuras de datos complejas (conjuntos de registros, listas de registros, etc.).

El acceso a una base de datos relacional desde un programa se plantea necesario cuando se piensa en el desarrollo de aplicaciones complejas que necesitan: manipulación individualizada de las tuplas, estructuras de programación tradicionales (selección y repetición), interacción con el usuario y gestión de errores.

Por ello, la propuesta estándar del lenguaje SQL/92 proporciona distintas interfaces del lenguaje, es decir una sintaxis para poder usar el SQL en distintos contextos. Estas interfaces son: el SQL directo (o interactivo) y el SQL embebido.

El SQL directo (o interactivo) está pensado para usarse desde una estación de trabajo. En él, las instrucciones se ejecutan directamente desde el terminal y el resultado de las consultas se visualiza en el monitor de la estación.

El SQL embebido está pensado para ser utilizado intercalando sus instrucciones en el código de un programa escrito en un lenguaje de programación al que se denomina lenguaje huésped (FORTRAN, COBOL, C, etc.). Estas instrucciones se ejecutan cuando se ejecuta el programa de acuerdo a su lógica interna. En este contexto, el intercambio de información con el SGBD se realiza a través de variables del lenguaje, a las que se denomina variables huéspedes; por ejemplo, el resultado de las consultas es asignado a variables del programa declaradas con ese fin.

Como ya se ha comentado anteriormente, la dificultad principal que se plantea en el acceso a una base de datos relacional desde un programa reside en la incompatibilidad entre los tipos de estructuras del modelo de datos y del lenguaje de programación. Para superar esta dificultad existen dos opciones: restringir las consultas a aquellas que devuelvan una única tupla o bien introducir algún mecanismo que permita seleccionar y manipular las tuplas de una relación individualmente, este último mecanismo viene representado por los cursores. En esencia un cursor es un puntero destinado a apuntar a las tuplas de una relación. El cursor puede moverse a través de las tuplas de la relación, pudiéndose en cualquier momento seleccionar o actualizar la tupla apuntada.

La estructura típica de un programa de manipulación de base de datos es la siguiente:

Programa …
    Declaración de variables
        …
        Declaración de variables huéspedes para acceso a la base de datos
            …
        Fin declaración de variables huéspedes para acceso a la base de datos
        …
    Fin declaración de variables
    Comienzo del código del programa
            …
            instrucciones propias del lenguaje
                …
            Conexión a la base de datos
                …
            instrucciones de SQL
                ...
            Desconexión de la base de datos
                …
            instrucciones propias del lenguaje
                …
Fin del código de programa

2. Instrucciones básicas del SQL embebido

En general, cualquier instrucción del SQL interactivo se puede utilizar en el SQL embebido con pequeñas variaciones sintácticas que se refieren principalmente al intercambio de información con el SGBD a través de variables del programa. Existen, además, instrucciones específicas del SQL embebido que permiten seleccionar y manipular las tuplas de una relación individualmente: son todas las relacionadas con el manejo de cursores.

Algunas consideraciones generales de sintaxis son:

Todas las instrucciones del SQL embebido van precedidas de las palabras reservadas EXEC SQL, de forma que son fácilmente identificables por el precompilador, y finalizan con un símbolo especial. En el lenguaje C este símbolo es el punto y coma (;).
Las variables del lenguaje que son utilizadas en instrucciones SQL (variables huéspedes) deben ser declaradas en una sección especial encabezada y terminada de la siguiente forma:

EXEC SQL BEGIN DECLARE SECTION;
...
EXEC SQL END DECLARE SECTION;

dos puntos (: )

Todo programa con SQL embebido debe incluir la declaración de una variable SQLCODE o una variable SQLSTATE o ambas. Después de la ejecución de cada instrucción SQL, el SGBD devuelve al programa en estas variables información sobre la ejecución de la instrucción.

En un programa, cualquier instrucción SQL debería estar seguida por código que controlase los valores de las variables SQLCODE y SQLSTATE relativos a la ejecución de la instrucción; para simplificar esta tarea el lenguaje proporciona una sentencia para el control general de errores, ésta es la sentencia WHENEVER:

EXEC SQL WHENEVER condición acción
condición ::= {SQLERROR | NOT FOUND}
acción ::={CONTINUE | GO TO etiqueta}

NOT FOUND se evalúa a cierto si no se han encontrado datos que cumplan las condiciones especificadas en la instrucción (SQLCODE =+100).

SQLERROR se evalúa a cierto si ha ocurrido algún error en la ejecución de la instrucción (SQLCODE < 0).

La sentencia WHENEVER no es una instrucción ejecutable, es más bien una directriz al procesador del lenguaje SQL:

WHENEVER condición GO TO etiqueta significa que el procesador de SQL incluirá detrás de cualquier sentencia SQL del programa la sentencia IFcondición THEN GO TO etiqueta
WHENEVER condición CONTINUE significa que el procesador de SQL no tomará ninguna acción siendo responsabilidad del programador controlar el flujo del programa.

2.1. Instrucciones de manipulación sin cursores

La operación de consulta se realiza con la siguiente variante de la sentencia SELECT del lenguaje SQL interactivo:

SELECT [ALL | DISTINCT] lista_elemento_ selección
INTO lista_variable
FROM lista_relación
[WHERE condición_búsqueda]
[GROUP BY lista_atributo]
[HAVING condición_búsqueda] En este contexto, la sentencia SELECT debe devolver una única tupla, en caso contrario se producirá un error.

Como se puede observar la única variante de la sentencia consiste en la cláusula INTO que especifica la lista de variables del programa que serán utilizadas para recibir los datos seleccionados.

Los elementos de las lista de selección y de la lista de variables se corresponden uno a uno en el orden en que aparecen, por ello ambas listas deben tener el mismo número de elementos y ser de tipos de datos compatibles (esto depende de la implementación del SQL que se esté utilizando).

Las sentencias de actualización: UPDATE, INSERT y DELETE, tienen la misma sintaxis que en el SQL interactivo con la única diferencia de que pueden incluir variables huéspedes.

A continuación se presentan algunos ejemplos de SQL embebido en C relativos a la base de datos Docencia del anexo.

Declaración de variables huéspedes:

EXEC SQL BEGIN DECLARE SECTION; char cd[6];
int aux;
char cpx[4];
char cpy[4]; EXEC SQL END DECLARE SECTION;     Consulta: "Obtener el código del departamento al que pertenece el profesor de código ‘JCR’ ". EXEC SQL SELECT cod_dep INTO :cd FROM Profesor
WHERE cod_pro = ’JCR’;     Como se puede ver, la variable cd recibirá el valor (cadena de 3 caracteres) de la columna de la tabla Profesor que cumpla la condición (como máximo una fila).
    De forma análoga, en la siguiente
    Consulta: "Número de profesores de un departamento", se puede ver el uso de la variable huésped cd para el paso de información al SGBD. EXEC SQL SELECT count(*) INTO :aux FROM Profesor
WHERE cod_dep= :cd;     Al ejecutar esta instrucción el valor de la variable huésped cd se transfiere al SGBD para que ejecute la consulta correspondiente y el resultado de la misma, un entero, es almacenado en la variable huésped aux.

Actualización: "Transferir la docencia de un profesor cuyo código se lee en la variable cpx a un profesor cuyo código se lee en la variable cpy"

EXEC SQL UPDATE Docencia SET cod_pro = :cdy
WHERE cod_pro = :cdx 2.2. Instrucciones de manipulación con cursores

Como se ha comentado anteriormente, un cursor es un objeto SQL que permite la manipulación de las tuplas de una relación de forma individualizada; intuitivamente se puede ver como un puntero a las tuplas de una relación.

Un cursor siempre va asociado a una relación que se especifica al ser declarado. Ya que el objetivo de su uso es poder navegar a través de las tuplas que constituyen dicha relación para posteriormente manipularlas, estas tuplas deben estar ordenadas, siendo este orden implícitamente definido por el sistema o bien definido por el programador en la declaración del cursor.

Desde que el cursor es activado (apertura del cursor) siempre tiene una posición (posición actual) dentro del conjunto ordenado de tuplas. Esta posición puede ser: delante de una tupla, en una tupla, y detrás de una tupla. Esta posición actual va a ser relevante para las instrucciones de manipulación de la base de datos basadas en el uso de cursores. A continuación se presentan cada una de ellas.

Definición del cursor

DECLARE cursor CURSOR
FOR cursor_especificación
cursor_especificación ::= expresión_de_relación
                        [ORDER BY lista_elemento_orden]
                        [FOR {READ ONLY | UPDATE [OF lista_nom_atributo]]
elemento_orden ::= {nom_atributo | entero_positivo} [ASC | DESC]     La expresión_de_relación define la relación asociada al cursor, que será una sentencia SELECT; su sintaxis es la misma que la del SQL interactivo con la diferencia de que puede incluir variables huéspedes.

La cláusula ORDER BY permite especificar un orden para las tuplas de la relación definida por expresión_de_relación. Los nombres de atributo o los enteros positivos (ordinal de la posición de un atributo) se refieren a atributos de la relación definida por expresión_de_relación; los enteros positivos se utilizan cuando el correspondiente atributo de la relación no lleva un nombre, es decir es un dato calculado. Si no se incluye la cláusula ORDER BY, el sistema establece un orden por defecto.

La cláusula FOR READ ONLY (resp., UPDATE [OF lista_nom_atributo] ) indica que la relación asociada al cursor no puede (resp., puede) ser actualizada por medio de las instrucciones UPDATE y DELETE.

Apertura del cursor

OPEN cursor La apertura de un cursor en un programa significa la evaluación de la expresión_de_relación asociada. Cuando un cursor se abre, su posición actual pasa a ser delante de la primera tupla.

Manejo del cursor y selección de tuplas

FETCH [[selector_de_tupla] FROM] cursor INTO lista_variables La instrucción FETCH permite mover el cursor dentro del conjunto ordenado de tuplas y seleccionar (leer) la tupla en la que queda colocado; su posición actual después de ejecutarse la instrucción es en la tupla seleccionada .

El selector_de_tupla puede ser: next, prior, first, last, absolute n, relative n. La semántica de cada una de estas opciones es obvia, si bien para los casos next, prior y relative el movimiento es relativo a la posición actual del cursor antes de ejecutarse la instrucción fetch. En las opciones absolute n y relative n, un número positivo (respectivamente, negativo) indica movimiento hacia delante (respectivamente, detrás) según la secuencia de ordenación de las tuplas.

La lista de variables debe contener una variable huésped por cada atributo de la relación asociada al cursor, estas variables recibirán los valores de la tupla seleccionada.

Si la instrucción FETCH no selecciona ninguna tupla, es decir no existe la tupla siguiente (next) o la tupla anterior (prior) a la tupla en la posición actual del cursor, o bien no existe la enésima tupla a partir de la posición actual (absolute y relative) hacia delante (n positivo) o hacia detrás (n negativo), entonces la posición actual del cursor después de ejecutarse la instrucción pasa a ser detrás de la última tupla o delante de la primera tupla.

Borrado de tuplas con cursor

DELETE FROM relación
WHERE CURRENT OF cursor Esta instrucción permite borrar la tupla en la posición actual del cursor, relación es la relación que va a ser actualizada (el cursor cursor debe estar asociado a ella). Después de ejecutarse la instrucción, la posición actual del cursor pasa a ser delante de la tupla siguiente a la tupla que ha sido borrada o detrás de la última tupla si es que la tupla borrada era la última de la relación.

Actualización de tuplas con cursor

UPDATE relación
SET lista_asignación
WHERE CURRENT OF cursor Esta instrucción permite actualizar la tupla en la posición actual del cursor. En lista_asignación se indican las actualizaciones que se van a aplicar a los atributos de la tupla apuntada. La sintaxis de esta cláusula es similar a la del SQL interactivo con la excepción de que se pueden incluir referencias a variables huéspedes.

La ejecución de esta instrucción no modifica la posición actual del cursor. La única restricción a su uso es que no pueden actualizarse los atributos que aparecen en la cláusula ORDER BY de la definición del cursor; es decir, los atributos por los cuales se ordena el conjunto de tuplas.

Ejemplo de operaciones de manipulación con cursor.

El siguiente fragmento de programa obtiene un informe de todos los profesores de la base de datos; de cada uno de ellos se presenta la siguiente información: código, nombre, departamento y el número total de créditos que imparte; si el profesor no tiene docencia asignada se debe indicar con el mensaje "Sin Docencia".

Declaración de variables huéspedes y cursores:

EXEC SQL BEGIN DECLARE SECTION;
STRUCT PRO_ { char cod_pro[4];
char nombre[41];
char cod_dep[6]; } pro; int aux1;
int aux2;
EXEC SQL END DECLARE SECTION;
EXEC SQL DECLARE c_pro CURSOR FOR
    SELECT cod_pro, nombre, cod_dep
    FROM Profesor ; Fragmento de código de programa: EXEC SQL OPEN c_pro;
EXEC SQL FETCH c_pro INTO :pro;
mientras sqlca.sqlcode = 0
    EXEC SQL SELECT count(*), sum(D.gteo * A.teoria )+ sum(D.gpra * A.prac)
            INTO :aux1, :aux2
            FROM Docencia D, Asignatura A
            WHERE D.cod_pro = :pro.cod_pro
                AND
                    D.cod_asg = A.cod_asg
    si aux1 = 0 entonces escribir (pro.cod_pro, pro.nombre, "Sin Docencia")
sinoescribir (pro.cod_pro, pro.nombre, aux2);     escribir("\n");
    EXEC SQL FETCH c_pro INTO :pro; } 3. Estructura de un programa en C con SQL embebido

En este apartado se va a presentar de forma breve la forma de construir un programa en C con SQL embebido. Estos programas tienen las siguientes particularidades:

Definición de un área de trabajo del programa mediante la declaración de un conjunto de variables huéspedes.
Utilización de instrucciones SQL mediante llamadas EXEC SQL.
Utilización del área de comunicación SQL para controlar la ejecución de las operaciones sobre la base de datos.

3.1. Definición del área de trabajo

La forma de definir el área de trabajo es la siguiente:

EXEC SQL BEGIN DECLARE SECTION;
declaración de tipos y variables en C
EXEC SQL END DECLARE SECTION; Las variables declaradas dentro de esta sección son variables huéspedes y sirven para recibir información de la base de datos o para enviar información a la base de datos.

3.2. Utilización de las instrucciones SQL mediante llamadas EXEC SQL

Las variables huéspedes se usan de la forma habitual en las instrucciones de C y en las instrucciones SQL deben ir precedidas del símbolo ‘:’.

De las instrucciones SQL que se pueden utilizar, es interesante destacar dos de ellas: la conexión a la base de datos y la desconexión. Estas instrucciones tienen la siguiente sintaxis:

EXEC SQL CONNECT 'base_datos';
EXEC SQL DISCONNECT; Es necesario destacar que antes de efectuar cualquier operación sobre la base de datos es necesario conectarse a la misma usando la primera instrucción. Asimismo, para un correcto funcionamiento de las sesiones de base de datos en el servidor, es obligatorio desconectarse de la base de datos antes de que el programa finalice.

3.3. Uso del área de comunicación SQL para el control de las operaciones sobre la base de datos

Un aspecto fundamental para el correcto funcionamiento de los programas es el control de las operaciones que se van realizando sobre la base de datos. Dependiendo de si la instrucción SQL ejecutada ha producido un error o no, o ha realizado la operación esperada o no, el programa ha de tomar las medidas correspondientes (informar al usuario, pedir nuevos datos, terminar la ejecución, etc.).

Para realizar este control se utiliza el área de comunicación del SQL (en inglés SQLCA). La SQLCA consiste en un conjunto de variables que almacenan información sobre el estado (y los errores, si se producen) de la última instrucción que el programa ejecutó sobre la base de datos.

Para poder hacer uso de estas variables hay que declararla de la siguiente manera:

EXEC SQL INCLUDE SQLCA; La inclusión de esta declaración implica la aparición de una nueva variable estructurada denominada sqlca.La declaración de esta variable es la siguiente: typedef struct { char sqlcaid[8];
long sqlcabc;
long sqlcode;
struct { short sqlerrml;
char sqlerrmc[70]; } sqlerrm; char sqlerrp[8];
long sqlerrd[6];
struct { char sqlwarn0;
char sqlwarn1;
char sqlwarn2;
char sqlwarn3;
char sqlwarn4;
char sqlwarn5;
char sqlwarn6;
char sqlwarn7; } sqlwarn; char sqlext[8]; } IISQLCA; extern IISQLCA sqlca; El uso de cada uno de los campos de la variable sqlca es el siguiente:

sqlcaid: de tipo cadena de 8 caracteres e inicializada a "SQLCA". El valor no cambia.
sqlcabc: de tipo entero de 4 bytes e inicializada a la longitud en bytes de SQLCA, esto es 136. Este valor permanece inalterado.
sqlcode: de tipo entero de 4 bytes que almacena el código de retorno de la instrucción SQL ejecutada en último lugar. Sus valores se puede agrupar de la siguiente forma:

= 0, la instrucción se ejecuto con éxito, aunque es posible que existan mensajes de atención (ver sqlwarn0)
< 0, sucedió algún error en la ejecución de la instrucción. El valor negativo contenido coincide en valor absoluto con el número de error (positivo) devuelto por la variable errorno.
> 0, la instrucción se ejecuto con éxito pero alguna condición excepcional se ha dado. Por ejemplo, el valor +100 indica que la instrucción no ha afectado a ninguna tupla de ninguna relación de la base de datos.

sqlerrm: de tipo cadena de longitud variable con un contador de 2 bytes y un buffer de 70 caracteres. Esta variable se usa para los mensajes de error.
sqlerrp: actualmente no se usa.
sqlerrd: un vector de 6 enteros de 4 bytes. Sólo se usan sqlerrd(1) para almacenar los números de error devueltos por el servidor y sqlerrd(3) para guardar el número de filas procesadas por la instrucción.
sqlwarn0-sqlwarn7: se actualizan a "W" para indicar que ha sucedido algún hecho especial que no puede ser considerado un error.

4. Programa ejemplo

El siguiente programa obtiene la información de los profesores que pertenecen a un departamento que el usuario especifica dando su código.

# include <stdio.h>
# include <string.h>
exec sql include sqlca;
exec sql begin declare section; struct pro_ { char cod_pro[4];
char nombre[41];
int telefono;
char cod_dep[6]; } pro;
char cd[6]; exec sql end declare section;
main(){ exec sql declare c_pro cursor for select cod_pro, nombre, telefono, cod_dep
from Profesor
where cod_dep = :cd; printf("Dame el código del departamento ");
scanf("%5s", cd);
printf("%-4s %-40s %-10s", "Código", "Nombre", "Teléfono");
printf("\n");
printf("-----------------------------------------------------\n");
exec sql connect ‘apb_emb’;
exec sql open c_pro;
exec sql fetch c_pro into :pro;
while ( sqlca.sqlcode == 0 )
{ printf("%-4s %-40.40s %-5d", pro.cod_pro, pro.nombre, pro.telefono);
printf("\n");
exec sql fetch c_pro into :pro; }
exec sql close c_pro;
exec sql disconnect; } 6. Ejercicios propuestos

Escribir los programas en C con SQL embebido que correspondan a las siguientes operaciones:

Generación de un listado de la información completa de los departamentos.
Petición del código de un departamento y obtención de toda su información. Es necesario que se controle la existencia del departamento. Además se ha de permitir que se pueda realizar la consulta de varios departamentos sin necesidad de ejecutar varias veces el programa.
Petición del código de un departamento y presentación del departamento así como el número de profesores que pertenecen a él. También hay que presentar un listado con la información de los profesores que pertenecen a él. Hay que controlar la existencia del departamento.
Presentación en pantalla de la información que corresponde al plan de ordenación docente de los departamentos. Se ha de extraer la información de cada departamento junto con los profesores que pertenecen a él indicado para cada uno de ellos: si imparte docencia, que asignaturas imparte y cuantos grupos de teoría y prácticas tiene a su carga para cada asignatura; si no imparte docencia, este mismo hecho.

Anexo

Esquema Relacional de la base de datos Docencia

Departamento (cod_dep, nombre, director, telefono)
        Clave Primaria: {cod_dep}
Asignatura (cod_asg, nombre, semestre, teoria, prac, cod_dep )
        Clave Primaria: {cod_asg}
        Clave Foranea: {cod_dep} ® Departamento
Profesor (cod_pro, nombre, telefono, cod_dep )
        Clave Primaria: {cod_pro}
        Clave Foranea: {cod_dep} ® Departamento
Docencia (cod_asg, cod_pro, gteo, gpra)
        Clave Primaria: {cod_asg, cod_pro}
        Clave Foranea: {cod_asg} ® Asignatura
        Clave Foranea: {cod_pro} ® Profesor

Desarrollo de Aplicaciones en Bases de Datos. Dpto. de Lenguajes y Sistemas Informáticos. Universidad Politécnica de Valencia.