La forma correcta de hacer un UPDATE

1 comentario

Seguramente sabes que para modificar una fila debes usar el comando UPDATE, pero ¿sabes cuál es la manera correcta de usar ese comando?

Veamos.

Tenemos una aplicación que muchos usuarios usan al mismo tiempo y al hacerlo están actualizando la misma fila de la misma tabla.

Nuestra transacción es READ COMMITTED, RECORD_VERSION, NO WAIT

¿Cómo actualizamos esa fila?

Opción 1. Obtener un valor, modificarlo, y actualizarlo

  • Con un SELECT obtienes el valor que te interesa
  • Modificas ese valor
  • Realizas el UPDATE

Opción 2. Actualizar de una sola vez

  • UPDATE MiTabla

Listado 1.

UPDATE
   MiTabla
SET
   MiColumna = MiColumna + :MiNuevoValor
WHERE
   MiCondición;

¿Es preferible usar la Opción 1. o la Opción 2.?

Si usas la Opción 1. entonces entre el SELECT y el UPDATE una transacción concurrente pudo haber modificado el valor que obtuviste con el SELECT. En consecuencia, tu tabla tendrá un valor erróneo aunque a simple vista parecerá que está todo bien.

Lo correcto es usar la Opción 2. porque en ese caso el Firebird de forma implícita bloqueará a la fila y así el valor guardado siempre estará bien.

La Opción 1. solamente puede usarse si nunca dos o más usuarios estarán modificando la misma fila de la misma tabla al mismo tiempo. Si puede ocurrir que dos o más usuarios la estén modificando al mismo tiempo, siempre hay que usar la Opción 2.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

Insertando en una tabla filas de otra tabla

3 comentarios

Un comando del Firebird que puede resultarnos muy útil en ciertas ocasiones es el MERGE. Éste nos permite insertar o actualizar las filas de una tabla de acuerdo al contenido de otra tabla.

Sintaxis:

MERGE INTO
   {Tabla1 | Vista1} [[AS] alias1]
USING
   {Tabla2 | Vista2 | (instrucción_SELECT)} [[AS] alias2]
ON 
   Condición
WHEN MATCHED THEN
   UPDATE 
      SET Columna1 = Valor1 [, Columna2 = Valor2...]
WHEN NOT MATCHED THEN 
   INSERT 
      [(<Columnas>)] VALUES (<valores>)

Podemos usar WHEN MATCHED y WHEN NOT MATCHED o solamente uno de ellos.

Ejemplo:

Tenemos una tabla llamada CLIENTES y otra tabla llamada PROSPECTOS. Los prospectos son los clientes potenciales, aquellos que quizás sí o quizás no lleguen a ser nuestros clientes. Mientras la venta no esté confirmada son prospectos o sea posibles clientes. Pero cuando confirmamos la venta entonces ya pasan a ser clientes, ya no son prospectos porque la venta ya se realizó.

En la tabla de PROSPECTOS tenemos registrados los datos de nuestros posibles futuros clientes, como sus nombres, direcciones, números de teléfono, e-mails, etc. Cuando la venta se confirma y por lo tanto pasan a ser clientes confirmados queremos copiar esos datos que están en la tabla de PROSPECTOS a la tabla de CLIENTES, para no tener que volver a escribir los mismos datos que ya teníamos cargados.

MERGE01

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

MERGE02

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

MERGE03

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

En la Captura 1. vemos la estructura de la tabla PROSPECTOS, en la Captura 2. vemos a nuestros posibles futuros clientes, y en la Captura 3. vemos a los clientes que tenemos actualmente.

En nuestra tabla de PROSPECTOS ya hemos confirmado la venta a quienes tienen una letra ‘T’ en la columna PRO_CONFIR, por lo tanto debemos pasar sus datos a la tabla de CLIENTES.

Eso lo conseguiremos fácilmente escribiendo:

MERGE INTO
   CLIENTES
USING
   PROSPECTOS
ON
   PRO_CONFIR = 'F'
WHEN NOT MATCHED THEN
   INSERT
          (CLI_IDENTI, CLI_NOMBRE, CLI_DIRECC, CLI_TELEFO, CLI_EMAILX)
   VALUES (0 , PRO_NOMBRE, PRO_DIRECC, PRO_TELEFO, PRO_EMAILX)

Notarás que para hacer un INSERT tenemos que poner la condición contraria, porque el INSERT se realiza cuando la condición no se cumple. Cuando se cumple la condición se realiza un UPDATE (que en este ejemplo no hemos utilizado).

MERGE04

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

Ahora en nuestra tabla de CLIENTES ya tenemos a los prospectos a quienes les habíamos vendido y por lo tanto ya dejaron de ser prospectos y pasaron a ser clientes.

El último paso que nos queda por completar es eliminar de la tabla de PROSPECTOS aquellos que tienen una letra ‘T’ en la columa PRO_CONFIR porque después de pasarlos a la tabla de CLIENTES ya no son prospectos y no deberían estar ahí.

DELETE FROM
   PROSPECTOS
WHERE
   PRO_CONFIR = 'T'

MERGE05

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

Conclusión:

Para poder copiar datos de una tabla, vista o instrucción SELECT a otra tabla o vista el Firebird dispone del comando MERGE, en el cual debemos elegir:

  1. la tabla o la vista que recibirá los datos
  2. la tabla, vista, o SELECT de la cual extraeremos los datos
  3. la condición para que una fila sea insertada o actualizada

Si la condición elegida se cumple, entonces se realizará un UPDATE. Si la condición elegida no se cumple, entonces se realizará un INSERT.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

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

Eliminando códigos de control en las cadenas alfanuméricas

4 comentarios

Como seguramente sabes, todos los caracteres que usas para escribir textos con la computadora tienen un código ASCII. Así por ejemplo el código ASCII de la letra A mayúscula es 65, el código ASCII del dígito 0 es 48, etc.

Los códigos ASCII cuyos valores van desde al 0 al 31 en su mayoría no son imprimibles y se llaman “códigos de control”.

Si una columna CHAR o VARCHAR tiene códigos de control entonces el resultado que obtendrás al ejecutar un SELECT o al utilizar funciones alfanuméricas tales como LEFT(), TRIM(), etc. podría no ser el esperado.

Entenderemos mejor el problema con el siguiente ejemplo.

Tenemos una tabla llamada PRODUCTOS la cual tiene las siguientes filas:

ASCII1

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

Ahora, reemplazamos todos los espacios en blanco por el código de control 0 (cero):

UPDATE
   PRODUCTOS
SET
   PRD_NOMBRE = REPLACE(PRD_NOMBRE, ' ', ASCII_CHAR(0))

Volvemos a consultar el contenido de la tabla PRODUCTOS y ahora esto es lo que obtenemos:

ASCII2

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

¿Qué pasó aquí? ¡¡¡Desaparecieron todos los caracteres que se encontraban después del primer espacio en blanco!!!

Pues no, no te asustes, no han desaparecido sino que no son visibles. Cuando el Firebird encuentra un caracter cuyo código ASCII es cero ya no muestra los siguientes caracteres, llega solamente hasta el que tiene código ASCII cero.

Entonces, si ahora actualizamos nuestra tabla PRODUCTOS de forma inversa (o sea, reemplazando los códigos ASCII cero con espacios en blanco):

UPDATE
   PRODUCTOS
SET
   PRD_NOMBRE = REPLACE(PRD_NOMBRE, ASCII_CHAR(0), ' ')

esto será lo que obtendremos:

ASCII1

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

¡¡¡Voilá!!! ¡¡¡Reaparecieron todos los caracteres faltantes!!!

Conclusión:

Si al consultar el contenido de una columna alfanumérica notas que faltan caracteres o que el comportamiento de las funciones alfanuméricas LEFT(), RIGHT(), TRIM(), etc., es extraño entonces es posible que la causa sea que hay caracteres de control en esa columna. Puedes utilizar la función REPLACE() para reemplazar a esos caracteres de control por otros caracteres, típicamente por espacios en blanco, como has visto en los ejemplos anteriores y de esa manera solucionarás el problema.

Artículo relacionado:

El índice del blog Firebird21

PHP: guardando un archivo en un campo BLOB

1 comentario

Si estás programando con PHP y usando una Base de Datos de Firebird puedes encontrarte con la necesidad de guardar un archivo en un campo BLOB, aquí está una forma de hacerlo, colaboración de Jaume.

$f = realpath("factura.pdf");

$stream = fopen($f, "r");

$dpdf = stream_get_contents($stream);

fclose($stream);

$sql = "UPDATE OR INSERT INTO MiTabla (MiColumna1, MiColumna2) VALUES (MiValor1, :foo)";

try {
   $queri = $co->prepare($sql);
   $queri->bindParam(':foo', $dpdf);
   $queri->execute();
   $queri = NULL;
}
catch (PDOException $e) {
   $ok    = false;
   $queri = NULL;
   $inf   = $e->getMessage();
}

Artículo relacionado:

El índice del blog Firebird21

Insertando datos en tablas externas

6 comentarios

En este artículo habíamos visto como insertar datos que se encuentran en un archivo externo dentro de una tabla de Firebird:

https://firebird21.wordpress.com/2013/03/03/insertando-una-gran-cantidad-de-filas/

Ahora veremos el proceso inverso: crear un archivo externo e insertarle filas.

En realidad es muy sencillo.

CREATE TABLE MITABLAEXTERNA EXTERNAL 'E:\DATABASES\EXTERNA.TXT' (
   EXT_IDENTI SMALLINT,
   EXT_NOMBRE VARCHAR(40));

Como ves, al comando CREATE TABLA solamente se le agrega la palabra clave EXTERNAL y el nombre que queremos darle al archivo externo, rodeado con apóstrofos.

Para insertarle datos, se hace exactamente igual que con una tabla normal:

INSERT INTO MITABLAEXTERNA (EXT_IDENTI, EXT_NOMBRE) VALUES (1, 'MARCELA')
INSERT INTO MITABLAEXTERNA (EXT_IDENTI, EXT_NOMBRE) VALUES (2, 'SILVIA')

EXTERNAL1

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

Pero a una tabla externa no se le pueden actualizar filas ni tampoco borrar filas. Solamente se le pueden insertar filas.

Si ahora abrimos el archivo ‘EXTERNA.TXT’ con el bloc de notas, esto es lo que veremos:

EXTERNAL2

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

Los nombres se ven bien, pero los números no. ¿Por qué? ¿qué pasó?

Es que los números se guardan con su representación binaria, no como su representación de texto. En otras palabras, todos los números serán ilegibles para la gran mayoría de los seres humanos. ¿Y cuál es la solución? Muy fácil, que todas las columnas sean de tipo CHAR, así siempre podremos visualizarlas correctamente:

CREATE TABLE MITABLAEXTERNA2 EXTERNAL 'E:\DATABASES\EXTERNA2.TXT' (
   EXT_IDENTI CHAR(5),
   EXT_NOMBRE CHAR(40));

Y como ahora todas las columnas son de tipo CHAR entonces podemos rodear a las constantes con apóstrofos si queremos (pero no es obligatorio con las constantes numéricas):

INSERT INTO MITABLAEXTERNA (EXT_IDENTI, EXT_NOMBRE) VALUES ('1', 'MARCELA')
INSERT INTO MITABLAEXTERNA (EXT_IDENTI, EXT_NOMBRE) VALUES ('2', 'SILVIA')

Y si ahora abrimos el archivo ‘EXTERNA2.TXT’ con el bloc de notas, esto es lo que veremos:

EXTERNAL3

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

¡¡¡PERFECTO!!! ya vemos los identificadores y los nombres de las personas. Que es exactamente lo que deseábamos.

Si los datos que queremos insertar se encuentran en alguna tabla de Firebird (que es el caso más común) entonces para insertarlos podemos utilizar una subconsulta:

INSERT INTO MITABLAEXTERNA2
   (EXT_IDENTI, EXT_NOMBRE)
SELECT
   CLI_IDENTI,
   CLI_NOMBRE
FROM
   CLIENTES

EXTERNAL4

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

A las dos filas que ya teníamos anteriormente en nuestra tabla externa ahora se le agregaron los datos de los clientes. Usar SELECT para insertar datos a las tablas es lo más rápido que existe.

IMPORTANTE:

Un archivo externo no puede tener Primary Key, ni Foreign Key, ni cualquier clase de índices. ¿Por qué no? porque no tiene sentido, ya que puede ser abierto por cualquier programa (como el Bloc de notas del Windows, por ejemplo) y por lo tanto está afuera del control del Firebird. Las restricciones y los índices solamente tienen sentido si están bajo el control del Firebird.

Tampoco se justifica especificar que una columna será NOT NULL porque en un archivo externo todas las columnas siempre son NOT NULL, así que declarar a una columna como NOT NULL es innecesario.

Conclusión:

Tener la posibilidad de exportar datos en archivos externos puede ser muy importante en algunas ocasiones, con Firebird es muy fácil hacerlo, solamente debemos recordar que a esos archivos solamente se le pueden insertar datos, no se puede modificar esos datos ni borrarlos. Y también hay que recordar que los archivos externos no pueden tener restricciones ni índices porque como son externos entonces pueden ser abiertos por otros programas y en ese caso se encontrarían afuera del control del Firebird.

Artículos relacionados:

Insertando una gran cantidad de filas

El índice del blog Firebird21

El comando INSERT

Deja un comentario

El comando INSERT (insertar) se utiliza para agregarle nuevas filas a una tabla. Tiene dos sintaxis:

Sintaxis 1:

INSERT INTO tabla | vista (<lista de columnas>)
VALUES (<lista de valores>)
[RETURNING <lista de columnas a retornar>
[INTO <lista con el nombre de las variables>]]

Se utiliza cuando se quiere agregar una fila a la tabla o vista.

Ejemplo:

INSERT INTO ALUMNOS
        (ALU_CODIGO, ALU_NOMBRE, ALU_APELLD)
VALUES  ('1253', 'MIRTHA', 'ROMERO');

Sintaxis 2:

INSERT INTO tabla | vista (<lista de columnas>)
SELECT <lista de valores> FROM ...
[RETURNING <lista de columnas a retornar>
[INTO <lista con el nombre de las variables>]]

 Ejemplo:

INSERT INTO GERENTES
         (GER_CODIGO, GER_NOMBRE)
   SELECT E.EMP_CODIGO, E.EMP_NOMBRE FROM EMPLEADOS E WHERE E.EMP_CARGOX = 'GERENTE'

Esta segunda sintaxis se utiliza cuando se quieren agregar muchas filas de una sola vez.

Si los valores que queremos agregarle a una tabla se encuentran en otra u otras tablas, esta segunda sintaxis es la adecuada porque es muy rápida.

Artículo relacionado:

El índice del blog Firebird21

Older Entries