The Verdú Method
Firebird 2.5: Native Multi-Database Queries Without External APIs

Chapter 1: Introduction to the Technique

This document describes an advanced technique developed by Salvador for Firebird 2.5 that allows querying and joining data from multiple databases (local and remote) from a single PROCEDURE, using only native engine features — no external libraries, UDFs, or APIs.

1.1 Purpose

The goal of this technique is to enable real-time consolidation of data from multiple Firebird installations (e.g., pharmacies or branches) and combine them with local data for analysis, reporting, or statistics — all from a native stored procedure.

1.2 Advantages

• Query and join data across multiple databases without prior replication.
• Uses only native Firebird 2.5 features.
• Supports remote databases over TCP/IP.
• Robust error handling: execution continues even if one remote database is unreachable.
• Scalable: add more remote databases by simply inserting rows into a configuration table (e.g. RMT_FARMACIAS).

1.3 Known Limitations

• Each remote database must be reachable via IP and open port (default: 3050).
• All databases must be Firebird 2.5 compatible.
• Requires correct user/permission setup (e.g. SYSDBA or a user with table access).
• Execution time increases with the number of remote databases.
• Firebird requires all fields to be declared in the PROCEDURE header in advance. Unlike SQL Server, Firebird 2.5 does not support dynamic field selection — any undeclared field will cause a runtime error.

Chapter 2: Basic Remote Query Example

SET TERM ^;

CREATE OR ALTER PROCEDURE ArticulosRemoto
(
  pCodigoArticulo VARCHAR(6),
  pIPRemotaFDB VARCHAR(100)
)
RETURNS
(
  fldCodigoArticuloRMT VARCHAR(6),
  fldStockRMT INTEGER,
  fldErrorCNN INTEGER
)
AS
  DECLARE VARIABLE lcComando VARCHAR(8192);
BEGIN
  lcComando =
    'SELECT' ||
    '    CODIGO_ARTICULO' ||
    '    ,STOCK' ||
    '  FROM ARTICULOS' ||
    '  WHERE (CODIGO_ARTICULO = :pCodigoArticulo)';

  EXECUTE STATEMENT lcComando
    ON EXTERNAL pIPRemotaFDB
    AS USER "SYSDBA" PASSWORD "masterkey"
    INTO
      :fldCodigoArticuloRMT,
      :fldStockRMT;

  IF (fldStockRMT IS NULL) THEN
    fldStockRMT = 0;

  fldErrorCNN = 0;

  SUSPEND;

  WHEN ANY DO
  BEGIN
    fldCodigoArticuloRMT = pCodigoArticulo;
    fldStockRMT = 0;
    fldErrorCNN = 1;
    SUSPEND;
  END
END
^

SET TERM ;^

Query from Delphi/Lazarus:

SELECT
    A.CODIGO_ARTICULO,
    A.DESC_ARTICULO,
    A.STOCK AS STOCK_ACTUAL,
    AR.STOCK AS STOCK_RMT
FROM ARTICULOS AS A
INNER JOIN ArticulosRemoto(A.CODIGO_ARTICULO, '172.18.41.91:C:\RUTA\BASE_DE_DATOS.FDB') AS AR
  ON AR.fldCodigoArticuloRMT = A.CODIGO_ARTICULO;

Chapter 3: Advanced Example — Multi-Month Remote Statistics

SET TERM ^;

CREATE OR ALTER PROCEDURE ADP_DAME_ESTADIS_ARTICLE_IP
(
  PCODIARTICLE VARCHAR(6),
  PANYO INTEGER,
  PMES INTEGER
)
RETURNS
(
  FLDCODI_ARTICLE VARCHAR(6),
  FLDNUM_FARMACIA INTEGER,
  FLDTOTAL_VENDES_MES_A DOUBLE PRECISION,
  FLDTOTAL_VENDES_MES_B DOUBLE PRECISION,
  FLDTOTAL_VENDES_MES_C DOUBLE PRECISION,
  FLDTOTAL_VENDES_MES_D DOUBLE PRECISION,
  FLDTOTAL_VENDES_MES_E DOUBLE PRECISION,
  FLDTOTAL_VENDES_MES_F DOUBLE PRECISION,
  FLDTOTAL_VENDES_MES_G DOUBLE PRECISION,
  FLDTOTAL_VENDES_MES_H DOUBLE PRECISION,
  FLDTOTAL_VENDES_MES_I DOUBLE PRECISION,
  FLDTOTAL_VENDES_MES_J DOUBLE PRECISION,
  FLDTOTAL_VENDES_MES_K DOUBLE PRECISION,
  FLDTOTAL_VENDES_MES_L DOUBLE PRECISION,
  FLDTOTAL_VENDES_MES_M DOUBLE PRECISION,
  FLDP_REFERENCIA DOUBLE PRECISION,
  FLDP_PVP DOUBLE PRECISION,
  FLDSTOCK INTEGER,
  FLDERROR_CNN INTEGER
)
AS
DECLARE VARIABLE vTOTAL_VENDES_MES double precision;
DECLARE VARIABLE vAnyo integer;
DECLARE VARIABLE vMes integer;
DECLARE VARIABLE vContador integer;
DECLARE VARIABLE vNumFarmacia integer;
DECLARE VARIABLE vIpFarmacia varchar(15);
DECLARE VARIABLE vIpFarmaciaFull varchar(100);
DECLARE VARIABLE lcComando varchar(8192);
BEGIN
  FOR SELECT NUM_FARMACIA, IP_FARMACIA FROM RMT_FARMACIAS
    INTO :vNumFarmacia, :vIpFarmacia
  DO
  BEGIN
    vIpFarmaciaFull = vIpFarmacia || ':C:\RUTA\DATOS\';
    vContador = 0;
    vAnyo = pAnyo -1;
    vMes = pMes;
    fldNUM_FARMACIA = vNumFarmacia;
    fldCODI_ARTICLE = pCodiArticle;
    
    WHILE (vContador <= 12) DO
    BEGIN
      vContador = vContador +1;
      lcComando =
        'SELECT ' ||
        '    AM.P_REFERENCIA' ||
        '    ,AM.P_PVP' ||
        '    ,AM.STOCK' ||
        '    ,SUM(AE.VENDES) AS TOTAL_VENDES' ||
        '  FROM ARTICLES_ESTADISTICA AE' ||
        '    LEFT JOIN ARTICLES_MESTRE AS AM ON (AM.CODI_ARTICLE = AE.CODI_ARTICLE)' ||
        '  WHERE (AE.CODI_ARTICLE = ' || pCodiArticle || ')' ||
        '        AND (AE.ANNY = ' || vAnyo || ')' ||
        '        AND (AE.MES = ' || vMes || ')' ||
        '  GROUP BY AM.P_REFERENCIA, AM.P_PVP, AM.STOCK';
      
      EXECUTE STATEMENT lcComando
        ON EXTERNAL vIpFarmaciaFull
        AS USER 'SYSDBA' PASSWORD 'masterkey'
        INTO :fldP_REFERENCIA, :fldP_PVP, :fldSTOCK, :vTOTAL_VENDES_MES;

      IF (vTOTAL_VENDES_MES IS NULL) THEN
        vTOTAL_VENDES_MES = 0;

      -- Assign to each month field
      IF (vContador = 1) THEN fldTOTAL_VENDES_MES_A = vTOTAL_VENDES_MES;
      IF (vContador = 2) THEN fldTOTAL_VENDES_MES_B = vTOTAL_VENDES_MES;
      IF (vContador = 3) THEN fldTOTAL_VENDES_MES_C = vTOTAL_VENDES_MES;
      IF (vContador = 4) THEN fldTOTAL_VENDES_MES_D = vTOTAL_VENDES_MES;
      IF (vContador = 5) THEN fldTOTAL_VENDES_MES_E = vTOTAL_VENDES_MES;
      IF (vContador = 6) THEN fldTOTAL_VENDES_MES_F = vTOTAL_VENDES_MES;
      IF (vContador = 7) THEN fldTOTAL_VENDES_MES_G = vTOTAL_VENDES_MES;
      IF (vContador = 8) THEN fldTOTAL_VENDES_MES_H = vTOTAL_VENDES_MES;
      IF (vContador = 9) THEN fldTOTAL_VENDES_MES_I = vTOTAL_VENDES_MES;
      IF (vContador = 10) THEN fldTOTAL_VENDES_MES_J = vTOTAL_VENDES_MES;
      IF (vContador = 11) THEN fldTOTAL_VENDES_MES_K = vTOTAL_VENDES_MES;
      IF (vContador = 12) THEN fldTOTAL_VENDES_MES_L = vTOTAL_VENDES_MES;
      IF (vContador = 13) THEN fldTOTAL_VENDES_MES_M = vTOTAL_VENDES_MES;

      vMes = vMes +1;
      IF (vMes > 12) THEN BEGIN vMes = 1; vAnyo = vAnyo +1; END
    END
    
    SUSPEND;

    WHEN ANY DO
    BEGIN
      fldCODI_ARTICLE = pCodiArticle;
      fldNUM_FARMACIA = vNumFarmacia;
      fldTOTAL_VENDES_MES_A = 0;
      fldTOTAL_VENDES_MES_B = 0;
      fldTOTAL_VENDES_MES_C = 0;
      fldTOTAL_VENDES_MES_D = 0;
      fldTOTAL_VENDES_MES_E = 0;
      fldTOTAL_VENDES_MES_F = 0;
      fldTOTAL_VENDES_MES_G = 0;
      fldTOTAL_VENDES_MES_H = 0;
      fldTOTAL_VENDES_MES_I = 0;
      fldTOTAL_VENDES_MES_J = 0;
      fldTOTAL_VENDES_MES_K = 0;
      fldTOTAL_VENDES_MES_L = 0;
      fldTOTAL_VENDES_MES_M = 0;
      fldP_REFERENCIA = 0;
      fldP_PVP = 0;
      fldSTOCK = 0;
      fldERROR_CNN = 1;
      SUSPEND;
    END
  END
END
^

SET TERM ;^

Chapter 4: Remote Price Update Example

This example shows how a procedure can update prices across multiple remote branches, enforcing unified PVP/PVA values.

SET TERM ^;

CREATE OR ALTER PROCEDURE RMT_ACTUALIZAR_PRECIOS
(
  pCodigoArticulo VARCHAR(6),
  pNuevoPVP DOUBLE PRECISION,
  pIPRemotaFDB VARCHAR(100)
)
AS
  DECLARE VARIABLE lcComando VARCHAR(8192);
BEGIN
  lcComando =
    'UPDATE ARTICULOS' ||
    '   SET PVP = ' || pNuevoPVP ||
    ' WHERE CODIGO_ARTICULO = :pCodigoArticulo';

  EXECUTE STATEMENT lcComando
    ON EXTERNAL pIPRemotaFDB
    AS USER 'SYSDBA' PASSWORD 'masterkey';

  WHEN ANY DO
  BEGIN
    -- Handle error here if needed
    SUSPEND;
  END
END
^

SET TERM ;^