Buscando texto eficientemente dentro de un string grande

1 comentario

Cuando necesitamos buscar un texto dentro de un string podemos usar LIKE ‘%TextoBuscado%’ o también podemos usar CONTAINING. Ambos funcionarán muy bien, pero tienen un problema: si la cantidad de filas es muy grande o la columna donde puede encontrarse el texto que buscamos tiene muchos caracteres, puede ser muy lento, a veces inclusive extremadamente lento.

La idea para este artículo la obtuve de aquí:

https://blog.cincura.net/233577-poor-mans-full-text-using-psql-only-on-firebird/

Listado 1.

SELECT
   *
FROM
   MiTabla
WHERE
   MiColumna LIKE '%Asunción%'

Listado 2.

SELECT
   *
FROM
   MiTabla
WHERE
   MiColumna CONTAINING 'Asunción'

Tanto el SELECT del Listado 1. como el SELECT del Listado 2. harán bien su trabajo, pero como ya dijimos antes, si la cantidad de filas de MiTabla es muy grande o la cantidad de caracteres en MiColumna es muy grande, pueden ser muy lentos. ¿Por qué? Porque el Firebird nunca usará un índice en esos casos y por lo tanto deberá buscar secuencialmente en cada columna de cada fila la palabra que deseamos encontrar.

Si necesitamos gran velocidad en la búsqueda entonces debemos emplear otro aprovechamiento: usar un diccionario de palabras.

La idea es la siguiente: cada palabra que aparece en la columna MiColumna la guardamos en otra tabla, junto con su correspondiente Identificador. A esa columna le definiremos un índice y de esa manera las búsquedas serán rapidísimas.

Desde luego que hacer eso solamente se justificará si las búsquedas realizadas mediante el Listado 1. o el Listado 2. son lentas, en otro caso no vale la pena tomarse la molestia de hacerlo.

Para que nuestra técnica sea más inteligente también debemos tener en cuenta que a veces los usuarios se equivocan al escribir las palabras que están buscando. Por ejemplo, quieren buscar ‘Asunción’ pero escribieron ‘Asuncion’; o sea, sin el acento sobre la letra ‘o’. O escribieron ‘ASUNCION’, o sea todo en mayúsculas; o escribieron ‘Asumción’, o sea que en lugar de la letra ‘n’ pusieron la letra ‘m’.

Entonces lo que haremos será lo siguiente:

  1. Crear un dominio que acepte tanto mayúsculas como mínusculas (case insensitive) y que acepte tanto palabras acentuadas como no acentuadas (accent insensitive)
  2. Crear una tabla donde se guardarán las palabras y en la cual usaremos el dominio creado
  3. Crear un índice normal que usaremos para buscar palabras normales
  4. Crear un índice inverso que usaremos para buscar palabras invertidas
  5. Crear un stored procedure seleccionable que servirá para extraer todas las palabras de un texto
  6. Crear un trigger que ejecutará al stored procedure seleccionable y luego guardará cada palabra distinta en la tabla, junto con el Identificador de la fila donde se encuentra

Paso 1. Crear un dominio

Listado 3.

CREATE DOMAIN D_PALABRAS_CI_AI 
AS VARCHAR(40) 
CHARACTER SET ISO8859_1
COLLATE ES_ES_CI_AI;

Definimos este dominio como VARCHAR(40) porque suponemos que ninguna de las palabras tendrá más de 40 caracteres, si eso pudiera ocurrir entonces tendrías que aumentar el tamaño.

Paso 2. Crear la tabla donde se guardarán las palabras

palabras01

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

Nuestra tabla (bastante apropiadamente) se llama DICCIONARIO porque contendrá una lista de palabras.

En la columna DIC_TABLAX se guardará el nombre de la tabla que contiene las palabras que podríamos buscar. Eso porque podríamos tener varias tablas en las cuales sería útil realizar esta búsqueda.

En la columna DIC_COLUMN se guardará el nombre de la columna que contiene las palabras que podríamos buscar. Eso porque dentro de una misma tabla podríamos tener varias columnas que nos interesan.

En la columna DIC_IDECAB se guardará el Identificador de la fila que corresponde a la tabla DIC_TABLAX. De esa manera podremos saber en cual fila de la tabla DIC_TABLAX se encuentra la palabra buscada.

En la columna DIC_PALABR se guardará la palabra que puede ser buscada.

Paso 3. Crear un índice normal

Listado 4.

CREATE INDEX IDX_DICCIONARIO ON DICCIONARIO COMPUTED BY (DIC_TABLAX || DIC_COLUMN || UPPER(DIC_PALABR)
);

Paso 4. Crear un índice inverso

Listado 5.

CREATE INDEX IDX_DICCIONARIO1 ON DICCIONARIO COMPUTED BY (DIC_TABLAX || DIC_COLUMN || REVERSE(UPPER(DIC_PALABR))
);

Paso 5. Crear un stored procedure seleccionable

Listado 6.

CREATE PROCEDURE SPG_HALLAR_PALABRAS(
   ftcTexto VARCHAR(32765))
RETURNS(
   ftcPalabra TYPE OF D_NOMBRE40)
AS
   DECLARE VARIABLE lnI        SMALLINT;
   DECLARE VARIABLE lnInicio   SMALLINT;
   DECLARE VARIABLE lnLongitud SMALLINT;
BEGIN

   lnI        = 1;
   lnInicio   = 1;
   ftcTexto   = ftcTexto || ' ';
   lnLongitud = CHARACTER_LENGTH(ftcTexto);

   WHILE (lnI <= lnLongitud) DO BEGIN
      IF(CAST(SUBSTRING(ftcTexto FROM lnI FOR 1) AS D_PALABRAS_CI_AI) NOT SIMILAR TO '[[:ALNUM:]]' AND POSITION(SUBSTRING(ftcTexto FROM lnI FOR 1) IN 'áéíóúñÁÉÍÓÚÑ') = 0) THEN BEGIN
         IF(lnI > lnInicio) THEN BEGIN
            ftcPalabra = SUBSTRING(ftcTexto FROM lnInicio FOR lnI - lnInicio);
            SUSPEND;
         END
         lnInicio = lnI + 1;
      END
      lnI = lnI + 1;
   END
END;

El stored procedure SPG_HALLAR_PALABRAS hallará cada una de las palabras contenidas en el texto que se le envíe como parámetro de entrada. Veamos algunos ejemplos:

Listado 7.

SELECT
   *
FROM
   SPG_HALLAR_PALABRAS('Hoy es un día lluvioso')

Listado 8.

SELECT
   *
FROM
   SPG_HALLAR_PALABRAS('        Hoy es un día lluvioso')

Listado 9.

SELECT
   *
FROM
   SPG_HALLAR_PALABRAS('       Hoy         es         un día lluvioso')

Listado 10.

SELECT
   *
FROM
   SPG_HALLAR_PALABRAS('   Hoy es un        día lluvioso            ')

Tanto si ejecutamos el Listado 7., como el Listado 8., como el Listado 9., como el Listado 10., siempre obtendremos el mismo resultado, aún cuando a la frase original se le hayan agregado espacios en blanco al principio, en el medio, y al final del texto:

palabras02

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

Paso 6. Crear un trigger 

Listado 11.

CREATE TRIGGER PRODUCTOS_BIUD FOR PRODUCTOS
   ACTIVE BEFORE INSERT OR UPDATE OR DELETE
   POSITION 1
AS
BEGIN

   -- Se borran las filas que se habían insertado correspondientes a este Producto

   IF (UPDATING OR DELETING) THEN
      DELETE FROM
         DICCIONARIO
      WHERE
         DIC_TABLAX = 'PRODUCTOS' AND
         DIC_COLUMN = 'PRD_NOMBRE' AND
         DIC_IDECAB = OLD.PRD_IDENTI;

   -- Se insertan las nuevas filas, una fila por cada palabra de la columna PRD_NOMBRE

   IF (INSERTING OR UPDATING) THEN BEGIN
      INSERT INTO DICCIONARIO (DIC_TABLAX, DIC_COLUMN, DIC_IDECAB, DIC_PALABR)
         SELECT 'PRODUCTOS', 'PRD_NOMBRE', NEW.PRD_IDENTI, ftcPALABRA FROM SPG_HALLAR_PALABRAS(NEW.PRD_NOMBRE);

   END

END;

Para cada tabla que nos interese deberemos crear un trigger similar al mostrado en el Listado 11., de esa manera cada vez que se realice un INSERT, un UPDATE, o un DELETE a alguna fila de esa tabla que nos interesa, se actualizará también la tabla DICCIONARIO.

En el Listado 11. la tabla que nos interesa se llama PRODUCTOS, y dentro de esa tabla la columna que nos interesa se llama PRD_NOMBRE.

Eso significa que cada una de las palabras que coloquemos en la columna PRD_NOMBRE se insertará en la tabla DICCIONARIO para que podamos buscarla muy rápidamente.

Agregando filas a la tabla DICCIONARIO

Para verificar que todo funciona bien le agregaremos algunas filas a la tabla PRODUCTOS y al hacerlo también le estaremos agregando filas a la tabla DICCIONARIO.

Listado 12.

INSERT INTO PRODUCTOS (PRD_CODIGO, PRD_NOMBRE) VALUES('CC267', 'COCA COLA DE 1 LITRO RETORNABLE');
INSERT INTO PRODUCTOS (PRD_CODIGO, PRD_NOMBRE) VALUES('CP389', 'CERVEZA PILSEN DE 750 C.C.');
INSERT INTO PRODUCTOS (PRD_CODIGO, PRD_NOMBRE) VALUES('LT224', 'Leche Trébol de 1 litro descremada');
INSERT INTO PRODUCTOS (PRD_CODIGO, PRD_NOMBRE) VALUES('CB357', 'Cerveza BUDWEISER 66 DE 1 ÑITRO');

Al insertar esas filas a la tabla PRODUCTOS nuestra tabla DICCIONARIO quedó así:

palabras03

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

Verificando que funciona

Ahora que tenemos todo listo lo único que nos falta comprobar es que funcione bien.

Mirando la Captura 3. podemos ver que la palabra “LITRO” está escrita en mayúsculas, en minúsculas, y también mal escrita (en la fila 24 dice “ÑITRO” en lugar de “LITRO”).

Entonces, ¿cómo podemos obtener rápidamente las 3 filas donde se encuentra la palabra ‘LITRO’?

Listado 13.

SELECT
   *
FROM
   DICCIONARIO
WHERE
   DIC_TABLAX || DIC_COLUMN || UPPER(DIC_PALABR) STARTING WITH 'PRODUCTOS' || 'PRD_NOMBRE' || 'LITRO' OR
   DIC_TABLAX || DIC_COLUMN || REVERSE(UPPER(DIC_PALABR)) STARTING WITH 'PRODUCTOS' || 'PRD_NOMBRE' || REVERSE('ITRO') OR
   DIC_TABLAX || DIC_COLUMN || REVERSE(UPPER(DIC_PALABR)) STARTING WITH 'PRODUCTOS' || 'PRD_NOMBRE' || REVERSE('TRO') OR
   DIC_TABLAX || DIC_COLUMN || REVERSE(UPPER(DIC_PALABR)) STARTING WITH 'PRODUCTOS' || 'PRD_NOMBRE' || REVERSE('RO')

Si ejecutamos el Listado 13., obtendremos:

palabras04

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

O sea, que tenemos todas las apariciones de la palabra ‘LITRO’ aún aquella que está mal escrita. Justamente para eso sirven las 3 últimas condiciones puestas en la cláusula WHERE, aquellas que usan la función REVERSE(). En el primer caso escribimos ‘ITRO’, eso significa que la primera letra puede estar mal escrita. En el segundo caso escribimos ‘TRO’, eso significa que las primeras dos letras pueden estar mal escritas. En el tercer caso escribimos ‘RO’, eso significa que las primeras tres letras pueden estar mal escritas.

¿Y qué tan eficiente es nuestro SELECT?

Veamos el PLAN que ejecutó el Firebird para saberlo.

palabras05

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

Como se puede ver en la Captura 5. para la primera línea de la cláusula WHERE usará el índice IDX_DICCIONARIO, y para la segunda, la tercera, y la cuarta líneas, usará el índice IDX_DICCIONARIO1.

O sea, exactamente lo que queríamos conseguir.

Veamos ahora la eficiencia del Listado 13. en forma gráfica.

palabras06

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

Está perfecto. Hay 3 filas que cumplen con la condición impuesta en la cláusula WHERE y hay 3 filas extraídas usando los índices.

Conclusión:

Normalmente podemos usar LIKE ‘%MiTextoBuscado%’ o CONTAINING ‘MiTextoBuscado’ cuando queremos obtener las filas que tienen a ‘MiTextoBuscado’ en ellas. Sin embargo, hay ocasiones en que usar dichas sub-cláusulas puede ser extremadamente lento: funciona bien, pero son muy lentas.

Para esos casos lo conveniente es tener un diccionario de palabras, que servirá para indicarnos en cuales filas está el texto que buscamos.

En este artículo hemos visto un método que podemos utilizar para conseguir nuestro objetivo: búsquedas muy rápidas del texto, aunque la tabla tenga muchísimas filas o aunque tenga columnas que contienen muchísimas palabras.

Artículos relacionados:

https://blog.cincura.net/233577-poor-mans-full-text-using-psql-only-on-firebird/

La función REVERSE()

El índice del blog Firebird21

El foro del blog Firebird21

 

 

Optimizando un SELECT que compara columnas de la misma tabla (2)

6 comentarios

En este artículo:

Optimizando un SELECT que compara columnas de la misma tabla

vimos una técnica para optimizar los SELECT que comparan columnas de la misma tabla. La ventaja de esa técnica es que funcionará con cualquier motor SQL que utilicemos. Pero con Firebird tenemos además otra posibilidad, que es mejor que la anterior: usar índices de expresiones.

Nuestra tabla PRODUCTOS tiene la siguiente estructura:

optimizando1

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

Y podríamos crear un índice de expresión como el siguiente:

Listado 1.

CREATE INDEX IDX_PRODUCTOS ON PRODUCTOS COMPUTED BY (PRD_PREVTA - PRD_PRECTO);

Y nuestro SELECT tendría que ser así:

Listado 2.

SELECT
   *
FROM
   PRODUCTOS
WHERE
   PRD_PREVTA - PRD_PRECTO < 0

Donde la condición puesta en el WHERE tiene que ser igual que la expresión entre paréntesis en el Listado 1. Si no son iguales, el índice no será usado.

Si ahora miramos el rendimiento obtenido:

optimizando2

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

Veremos que efectivamente se ha usado el índice de expresión que creamos.

Las ventajas de usar un índice de expresión son:

  1. No necesitas crear una columna adicional
  2. No necesitas ejecutar un UPDATE para actualizar el contenido de la columna adicional
  3. No necesitas escribir un trigger que se dedique a actualizar el contenido de la columna adicional

Conclusión:

Usar un índice de expresión nos facilita el trabajo cuando necesitamos poner en la cláusula WHERE una condición que compara columnas, pero como todo índice hacemos trabajar más al motor cada vez que se realiza un INSERT, un UPDATE, o un DELETE en la tabla, así que debemos sopesar las ventajas y las desventajas de utilizarlo.

Artículos relacionados:

Optimizando un SELECT que compara columnas de la misma tabla

El índice del blog Firebird21

El foro del blog Firebird21

 

 

 

Optimizando un SELECT que compara columnas de la misma tabla

3 comentarios

En general, debemos tener a todas las columnas de todas nuestras tablas normalizadas. Eso es lo correcto y es lo recomendable. Sin embargo, hay ocasiones en que desnormalizar las columnas es conveniente.

Una de esas ocasiones es cuando debemos escribir un SELECT que en la cláusula WHERE compara el contenido de dos columnas. Veamos un ejemplo.

Tenemos la tabla PRODUCTOS con la siguiente estructura:

optimizando1

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

Y queremos saber si hay productos cuyo precio de venta es menor que su precio de costo, así que escribimos el siguiente SELECT.

Listado 1.

SELECT
   *
FROM
   PRODUCTOS
WHERE
   PRD_PREVTA < PRD_PRECTO

La consulta nos mostrará el resultado correcto, pero si analizamos su rendimiento, encontraremos que no ha usado un índice.

optimizando2

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

El problema es que no podemos tener un índice que pueda ser usado en casos como este. O sea, que no podemos tener índices para:

  • Comparar dos columnas de la misma tabla por =
  • Comparar dos columnas de la misma tabla por <
  • Comparar dos columnas de la misma tabla por >
  • Comparar dos columnas de la misma tabla por <=
  • Comparar dos columnas de la misma tabla por >=
  • Comparar dos columnas de la misma tabla por <>

Si la tabla tiene pocas filas, eso no es un problema, Firebird es muy rápido para devolver el resultado de los SELECT. Pero si la tabla tiene muchas filas, allí ya es otro tema.

¿Y cómo podemos hacer para mejorar la velocidad de nuestro SELECT?

La solución es crear una columna que contenga la diferencia entre las dos columnas que nos interesan. La estructura de la tabla PRODUCTOS quedaría entonces así:

optimizando3

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

Para mantener actualizada a la columna PRD_DIFERE podríamos escribir un trigger como el siguiente:

Listado 2.

CREATE TRIGGER PRODUCTOS_BIU FOR PRODUCTOS
   ACTIVE BEFORE
   INSERT OR
   UPDATE
   POSITION 1
AS
BEGIN

   NEW.PRD_DIFERE = NEW.PRD_PREVTA - NEW.PRD_PRECTO;

END;

Y para usar un índice entonces deberemos crearlo.

Listado 3.

CREATE INDEX IDX_PRODUCTOS ON PRODUCTOS(PRD_DIFERE);

Y si ahora escribimos el SELECT del Listado 1. modificado para que utilice a la columna PRD_DIFERE, tendríamos:

Listado 4.

SELECT
   *
FROM
   PRODUCTOS
WHERE
  PRD_DIFERE < 0

Queremos verificar si ahora se está usando un índice, así que miramos el rendimiento y encontramos:

optimizando4

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

Y comprobamos que sí, efectivamente ahora se usa un índice, y por lo tanto nuestro SELECT será mucho más rápido que antes.

Conclusión:

En general debemos tener a todas las columnas de todas nuestras tablas normalizadas, pero hay excepciones, como el caso mostrado en este artículo. Eso se debe a que el Firebird no utiliza índices cuando comparamos el contenido de una columna con el contenido de otra columna. La solución es crear una columna adicional que contendrá la diferencia entre los valores de las columnas que necesitamos comparar.

Desde luego que comparar precio de costo con precio de venta es sólo un ejemplo. También podemos comparar importe vendido contra importe cobrado, importe comprado contra importe pagado, etc.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

DSN de archivo con EXCEL

Deja un comentario

En los ejemplos vistos hasta aquí, para enviar el contenido de las tablas o de las vistas de una Base de Datos del Firebird a Excel usábamos un DSN de usuario previamente definido. Funcionaba bien, pero nos obligaba a definir previamente ese DSN.

DSN01

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

Ahora veremos una alternativa que en muchos casos nos resultará mucho mejor: crear un DSN de archivo.

Creando un archivo .DSN

Los datos que el Excel necesita para conectarse a una Base de Datos del Firebird podemos colocarlos en un archivo de texto. Las características de ese archivo de texto son:

  1. El nombre puede ser cualquiera
  2. La extensión debe ser .DSN
  3. La primera línea debe ser [ODBC]
  4. En las siguientes líneas deben colocarse los parámetros de conexión

DSN02

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

Como podemos ver en la Captura 2., en nuestro archivo de texto llamado CONSULTA-EXCEL.DSN hemos especificado todos los parámetros que Excel necesitará saber para poder conectarse a nuestra Base de Datos del Firebird.

Usando el archivo .DSN en Excel

Estando en Excel, hacemos clic en Datos | De otras fuentes | Desde Microsoft Query

DSN03

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

Y en el cuadro de diálogo “Elegir origen de datos” hacemos clic sobre el botón “Examinar…”, como vemos en la Captura 4.

DSN04

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

DSN05

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

Y buscamos en nuestro disco duro el archivo .DSN que nos interesa.

Si el Excel no puede localizar al archivo GDS32.DLL o al archivo FBCLIENT.DLL, entonces verás la ventanita mostrada en la Captura 6.

DSN06

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

¿La solución?

Copiar el archivo FBCLIENT.DLL en la misma carpeta en donde guardamos a nuestro archivo .DSN, o guardar nuestro archivo .DSN en la carpeta donde se encuentra el archivo FBCLIENT.DLL

En Windows se aconseja que tengas al archivo FBCLIENT.DLL en la misma carpeta en donde tienes a tu archivo .EXE

Y por lo tanto, a tu archivo .DSN también lo guardarás en esa carpeta.

A partir de aquí, se continúa como habíamos visto en los artículos:

Planillas EXCEL dinámicas con Firebird (1)

Planillas EXCEL dinámicas con Firebird (2)

Ventaja de usar un DSN de archivo

La ventaja es que ese DSN de archivo (que es un simple archivo de texto) puedes crearlo con tu aplicación, y eso es muy útil cuando la cantidad de bases de datos es grande o es variable.

Si tu usuario siempre se conectará a la misma Base de Datos, entonces podrías crear un DSN de usuario y listo, asunto solucionado. Pero si puede conectarse a muchas bases de datos o si se irán creando nuevas bases de datos lo conveniente es crear un DSN de archivo desde tu aplicación. Este caso puede darse, por ejemplo, si tienes una aplicación contable y para cada cliente del estudio contable se crea una Base de Datos. Un estudio contable puede tener decenas o centenas de bases de datos.

Artículos relacionados:

Usando EXCEL para leer bases de datos de Firebird

Planillas EXCEL dinámicas con Firebird (1)

Planillas EXCEL dinámicas con Firebird (2)

Enviando una consulta a EXCEL

El índice del blog Firebird21

El foro del blog Firebird21

Planillas EXCEL dinámicas con Firebird (2)

7 comentarios

Si consultar desde Excel los datos de una tabla o de una vista perteneciente a una Base de Datos del Firebird resultó útil, que al cambiar el valor de algunas celdas en la planilla Excel esa planilla se actualice puede ser aún más útil.

Veamos como hacerlo.

Primero, creamos una nueva planilla y definimos las celdas que se usarán como parámetros de la consulta, y colocamos el cursor donde queremos insertar la tabla.

EXCEL01

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

En este ejemplo, las celdas B1, B2, y B3 contendrán los parámetros y la tabla que crearemos se insertará a partir de la celda A5.

Luego vamos a: Datos | De otras fuentes | Desde Microsoft Query

EXCEL02

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

Luego, elegimos el origen de datos. Ya habíamos visto como crear un origen de datos en el artículo:

Planillas EXCEL dinámicas con Firebird (1)

Puedes referirte a ese artículo del blog si no recuerdas como crear un origen de datos.

EXCEL03

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

Al elegir como origen de datos a nuestra Base de Datos del Firebird nos mostrará todas las tablas y/o vistas que tengamos en esa Base de Datos.

EXCEL04

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

Si lo deseamos, a continuación podemos Filtrar datos y elegir un Criterio de ordenación.

 EXCEL05

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

En este último cuadro de diálogo elegimos la opción “Ver datos o modificar consulta en Microsoft Query”

 EXCEL06Captura 6. Si haces clic en la imagen la verás más  grande

Microsoft Query nos mostrará el resultado de consultar a nuestra tabla o a nuestra vista del Firebird. Ahora debemos decirle que queremos parametrizar esa consulta, para ello debemos hacer clic en el botón “Ocultar o mostrar los criterios”.

EXCEL07

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

A continuación, en los “Campos de criterios” debemos poner los nombres de las columnas (campos) de nuestra tabla o vista que usaremos para parametrizar. Y en valor debemos escribir cualquier nombre que queremos darle a las celdas donde se encuentran esos parámetros. Esos nombres deberán estar rodeados por corchetes para que sean reconocidos como variables. Por ejemplo: [moneda], [fechaini], [fechafin], etc.

 EXCEL08

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

Al hacer clic sobre la esquina superior derecha de una celda de “Campos de criterios” nos muestra un menú contextual con los nombres de todas las columnas de nuestra tabla o vista del Firebird. Debemos elegir alguna de esas columnas.

 EXCEL09

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

Como podemos ver en la Captura 9., aquí se eligió la columna ASC_MONEDA y se definió una variable llamada [moneda]. El nombre de la variable puede ser cualquiera, ese es sólo un ejemplo. Al salir de la celda apareció una ventanita de diálogo preguntando por el valor de la variable [moneda]. Debemos ignorarlo y dejarlo en blanco, simplemente hacemos clic sobre el botón “Aceptar” y continuamos.

 EXCEL10

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

En la Captura 10. vemos que elegimos otra columna más para el criterio, en este caso la columna es ASC_FECHAX y como queremos un rango de fechas entonces escribimos: “Entre [fechaini] y [fechafin]”. Como ya sabes, tanto [fechaini] como [fechafin] son variables.

Aún no le hemos dicho a Microsoft Query de donde debe obtener los valores de las variables [moneda], [fechaini] y [fechafin], eso lo haremos dentro de poco. Y recuerda que esos nombres de variables (o de parámetros) son de ejemplo, tú puedes elegir cualquier nombre que prefieras.

 EXCEL11

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

Después de haber elegido todos los criterios que deseamos debemos salir de Microsoft Query, para eso hacemos clic en el botón “Devolver datos”, tal como se muestra en la Captura 11.

EXCEL12

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

Todavía no le hemos dicho de que celdas debe obtener los valores de las variables [moneda], [fechaini], y [fechafin], eso lo haremos ahora. En el cuadro de diálogo “Importar datos” debemos hacer clic sobre el botón “Propiedades…”, como se muestra en la Captura 12.

EXCEL13

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

Luego hacemos clic en la pestaña “Definición” y en el botón “Parámetros…”, como se muestra en la Captura 13.

EXCEL14

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

¡¡¡Y allí están los nombres de todos los parámetros que habíamos definido!!!

EXCEL15

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

Como lo que queremos es que el valor de esos parámetros se tome de celdas, entonces debemos hacer clic sobre el botón de radio “Tomar el valor de la siguiente celda:” (1), luego clic en el campo de texto para que el cursor se quede ahí (2), luego hacemos clic sobre la celda que corresponde al parámetro [moneda] (3) y finalmente, para que la planilla Excel se actualice automáticamente al cambiar el valor de la celda marcamos la casilla de verificación “Actualizar automáticamente cuando cambie el valor de las celdas” (4).

EXCEL16

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

En el campo de texto, el valor: =Hoja1!$B$1 fue puesto por el Excel al hacer clic en la celda B1.

Lo mismo que se hizo para el parámetro [moneda] hay que hacer para los parámetros [fechaini] y [fechafin]. O sea, repetir desde la Captura 13.

EXCEL17

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

Una vez que ya le hemos indicado que los valores de los parámetros [moneda], [fechaini], y [fechafin] debe obtener de las celdas B1, B2, y B3, en el cuadro de diálogo “Importar datos” hacemos clic en el botón “Aceptar”, como se muestra en la Captura 17.

EXCEL18

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

¡¡¡Y listo!!!

Pero lo más interesante de todo viene ahora, si se cambia el valor de las celdas B1, B2, o B3, el contenido de la planilla Excel … ¡¡¡también cambia!!!

EXCEL19

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

 EXCEL20Captura 20. Si haces clic en la imagen la verás más  grande

Al cambiar el valor de cualquiera de las celdas: B1, B2, o B3, la planilla Excel cambia acordemente. ¿Verdad que está muy bueno eso?

Conclusión:

Con lo visto en el artículo anterior, más lo visto en este artículo, ya conocemos como darle un muy alto valor agregado a nuestras aplicaciones, porque podemos enviar el contenido de nuestras tablas y de nuestras vistas de una Base de Datos del Firebird a una planilla Excel y tener a esos datos actualizados. Y Excel es la herramienta administrativa más usada en todo el mundo.

Por supuesto que esto no termina aquí, hay aún muchísimo por aprender pero con estos dos artículos ya tienes más que suficiente para empezar. En Internet hay muchísimo material y deberías aprovechar eso.

Artículos relacionados:

Usando EXCEL para leer Bases de Datos de Firebird

Planillas EXCEL dinámicas con Firebird (1)

El índice del blog Firebird21

El foro del blog Firebird21

Planillas EXCEL dinámicas con Firebird (1)

Deja un comentario

Si queremos darle un muy alto valor agregado a nuestras aplicaciones entonces podemos proveerles de planillas Excel dinámicas. A nuestros clientes les encantará eso.

¿Qué es una planilla Excel dinámica?

Una planilla cuyos datos no fueron introducidos a mano sino que provienen de una fuente externa, en nuestro caso de una Base de Datos de Firebird.

Pero lo interesante aquí es que si cambian los datos de nuestra Base de Datos, también cambia la planilla Excel, automáticamente. Y si cambiamos el valor de algunas celdas en nuestra planilla Excel, entonces obtendremos una planilla Excel actualizada.

Muy interesante ¿verdad?

En este primer artículo de la serie veremos como obtener datos de las tablas y de las vistas que tenemos en nuestra Base de Datos de Firebird. En el siguiente artículo veremos algo quizás más interesante: cambiar el valor de una celda en la planilla Excel y que la planilla se actualice automáticamente.

EXCEL01

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

El Diagrama 1. nos muestra el proceso.

  1. Es nuestra Base de Datos creada con Firebird
  2. Usamos OLEDB u ODBC para enviar los datos
  3. Un archivo de conexión define  toda la información que se necesita para acceder y recuperar los datos de nuestra Base de Datos
  4. La información de la conexión es copiada desde el archivo de conexión hacia la planilla Excel
  5. Los datos son copiados dentro de la planilla Excel y ya pueden ser usados como si hubieran sido introducidos manualmente

Usando ODBC para la conexión

  1. El driver ODBC de Firebird debe estar instalado en la computadora donde se encuentra la Base de Datos
  2. Hay que definir un DSN (Data Source Name) para que la conexión pueda realizarse: Inicio | Panel de Control | Herramientas administrativas | Orígenes de datos ODBC | DSN de usuario | Agregar … | Firebird/Interbase driver | completar los datos pedidos

EXCEL02

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

Para verificar que la conexión puede realizarse exitosamente es aconsejable hacer clic sobre el botón “Probar conexión”. Si todo está ok veremos algo así:

EXCEL03

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

Conectando la planilla Excel a la Base de Datos de Firebird

  1. Abrir Excel
  2. Crear una nueva planilla
  3. Datos | De otras fuentes | Desde Microsoft Query | elegimos el DSN de nuestra Base de Datos

EXCEL04

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

EXCEL05

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

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

Como podemos ver en la Captura 5., el Excel nos muestra los nombres de todas las tablas y de todas las vistas que se encuentran en nuestra Base de Datos. Para ver solamente tablas o solamente vistas, podemos hacer clic en Opciones, como vemos a continuación:

EXCEL19

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

y si hacemos clic sobre el signo + que se encuentra a la izquierda de las tablas y de las vistas, veremos los nombres de las columnas pertenecientes a la tabla o a la vista elegida.

EXCEL07

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

Para elegir cuales de esas columnas queremos ver en nuestra planilla Excel, la seleccionamos y luego hacemos clic en el botón que la trasladará a “Columnas en la consulta:”

EXCEL08

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

A continuación podemos Filtrar los datos y elegir el Criterio de ordenación y pedirle que muestre los datos en una planilla Excel.

EXCEL09

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

EXCEL10

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

Y ahora viene lo más interesante de todo, si elegimos “Informe de tabla dinámica” o si elegimos “Informe de gráfico y tabla dinámicos” entonces cada vez que se cambien los datos de nuestra Base de Datos también se cambiarán en la planilla Excel.

Algunos puntos a considerar

a) Los datos en la tabla dinámica están resumidos, o agrupados por filas. Varias filas de los datos originales ocuparán una sola fila en la planilla Excel

EXCEL11

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

EXCEL12

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

En la Captura 11. podemos ver los datos originales que tenemos en la tabla de nuestra Base de Datos. En la Captura 12. vemos la planilla Excel que fue creada a partir de esos datos. Como puedes ver, las filas se agruparon por fechas, y en las columnas numéricas se muestran las sumas de dichas columnas. Eso muchas veces no tiene sentido (como sumar los años en la columna ASC_ANOEJE, año del ejercicio contable, por ejemplo) pero es lo que hace Excel.

Para ver las filas originales, no agrupadas, podemos hacer clic sobre una de las filas de la planilla, clic con el botón derecho, y luego elegir la opción “Mostrar detalles”.

EXCEL13

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

En este caso, como el cursor está sobre la fila que tiene fecha 03/01/2014, las filas de esa fecha que tenemos en nuestra tabla original serán las mostradas.

 EXCEL14

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

b) Los resultados se crean en una nueva hoja. Un detalle a notar, es que el resultado de esa consulta fue creado en una nueva hoja de la planilla, no en la hoja que estábamos usando.

c) Se puede ver el detalle de todas las filas importadas. Para conseguirlo, arrastramos los nombres de las columnas que se encuentran en “Etiquetas de fila” hacia “Etiquetas de columna”.

EXCEL17

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

EXCEL18

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

y luego continuamos igual que en la Captura 13.

Actualizando la planilla Excel

Por defecto, el contenido de la tabla dinámica que creamos en Excel no se actualiza cuando cambian los datos en el origen (o sea, en nuestra Base de Datos del Firebird)

Si el contenido de nuestra tabla en la Base de Datos de Firebird cambió, podemos mostrar los datos actualizados en la planilla Excel. Tenemos tres opciones:

a) Actualización manual. Colocamos el mouse en cualquier celda de la tabla que insertamos y luego clic con el botón derecho y elegimos la opción Actualizar

EXCEL15

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

Si después de hacer clic en Actualizar volvemos a pedirle que nos muestre los detalles, como ya vimos en la Captura 13., entonces nuestra planilla Excel mostrará los datos actualizados.

EXCEL16

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

EXCEL23

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

También se puede actualizar manualmente la planilla Excel haciendo clic sobre cualquier celda de la tabla dinámica, luego en Opciones | Actualizar | Actualizar o Actualizar todo

b) Actualización automática. También podemos conseguir que la planilla Excel se actualice automáticamente, para ello hacemos clic en cualquier celda de la tabla dinámica creada, luego Opciones | Cambiar origen de datos | Propiedades de conexión … | elegimos el tiempo deseado

EXCEL20

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

En la Captura 20. se pide que la planilla Excel sea actualizada cada 1 minuto, desde luego que tú puedes elegir otro tiempo, esto es sólo un ejemplo.

IMPORTANTE: La hoja creada con “Mostrar detalles”, tal como vimos en la Captura 13. no cambiará, solamente cambia la hoja original.

EXCEL21

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

EXCEL22

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

En la Captura 21. vemos el contenido original de la tabla, se cambió un valor en la columna ASC_COTIZA en la Base de Datos del Firebird y un minuto después vemos que la planilla Excel se actualizó automáticamente, tal como podemos observar en la Captura 22.

c) Actualizar al abrir la planilla Excel. Si queremos que cada vez que se abra la planilla Excel muestre a sus datos actualizados, debemos marcar la casilla “Actualizar al abrir el archivo” que se muestra en la Captura 20.

Conclusión:

Poder enviar el resultado de consultar a nuestras tablas y a nuestras vistas de una Base de Datos del Firebird a una planilla Excel les resultará muy útil a nuestros clientes. Las tablas dinámicas muestran los datos agrupados y permiten que esos datos se actualicen cuando la tabla subyacente del Firebird se actualice. Eso es muy bueno para poder tomar buenas decisiones empresariales.

Hay muchísimo más que aprender sobre las tablas dinámicas de Excel. Es muy aconsejable que busques información en Internet sobre ellas, te resultará muy útil para que tus aplicaciones se destaquen sobre la competencia.

Artículos relacionados:

Tablas dinámicas en Excel

El índice del blog Firebird21

El foro del blog Firebird21

Una tabla para encontrar lo que necesitas

Deja un comentario

Muchas veces sucede que hemos leído algo, o hemos escrito algo, … pero no recordamos donde está. A todos nos puede pasar, nadie tiene una memoria infalible. Pues bien, para ayudarnos a encontrar lo que necesitamos podemos usar a Firebird.

La idea es la siguiente: en una tabla escribimos los datos que nos ayudarán a encontrar la información que alguna vez pueda ser relevante.

Por ejemplo, supongamos que queremos fácilmente buscar y encontrar todo lo referente a Firebird. Para ello, crearemos una tabla llamada FIREBIRD_DOC

FDC01

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

La columna FDC_TIPINF (tipo de información guardada) puede tener estos valores:

E=Enlace
S=Stored procedure
T=Texto

Y para asegurarnos que ningún otro valor se pueda insertar, nuestro dominio D_TIPO_INFORMACION está definido como:

CREATE DOMAIN D_TIPO_INFORMACION AS
   CHAR(1)
   CHECK (VALUE IN ('E', 'S', 'T'));

Una vez que le insertamos datos a nuestra tabla tendremos algo como esto:

FDC02

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

 Y si por ejemplo queremos saber como averiguar si un año es bisiesto o no lo es, podemos escribir algo como:

SELECT
   FDC_CONTEN
FROM
   FIREBIRD_DOC
WHERE
   FDC_CONTEN CONTAINING 'BISIESTO'

Como la columna FDC_CONTEN es de tipo BLOB para ver su contenido hay que copiarlo como texto plano. Por ejemplo si usas el EMS SQL Manager debes hacer clic con el botón derecho sobre el contenido de esa columna y luego elegir la opción “Copy cell”

FDC03

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

A continuación hay que “pegar” el contenido de esa celda en un archivo de texto plano, por ejemplo en el Bloc de  Notas del Windows.

FDC04

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

 Y listo, nuestro “ayuda memoria” ha funcionado perfectamente.

Conclusión:

Nadie tiene una memoria infalible y ya que sabemos como utilizar una Base de Datos podemos usar ese conocimiento para facilitarnos las tareas de búsqueda de la información.

En este ejemplo, nuestra tabla FIREBIRD_DOC sirve para guardar toda la documentación relevante a Firebird en ella para que cuando necesitemos algo no debamos estar buscando en Internet (el cual no siempre tendremos disponible). Un simple SELECT a nuestra tabla nos permitirá encontrar lo que sea que estemos buscando (por supuesto, si previamente lo hemos insertado en la tabla).

Evidentemente podemos usar esta idea para guardar cualquier tipo de conocimiento que pueda sernos útiles: canciones, películas, libros, revistas, deportes, temas de interés, lo que sea.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

Older Entries