Usando EXECUTE BLOCK

4 comentarios

Firebird dispone de una característica buenísima, que puede ayudarnos mucho cuando no disponemos del tiempo suficiente para trabajar directamente con la Base de Datos: EXECUTE BLOCK

¿Para qué sirve EXECUTE BLOCK?

Para ejecutar un bloque de código tal como el que puede escribirse en un stored procedure pero dentro del código fuente de nuestro lenguaje de programación. Dentro del EXECUTE BLOCK podemos tener parámetros de entrada, parámetros de salida, y variables locales.

En otras palabras, en lugar de escribir el stored procedure dentro de la Base de Datos, lo escribimos dentro de nuestro lenguaje de programación (Visual FoxPro, Visual Basic, C, C++, Delphi, Java, etc.)

¿Cuál es la sintaxis de EXECUTE BLOCK?

EXECUTE BLOCK [(<parámetros de entrada>)]

[RETURNS (<parámetros de salida>)]

AS

[<declaraciones de las variables locales>]

BEGIN

[<instrucciones que pueden escribirse en un stored procedure>]

END

A continuación se muestran varios ejemplos, todos ellos fueron realizados y comprobados con el lenguaje Visual FoxPro, pero adaptarlos a otros lenguajes debería ser fácil y sencillo.

La tabla de BANCOS tiene tres columnas:

  • BAN_CODSUC (código de la sucursal)
  • BAN_IDENTI (identificador del Banco)
  • BAN_NOMBRE (nombre del Banco)

Ejemplo 1:

TEXT TO lcBloque NOSHOW
   EXECUTE BLOCK
   AS
   BEGIN
      INSERT INTO BANCOS VALUES(0, 17, 'Prueba 1');
      INSERT INTO BANCOS VALUES(0, 18, 'Prueba 2');
      INSERT INTO BANCOS VALUES(0, 19, 'Prueba 3');
   END
ENDTEXT

lnResultado = SQLExec(gnHandle, lcBloque)

if lnResultado > 0     && Si se ejecutó exitosamente
   =SQLExec(gnHandle, "COMMIT")
else                   && Si ocurrió algún error
   =SQLExec(gnHandle, "ROLLBACK")
endif

En este ejemplo se intentó insertar 3 filas dentro de la tabla de BANCOS. Si las inserciones fueron exitosas entonces se realizó el COMMIT, en caso contrario el ROLLBACK.

Ejemplo 2:

TEXT TO lcBloque NOSHOW
   EXECUTE BLOCK
      RETURNS (cNomBan VARCHAR(60))
   AS
   BEGIN
      INSERT INTO BANCOS VALUES(0, 20, 'Prueba 4');
      INSERT INTO BANCOS VALUES(0, 21, 'Prueba 5');
      SELECT
         BAN_NOMBRE
      FROM
         BANCOS
      WHERE
         BAN_IDENTI = 20
      INTO
         :cNomBan;
      SUSPEND;
    END
ENDTEXT

lnResultado = SQLExec(gnHandle, lcBloque)

if lnResultado > 0     && Todo ocurrió exitosamente
   BROWSE
   =SQLExec(gnHandle, "COMMIT")
else                   && Ocurrió algún error
   =SQLExec(gnHandle, "ROLLBACK")
   =MessageBox("Un error ocurrió")
endif

En este ejemplo se intentó insertar 2 filas a la tabla de BANCOS y se devolvió el nombre del Banco cuyo identificador es 20 al programa de Visual FoxPro. Si no hubo error entonces lnResultado será mayor que cero y en ese caso se muestra el nombre del Banco cuyo identificador es 20.

Cuando se quiere devolver un valor (o más de un valor) del EXECUTE BLOCK entonces siempre debemos usar la sentencia SUSPEND.

Ejemplo 3:

Local lnNumeroBanco, lcNombreBanco

lnNumeroBanco = 22
lcNombreBanco = 'Prueba 6'

TEXT TO lcBloque NOSHOW
   EXECUTE BLOCK (nIdeBan Integer = ?lnNumeroBanco, cNomBan VarChar(40) = ?lcNombreBanco)
   AS
   BEGIN
      INSERT INTO BANCOS VALUES(0, :nIdeBan, :cNomBan);
   END
ENDTEXT

lnResultado = SQLPrepare(gnHandle, lcBloque)

if lnResultado < 0
   =AError(laError)
   =MessageBox("Error: " + laError[2])
endif

llResultadoOK = SQLExec(gnHandle) > 0

if llResultadoOK
   if !Empty(Alias())
      BROWSE
   endif
   =SQLExec(gnHandle, "COMMIT")
else
   =AError(laError)
   =MessageBox("Error: " + laError[2])
   =SQLExec(gnHandle, "ROLLBACK")
endif

En este ejemplo se usaron 2 parámetros de entrada. Para que funcionara previamente hubo que ejecutar la función SQLPREPARE(). Además, para saber cual fue el error ocurrido se utilizó la función AERROR() la cual en su segundo elemento tiene un mensaje descriptivo.

Tanto en el Ejemplo 1. como en el Ejemplo 2. los datos insertados eran fijos, en cambio ahora se usaron datos variables, lo cual le da mucha mayor potencia.

Ejemplo 4:

lnNumeroBanco = 23
lcNombreBanco = 'Prueba 7'

TEXT TO lcBloque NOSHOW
   EXECUTE BLOCK (nIdeBan Integer = ?lnNumeroBanco, cNomBan VarChar(40) = ?lcNombreBanco)
      RETURNS(cMayusc VarChar(80))
   AS
      DECLARE cTemp VarChar(40);
   BEGIN
      INSERT INTO BANCOS VALUES(0, :nIdeBan, Upper(:cNomBan));
      SELECT
         BAN_NOMBRE
      FROM
         BANCOS
      WHERE
         BAN_IDENTI = :nIdeBan
      INTO
         :cTemp;
      cMayusc = 'Creado en un EXECUTE BLOCK. ' || Trim(cTemp);
      SUSPEND;
   END
ENDTEXT

lnResultado = SQLPrepare(gnHandle, lcBloque)

if lnResultado < 0
   =AError(laError)
   =MessageBox("Error: " + laError[2])
endif

llResultadoOK = SQLExec(gnHandle) > 0

if llResultadoOK
   if !Empty(Alias())
      BROWSE
   endif
   =SQLExec("COMMIT")
else
   =AError(laError)
   =MessageBox("Error: " + laError[2])
   =SQLExec("ROLLBACK")
endif

Este último ejemplo tiene todo junto: parámetros de entrada, parámetros de salida y variables locales. En realidad no es útil lo que hace pero sirve para demostrar como se lo utiliza. Recuerda que siempre para devolver parámetros de salida debes escribir la palabra SUSPEND o no obtendrás los valores de dichos parámetros de salida.

Conclusión:

La instrucción EXECUTE BLOCK nos permite crear un stored procedure dentro de nuestro propio lenguaje de programación y ese EXECUTE BLOCK será luego ejecutado por el Servidor del Firebird. Esta posibilidad nos puede resultar muy útil en muchas circunstancias aunque en general es preferible que los stored procedures estén dentro de la Base de Datos y no dentro del código fuente que escribimos en un lenguaje de programación, ya que estando dentro de la Base de Datos estarán disponibles para todos los programadores, cualquiera sea el lenguaje de programación que utilicen.

Entendiendo ACID

4 comentarios

Desde los inicios de la Informática se guardaron datos para ser recuperados y procesados más tarde pero muchísimas veces esos datos eran inconsistentes y causaban problemas muy graves.

Veamos un ejemplo:

Se tiene que grabar una venta y para ello se tienen dos tablas:

  • Cabecera de ventas
  • Detalles de ventas

Se grabó la cabecera y antes de que se grabaran todos los detalles ocurrió un corte en la energía eléctrica o se dañó la red o algún otro problema que impidió grabar todos los detalles de dicha venta.

Eso implica que algo está muy mal: la cabecera no corresponde exactamente con los detalles. Y eso es inaceptable.

A veces, ese tipo de error puede ser detectado y corregido enseguida, pero a veces pueden pasar días, meses o inclusive años antes de ser detectado.

Este tipo de problemas y muchos más causaban serios perjuicios a las empresas, motivo por el cual muchas personas se dedicaron a investigar como solucionarlos. A finales de los años 1970 Jim Gray definió las propiedades que debía tener una transacción confiable y en 1983 Andreas Reuter y Theo Härder inventaron la palabra ACID para describir a ese tipo de transacción.

ACID es el acrónimo de Atomicity, Consistency, Isolation, Durability (en castellano: Atomicidad, Consistencia, Aislamiento, Durabilidad)

Atomicidad significa que las modificaciones a la Base de Datos (agregar, borrar o modificar) deben seguir la regla de todo o nada. Si cualquier parte de la transacción falló, por más pequeña que sea dicha parte, toda la transacción falló.

Consistencia significa que solamente datos válidos pueden ser escritos en la Base de Datos. Dentro de una transacción se pueden tener temporalmente datos inválidos pero cuando la transacción termina, sea de la forma que sea, la Base de Datos debe tener solamente datos válidos.

Aislamiento significa que si varias transacciones se están ejecutando al mismo tiempo, ninguna de ellas interfiere con las otras. Eso implica que la transacción A no conoce ni puede cambiar lo que la transacción B está haciendo. Cada transacción está aislada de todas las demás transacciones, o sea que cada transacción es totalmente independiente de todas las demás transacciones.

Durabilidad significa que cuando los cambios a los datos que realizó la transacción son grabados, dichos cambios permanecerán aún cuando se corte la energía eléctrica, se interrumpa la conexión a la red, o cualquier otro problema físico.

Si una Base de Datos no cumple con alguno de esos 4 criterios entonces no puede ser considerada confiable.

Para quienes usamos Firebird hay una muy buena noticia: Firebird cumple con ACID al 100%

¿Cómo se termina una transacción?

Solamente hay dos formas “normales” de terminar una transacción:

    • con un COMMIT
    • con un ROLLBACK

Si una transacción termina con un COMMIT exitoso entonces todos los cambios que ocurrieron dentro de esa transacción (es decir, todos los INSERT, DELETE, UPDATE) son grabados permanentemente en la Base de Datos

Si una transacción terminó con un ROLLBACK entonces ninguno de los cambios que ocurrieron dentro de esa transacción (es decir, ningún INSERT, DELETE, UPDATE) será grabado y la Base de Datos regresará al estado que tenía cuando comenzó la transacción

¿Se puede escribir alguna operación en Firebird afuera de una transacción?

No, Firebird cumple con ACID al 100% y eso implica que todas las operaciones que escribas (INSERT, DELETE, UPDATE, SELECT, EXECUTE PROCEDURE, etc.) siempre estarán, sí o sí, dentro de una transacción.

Sin embargo, yo muchas veces he escrito operaciones sin previamente iniciar una transacción

Es cierto, tú no iniciaste la transacción pero el Cliente del Firebird la inició por tí. Pero no debes olvidarte de lo siguiente: el Cliente del Firebird inició esa transacción con los parámetros por defecto, que son los siguientes:

READ WRITE: lo cual significa que en dicha transacción podrás insertar, borrar o modificar datos

SNAPSHOT: lo cual significa que esa transacción solamente podrá “ver” y conocer los datos que fueron “commiteados” antes de que la transacción empezara

WAIT: lo cual significa que si hay un conflicto con otra transacción porque ambas están queriendo actualizar o borrar la misma fila, esta transacción esperará hasta que la otra transacción termine

Si lo que necesitas es solamente un SELECT entonces sería mejor que tu transacción fuera READ ONLY porque de esa manera obtendrás los resultados más rápidamente. Aquí puedes ver la importancia de iniciar tu mismo las transacciones y no depender del Cliente del Firebird.

¿Por que se necesita que un SELECT esté dentro de una transacción?

Esta es una pregunta muy común de los principiantes, después de todo, si lo único que se hará será obtener datos: ¿cuál es la necesidad de abrir una transacción para ello?

La respuesta es la siguiente: si se pudiera tener a un SELECT afuera de la transacción entonces ese SELECT no estaría aislado y las operaciones de inserción, borrado, modificación de datos que realizaran otras transacciones estarían interfiriendo con él.

Por ejemplo se escribe:

SELECT
   PRD_CODIGO,
   PRD_NOMBRE,
   PRD_PREVTA
FROM
   PRODUCTOS

porque se necesita imprimir un informe con el Código, el Nombre, y el Precio de Venta de los productos para entregarlo a los vendedores. La tabla de PRODUCTOS tiene 2000 filas y antes de imprimir la fila 800 alguien le cambió el Precio de Venta. Después de imprimir la fila 200 alguien le cambió el Precio de Venta.

Esto implica que si inmediatamente después de ejecutarse el primer SELECT se escribió un SELECT exactamente igual, los datos obtenidos serán distintos. Y eso es inaceptable, porque todos los vendedores deben tener el mismo informe.

En cambio, si usamos transacciones y el modo de aislamiento es SNAPSHOT podremos ejecutar ese SELECT miles de veces y todas esas veces obtendremos exactamente el mismo resultado.

Artículos relacionados:

Modos de bloqueo de las transacciones

Bloqueos mortales

Terminar las transacciones de los SELECTs

COMMIT y ROLLBACK en stored procedures y triggers

Detectando aplicaciones y usuarios que mantienen las transacciones abiertas durante mucho tiempo

El índice del blog Firebird21