Inicio > Bases de Datos, Delphi, FireDAC > Eventos con SQL Server y FireDAC (Query Notifications)

Eventos con SQL Server y FireDAC (Query Notifications)

Share Button

QueryNotificationsLa mayoría de SGBD’s disponen de un sistema para poder utilizar a lo que habitualmente llamamos eventos o notificaciones, lo que ya no es tan habitual, es que los componentes de acceso implementen esta funcionalidad.

En este artículo me voy a centrar en el sistema que nos ofrece SQL Server (las “Query Notifications”) + FireDAC (TFDEventAlerter).

¿QUE SON LAS “QUERY NOTIFICATIONS”?

Resumiendo y simplificando el concepto, podemos decir que una aplicación cliente recibe una notificación desde el Gestor de Base de Datos, cuando el contenido de una consulta que definimos, cambia. En la práctica funcionaría de la siguiente manera.

Las “query notifications” nos permiten definir la siguiente consulta:

SELECT NOMBRE, APELLIDOS FROM CLIENTES

Y recibir una notificación cuando los datos involucrados en ese consulta (NOMBRE o APELLIDOS del cliente) cambian.

Con esta configuración recibiré una notificación siempre que se AÑADA o BORRE un registro de la tabla CLIENTES y también cuando se modifiquen los campos NOMBRE y APELLIDOS de esta tabla (no así el resto de campos de la tabla).

NOTIFICACIONES EN SQL SERVER

Las notificaciones en SQL Server se introducen en a partir de la versión 2005 (9.x). Y existen varias formas de implementar las notificaciones:

  • SQLNotificationRequest: Está programado a un nivel más bajo y es en cierta forma menos automático, ya que el usuario debe implementar parte de la estructura necesaria para hacerlas funcionar.
  • SQLDependency: Su implementación es a nivel más alto y el sistema es más automatizado. Se inicia un proceso que procesa las notificaciones que se publican en una cola.
  • SQLCacheDependency: Que se utiliza para aplicaciones Web diseñadas mediante ASP.NET 2.0 o posterior.

En nuestro caso nos vamos a centrar en SqlDependency que es el método que utiliza el componente TFDEventAlerter de las FireDAC.

Para poder utilizar las notificaciones es necesario tener activado “Service Broker” y los usuarios deben tener permisos para recibirlas.

Como primer paso para nuestras pruebas debemos crear la Base de Datos BDDemo en SQL server y consultaremos el estado del servicio para esta Base de Datos en concreto.

SELECT IS_BROKER_ENABLED FROM SYS.DATABASES WITH (NOLOCK) WHERE NAME = 'BDDemo'

O consultar el estado del servicio para todas las Bases de Datos que tenemos registradas:

 

SELECT IS_BROKER_ENABLED,NAME FROM SYS.DATABASES WITH (NOLOCK)

Por defecto la consulta nos devolverá 0 (desactivado). Para activar el servicio podemos lanzar la siguiente sentencia SQL:

 

ALTER DATABASE BDDemo SET ENABLE_BROKER;

Si consultamos ahora, deberíamos obtener un 1 (activado).

NOTA1: En la documentación de SQLServer nos avisa de que si la Base de Datos está activa y la estamos utilizando, es posible que esta consulta tarde en ejecutarse debido a transacciones activas (aquí podéis encontrar también más información al respecto). Para ello recomiendan forzar que las transacciones finalicen utilizando esta alternativa:

ALTER DATABASE BDDemo SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

NOTA2: En mi caso he llegado a la conclusión de que otra alternativa (si tenéis acceso de administración para el servidor) es reiniciar el servicio de SQLServer. De esta forma todas las operaciones pendientes se terminan y el resultado al ejecutar el ALTER es inmediato.

En este punto ya tenemos nuestra Base de Datos preparada.

COMPONENTE TFDEventAlerter (FireDAC)

El componente TFDEventAlerter de FireDAC, permite acceder de forma unificada, a los diferentes mecanismos que cada SGBD tiene para generar Alertas/Eventos.

A continuación se muestra una tabla con los diferentes gestores con los que el componente permite trabajar y una anotación de la forma en que se tratan los eventos para cada uno de ellos.

image

Información más detallada, podéis encontrarla en la Wiki de Embarcadero, en la referencia de este componente.

El componente utiliza un Thread en segundo plano para crear una conexión alternativa a la Base de Datos. Esta conexión es la que usa para “escuchar” y recibir las notificaciones. Si se usa más de un componente TFDEventAlerter, habría que considerar utilizar un pool de conexiones para mejorar el rendimiento.

¿CUANDO UTILIZAR Query Notifications?

Ahora es el momento de preguntarnos, ¿cuándo puede sernos útil esta característica?

Seguro que a los que lleváis trabajando con Bases de Datos unos años ya se os ha ocurrido alguna ocasión en que las notificaciones pueden ser útiles.

La primera que se me viene a la mente son aquellas ocasiones en que nuestra aplicación necesita y mantiene una caché de datos, que originalmente se almacenan en una Base de Datos. Recuerdo varias de mis aplicaciones que mantienen en memoria una copia de la tabla de configuración, por ejemplo.

Normalmente tablas que tienen pocos cambios durante la ejecución de la aplicación y que se consultan muy a menudo mientras se trabaja con ella.

No es una característica que se deba usar a “modo general” y de forma intensiva (para muchas/todas las tablas); No tendría sentido.

PREPARAR ENTORNO PARA PRUEBAS

Para realizar las pruebas desde cero, vamos a crear la tabla que utilizaremos en el ejemplo y rellenaremos con datos.

Captura_2019-06-12 09_42_27Ya lo he comentado en otras ocasiones, pero para estos casos recomiendo la web generatedata.com, que nos facilita la creación de las tablas necesarias y de los datos aleatorios para las pruebas. Además la salida permite varios formatos (CSV, Excel, HTML, JSON, SQL, XML) y las Bases de Datos ”destino” para la sintaxis también son variadas (MySQL, Postgres, SQLite, Oracle, SQLServer).

Para crear la tabla utilizaremos en siguiente código:

CREATE TABLE [CLIENTES] ( 
    [ID] INTEGER NOT NULL IDENTITY(1, 1), 
    [Nombre] VARCHAR(255) NULL, 
    [Apellidos] VARCHAR(255) NULL, 
    [Email] VARCHAR(255) NULL, 
    [Telefono] VARCHAR(100) NULL, 
    PRIMARY KEY ([ID]) 
 );

Para añadir datos podéis lanzar el siguiente Script:

 

INSERT INTO CLIENTES([Nombre],[Apellidos],[Email],[Telefono]) VALUES('Elvis','Riley','Proin.sed@sitametrisus.org','580.883.521');
INSERT INTO CLIENTES([Nombre],[Apellidos],[Email],[Telefono]) VALUES('Lael','Tyson','arcu.Vestibulum.ut@ligulaNullamenim.ca','519.350.417');
INSERT INTO CLIENTES([Nombre],[Apellidos],[Email],[Telefono]) VALUES('Wanda','Case','vulputate@malesuadavelvenenatis.net','169.695.303');
INSERT INTO CLIENTES([Nombre],[Apellidos],[Email],[Telefono]) VALUES('Colleen','Miranda','Fusce.fermentum@Nunc.com','419.743.554');
INSERT INTO CLIENTES([Nombre],[Apellidos],[Email],[Telefono]) VALUES('Hamilton','Rasmussen','nec.urna@leoelementum.co.uk','740.763.623');
INSERT INTO CLIENTES([Nombre],[Apellidos],[Email],[Telefono]) VALUES('Ainsley','Harper','non.lorem.vitae@Maecenasmalesuada.org','744.289.215');
INSERT INTO CLIENTES([Nombre],[Apellidos],[Email],[Telefono]) VALUES('Tasha','Burris','diam.Duis.mi@semNulla.com','699.116.452');
INSERT INTO CLIENTES([Nombre],[Apellidos],[Email],[Telefono]) VALUES('Brock','Stevenson','arcu.Sed.eu@lectussitamet.co.uk','322.701.219');
INSERT INTO CLIENTES([Nombre],[Apellidos],[Email],[Telefono]) VALUES('Davis','Solis','ligula@ridiculusmus.edu','964.437.058');
INSERT INTO CLIENTES([Nombre],[Apellidos],[Email],[Telefono]) VALUES('Baxter','Houston','nisl.elementum@malesuadautsem.org','521.366.536');
INSERT INTO CLIENTES([Nombre],[Apellidos],[Email],[Telefono]) VALUES('Raya','Norton','augue.scelerisque@hendreritDonecporttitor.ca','562.397.708');
INSERT INTO CLIENTES([Nombre],[Apellidos],[Email],[Telefono]) VALUES('Ori','Sloan','Cras.pellentesque@arcuVestibulumante.com','494.121.410');
INSERT INTO CLIENTES([Nombre],[Apellidos],[Email],[Telefono]) VALUES('Taylor','Salazar','amet@mauris.co.uk','990.897.769');
INSERT INTO CLIENTES([Nombre],[Apellidos],[Email],[Telefono]) VALUES('Shelly','Parker','mauris.blandit.mattis@loremDonecelementum.edu','539.937.227');
INSERT INTO CLIENTES([Nombre],[Apellidos],[Email],[Telefono]) VALUES('Idola','Tran','diam.luctus.lobortis@portaelit.co.uk','658.652.443');
INSERT INTO CLIENTES([Nombre],[Apellidos],[Email],[Telefono]) VALUES('Maia','Bonner','erat@sagittisaugueeu.co.uk','567.282.873');
INSERT INTO CLIENTES([Nombre],[Apellidos],[Email],[Telefono]) VALUES('Ainsley','Jefferson','a@ligulaDonecluctus.ca','427.881.031');
INSERT INTO CLIENTES([Nombre],[Apellidos],[Email],[Telefono]) VALUES('Mason','Rich','libero.Proin@ut.org','985.932.402');
INSERT INTO CLIENTES([Nombre],[Apellidos],[Email],[Telefono]) VALUES('Rahim','Gomez','fringilla.ornare.placerat@orciDonecnibh.com','967.799.731');
INSERT INTO CLIENTES([Nombre],[Apellidos],[Email],[Telefono]) VALUES('Lewis','Dodson','dis.parturient@luctus.edu','439.816.835');
INSERT INTO CLIENTES([Nombre],[Apellidos],[Email],[Telefono]) VALUES('Zoe','Alston','ligula.tortor@adipiscingelit.org','917.585.503');
INSERT INTO CLIENTES([Nombre],[Apellidos],[Email],[Telefono]) VALUES('Penelope','Meyers','commodo.auctor.velit@necimperdietnec.co.uk','339.743.915');
INSERT INTO CLIENTES([Nombre],[Apellidos],[Email],[Telefono]) VALUES('Rajah','Robbins','imperdiet@Duisdignissimtempor.com','182.728.195');
INSERT INTO CLIENTES([Nombre],[Apellidos],[Email],[Telefono]) VALUES('Ivy','Brock','ligula@Vivamuseuismod.org','547.136.406');

DESARROLLAR EJEMPLO EN DELPHI (detectar cambios)

Para desarrollar el primer ejemplo en Delphi, vamos a crear un formulario con los siguientes componentes de FireDAC, tal y como se ve en la imagen inferior. Los habituales para mostrar datos en un grid, TFDConnection, TFDQuery, TDataSource y para este ejemplo añadiremos un TFDEventAlerter.

La parte central de la pantalla contiene un TMemo, para mostrar mensajes.

Y la parte inferior los botones que se ven en la imagen.

Captura_2019-06-06 17_07_37

Configuramos la Conexión y el Query para mostrar en el Grid los datos de la tabla de CLIENTES que acabamos de crear. Será algo similar a como se ve en esta imagen, con vuestro Server, Usuario y Password de acceso.

Captura_2019-07-02 13_19_20

En la SQL del Query basta con colocar esta SQL para ver todos los clientes:

SELECT * FROM CLIENTES

Hasta aquí, si activamos la conexión y el query en diseño, ya deberíamos poder ver los datos de la tabla de clientes en el Grid.

El TFDEventAlerter no dispone de muchas opciones y en el caso de SQLServer son bastante simples de configurar. En este primer ejemplo vamos a detectar cambios que se realizan en la tabla de CLIENTES. Para ello podemos definir una o varias consultas que definan los cambios que queremos detectar.

Para ver el funcionamiento, en este ejemplo voy a definir 2 consultas:

SELECT NOMBRE FROM dbo.CLIENTES  
SELECT ID, NOMBRE, APELLIDOS, EMAIL, TELEFONO FROM dbo.CLIENTES

IMPORTANTE: Realizando las pruebas me he dado cuenta de que a veces da problemas si no incluimos el dbo. (en mi caso) junto al nombre de la tabla.

Como veis estas 2 consultas se “solapan”. Es decir, que una es un subconjunto de la otra. En un código real no tendría sentido, pero en este ejemplo nos sirve para entender mejor cómo funciona el componente y conocer sus posibilidades.

Con esto vemos que en un mismo componente podemos añadir más de una consulta para “detectar” cambios.

En este caso:

  • Si yo modifico el campo APELLIDOS obtendré 1 aviso de notificación.
  • Si modifico el campo NOMBRE obtendré 2 avisos, uno de cada consulta configurada.

Además de las consultas para detectar cambios necesitamos definir un nombre único para la cola (QUEUE) y para el servicio (SERVICE) que sirven para identificar de forma única estos elementos.

En siguiente código podemos utilizarlo para configurar nuestro componente; Para ello utilizaremos en botón de “Activar” que hemos colocado en el formulario:

  Memo1.Lines.Clear;
  // Activar componentes
  FDConnection1.Connected := True;    // conexión
  Memo1.Lines.Add('Conectado.');
  FDQuery1.Active := True;    // query sobre la tabla de clientes
  // Configurar el EventAlerter
  FDEventAlerter1.Names.Clear;
  FDEventAlerter1.Names.Add('QUEUE=?');
  FDEventAlerter1.Names.Add('SERVICE=?');
  // Configurar la consulta para detectar los cambios realizados
  FDEventAlerter1.Names.Add('CHANGE1=EventNOMBRE;SELECT NOMBRE FROM CLIENTES');
  FDEventAlerter1.Names.Add('CHANGE2=EventTODOS;SELECT ID, NOMBRE, APELLIDOS, EMAIL, TELEFONO FROM CLIENTES');
  // Activar el componente
  FDEventAlerter1.Active := True;

La parte importante es la asignación de valores a la propiedad Names.

Primero se asignan valores para la cola y el servicio y luego se definen las consultas SQL, que definen los cambios que queremos que el SGBD nos notifique.

Los 2 primeros se definen con valor “?” que indica a SQLServer que genere un nombre único para ambos y los libere una vez que acabemos:

  FDEventAlerter1.Names.Add('QUEUE=?');
  FDEventAlerter1.Names.Add('SERVICE=?');
    Lo siguiente son las consultas SQL con el siguiente formato:
  • <index>: Índice entero para el evento.
  • <name>: Nombre identificativo para el evento.  Este nombre lo recibiremos entre los parámetros que llegan cuando el componente nos avisa de los cambios
  • SELECT query>: Consulta SELECT donde definimos los cambios que queremos detectar o de los cuales queremos notificación.

Por último sólo nos queda el código para el evento OnAlert. Que se ejecutará cuando al componente le llegue una notificación desde el servidor SQL.

procedure TForm5.FDEventAlerter1Alert(ASender: TFDCustomEventAlerter;
  const AEventName: string; const AArgument: Variant);
var
  Argumentos:string;
  i:integer;
begin
  // Si tenemos un array, extraemos las partes
  if VarIsArray(AArgument) then begin
    Argumentos := string.empty;
    for i := VarArrayLowBound(AArgument, 1) to VarArrayHighBound(AArgument, 1) do begin
      if (Argumentos &lt;&gt; string.empty) then
        Argumentos := Argumentos + ', ';
      Argumentos := Argumentos + VarToStr(AArgument[i]);
    end;
  end
  // Nulo
  else if VarIsNull(AArgument) then
    Argumentos := ''
  // sin valor
  else if VarIsEmpty(AArgument) then
    Argumentos := ''
  else
    Argumentos := VarToStr(AArgument);
  Memo1.Lines.Add('Evento -&gt; Nombre: ' + AEventName + ' | Argumentos: ' + Argumentos);
  Memo1.Lines.Add('-----------------------------------------------------');
end;

Las modificaciones realizadas llegan en el parámetro AArgument y tal y como se explica en la docwiki (Evento OnAlert) puede ser un valor vacío/no-asignado o un array de valores (tipo variant). Debemos tenerlo en cuenta a la hora de tratarlo.

Con este código se nos mostrarán en el TMemo los diferentes eventos que se han disparado en el Gestor.

A continuación podéis ver una animación del programa de ejemplo funcionando.

(pulsar sobre la imagen para verla más grande en una ventana nueva)

EventosEnSQLServer

Tal y como era de esperar cuando:

    • Modificamos el nombre “saltan” 2 eventos
    • Modificamos otro campo (apellido) “saltan” 1 evento
    • Eliminamos un registro “saltan” 2 eventos
    • Añadimos un registro “saltan” 2 eventos

Cada uno de ellos, además incluye la operación que se está realizando. Aquí se están ejecutando dos instancias de la aplicación en la misma máquina y podemos ver como ambas reciben las notificaciones del SGBD.

A continuación os adjunto el código del proyecto.

<Descargar el código fuente del proyecto>

Finalmente la entrada se ha alargado más de la cuenta, así que he dejado el final para una segunda entrega, que espero publicar en breve.

Como siempre espero que sea útil y los comentarios, sugerencias, críticas,… son bienvenidos.

Un saludo y hasta la próxima.

5/5 - (2 votos)
  1. viernes, 5 de julio de 2019 a las 21:15 | #1

    Muy interesante; no sabía nada de ésto, y me será muy útil
    Gracias :)

  2. Al Gonzalez
    sábado, 6 de julio de 2019 a las 17:46 | #2

    Muy interesante artículo, Germán.

    Definitivamente hay que usar FireDAC siempre que se pueda. Y resultará muy útil emplear este componente, TFDEventAlerter, con bases de datos Firebird.

    Muchas gracias por la valiosa contribución.

    Al González.

  3. lunes, 8 de julio de 2019 a las 11:28 | #3

    @Al Gonzalez
    Estoy de acuerdo contigo Al.
    Hay que ir convergiendo al uso de FireDAC. A medida que lo voy probando me gusta más…

  4. lunes, 8 de julio de 2019 a las 11:39 | #4

    @Javierus
    La mayoría de los Gestores de Bases de Datos a día de hoy poseen estos eventos, lo que es más difílcil es que los componentes de Acceso a Dartos los implementen, por eso no es habitual usarlos.

  5. Doock
    jueves, 3 de octubre de 2019 a las 11:51 | #5

    Eres un grande!!!!!!

  6. jueves, 3 de octubre de 2019 a las 12:58 | #6

    @Doock
    Gracias Doock.
    Espero que eso signifique que el artículo te ha sido útil.

  7. Pei Iborra
    martes, 19 de noviembre de 2019 a las 04:51 | #7

    Hola German,
    Muy interesante este artículo, estoy seguro que me sera útil en algún momento.
    Por el momento no encuentro lo que estoy buscando: Necesito saber cual es la tabla de una base de datos que ha experimentado un cambio mas recientemente. Concretamente, utilizando una aplicación de terceros (de la cual no dispongo del código) modifico un dato de la BD. Pues bien, necesito saber cual ha sido la tabla afectada. Se trata de un update, no de un insert.
    Tu crees que se pueden usar los FireDAC para este propósito?
    Muchas gracias de antemano.

  8. martes, 19 de noviembre de 2019 a las 08:21 | #8

    Hola.
    Utilizando este sistema puedes “engancharte” a una o varias tablas para detectar cambios, ya sean mediante un INSERT,un DELETE o un UPDATE. El problema es que necesitas saber la tabla o tablas que deseas “vigilar”.

    Un saludo.

  9. Alex
    domingo, 26 de abril de 2020 a las 03:49 | #9

    No se puede descargar el proyecto.
    Saludos

  10. lunes, 27 de abril de 2020 a las 10:50 | #10

    @Alex
    Gracias por el aviso Alex.
    Había un error en el link.
    Prueba a descargarlo ahora.

    https://neftali.clubdelphi.com/ejemplos_files/EjemploEventosFireDAC.zip

    Un saludo.

  11. mapleon
    jueves, 13 de agosto de 2020 a las 16:40 | #11

    Germán te sigo desde hace varios años, aunque es la primera vez que escribo.

    Definitivamente un tema más que útil, para las aplicaciones que desarrollo, ojala sigas publicando sobre FireDAC.

    un abrazo

  12. viernes, 14 de agosto de 2020 a las 09:01 | #12

    @mapleon
    Hola.
    Gracias por el comentario.
    Desgraciadamente no trabajo habitualmente con FireDAC y no puedo publicar tanto como me gustaría. Si voy investigando, pero en un handicap el no poder estar día a día con estos componentes.

    Un saludo.

  13. martes, 26 de octubre de 2021 a las 14:02 | #13

    Todo muy claro.

    Lo había usando con Firebird, pero no sabía como hacerlo con MSSQL. Agradecido.

  14. martes, 26 de octubre de 2021 a las 14:13 | #14

    Estimado Germán, además del agradecimiento, quizás puedas incluir ejemplos para las SGBD’s más usadas.

  15. martes, 16 de noviembre de 2021 a las 08:45 | #15

    @Alfredo
    Hola Alfredo.
    Te agradezco el comentario y te pido dsculpas por que no contestarte antes (no me llegó el aviso).
    El código es compatible con SQLServer y es bastante similar (en la parte de delphi) para el resto de Base de Datos.

    Un saludo.

  1. Sin trackbacks aún.