/*Prog*/ Delphi-Neftalí /*finProg*/

La 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.

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.

Ya 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.

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.

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 <> 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 -> 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)

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.

Germán Estévez

Embarcadero MVP.
Analista y Programador de Sistemas Informáticos.
Estudios de Informática (Ingeniería Técnica Superior) en la UPC (Universidad Politécnica de Barcelona).
Llevo utilizando Delphi desde su versión 3. Especialista en diseño de componentes, Bases de Datos, Frameworks de Persistencia, Integración Continua, Desarrollo móvil,…