Capturando una excepción y continuando con el procesamiento

7 comentarios

Supongamos la siguiente situación: tenemos que insertar 100 filas en una tabla, pero no serán insertadas manualmente sino leídas desde otra tabla o archivo. En otras palabras, un proceso por lotes.

Pero si alguna de esas 100 filas tiene algún problema el proceso debería continuar. La fila problemática no será insertada (o será insertada con alguno de sus valores originales cambiados) pero las restantes 99 filas sí deberán ser insertadas.

Por ejemplo, la fila 27 tiene un error: no cumple con una restricción UNIQUE KEY.

Así que aquí tenemos dos opciones:

  1. No capturamos la excepción que detecta la violación a las restricciones UNIQUE KEY
  2. Capturamos la excepción que detecta la violación a las restricciones UNIQUE KEY

Si elegimos la opción 1. entonces el Servidor del Firebird cuando encuentre una violación a la restricción UNIQUE KEY detendrá el procesamiento y las filas 27 a 100 nunca serán insertadas. Las filas 1 a 26 serán insertadas o no dependiendo de como se manejan las transacciones: si después de cada INSERT hay un COMMIT entonces sí habrán sido insertadas, pero si el COMMIT se ejecutará solamente después de procesar las 100 filas, entonces no serán insertadas. Todo esto implica que si elegimos la opción 1. entonces es seguro que (algunas, muchas, o todas) las filas no serán insertadas si ocurre una violación a la restricción UNIQUE KEY.

Si elegimos la opción 2. tenemos más control sobre lo que ocurrirá cuando se detecte una violación a la restricción UNIQUE KEY. Al capturar una excepción:

  • Podemos manejarla y “engañarle” al Firebird diciéndole que tal excepción nunca ocurrió
  • Podemos manejarla y decirle al Firebird que continúe con sus acciones normales. O sea que a partir de ahí continuará como si hubiéramos elegido la opción 1. Decirle al Firebird que continúe con sus acciones normales después de que nosotros hayamos manejado la excepción se llama “relanzar la excepción”.

En general, aunque no es obligatorio, es muy útil escribir en una tabla de LOG los errores que causaron que una excepción ocurriera. Así, más adelante podremos revisar esa tabla de LOG y descubrir que fue lo que anduvo mal. Y tomar las medidas adecuadas para que no vuelva a ocurrir.

Múltiples niveles

A veces, tenemos un caso como el siguiente:

El stored procedure 1 llama al stored procedure 2 el cual llama al stored procedure 3

Servidor de Firebird —> stored procedure 1 —> stored procedure 2 —> stored procedure 3

¿Qué ocurre con las excepciones en ese caso?

Si la excepción ocurrió en el stored procedure 3 entonces éste puede manejarla o no. Si la manejó puede relanzarla o no. Si no la manejó o la relanzó entonces la excepción llegará al stored procedure 2. Así mismo, el stored procedure 2 puede manejar la excepción que recibió del stored procedure 3 o no. Si no la manejó o si la relanzó entonces la excepción llegará al stored procedure 1. También el stored procedure 1 puede manejar o no la excepción que le llegó desde el stored procedure 2. Si no la manejó o si la relanzó entonces la excepción llegará al Servidor del Firebird.

Si la excepción llega al Servidor del Firebird entonces allí mismo se detiene el procesamiento. Se termina.

Pero si la excepción nunca llegó al Servidor del Firebird entonces el procesamiento continuará normalmente, como si la excepción nunca hubiera ocurrido.

En nuestro ejemplo de las 100 filas a insertar con la fila 27 teniendo problemas, si alguno de los stored procedures capturó la excepción y no la relanzó entonces el Servidor del Firebird nunca sabrá que tal excepción ocurrió. Y por ello continuará procesando normalmente a las restantes filas.

CREATE PROCEDURE MISTOREDPROCEDURE3
AS
   DECLARE VARIABLE lcNombre VARCHAR(128);
BEGIN

   FOR SELECT
      BAN_NOMBRE
   FROM
      BANCOS
   INTO
      :lcNombre
   DO BEGIN
      INSERT INTO BANCOS_NUEVOS
                 (BAN_NOMBRE)
          VALUES (:lcNombre);
      WHEN SQLCODE -803 DO BEGIN -- Violación de una UNIQUE KEY
         IN AUTONOMOUS TRANSACTION DO BEGIN
            INSERT INTO LOG
                       (LOG_COMENT)
                VALUES ('ERROR AL INSERTAR EL BANCO: ' || :lcNombre);
         END
      END
   END

END;

Tenemos dos tablas: BANCOS y BANCOS_NUEVOS, y queremos que los nombres de todos los Bancos que se encuentran en la tabla BANCOS se inserten en la tabla BANCOS_NUEVOS. Pero como la tabla BANCOS_NUEVOS ya tiene algunas filas entonces algunos nombres podrían estar repetidos, violando por lo tanto una restricción UNIQUE KEY. Pero todos los nombres que no estén repetidos deben ser insertados en la tabla BANCOS_NUEVOS.

El stored procedure MiStoredProcedure3 se encarga de esa tarea. Como la excepción -803 (que detecta las violaciones a la restricción UNIQUE KEY) no fue relanzada entonces ni el stored procedure MiStoredProcedure2 ni el stored procedure MiStoredProcedure1 ni el Servidor del Firebird se enterarán de que tal excepción ocurrió alguna vez.

¿Queremos que el stored procedure MiStoredProcedure2 se entere de que ocurrió la excepción -803? Entonces debemos escribir la palabra EXCEPTION para relanzarla.

WHEN SQLCODE -803 DO BEGIN -- Violación de una UNIQUE KEY
   IN AUTONOMOUS TRANSACTION DO BEGIN
      INSERT INTO LOG
                 (LOG_COMENT)
          VALUES ('ERROR AL INSERTAR EL BANCO: ' || :lcNombre);
   END
   EXCEPTION;
END

Aquí, se maneja la excepción -803 (la cual indica que ocurrió una violación a la restricción UNIQUE KEY) escribiendo en un archivo de LOG el error ocurrido. Luego, se relanza la excepción para que el stored procedure MiStoredProcedure2 se entere de que ocurrió esa excepción. E igualmente puede manejarla o no.

Resumiendo:

Cuando ocurre una excepción dentro de un stored procedure o de un trigger tenemos tres posibilidades:

  1. No manejarla
  2. Manejarla y no relanzarla
  3. Manejarla y relanzarla

Si no la manejamos entonces subirá un nivel. Si en ese nivel tampoco la manejamos subirá otro nivel. Y así hasta llegar al Servidor del Firebird el cual detendrá el procesamiento.

Si la manejamos y no la relanzamos entonces el nivel superior nunca se enterará de que la excepción ocurrió.

Si la manejamos y la relanzamos entonces el nivel superior sabrá que ocurrió esa excepción en el nivel inferior.

Conclusión:

Las excepciones son una herramienta buenísima de la que disponemos y que debemos utilizar para detectar y manejar cualquier error que ocurra dentro de un stored procedure o de un trigger.

Si nosotros no manejamos a las excepciones entonces el Servidor del Firebird se encargará de ello, pero eso implica que el proceso que estábamos realizando se detendrá.

Si no queremos que el proceso se detenga entonces debemos capturar la excepción y no relanzarla, de esa manera el Servidor del Firebird no se enterará de que la excepción ocurrió y no detendrá el proceso que estábamos realizando.

Artículos relacionados:

Entendiendo las excepciones

Capturando excepciones

Capturando excepciones. Códigos de error predeterminados

Capturando excepciones del usuario

El índice del blog Firebird21

El foro del blog Firebird21

 

Obteniendo el TIMESTAMP de la última actualización a una tabla

4 comentarios

Este artículo está basado en el FAQ1 escrito por Karol Bieniaszewski:

http://itstop.pl/en-en/Porady/Firebird/FAQ1/TABLE-MODIFICATION-TIMESTAMP

A veces puede ser útil para nosotros conocer cual fue la última vez que se realizó un INSERT, UPDATE, o DELETE a una tabla. Tenemos 3 formas de obtener esa información:

  1. Guardar el TIMESTAMP en cada fila de la tabla
  2. Crear otra tabla y guardar en ella el Nombre de la Tabla y el TIMESTAMP
  3. Usar generadores

Opción 1. Guardar el TIMESTAMP en cada fila de la tabla

La opción 1. es muy fácil de implementar (simplemente le agregamos una columna TIMESTAMP a la tabla) y también puede ser muy conveniente porque sabremos no solamente cuando se actualizó por última vez la tabla sino también cada fila de ella. Lo único que necesitamos hacer es un SELECT y en la cláusula ORDER BY elegimos la columna donde guardamos el TIMESTAMP y la ordenamos descendentemente con DESC. Por ejemplo:

SELECT
   PRD_TIMEST
FROM
   PRODUCTOS
ORDER BY
   PRD_TIMEST DESC
ROWS
   1

En este caso, a la columna donde guardamos el TIMESTAMP la hemos llamado PRD_TIMEST. El gran problema con esta solución es cuando las filas de nuestra tabla se cuentan por muchos millones. Si no tenemos un índice DESC sobre la columna PRD_TIMEST entonces obtener el resultado puede demorar una eternidad. Y si tenemos un índice DESC sobre esa columna entonces cada INSERT o UPDATE modificará el contenido de esa columna y también al índice asociado, lo cual causará una demora. En otras palabras, si no tenemos un índice DESC el SELECT será muy lento, y si tenemos un índice DESC entonces el INSERT y el UPDATE serán lentos. No es una buena situación cuando la tabla tiene millones de filas.

Opción 2. Crear otra tabla y guardar en ella el Nombre de la Tabla y el TIMESTAMP

Supongamos que creamos una tabla llamada TIEMPOS con las columnas TIE_NOMBRE y TIE_TIMEST, para guardar en esas columnas el nombre de la tabla y el TIMESTAMP de su última modificación.

Aquí podemos tener problemas de lentitud si se hacen muchos INSERT y UPDATE simultáneamente a la tabla original (a la tabla PRODUCTOS, en nuestro ejemplo). ¿Por qué? porque nuestra tabla TIEMPOS tendrá problemas de concurrencia cuando dos o más transacciones quieran actualizar la misma fila. Supongamos que 20 usuarios están actualizando a la tabla PRODUCTOS, entonces cada uno de esos 20 usuarios quiere guardar el TIMESTAMP de su modificación a la tabla PRODUCTOS en una fila de la tabla TIEMPOS. Como la fila es una sola (porque el TIMESTAMP de la tabla PRODUCTOS se guarda en una sola fila de la tabla TIEMPOS) entonces 1 solo usuario podrá guardar el TIMESTAMP, los restantes 19 tendrán que esperar. Luego, el usuario 2 modifica el TIMESTAMP y los restantes 18 tienen que seguir esperando. Y así sucesivamente. No es una buena situación tampoco.

Opción 3. Usando generadores

Esta solución es más complicada de implementar que las dos anteriores pero tiene la gran ventaja de que es rapidísima en todas las situaciones.

Paso 1. Crear un generador para la tabla original

CREATE GENERATOR ULTIMA_MODIFICACION_PRODUCTOS

En este generador se guardará el TIMESTAMP que nos interesa

Paso 2. Crear un trigger

CREATE TRIGGER AIU_PRODUCTOS FOR PRODUCTOS
   ACTIVE AFTER INSERT OR UPDATE
   POSITION 1
AS
   DECLARE VARIABLE ltTimeStamp TIMESTAMP;
   DECLARE VARIABLE lnNumero    BIGINT;
BEGIN

   ltTimeStamp = (SELECT CURRENT_TIMESTAMP FROM RDB$DATABASE);

   lnNumero = CAST(EXTRACT(YEAR        FROM ltTimeStamp) ||
                   LPAD(EXTRACT(MONTH  FROM ltTimeStamp), 2, '0') ||
                   LPAD(EXTRACT(DAY    FROM ltTimeStamp), 2, '0') ||
                   LPAD(EXTRACT(HOUR   FROM ltTimeStamp), 2, '0') ||
                   LPAD(EXTRACT(MINUTE FROM ltTimeStamp), 2, '0') ||
                   LPAD(CAST(EXTRACT(SECOND FROM ltTimeStamp) * 10000 AS INTEGER), 6, '0')
              AS BIGINT);

   lnNumero = GEN_ID(ULTIMA_MODIFICACION_PRODUCTOS, -GEN_ID(ULTIMA_MODIFICACION_PRODUCTOS, 0) + lnNumero);

END;

Lo que hace este trigger es:

  1. Obtener la fecha y hora actuales
  2. Convertir la fecha y hora en un número BIGINT
  3. Actualizar el generador para guardar en él el número BIGINT obtenido en el punto 2.

Cuando finaliza el trigger tendremos en el generador ULTIMA_MODIFICACION_PRODUCTOS la fecha y la hora en que se realizó el último INSERT o el último UPDATE a la tabla PRODUCTOS.

Por supuesto que si lo deseamos podemos tener tres generadores: uno para guardar los INSERT, otro para guardar los UPDATE y otro para guardar los DELETE, eso ya dependerá de nosotros.

Paso 3. Convertir el valor BIGINT del generador en un TIMESTAMP

El generador cuyo valor establecimos con el trigger será algo así: 201403112149285420

O sea, año 2014, mes 03, día 11, hora 21, minutos 49, segundos 28, milisegundos 5420

Pero en general no nos interesará ver un número entero sino un TIMESTAMP y para ello necesitamos escribir un stored procedure seleccionable que se encargue de convertir al número BIGINT en un TIMESTAMP.

CREATE PROCEDURE OBTENER_TIMESTAMP(
   tnGenerador BIGINT)
RETURNS(
   fttTimeStamp TIMESTAMP)
AS
BEGIN

   IF (tnGenerador = 0) THEN
      fttTimeStamp = NULL;
   ELSE
      fttTimeStamp = CAST(SUBSTRING(tnGenerador FROM  1 FOR 4) || '-' ||
                          SUBSTRING(tnGenerador FROM  5 FOR 2) || '-' ||
                          SUBSTRING(tnGenerador FROM  7 FOR 2) || '-' ||
                          SUBSTRING(tnGenerador FROM  9 FOR 2) || '-' ||
                          SUBSTRING(tnGenerador FROM 11 FOR 2) || '-' ||
                          SUBSTRING(tnGenerador FROM 13 FOR 2) || '-' ||
                          SUBSTRING(tnGenerador FROM 15 FOR 4)
                     AS TIMESTAMP);

   SUSPEND;

END;

Paso 4. Obtener el valor del generador

Ahora, queremos conocer el TIMESTAMP de la última modificación a la tabla PRODUCTOS, lo conseguimos con:

SELECT
   fttTimeStamp
FROM
   OBTENER_TIMESTAMP(GEN_ID(ULTIMA_MODIFICACION_PRODUCTOS, 0))

Y obtendremos algo como:

TIMESTAMP1

Captura 1. Si haces clic en la imagen la verás más grande

que es justamente lo que estábamos necesitando.

Conclusión:

Usar el método de los generadores para obtener el TIMESTAMP de la última modificación a una tabla es lo más rápido que podemos tener. Si la tabla no tiene muchas filas o si se la actualiza raramente entonces se podría emplear cualquiera de los otros dos métodos también, pero en tablas grandes y que son actualizadas muy frecuentemente el mejor método es el de los generadores.

Artículo relacionado:

El índice del blog Firebird21