motivación: sql interactivo vs. no...

1

Tema 5 : SQL en entorno de programación C 1

SQL en entorno de programación C

SQL embebido

Variables

Errores / Whenever

Select

Cursores: Cursor y Fetch

Prepare y Execute

2009/2010


Motivación: SQL Interactivo vs. No Interactivo

Hasta la fecha hemos considerado sólo consultas SQL en las que tecleamos en “psql” o “pgaccess” (cliente) las ordenes que deseamos ejecutar. Esto se conoce como SQL Interactivo.

SQL interactivo es ideal para:

– Definir la estructura de la base de datos

– Probar consultas

– realizar prototipos

El SQL interactivo no es una buena solución para la mayoría de las aplicaciones “reales” que requieren un interfaz con un usuario sin conocimientos de SQL. Para las cuales se necesita, además de SQL, un lenguaje de programación de alto nivel.

2


SQL embebido

Los comandos SQL deben seguir una sintaxis especial que los "aísla" del resto de las ordenes del programa

– EXEC SQL SQL_statement

Un precompilador se encargará de procesar estas ordenes y trasladarlas a llamadas a una librería (que se encarga de las comunicaciones). ecpg

Finalmente el compilador nativo compila el programa. gcc


Precompilador SQL

Editor

Precompilador

Compilador

Lincador

Programa “nativo” + SQL embebido

Programa “nativo” + SQL traducido

(ecpg)

Código objeto

Librerías nativas

Librerías DDBB

Ejecutable

3


Un ejemplo

Preprocesador para C y SQL embebido (ECPG)

#include <stdio.h>

/* Esta zona se descibirá más adelante */

EXEC SQL BEGIN DECLARE SECTION;

int resultado;

EXEC SQL END DECLARE SECTION;

int main()

{

EXEC SQL CONNECT TO peliculas;

EXEC SQL SELECT (1+1) INTO :resultado;

printf("1+1=%d\n",resultado);

return 0;} /* fichero 297.pgc */

/* hacemos: ecpg 297.pgc -o 297.c */


Un ejemplo (despues de precompilarlo)

/* Processed by ecpg (3.1.1) */

/* These include files are added by the preprocessor */

#include <ecpgtype.h>

#include <ecpglib.h>

#include <ecpgerrno.h>

#include <sqlca.h>

#line 1 "297.pgc"

/* End of automatic include section */

#include <stdio.h>

/* exec sql begin declare section */

#line 5 "297.pgc"

int resultado ;

/* exec sql end declare section */

#line 6 "297.pgc"

int main()

{

{ ECPGconnect(__LINE__, 0, "peliculas" , NULL,NULL , NULL, 0); }

#line 9 "297.pgc"

{ ECPGdo(__LINE__, 0, 1, NULL, "select ( 1 + 1 ) ", ECPGt_EOIT,

ECPGt_int,&(resultado),(long)1,(long)1,sizeof(int),

ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);}

#line 10 "297.pgc"


return 0;

} /* fichero 297.c */

4


Un ejemplo (ahora compilarlo y linkarlo)

Fichero: 297.makINC= /usr/include/pgsql

LIB= /usr/share/pgsql

PROG= 297

TEMP= 297.c

$(PROG): $(PROG).c

gcc -I$(INC) -o $(PROG) $(PROG).c -L$(LIB) -lecpg -lpq

$(PROG).c: $(PROG).pgc

ecpg -o $(PROG).c $(PROG).pgc

Hacemos: make -f 297.mak -n


Compartir Variables

Es posible compartir variables entre el código SQL y el nativo.

La definición se realiza usando la sintaxis:

EXEC SQL begin declare section;varchar userid[ 10], password[ 10], cname[ 15];int cno;EXEC SQL end declare section;

Declaration section: variables compartidas por SQL y lenguaje nativo

":" se antepone a las variables cuando se usan en SQL

5


Compartir variables: un ejemplo

Declaration section: variables compartidas por SQL y lenguaje nativo

":" se antepone a las variables cuando se usan en SQL


unsigned long num_enrolled;

char crs_code;

char SQLSTATE [6];


……….

EXEC SQL SELECT C.NumEnrolled

INTO :num_enrolled

FROM Course AS C

WHERE C.CrsCode = :crs_code;

Variables

compartidas

“:” se usa para las

variables en SQL


NULL

SQL trata el “valor” NULL de forma muy especial que no es directamente exportable a otros lenguajes de programación.

Hay unas variables llamadas “variables indicadoras” quecontienen un código que “matiza” los valores devueltos por labase de datos:– 0 el valor de la variable “nativa” es correcto.

– -1 el valor de la variable “nativa” debe tratarse como si fueraNULL. Su valor es irrelevante.

– >0 el valor de la variable “nativa” ha sido procesado(probablemente un casting que ha producido una truncación o redondeo)

6


Ejemplo!!!

#include <stdio.h>

int main()

{


char resultado[11];

int resultadoInd;


//las variables indicadoras funcionan en las dos direcciones


resultadoInd = 0;

strcpy(resultado,"rojo");

EXEC SQL INSERT INTO pepinillo (id,color) VALUES (1,:resultado:resultadoInd);

return 0;

} /* 297b.pgc */


Ejemplo!!!

#include <stdio.h>

int main()

{


char resultado[11];

int resultadoInd;


//las variables indicadoras funcionan en las dos direcciones


resultadoInd = -1;

strcpy(resultado,"rojo");

EXEC SQL INSERT INTO pepinillo (id,color) VALUES (2,:resultado:resultadoInd);

return 0;

} /* 297d.pgc */

pelis=> select * from pepinillo ;

id | color

----+-------

1 | rojo

2 |

(2 rows)

7


Conexión

Hasta la fecha hemos usado:EXEC SQL CONNECT TO peliculas;

para conectarnos

Obviamente faltan campos para:

– usuario

– password

– servidor (dirección IP)

– puerto


Ejemplo

#include <stdio.h>EXEC SQL INCLUDE sqlca;

EXEC SQL BEGIN DECLARE SECTION;int resultado;

EXEC SQL END DECLARE SECTION;int main(){EXEC SQL CONNECT TO peliculas@localhost:5432 USER

usuario/contraseña;if (sqlca.sqlcode)

{printf("%s\n", sqlca.sqlerrm.sqlerrmc);

exit(1);

}


printf("1+1=%d\n",resultado);return 0;}

8


Errores

Se puede acceder a la base de datos para conocer su estado ¿Cómo saber si ha habido algún error durante la ejecución?

EXEC SQL include sqlca;

El elemento más interesante de la estructura sqlca es sqlcode. El– 0 consulta correcta.

– >0 la consulta se ha realizado satisfactoriamente pero ha ocurrido una excepción (por ejemplo no hay más tuplas disponibles)

– < 0 ha ocurrido un error


Errores

#include <stdio.h>

EXEC SQL INCLUDE sqlca;


int resultado;


int main()

{

EXEC SQL CONNECT TO basequenoexiste;

if (sqlca.sqlcode)

{printf("Error: No puedo conectar a la base (%d)\n",

sqlca.sqlcode);

exit(1);

}



return 0;

} /* 300.pgc */

maquina:~> ./300

Error: No puedo conectar a la base (-402)

9


Códigos de Error http://www.postgresql.org/docs/7.4/static/ecpg-errors.html

struct

{

char sqlcaid[8];

long sqlabc;

long sqlcode;

struct

{int sqlerrml;

char sqlerrmc[70];} sqlerrm;

char sqlerrp[8];

long sqlerrd[6];

char sqlwarn[8];

char sqlstate[5];

} sqlca;

______________________________________________


WHENEVER

Cada comando de SQL embebido puede (potencialmente)

generar un error.

Para aliviar el trabajo del programador existe la directiva

WHENEVER que instruye al precompilador para que genere el

código que procesará los errores

La sintaxis de la directiva WHENEVER es:

EXEC SQL WHENEVER <condición> <acción>

10


WHENEVER (cont)

La condición puede ser:

– SQLERROR - genera código que se encargue de los errores (SQLCODE < 0).

– SQLWARNING – genera codigo que se encarge de las advertencias (warnings)(SQLCODE > 0)

– NOT FOUND - genera código que se encargue del caso especial en que todaslas tuplas obtenidas como resultado de la consulta ya se han transferido.


WHENEVER (CONT)

La acción puede ser:

CONTINUE – ignora el error y continua la ejecución

función – transfiere el error a la funcion que se encarga de procesar loserror.

DO BREAK – salte del bucle

DO CONTINUE – continua con la interación siguiente (se supone dentrode un bucle)

GOTO label - transfiere el control a otra parte del programa.

STOP – Aborta transacción.

SQLPRINT – Muestar mensaje con el error ocurrido (predeterminado).

11


WHENEVER: Ejemplo!!!

#include <stdio.h>

int main()

{


int resultadoInd;



EXEC SQL WHENEVER sqlerror sqlprint;

EXEC SQL INSERT INTO tablaquenoexiste (id,color) VALUES (1,2);

printf(“El programa no ha abortado tras hacer sqlprint”);

return 0;

} /* 297c.pgc */

maquina:~> ./297c

sql error 'relation "tablaquenoexiste" does not exist' in line 13.

El programa no ha abortado tras hacer sqlprint


SELECT

SELECT es un comando de SQL potencialmente complicado de usar.

Problema: los lenguajes de programación tradicionales están concebidos para procesar variables aisladas y no grandes tablas de dimensión virtualmente infinita

SQL, por el contrario, puede procesar una gran cantidad de filas con un solo comando

Aquellos SELECTs que devuelvan 0 o una tupla son fácilmente procesables pero... ¿qué hacer cuando „n‟ tuplas son devueltas?

12


SELECT – Devuelve una sola tupla

EXEC SQL SELECT fName, lName, address

INTO :firstName, :lastName, :address:addressInd,

FROM PrivateOwner

WHERE ownerNo = „CO21';

Si se devuelve más de una tupla solo la primera de ellas es retenida


Cursores

Si una consulta puede devolver un número arbitrario de tuplas, esnecesario usar cursores.

Los cursores permiten al lenguaje “nativo” acceder a una relacióntupla a tupla.

Los cursores se comportan como punteros a una tupla del resultadoy pueden ser movidos de una tupla a la siguiente (se puede saltarhacia atrás y delante).

Los cursores se deben declarar y “abrir” antes de que puedan serusados.

Una vez abiertos el comando fetch permite avanzar hasta lasiguiente tupla

Cuando se deje de utilizar: cerrar cursor.

13


CURSOR - Ejemplo

título puntuacion votos

High and the Mighty, The 9.5 256

Flåklypa Grand Prix 9.8 255

Consideremos la consulta:SELECT titulo, puntuacion ,votosFROM peliculaWHERE puntuacion > 9.4;


Cursor: Declaración

Los cursores se declara usando las palabrasreservadas:– DECLARE <nombre-cursor> CURSOR FOR <consulta>

– el nombre del cursor

– y la consulta a realizar

EXEC SQL DECLARE

miprimercursor CURSOR FOR

SELECT titulo, puntuación, votos

FROM pelicula

WHERE puntuacion > 9.4;

14


Cursor - OPEN

La orden OPEN ejecuta la consulta y coloca un puntero apuntandohacia la primera tupla de la relación resultante

EXEC SQL OPEN miprimercursor;

Pointer




Cursores - FETCH

FETCH devuelve la siguiente tupla en la relación resultante denuestra consulta y la coloca en una variable accesible por ellenguaje nativo

EXEC SQL FETCH miprimercursor

INTO :titulo, :puntuación, :votos

FETCH se coloca normalmente dentro de un bucle que itera hastaque no se devuelven más filas. Esto es, SQLCODE es ????.

15


Cursores: FETCH

EXEC SQL WHENEVER NOT FOUND DO BREAK;

while(1) {

EXEC SQL FETCH miprimercursor

INTO :titulo, :puntuación, :votos;

//MAS CODIGO AQUI

}

Fetch 1

Fetch 2




CURSOR - CLOSE

La orden CLOSE “cierra” el cursor activo.

EXEC SQL CLOSE miprimercursor;

16


Un ejemplo con todo junto

#include <stdio.h>



char titulo[256]; float fPuntuacion; int votos;

char miquery[256];


EXEC SQL WHENEVER SQLERROR sqlprint;

int main()

{

// conectarse


if (sqlca.sqlcode)

{printf("Error: No puedo conectar a la base (%d)\n",

sqlca.sqlcode);

exit(1);

}


// declara cursor

EXEC SQL DECLARE micursor CURSOR FOR

SELECT titulo, puntuacion, votos

FROM pelicula

WHERE puntuacion > 9.4;

EXEC SQL OPEN micursor;

EXEC SQL WHENEVER NOT FOUND DO BREAK;

while (1)

{

EXEC SQL FETCH IN micursor INTO :titulo, :fPuntuacion, :votos;

printf("%s %f %d\n",titulo, fPuntuacion, votos);

}

EXEC SQL CLOSE micursor;

EXEC SQL DISCONNECT;

return 0;

} /* 312.pgc */

maquina:~> ./312



17


Modificar datos usando cursores

Los cursores pueden ser declarados como “READ ONLY” o “UPDATE”

Aquellos declarados como “UPDATE” pueden ser usados para modificar o

borrar tuplas

“READ ONLY” es la opción por defecto para declarar un cursor que será usado

para modificar la relación debe usarse la sintaxis:

EXEC SQL DECLARE

misegundocursor CURSOR FOR

SELECT titulo, puntuacion, votos

FROM peliculas

WHERE puntuacion < 5

FOR UPDATE OF titulo,puntuacion,votos;


Ejemplo: modificar relaciones usando cursores

#include <stdio.h>

#include <stdlib.h>

#include <string.h>


#define CHECKERR(CE_STR) if (sqlca.sqlcode != 0) {printf("%s failed. Reason %ld\n", CE_STR, sqlca.sqlcode); exit(1); }

EXEC SQL WHENEVER SQLERROR sqlprint;

int main() {


char titulo[256]; float fPuntuacion; int votos;

char miquery[256];


EXEC SQL CONNECT TO peliculas; CHECKERR ("CONNECT TO peliculas");

18


Ejemplo: modificar relaciones usando cursores-II

/* Maniaco de Star Trek */

EXEC SQL DECLARE micursor CURSOR FOR SELECT puntuacion, votos, id

FROM pelicula

WHERE titulo LIKE 'Star Trek%'

FOR UPDATE OF puntuacion,votos;

EXEC SQL OPEN micursor;

CHECKERR ("OPEN CURSOR");

do {

EXEC SQL FETCH micursor INTO :fPuntuacion, :votos, :id_peli

printf("%f %d %d\n",fPuntuacion,votos, id_peli);

if (SQLCODE != 0) break;

if(fPuntuacion < 99999){

EXEC SQL UPDATE pelicula SET puntuacion = 999999,

votos = 99999 WHERE id_pelicula=:id_peli;

CHECKERR ("UPDATE pelicula");

}} while ( 1 );

EXEC SQL CLOSE micursor;

EXEC SQL commit;

} /* 326.pgc */


SQL Dinámico vs Estático

Hasta el momento hemos visto SQL (embebido) estático

SQL estático es, posiblemente, la formula más popular de programar con SQL pero sólo es usable si se conoce las consultas que una aplicación va a hacer durante la fase de diseño de la aplicación.

Su principal limitación es que no permite decidir en tiempo real a que tablas o atributos (u otros objetos) nos queremos referir.

Esto es: con SQL estático puedes decidir que valor va a tener un atributo en tiempo real pero no que atributo vas a cambiar (o a que base de datos vas a acceder)

19


EXECUTE IMMEDIATELY

La forma más sencilla de ejecutar una sentencia arbitraria en SQLconsiste en usar la orden EXECUTE IMMEDIATE :

EXEC SQL EXECUTE IMMEDIATE

[cadena_con_la_consulta]

Por ejemplo:


const char *stmt = "CREATE TABLE test1(...);";


EXEC SQL EXECUTE IMMEDIATE :stmt;

No se pueden ejecutar sentencias que devuelvan datos de estamanera. (SELECT no esta permitido pero INSERT o UPDATE sonposibles)


Ejemplo

#include <stdio.h>

int main()

{


char resultado[11];

int resultadoInd;



//Dos Declare secction estilo c++


const char *stmt = "CREATE TABLE test2 (i int, f float);";


EXEC SQL EXECUTE IMMEDIATE :stmt;

EXEC SQL COMMIT;

return 0;

}//immediate /* 500.pgc */

20


PREPARE y EXECUTE

La base de datos tiene que analizar, validar y optimizar cadasentencia SQL de tipo “EXECUTE IMMEDIATE”.

Si se planea utilizar repetidamente la misma sentencia (o variantesparametrizadas de la misma) entonces combiene “preparar” lasconsultas previamente de forma que la validación, optimización yanalisis se realice una única vez por “grupo” de consultas.

SQL dinámico cuenta con las intrucciones: PREPARE y EXECUTEpara realizar esta tarea


Immediate vs Prepare/Execute

21


int main() {/*No se comprueban errores para ahorrar espacio*/


char dbname[64] = “peliculas”;

char sql_string[1000];

int i; float f;


/* Conectarse a la base de datos*/

EXEC SQL CONNECT TO :dbname;

/*EXEC SQL SET AUTOCOMMIT TO ON; */

/* Preparara la orden INSERT */

strcpy(sql_string, "INSERT INTO test1 VALUES(?, ?)");

EXEC SQL PREPARE prep_ins FROM :sql_string;

/* Insertar varias tuplas (en una sola transaccion*/

EXEC SQL BEGIN;

for(i = 0; i < 3; i++) {

switch(i) {

case 0: f=0.; break;



}

EXEC SQL EXECUTE prep_ins USING :i, :f;

}

EXEC SQL COMMIT;

}

/* 400.pgc */

motivación: sql interactivo vs. no...

Documents