Usando un cursor con parámetros

Deja un comentario

En este artículo ya habíamos visto como usar cursores, y lo útiles que pueden ser:

https://firebird21.wordpress.com/2013/06/02/usando-cursores/

Ahora veremos un ejemplo donde el cursor tiene condiciones que vienen como parámetros de entrada del stored procedure.

CREATE PROCEDURE CURSOR_1(
   tnCodSuc TYPE OF COLUMN CLIENTES.CLI_CODSUC,
   tnIdent1 TYPE OF COLUMN CLIENTES.CLI_IDENTI,
   tnIdent2 TYPE OF COLUMN CLIENTES.CLI_IDENTI)
   RETURNS(
      ftnIdenti INTEGER,
      ftcNombre VARCHAR(40))
AS
   DECLARE VARIABLE lcContinuar CHAR(1);
   DECLARE VARIABLE MiCursor CURSOR FOR (
                                         SELECT
                                            CLI_IDENTI,
                                            CLI_NOMBRE
                                         FROM
                                            CLIENTES
                                         WHERE
                                            CLI_CODSUC = :tnCodSuc AND
                                            CLI_IDENTI BETWEEN :tnIdent1 AND :tnIdent2);
BEGIN

   OPEN MiCursor; /* Se abre el cursor */

   lcContinuar = 'S';

   WHILE (lcContinuar = 'S') DO BEGIN
      FETCH                             /* extrae una fila */
         MiCursor
      INTO
         :ftnIdenti,
         :ftcNombre;
      IF (ROW_COUNT = 1) THEN BEGIN     /* si ROW_COUNT = 1, se leyó una fila */
         /* Aquí se puede realizar algún proceso con los datos */
         SUSPEND;
      END ELSE
         lcContinuar = 'N';             /* Si ROW_COUNT = 0, ya no hay más filas */
   END

   CLOSE MiCursor; /* Se cierra el cursor */

END;

Como puedes ver, este stored procedure tiene 3 parámetros de entrada:

  • el código de la Sucursal
  • el identificador del primer cliente que nos interesa
  • el identificador del último cliente que nos interesa

y por lo tanto en el cursor tendremos los datos de los clientes que pertenecen a esa sucursal y cuyos identificadores están dentro del rango.

Artículos relacionados:

Usando cursores

El índice del blog Firebird21

Entendiendo a las transacciones

7 comentarios

Entender correctamente como funcionan las transacciones es importantísimo en Firebird porque todo lo haces dentro de una transacción, no existe alguna operación que pueda realizarse afuera de ellas. Eso significa que siempre todos tus INSERT, UPDATE, DELETE, FETCH, SELECT y EXECUTE PROCEDURE se ejecutan dentro de una transacción, sí o sí.

¿Por qué eso?

Porque de esta manera existe la seguridad 100% de que tu Base de Datos cumpla con ACID, que es un paradigma que todos los buenos SGBDR (y por lo tanto todas sus bases de datos) deben cumplir.

Las bases de datos que cumplen con ACID son totalmente confiables y todas las bases de datos de Firebird cumplen con ACID al 100%, sin excepción.

https://firebird21.wordpress.com/2013/05/10/entendiendo-acid/

Iniciando una transacción

Si no había una transacción abierta entonces una transacción se inicia automáticamente cuando escribes un comando (por ejemplo: INSERT, UPDATE, DELETE, SELECT) .

El comando SET TRANSACTION (que verás más abajo) no inicia la transacción, lo que hace es:

  1. Un COMMIT a la transacción actual (el cual, como todo COMMIT, puede finalizar exitosamente o fallar)
  2. Establecer los parámetros que serán usados por la siguiente transacción

Finalizando una transacción

Hay solamente dos formas (normales, porque también hay formas anormales) en que una transacción puede ser finalizada:

  • Con un COMMIT
  • Con un ROLLBACK

Si finaliza con un COMMIT entonces todo lo que se hizo dentro de la transacción queda guardado en las tablas de la Base de Datos.

Si finaliza con un ROLLBACK entonces todo lo que se hizo dentro de la transacción es desechado, eliminado, como si nunca se hubiera hecho.

Un COMMIT puede fallar, un ROLLBACK jamás falla.

En los ejemplos que hay más abajo puedes ver casos de COMMITs que fallan.

Sintaxis de SET TRANSACTION:

SET TRANSACTION
   [NAME NombreTransacción]
[READ WRITE | READ ONLY]
[ [ISOLATION LEVEL] { SNAPSHOT [TABLE STABILITY]
| READ COMMITTED [[NO] RECORD_VERSION] } ]
[WAIT | NO WAIT]
[LOCK TIMEOUT segundos]
[NO AUTO UNDO]
[IGNORE LIMBO]
[RESERVING | USING ]

NAME se usa cuando abres una transacción dentro de tu programa. Te sirve para asignarle un nombre a la transacción. De esa manera podrías tener varias transacciones abiertas al mismo tiempo y realizar operaciones independientes en cada una de ellas. Es opcional, si no quieres no lo usas pero si no lo usas entonces solamente puedes tener una transacción abierta.

Con READ WRITE le pides al Firebird que abra la transacción para lectura y para escritura. Es el parámetro por defecto. Y es el que debes utilizar si dentro de la transacción tendrás los comandos INSERT o UPDATE o DELETE. Con READ ONLY le pides al Firebird que abra la transacción para lectura solamente. Y es el que debes utilizar cuando dentro de tu transacción solamente tendrás el comando SELECT o el comando EXECUTE PROCEDURE y éste no realiza ningún INSERT ni UPDATE ni DELETE. Usar READ ONLY en una transacción que no modifica la Base de Datos hace que ésta finalice más rápido y eso es algo muy bueno.

ISOLATION LEVEL es el nivel de aislamiento de la transacción y puede tener uno de estos valores:

SNAPSHOT. Significa que la transacción solamente conoce los valores que fueron confirmados (se confirma con un COMMIT) antes de que ella empezara. En general se usa este nivel de aislamiento cuando la transacción solamente tendrá SELECTs.

SNAPSHOT TABLE STABILITY. Obtiene acceso exclusivo para escritura en todas las tablas involucradas y también en todas las tablas relacionadas con las anteriores mediante una Foreign Key. Ninguna otra transacción podrá modificar (insertar, actualizar, borrar) las tablas que una transacción SNAPSHOT TABLE STABILITY está usando. Y una transacción SNAPSHOT TABLE STABILITY no podrá iniciar si alguna de las tablas que necesita está siendo usada por otra transacción READ WRITE. En Firebird no es recomendable usar este aislamiento. Es muy peligroso porque impide que otras transacciones modifiquen las tablas. En el rarísimo caso de que debas usar este aislamiento trata de que la transacción termine muy rápido o de que se ejecute cuando nadie más usa la Base de Datos.

READ COMMITED. Permite que la transacción pueda “ver” todas las filas que fueron insertadas, actualizadas o borradas por otras transacciones y que fueron confirmadas (mediante un COMMIT) por esas otras transacciones. Es el aislamiento que normalmente se usa en los programas que insertan, modifican o borran filas de las tablas. Este aislamiento además puede ser RECORD_VERSION o NO RECORD_VERSION.

RECORD VERSION. En este caso la transacción puede leer todas las filas que fueron confirmadas por otras transacciones. Y si la transacción es READ WRITE entonces podrá modificar también esas filas pero siempre y cuando haya empezado después que las otras. Ejemplo:

      • En la tabla PRODUCTOS el precio de venta de “Televisor Toshiba de 20 pulgadas” es de 120 dólares
      • Empezó la transacción T1
      • Empezó la transacción T2
      • La transacción T1 cambió el precio de venta de “Televisor Toshiba de 20 pulgadas” a 100 dólares
      • La transacción T2 ve que el precio de venta de “Televisor Toshiba de 20 pulgadas” es de 120 dólares. Como la transacción T1 aún no finalizó con un COMMIT, la transacción T2 ve el precio que ese producto tenía cuando empezó la transacción T2. La transacción T2 no puede saber lo que hace la transacción T1 antes de que la transacción T1 finalice con un COMMIT.
      • La transacción T1 finalizó con un COMMIT
      • La transacción T2 cambió el precio de venta de “Televisor Toshiba de 20 pulgadas” a 110 dólares
      • La transacción T2 finalizó con un COMMIT
      • Ahora, el precio de venta es de 110 dólares porque es el que corresponde al último COMMIT

Como la transacción T2 había empezado después que la transacción T1 entonces el COMMIT de la transacción T2 fue exitoso. Si hubiera empezado antes, hubiera sido rechazado. Por ejemplo:

      • En la tabla PRODUCTOS el precio de venta de “Televisor Toshiba de 20 pulgadas” es de 120 dólares
      • Empezó la transacción T1
      • Empezó la transacción T2
      • La transacción T2 cambió el precio de venta de “Televisor Toshiba de 20 pulgadas” a 110 dólares
      • La transacción T1 ve que el precio de venta de “Televisor Toshiba de 20 pulgadas” es de 120 dólares. Como la transacción T2 aún no finalizó con un COMMIT, la transacción T1 ve el precio que ese producto tenía cuando empezó la transacción T1.  La transacción T1 no puede saber lo que hace la transacción T2 antes de que la transacción T2 finalice con un COMMIT.
      • La transacción T2 finalizó con un COMMIT
      • La transacción T1 cambió el precio de venta de “Televisor Toshiba de 20 pulgadas” a 100 dólares
      • La transacción T1 intenta finalizar con un COMMIT
      • Su intento de COMMIT es rechazado. Como la transacción T1 empezó antes que la transacción T2 entonces la transacción T1 no puede modificar una fila que fue modificada por la transacción T2, ya que la transacción T2 es más nueva
      • Ahora, el precio de venta es de 110 dólares, que corresponde al último COMMIT exitoso

NO RECORD_VERSION. Es el valor por defecto. Impide que la transacción pueda siquiera leer una fila que fue actualizada por otra transacción y que aún no fue confirmada. Por ejemplo:

      • En la tabla PRODUCTOS el precio de venta de “Televisor Toshiba de 20 pulgadas” es de 120 dólares
      • Empezó la transacción T1
      • Empezó la transacción T2
      • La transacción T1 cambió el precio de venta de “Televisor Toshiba de 20 pulgadas” a 100 dólares
      • La transacción T2 no puede ver cual es el precio de venta de “Televisor Toshiba de 20 pulgadas” porque la transacción T1 aún no finalizó con un COMMIT. Por lo tanto la transacción T2 no tiene acceso a esa fila, ni siquiera para lectura. Y ahora pueden ocurrir dos cosas:
        1. Si el modo de bloqueo de la transacción T2 es WAIT entonces la transacción T2 esperará hasta que la transacción T1 finalice con un COMMIT o con un ROLLBACK. En ambos casos, como la transacción T2 es más nueva que la transacción T1 entonces la transacción T2 podrá finalizar con un COMMIT
        2. Si el modo de bloqueo de la transacción T2 es NO WAIT entonces la transacción T2 recibirá una notificación de que la fila que quiso modificar está bloqueada
      • Si la transacción T1 finaliza con un COMMIT, entonces el precio de venta será de 100 dólares, si finaliza con un ROLLBACK el precio de venta continuará en 120 dólares

WAIT. Este es el modo de bloqueo por defecto. Sirve para lo siguiente: cuando una transacción no puede modificar o borrar una fila porque otra transacción está usando esa fila, espera hasta que la otra transacción termine. Si la transacción que estaba esperando es más nueva entonces podrá modificar a esa fila cuando sea desbloqueada. Si es más vieja, obtendrá inmediatamente una notificación de error. No hay que usar WAIT en programas que muchos usuarios utilizan para modificar o borrar las mismas filas porque puede causar que muchos usuarios estén esperando varios minutos antes de poder continuar con sus tareas. Tampoco tiene sentido usar WAIT en transacciones SNAPSHOT sin usar también LOCK TIMEOUT en ellas. De todas maneras hay excepciones y lo que puedes hacer es verificar si en tu caso es bueno usar o no usar WAIT. En general, si son pocos los usuarios que modifican las mismas filas y las transacciones son cortas, usar WAIT suele ser lo correcto. Cuando el modo de bloqueo es NOWAIT el Servidor del Firebird inmediatamente informa que ocurrió un conflicto porque una transacción intenta modificar una fila que otra transacción también está modificando. Si muchos usuarios están modificando las mismas filas NOWAIT les informará rápidamente que ocurrió un problema mientras que WAIT les hará esperar. En transacciones SNAPSHOT lo recomendable suele ser usar NOWAIT y cuando se encuentra un conflicto hacer un ROLLBACK o esperar unos segundos y reintentar la modificación.

LOCK TIMEOUT es muy útil para evitar que una transacción espere indefinidamente que la fila sea desbloqueada. Solamente se puede usar cuando el modo de bloqueo es WAIT. Por ejemplo: WAIT LOCK TIMEOUT 5 esperará un máximo de 5 segundos para que la fila sea desbloqueada. Si no fue desbloqueada entonces se recibirá una notificación, como si el modo de bloqueo hubiera sido NOWAIT. Lastimosamente no puede usarse en transacciones iniciadas dentro de un programa.

NO AUTO UNDO le indica al Servidor del Firebird que no guarde los datos que normalmente guarda para hacer el ROLLBACK de la transacción. Si tu transacción tendrá muchos INSERT y estás seguro que no finalizará con un ROLLBACK entonces con esta opción conseguirás que la transacción finalice más rápidamente.

IGNORE LIMBO. Con esta opción los registros creados por las transacciones limbo son ignorados. Una transacción está en limbo si la segunda etapa de un COMMIT de dos etapas falla. Los COMMIT de dos etapas se usan en transacciones que involucran a dos bases de datos. No se usa esta opción en transacciones que son iniciadas dentro de los programas.

RESERVING reserva todas las tablas cuyos nombres se encuentran a continuación. La reserva empieza cuando la transacción empieza y termina cuando la transacción finaliza. Eso le garantiza a la transacción tener acceso a todas esas tablas y ninguna otra transacción podrá impedirle el acceso a las tablas listadas. SNAPSHOT TABLE STABILITY bloquea a todas las tablas que están dentro de la transacción (si hay 7 tablas bloquea a las 7), en cambio RESERVING solamente reserva a las tablas cuyos nombres se especificaron (quizás de esas 7 solamente 2 necesitaban realmente ser reservadas), siendo por lo tanto menos restrictivo y el método preferible cuando se deben bloquear o reservar tablas (algo que en Firebird muy raramente debería hacerse). USING limita los nombres de las bases de datos a los cuales la transacción puede acceder a los que aquí se hayan especificado.

Opciones por defecto

Las opciones por defecto de las transacciones (o sea, las que usará el Servidor del Firebird si no le indicas otra cosa) son:

READ WRITE + WAIT + SNAPSHOT

Lecturas sucias

Firebird no permite las lecturas sucias. Se le llama lectura sucia a los cambios que hace una transacción y antes de que dicha transacción termine con un COMMIT desde otra transacción se pueden ver esos cambios. Por ejemplo: la transacción T1 está haciendo un INSERT y antes de que la transacción T1 finalice con un COMMIT desde la transacción T2 ya se está viendo ese INSERT que hizo la transacción T1. Otros SGBDR permiten esa situación, pero Firebird no. Y es lo correcto porque eso otorga mucha mayor seguridad.

NOTA:

Todas las transacciones siempre pueden ver a todos los registros que fueron confirmados (finalizaron con un COMMITantes de que ellas empezaran. Pero solamente las transacciones READ COMMITED pueden ver a los registros que fueron confirmados después de que ellas empezaran.

Artículos relacionados:

COMMIT y ROLLBACK en stored procedures y triggers

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

Terminar las transacciones de los SELECTs

Modos de bloqueo de las transacciones

Bloqueos mortales

Entendiendo ACID

La arquitectura MGA

Transacciones optimistas y transacciones pesimistas

El índice del blog Firebird21

El foro del blog Firebird21

Evitando que los mirones averigüen nuestro password en ISQL

1 comentario

Cuando nos conectamos a ISQL todo lo que escribimos es visible para cualquier mirón que esté parado detrás nuestro.

Por ejemplo, si escribimos:

MIRONES1

(si haces clic en la imagen la verás más grande)

Cualquiera que esté a nuestro costado o atrás nuestro mirando nuestro monitor sabrá que nos conectamos a la Base de Datos con nombre de usuario SYSDBA y con password 12345678

Si eso ocurre mientras estamos en la oficina de nuestro cliente después que terminamos nuestra tarea, nos vamos a nuestra propia oficina …. y el mirón aprovecha para conectarse a la Base de Datos como SYSDBA ya que conoce el password de ese usuario.

Eso, es un gran riesgo de seguridad. Nunca faltan por ahí los que quieren hacerse de los hackers y estarán más que felices de entrar en un lugar donde se supone no deberían entrar.

Pero necesitamos usar ISQL para realizar nuestra tarea, y tampoco podemos ser tan desagradables como para decirles que se vayan lejos, que se manden a mudar, que se alejen de nosotros porque vamos a escribir algo que queremos mantener en secreto ¿qué hacemos entonces, cómo podemos solucionar este problema, cómo podemos evitar que conozcan el password de SYSDBA o del usuario que usamos para conectarnos?

Afortunadamente el programa ISQL cuenta con una opción -fetch (extraer) la cual nos permite tener guardado el password en un archivo de texto y usará ese password en la conexión.

Por lo tanto lo que debemos hacer es:

  1. En un archivo de texto (que puede tener cualquier nombre) escribir el password
  2. Al ejecutar el programa ISQL escribir -fetch NombreArchivoTexto
  3. De este modo, para conectarnos a la Base de Datos no deberemos especificar el password
  4. Si no le mostramos a los usuarios el contenido de nuestro archivo de texto jamás sabrán cual es nuestro password

MIRONES2

(si haces clic en la imagen la verás más grande)

En este ejemplo, el password del usuario SYSDBA está guardado en un archivo de texto llamado E:\SISTEMAS\PASS.TXT

Pero ¿cuál es ese password? Ningún mirón puede saberlo, ya que lo que él verá será la captura de pantalla que está arriba.

Nuestro archivo de texto E:\SISTEMAS\PASS.TXT puede estar en un pen-drive y de esa manera ningún mirón jamás tendrá la menor idea de cual es el password allí guardado.

Haciendo así nos aseguramos que ningún mirón se entere de nuestro password y además lo hacemos de una forma “políticamente correcta”, es decir sin necesidad de ser desagradables con otras personas.

Artículo relacionado:

El índice del blog Firebird21

Usando cursores

9 comentarios

Firebird nos da la posibilidad de usar SELECTs abreviados a los cuales se les llama “Cursores”. Son muy útiles cuando  los SELECTs son complejos y necesitamos utilizar varios de ellos en un stored procedure, trigger o execute block.

Funcionan de la siguiente manera:

  1. Se declara el cursor. Es allí donde se indica cual será el SELECT que le corresponde
  2. Se abre el cursor. A partir de ese momento ya podremos obtener las columnas que nos interesan
  3. Se extraen las filas del cursor. Se obtiene el valor de cada columna que declaramos en el SELECT, una fila por vez
  4. Se procesan las variables extraídas. Una vez que obtuvimos los valores de las columnas podemos utilizarlos como nos parezca
  5. Se cierra el cursor. Es la mejor práctica aunque si no lo hacemos el mismo Firebird lo cerrará cuando finalice el stored procedure, trigger o execute block donde se encuentra
CREATE PROCEDURE USANDO_CURSOR
   RETURNS(
      tnIdenti BIGINT,
      tcNombre VARCHAR(40))
AS
   DECLARE VARIABLE lcContinuar CHAR(1);
   DECLARE VARIABLE MiCursor CURSOR FOR (
      SELECT TAR_IDENTI, TAR_NOMBRE FROM TARJETAS);
BEGIN

   OPEN MiCursor;     /* Se abre el cursor */

   lcContinuar = 'S';

   WHILE (lcContinuar = 'S') DO BEGIN
      FETCH MiCursor INTO :tnIdenti, :tcNombre;     /* extrae una fila */
      IF (ROW_COUNT = 1) THEN BEGIN     /* si ROW_COUNT = 1 se leyó una fila */
         tcNombre = tcNombre || ' tarjeta procesada....';
         SUSPEND;
      END ELSE
         lcContinuar = 'N';     /* Si ROW_COUNT = 0 ya no hay más filas */
   END

   CLOSE MiCursor; /* Se cierra el cursor */

END;

¿Qué se hizo en este stored procedure?

Primero, se declaró un cursor de nombre “MiCursor” el cual corresponde a un SELECT de la tabla TARJETAS, de la cual nos interesan dos columnas: TAR_IDENTI y TAR_NOMBRE (Identificador de la tarjeta de crédito y Nombre de la tarjeta de crédito).

Segundo, se abrió el cursor de nombre “MiCursor” con el comando OPEN

Tercero, se escribió un ciclo WHILE el cual finalizará cuando la variable “lcContinuar” sea distinta de “S”

Cuarto, se extrajo una fila del SELECT que corresponde al cursor “MiCursor” con el comando FETCH

Quinto, se verificó si hay más filas para procesar o si ya se leyeron todas las filas. Cuando todas las filas han sido leídas el valor de la variable de contexto ROW_COUNT es siempre 0 (cero). Mientras haya filas por leer su valor es 1 (uno). Eso significa que podemos usar ROW_COUNT para saber cuando se leyeron todas las filas del SELECT, es el equivalente a la función EOF() que tienen algunos lenguajes de programación.

Sexto, si se pudo extraer una fila entonces se la procesó. Por supuesto que aquí podemos hacer lo que se nos ocurra con las variables obtenidas mediante el FETCH.

Séptimo, se regresó al inicio del WHILE para comprobar si debe repetirse el ciclo una vez más.

Octavo, se cerró el cursor.

Conclusión:

Los cursores son muy útiles sobre todo cuando no podemos tener todas las columnas que necesitamos en un solo SELECT. Si podemos tener a todas las columnas en un solo SELECT puede ser más práctico usar el comando SELECT … INTO

Como el Firebird nos permite tener abiertos dos, tres, o más cursores al mismo tiempo entonces podemos extraer de cada uno de ellos las columnas que necesitamos para procesarlas.

Muchas veces, en lugar de estar escribiendo un SELECT complicado, lleno de JOINs o de UNIONs es mucho más fácil, rápido y sencillo, usar varios cursores, se nos simplifica la vida de esta manera.

Los cursores son una gran facilidad que el Firebird nos provee, hay que utilizarlos para que nuestro código fuente sea más legible.