Componente. Acceso a BD

Objetivo

Crear un componente que sea capaz de devolver un registro o una lista de registros de una o varias entidades de una base de datos. Debe permitir que se pueda seleccionar un filtro de datos y también indicar la ordenación que se desea. Además debe ser posible indicar que se devuelvan los registros de una determinada página, es decir, desde una determinada posición y un número determinado de registros.

En este ejemplo, se realizará un componente para obtener información de la tabla de clientes.

Referencias: ILE estrategia, Fetch relative, Estructuras de datos, BD Pruebas

Configuración

Se utiliza SQL para el desarrollo del código. El componente  formará parte de un programa de servicio y será exportable por lo que podrá ser llamado por el programa que enlace este programa de servicio.

• El nombre de componente (Obt_LstClientes). Utilizo siempre Obt_Lstxxxxxx donde xxxxx es la entidad para la definición del componente de acceso a base de datos para recuperar una  lista de registros.
• El nombre del módulo donde estarán los componentes BAN01M1
  Tendrá los siguientes componentes:
• (Obt_LstClientes) para recuperar la lista de clientes. Exportable
• (Obt_LstCuentas) para recuperar la lista de cuentas. Exportable
• (Sentencia_Ini) para inicializar el cursor de la ejecución de la sentencia. INTERNO.
• (Sentencia_Fin) para finalizar el cursor de la ejecución de la sentencia. INTERNO.
• El miembro (/Copy) con la definición del prototipo con los componentes y con la definición de estructuras será BAN01M1CP
• El programa de servicio donde estarán los módulos BAN01SRV. Tendrá un miembro asociado con el mismo nombre con la definición (binder) de los componentes a exportar.

Miembro /Copy. BAN01M1CP

Se describen las estructuras de datos que dan servicio al componente de acceso a base de datos y el prototipo de llamada. Estarán en un miembro fuente independiente para que sea utilizado como /Copy tanto por el fuente del componente como los fuentes de los posibles programas que lo utilicen.

      ** Estrutura datos basicos de Clientes
DDs_BasClie       DS                  QUALIFIED
D IdCliente                     10  0
D Nombre                        50
D Apellidos                     50
D Direccion                     50
D IdPais                         3
D DsPais                        50
D IdTipCli                       1
D DsTipCli                      50

** Estructura seleccionador de datos
DDs_SelClie       DS                  QUALIFIED
D K_IdCliente                   10  0
D S_DsCliente                   50
D S_IdPais                       3
D S_IdTipCli                     1

** Prototipo de lista de clientes.
DObt_LstClientes  PR
D P_DatosClie                         Dim(99) LikeDs(Ds_BasClie)
D P_NumReg                       2  0
D P_TotReg                      10  0
 *Opcionales
D P_Pag                          6  0 Const OPTIONS(*NOPASS)
D P_RegCarPag                    3  0 Const OPTIONS(*NOPASS)
D P_OrderBy                      2    Const OPTIONS(*NOPASS)
D P_Sel                               LikeDs(Ds_SelClie) Const
D                                     OPTIONS(*NOPASS)

Está compuesto por:

• (Ds_BasClie) Estructura de datos con los campos que se recuperarán de la instrucción SQL y que estarán en el array de datos que se devuelve.
• (Ds_SelClie) Estructura de datos con los campos que se permite utilizar para seleccionar (se utilizarán en WHERE) para filtrar la sentencia SQL.
• (K_) Seleccionador de clave primaria del fichero, en este ejemplo, será el código de cliente.
• (S_) Otros posibles seleccionadores según la definición que se haga en el componente. 
• (Obt_LstClientes) La definición del prototipo (interfaz) de llamada al componente. Más adelante explicaré los parámetros del mismo.

Al principio del fuente del componente se utiliza el /COPY para “cargar” las estructuras de datos y los prototipos de los componentes. 

/COPY *LIBL/QRPGLESRC,BAN01M1CP

Fuente del componente. Obt_LstClientes en BAN01M1

Parámetros

El componente tendrá la siguiente definición de parámetros:


PObt_LstClientes  B                   EXPORT
DObt_LstClientes  PI
D P_Datos                             Dim(99) LikeDs(Ds_BasClie)
D P_NumReg                       2  0
D P_TotReg                      10  0
 *Opcionales
D P_Pag                          6  0 Const OPTIONS(*NOPASS)
D P_RegCarPag                    3  0 Const OPTIONS(*NOPASS)
D P_OrderBy                      2    Const OPTIONS(*NOPASS)
D P_Sel                               LikeDs(Ds_SelClie) Const
D                                     OPTIONS(*NOPASS)

Parámetros de salida

• (P_Datos) Array con los datos recuperados según la selección y ordenación realizada. La definición de la estructura detallada (Ds_BasClie) del array se encuentra en el miembro /copy 
• (P_NumReg) Número de registros devueltos en el array. Será un valor entre cero y el límite de la dimensión del array.
• (P_TotReg) Número total de registros que devolvería la sentencia SQL completa según la selección indicada. 

Parámetros de entrada (todos OPCIONALES)

• (P_Pag) Página de datos a solicitar. Si no se pasa, será el valor 1 por defecto.
• (P_RegCarPag) Número de registros por página. Si no se pasa, será el valor n correspondiente al máximo número de registros que permite la dimensión del array.
• (P_OrderBy) Ordenación. Es un valor que identifica como se desea que sea la ordenación de los datos que se devolverán. Consta de dos posiciones, la primera es un número que representa por qué se ordena (por ejemplo 1 representa código de cliente, 2 representa el nombre, etc.). Quien desarrolla el componente define estas ordenaciones. El segundo será como se ordena y podrá ser A ó D que representa ascendente o descendente. Si no se pasa, será el valor de ordenación por defecto. 
• (P_Sel) Estructura de datos para selección de registros. Esta estructura también está definida en /copy. Si no se pasa, no habrá filtrado de datos. Si el valor de un campo de selección concreto es blancos o ceros (dependiendo del tipo) no se tendrá en cuenta para el filtrado de los datos.

Variables de trabajo

 * Sentencia SQL

D WBusqueda       DS

D  WBus_IdClient               100

D  WBus_DsClient               100

D  WBus_IdPais                 100

D  WBus_IdTipCli               100

D WOrderBy        S            100

D wDesc           S              4

D Sentencia       S           1000    Varying

D SentenciaSql    S           1000    Varying

 * Ordenacion

D InfOderBy       DS                  Qualified

D  OrderByCampo                  1

D  OrderByTipo                   1

 * Paginaciones

D WPag            S              6  0

D WRegIni         S              9  0

D WRegCarPag      S              3  0

• (wBus_) Son las variables que se utilizarán en la cláusula WHERE y se corresponden con los campos seleccionadores (filtros) que gestiona el componente. Habrá uno para cada seleccionador definido en la estructura (Ds_SelClie) 
  Estas variables tendrán contenido similar a “ and IdCliente=1 ”
• (WOrderBy) y (wDesc) Son las variables que se utilizarán en la cláusula ORDER BY. La primera tendrá los campos de ordenación (por ejemplo, “ IdCliente desc ”) y la segunda se utiliza para poner o no “desc “.
• (Sentencia) y (SentenciaSQL) Tendrán la instrucción SQL a ejecutar. La primera solo tendrá la cláusula FROM y WHERE y se utilizará para obtener el count(*) de registros y la segunda tendrá la SQL completa.
• (OrderByCampo) y (OrderByTipo). Se utilizan para extraer las dos posiciones que se reciben y que indican la ordenación que se desea. La primera será un número e identifica por que campos se quiere ordenar y la segunda será ‘A’ o ‘D’ que identifica ascendente o descendente.
• (WPag) (WRegCarPag) y (WRegIni). Se utilizan para calcular la página de datos a devolver. La primera y segunda corresponden a los parámetros de entrada de número de pagina y numero de registros a cargar y la tercera será el número relativo (según la SQL) del primer registro a devolver.

Código de inicialización

/Free

 Clear P_Datos;

 P_NumReg = *Zeros;

 P_TotReg = *Zeros;

Se inicializan los parámetros de salida.

Paginación

If %PARMS >=4 and P_Pag>*Zeros;

  WPag = P_Pag;

EndIf;

If %PARMS >=5 and P_RegCarPag>*Zeros;

  WRegCarPag = P_RegCarPag;

EndIf;

If WPag=*Zeros;

  WPag = 1;

EndIf;

If WRegCarPag = *Zeros;

  WRegCarPag = 99;

EndIf;

Permite definir la página y numero de registros por página que se va a utilizar en la sentencia.

Se verifican si los datos opciones de paginación que son opcionales se han recibido.

Si se reciben, se inicializan con esos datos (WPag = P_Pag) y (WRegCarPag = P_RegCarPag)

En caso de que no se reciban, se utilizan los valores por defecto (WPag = 1) y (WRegCarPag = 99) dimensión array.

Ordenación

// Orden ese valor

If %PARMS >5;

  InfOderBy = P_OrderBy;

Else;

  InfOderBy = 'XX';   //==> Por defecto

EndIf;

If InfOderBy.OrderByTipo='D';

  wDesc='Desc';

Else;

  wDesc=' ';

EndIf;

Select;

  When InfOderBy.OrderByCampo='1';     // Id Cliente

    WOrderBy = ' a.IdCliente ' + %Trim(wDesc);

  When InfOderBy.OrderByCampo='2';     // Nombre, apellidos

    WOrderBy = ' a.Nombre ' + %Trim(wDesc) + ', ' +

               ' a.Apellido ' + %Trim(wDesc) + ' ';

  When InfOderBy.OrderByCampo='3';     // Apellidos, Nombre

    WOrderBy = ' a.Apellido ' + %Trim(wDesc) + ', ' +

               ' a.Nombre ' + %Trim(wDesc) + ' ';

  When InfOderBy.OrderByCampo='4';     // Tp e Id cliente

    WOrderBy = ' a.IdTipCli ' + %Trim(wDesc) + ', ' +

               ' a.IdCliente ';

  Other;                               // Por defecto

    WOrderBy = ' a.IdCliente ' + %Trim(wDesc);

EndSl;

Permite definir la ordenación que se va a utilizar en la sentencia. 

Se revisan si se han recibido los parámetros de entrada de ordenación.

Si se han recibido se recogen del parámetro de entrada (InfOderBy = P_OrderBy)

En caso contrario se utiliza (InfOderBy = 'XX') cuyo efecto será que se utilice la ordenación por defecto.

Después se comprueba si se ha indicado descendente (InfOderBy.OrderByTipo='D') para poner “ Desc ” o no poner nada.

Posteriormente se comprueba la primera posición (InfOderBy.OrderByCampo=??) que indica que ordenación se va a utilizar y por cada valor posible, se pondrá el literal (WOrderBy) de ordenación que se desee y se incluye la referencia a descendente o ascendente.

En esta parte, se codifican todos los (InfOderBy.OrderByCampo=??) que se deseen y que están definidos por quien hace el componente.  Finalmente, tiene que haber definida una opción (Other;) que será la ordenación por defecto cuando se indique nada o cuando se pase un valor no definido.

Condiciones de selección

// Crear condiciones busqueda. Si por Id, solo utiliza ese valor

WBusqueda = *Blanks;

If %PARMS >6;                 //Se han dado selecciones
 If P_Sel.K_IdCliente = *Zeros;

  If P_Sel.S_DsCliente<>*Blanks;

    WBus_DsClient= ' and ' +

    ' UCASE(trim(Nombre) || '' '' || trim(Apellidos)) ' +

    ' like UCASE(''%' + %Trim(P_Sel.S_DsCliente) + '%'')';

  EndIf;

  If P_Sel.S_IdPais<>*Blanks;

   WBus_IdPais=' and a.IdPais =''' + P_Sel.S_IdPais + '''';

  EndIf;

  If P_Sel.S_IdTipCli<>*Blanks;

   WBus_IdTipCli =' and a.IdTipCli =''' +

                  P_Sel.S_IdTipCli + '''';

  EndIf;

 Else;

  WPag        = 1;   //Si es el Id, pag=1

  WBus_IdClient = ' and a.IdCliente =' +

                  %Char(P_Sel.K_IdCliente);

 EndIf;

EndIf;

Permite definir las selecciones o filtrados que se van a utilizar en la sentencia. 

Si viene pasado el parámetro de entrada de seleccionadores (P_Sel), se creará el contenido asociado a cada campo de búsqueda (WBus_) siempre y cuando haya pasado como parámetro de entrada

Si el campo de clave primaria (P_Sel.K_) viene informado, SOLO se buscará por ese campo, el resto de seleccionadores no los utilizará. Lógicamente si he pedido la clave x no hace falta filtrar por otros campos.

Si el campo de clave primaria no viene informado, se revisarán cada uno de los otros seleccionadores (P_Sel.S_) y se generarán las condiciones de búsqueda de cada uno.

En general:

• Si es un campo numérico, se busca por el valor ' and a.IdTipCli=' + %Char(P_Sel.K_IdCliente)
• Si es un campo alfa de tipo identificador, se busca por el valor ' and a.IdPais =' + P_Sel.S_IdPais
• Si es un campo alfa de tipo descripción, nombre, etc. se busca con like (sin distinguir mayúsculas-minúsculas)
  UCASE(trim(Nombre) || ' ' || trim(Apellidos)) like UCASE('%’ + %Trim(P_Sel.S_DsCliente) + '%’)

Sentencia

// Calculo del registro inicial segun paginacion

WRegIni = ((WPag - 1) * WRegCarPag) + 1;

// Sentencia SQL con la condicion de busqueda

Sentencia=' FROM '                                           +

            ' Cliente a, Pais b, TpCliente c '               +

           ' WHERE '                                         +

            ' a.IdPais = b.IdPais and '                      +

            ' a.IdTipCli = c.IdTipCli '                      +

              %Trim(WBus_IdClient)                     + ' ' +

              %Trim(WBus_DsClient)                     + ' ' +

              %Trim(WBus_IdPais)                       + ' ' +

              %Trim(WBus_IdTipCli)                     + ' ' ;

SentenciaSQL   ='SELECT '                                    +

            ' a.IdCliente, '                                 +

            ' a.Nombre, '                                    +

            ' a.Apellidos, '                                 +

            ' a.Direccion, '                                 +

            ' a.IdPais, '                                    +

            ' b.DsPais, '                                    +

            ' a.IdTipCli, '                                  +

            ' c.DsTipCli '                                   +

           %Trim(Sentencia)                                  +

          ' ORDER BY '                                       +

              %Trim(WOrderBy)                                ;

Permite definir la sentencia completa que se va a ejecutar. 

Se calcula el número del primer registro a recuperar en base a la página que se desea y el número de registros por página. Después se genera el literal de la sentencia (Sentencia) pero de las cláusulas FROM y WHERE. Esta variable se utilizará para realizar el count(*) y obtener el total de registros que devolvería la sentencia. Por ultimo la variable (SentenciaSQL) contiene la sentencia completa a ejecutar.

Ejecución

  // Se declara e inicializa el cursor y obtiene el total de reg

  Sentencia_Ini(Sentencia : SentenciaSql : P_TotReg);

  // Se recuperan todos los datos

  Exec SQL  Fetch RELATIVE :WRegIni FROM KLst

            FOR :WRegCarPag ROWS into :P_Datos;

  // Se cierra el cursor y se obtiene el numero de reg. cargados

  Sentencia_Fin(P_NumReg);

 /End-Free

P                 E

Por ultimo, para finalizar el componente, la ejecución de la sentencia. Tendrá la llamada a un componente interno (Sentencia_Ini), definido más adelante, que realizará la definición del cursor y también obtendrá el número total de registros que devolvería la SQL. El cursor siempre se llamará (KLst). Después tiene la ejecución con Fetch Relative (ampliar información) y por ultimo llama a un componente interno (Sentencia_Fin), definido más adelante,  que comprobará si la ejecución ha sido correcta, devolverá el número de registros que ha cargado en el array y cerrará el cursor.

He incluido dos componentes internos debido a que esa acciones serán siempre iguales y de esta forma lo reutilizo en otros componentes de acceso  a base de datos que codifico en el mismo fuente.

Componente INTERNO de inicialización de cursor () 

PSentencia_Ini    B
DSentencia_Ini    PI
D P_Sentencia                 1000    Const Varying
D P_SentenciaSql              1000    Varying
D P_TotReg                      10  0

D SentenciaCount  S           1000    Varying
 /Free
   // Se obtiene el total de registros que daria la consulta
   SentenciaCount ='SELECT Count(*) '                           +
                    %Trim(P_Sentencia)                          ;
   Exec SQL  Declare SCountReg STATEMENT;
   Exec SQL  Declare KCountReg cursor for SCountReg;
   Exec SQL  Prepare SCountReg FROM :SentenciaCount;
   Exec SQL  Open KCountReg;
   Exec SQl  FETCH From KCountReg Into :P_TotReg;
   Exec SQL  Close KCountReg;

   // Se declara el cursor de la consulta específica con
   // la pagina definida
   Exec SQL  Declare SLst  STATEMENT;
   Exec SQL  Declare KLst  SCROLL cursor for SLst;
   Exec SQL  Prepare SLst  FROM :P_SentenciaSql;
   Exec SQL  Open KLst;
 /End-Free
P                 E

Componente interno reutilizable para todos los componentes de acceso que realiza la ejecución de la sentencia para obtener el count(*) y declara y abre el cursor (KLst) con la definición de la instrucción SQL a ejecutar.

Componente INTERNO de finalización de cursor ()

PSentencia_Fin    B
DSentencia_Fin    PI
D P_NumReg                       2  0
 /Free
    // Si es correcta, recoge el número de registros devueltos.
    If SqlCod >= *Zeros;
      P_NumReg = SQLerrd(3);
    EndIf;

    // Se cierra el cursor
    Exec SQL  Close KLst;
 /End-Free
P                 E

Componente interno reutilizable para todos los componentes de acceso que comprueba si la ejecución fue correcta y recupera el número de registros (SQLerrd(3)) que ha cargado en el array y cierra el cursor.

Anexos

Plantilla

Este componente se podrá tomar como plantilla para realizar cualquier otro componente de acceso a BD. Para ello, NO se deberían cambiar los nombres de la variables generales que se utilizan y así siempre se llamarían igual en todos los desarrollos que se hicieran y lo que habría que cambiar es:

• La composición de la ordenación
• La parte de seleccionadores
• La instrucción SQL

Con ello la generación de un nuevo componente de acceso a BD de datos para otra entidad sería muy rápida. Por ejemplo, sobre la misma base de datos generar el componente Ob_LstCuentas, ¿lo intentas?

Programa de prueba

Dejo este proceso donde se puede comprobar el funcionamiento del componente de acceso a base de datos. 

D W_DatosClie     DS                  Dim(99) LikeDs(Ds_BasClie)
D W_NumReg        S              2  0
D W_TotReg        S             10  0
D W_Pag           S              6  0
D W_RegCarPag     S              3  0
D W_OrderBy       S              2
D W_Sel           DS                  LikeDs(Ds_SelClie)

 /COPY *LIBL/QRPGLESRC,BAN01M1CP
 /Free
    Obt_LstClientes(w_DatosClie : W_NumReg : W_TotReg);

    W_Pag        = 2;
    W_RegCarPag  = 4;
    W_OrderBy    = '1D';
    Obt_LstClientes(w_DatosClie : W_NumReg : W_TotReg :
                    W_Pag : W_RegCarPag : W_OrderBy);

    W_Pag        = *Zeros;
    W_RegCarPag  = *Zeros;
    W_OrderBy    = '1D';
    Clear w_Sel;
    W_Sel.K_IdCliente = 1;
    Obt_LstClientes(w_DatosClie : W_NumReg : W_TotReg :
                    W_Pag : W_RegCarPag : W_OrderBy :
                    w_Sel);

    Clear w_Sel;
    W_Sel.S_DsCliente = 'Lopez';
    Obt_LstClientes(w_DatosClie : W_NumReg : W_TotReg :
                    W_Pag : W_RegCarPag : W_OrderBy :
                    w_Sel);

    *InLr = *On;
 /End-Free 

La primera ejecución devolverá el máximo número de registros de los datos del fichero.
La segunda devolverá la segunda página de datos con 4 registros por página con ordenación descendente con la ordenación que se haya definido para el valor 1.
La tercera devolverá el registro de clave Idcliente=1
La cuarta devolverá la lista de registros cuyo nombre de cliente contenga "Lopez".