< Anterior | Siguiente >

Lección 4: Construir consultas dinámicamente con prepare

La sentencia EGL prepare permite construir sentencias SQL dinámicamente.
Por qué y cuándo se efectúa esta tarea
En la lección anterior, ha creado una función que seleccionaba artículos de una base de datos cuyo precio era igual o superior al de un parámetro: 
function printExpensiveItems(maxPrice Price in)
  
tempItem Item;
open expensiveItems for tempItem with
  #sql{
    select
      EGL.ITEM.ITEM_ID, EGL.ITEM.NAME, EGL.ITEM.IMAGE, 
      EGL.ITEM.PRICE, EGL.ITEM.DESCRIPTION
    from EGL.ITEM
    where
      EGL.ITEM.PRICE >= :maxPrice
    order by
      EGL.ITEM.ITEM_ID asc
  };
  
forEach (tempItem)
  SysLib.writeStdout(tempItem.Name :: " " ::
    tempItem.Description :: " $" :: tempItem.Price);
end
Pero ¿qué debe hacer si desea que se devuelvan los artículos cuyo precio es igual o inferior al del parámetro? O bien, ¿qué debe hacer si desea que se devuelvan los artículos cuyo precio se sitúa entre un precio máximo y un precio mínimo? Utilizando el código SQL explícito de la sentencia open, tendría que codificar tres funciones, una para cada una de las posibilidades, ya que las comparaciones de la cláusula WHERE de la sentencia SQL se codifican manualmente.
Un modo más flexible de crear una sentencia SELECT es utilizar la sentencia EGL prepare para ensamblar la consulta en tiempo de ejecución. Al utilizar prepare, primero se crea una variable STRING que contiene el texto del código SQL: 
selectStatement STRING = 
  "select * from EGL.ITEM where EGL.ITEM.PRICE >= 500";
A continuación, se utiliza la sentencia prepare para convertir la serie en una sentencia SQL. También debe especificar un nombre para la sentencia y, opcionalmente, una variable de registro para indicar el componente SQLRecord sobre el que operará la sentencia:
tempItem Item;
prepare preparedStatement from selectStatement for tempItem;
A continuación, puede utilizar la sentencia preparada en lugar de SQL explícito en una sentencia EGL get, open o execute (aprenderá más acerca de la sentencia execute en una lección posterior):
open expensiveItems for tempItem with preparedStatement;

Errores habituales de las sentencias preparadas

La creación de una sentencia SQL a partir de una serie mediante este procedimiento es una técnica avanzada para programadores familiarizados con SQL. EGL no comprueba la exactitud de la sentencia SQL durante el diseño, por lo que debe asegurarse de que la serie se resuelva en una sentencia SQL correcta en tiempo de ejecución. A continuación figura una lista de los errores habituales que se producen al trabajar con sentencias preparadas:
Por qué y cuándo se efectúa esta tarea
Variables de lenguaje principal
No pueden utilizarse variables de lenguaje principal en sentencias preparadas del mismo modo que en SQL explícito. Tomemos el ejemplo siguiente:
tempItem Item;
maxPrice Price = 500;
selectStatement STRING = 
  "select * from EGL.ITEM where EGL.ITEM.PRICE >= :maxPrice";
prepare preparedStatement from selectStatement for tempItem;
En este caso, la sentencia SQL real incluirá la serie :maxPrice exactamente tal como está, sin utilizarla como variable de lenguaje principal y sustituirla por el valor de la variable maxPrice. El resultado será una sentencia SQL incorrecta.
En lugar de ello, debe insertar los valores de las variables en la serie:
tempItem Item;
maxPrice Price = 500;
selectStatement STRING = 
  "select * from EGL.ITEM where EGL.ITEM.PRICE >= " :: maxPrice;
prepare preparedStatement from selectStatement for tempItem;
En este caso, el valor de la variable maxPrice se resuelve cuando EGL crea la serie, creando la siguiente sentencia SQL:
select * from EGL.ITEM where EGL.ITEM.PRICE >= 500
Más adelante en esta lección aprenderá un procedimiento alternativo de utilizar variables en sentencias preparadas.
Espaciado
Tenga cuidado de separar las palabras clave de la sentencia SQL mediante espacios. Por ejemplo, tomemos la siguiente sentencia SQL, que se prepara concatenando varios valores de serie con el operador de concatenación (::=):
incorrectSQL STRING;
incorrectSQL = "select * from EGL.ITEM where";
incorrectSQL ::= "EGL.ITEM.PRICE >= :maxPrice";
incorrectSQL ::= "order by EGL.ITEM.PRICE";
Este ejemplo crea la siguiente sentencia SQL incorrecta: 
select * from EGL.ITEM whereEGL.ITEM.PRICE >= :maxPriceorder by EGL.ITEM.PRICE
Esta sentencia necesita un espacio después de WHERE y otro antes de ORDER BY.
Sentencias correctas y apropiadas
EGL no valida sentencias preparadas, por lo que es tarea del usuario asegurarse de que la sentencia corresponda a SQL correcto.

Asimismo, las sentencias SQL preparadas, como el SQL explícito, deben ser apropiadas para la sentencia EGL con la que van a utilizarse. Por ejemplo, las sentencias EGL get y open esperan un conjunto de resultados. Para utilizar una sentencia preparada con cualquiera de estas sentencias, el código SQL debe devolver un conjunto de resultados. Por ello, no puede prepararse una sentencia SQL INSERT o DELETE y luego utilizarla con get u open.

Lección 4A: Utilizar una sentencia preparada

En esta lección modificará el programa que ha creado en el ejercicio anterior para que acepte dos parámetros, uno de los cuales o ambos pueden a ser nulos. La función creará una consulta basada en los parámetros que no son nulos, devolviendo los artículos comprendidos en un determinado rango de precios.
  1. Abra el archivo selectItems.egl.
  2. Añada una función denominada printItemRange que reciba dos parámetros Price con capacidad de nulo: 
    function printItemRange(minPrice Price? in, maxPrice Price? in)

end

    El signo de interrogación (?) situado después del tipo de parámetro indica que la variable puede recibir un valor nulo. De ese modo, la función podrá aceptar un valor mínimo o máximo, o ambos, para el rango de resultados.

  3. Cree una variable STRING para que contenga el código SQL e inserte en ella el principio de una sentencia SELECT: 
    selectStatement STRING = "select * from EGL.ITEM where ";
    El próximo paso consiste en determinar cuál debe ser la cláusula WHERE adecuada. Existen cuatro situaciones posibles:
    • Ambos parámetros son nulos. En este caso, la función mostrará todos los artículos cuyo precio sea igual o superior a cero, por lo que la cláusula WHERE debe ser where EGL.ITEM.PRICE > 0.
    • Se especifican ambos parámetros. En este caso, la función utilizará la palabra clave SQL BETWEEN para mostrar los valores situados entre los parámetros o iguales a ellos. La cláusula WHERE debe ser where EGL.ITEM.PRICE between :minPrice and :maxPrice.
    • Sólo se especifica el parámetro máximo. En este caso, la función mostrará todos los artículos cuyo precio sea igual o inferior al máximo; where EGL.ITEM.PRICE < = :maxPrice.
    • Sólo se especifica el parámetro mínimo. En este caso, la función mostrará todos los artículos cuyo precio sea igual o superior al mínimo; where EGL.ITEM.PRICE > = :minPrice.
    Existen varios procedimientos para tomar esta decisión en EGL, pero un modo simple es utilizar tres sentencias if anidadas.
  4. Añada el código siguiente a la función para completar la cláusula WHERE: 
    if (minPrice == NULL && maxPrice == NULL)
  // Dos parámetros vacíos;
  // devolver todas las filas con PRICE mayor que cero.
  selectStatement ::= "EGL.ITEM.PRICE > 0 ";
else

  if (minPrice != NULL && maxPrice != NULL)
    // Se han especificado ambos parámetros.
    selectStatement ::= "EGL.ITEM.PRICE between " :: minPrice :: " and " :: maxPrice :: " ";
  else
    // Sólo se ha especificado un parámetro.
    if (minPrice == NULL)
      // Se ha especificado el máximo.
      selectStatement ::= "EGL.ITEM.PRICE <= " :: maxPrice :: " ";
    else
      // Se ha especificado el mínimo.
      selectStatement ::= "EGL.ITEM.PRICE >= ":: minPrice :: " ";
    end
  end
  
end
  5. Complete la sentencia con una cláusula ORDER BY: 
    selectStatement ::= "order by EGL.ITEM.PRICE";
  6. Para asegurarse de que la sentencia SQL es correcta, visualícela en la consola: 
    SysLib.writeStdout("Consulta completada: "::selectStatement);
  7. Prepare la sentencia que debe utilizarse, haciendo referencia a un registro creado desde la tabla a la que la sentencia preparada va a acceder: 
    tempItem Item;
prepare preparedStatement 
  from selectStatement
  for tempItem;
  8. Utilice la sentencia preparada en lugar de SQL explícito en una sentencia open. 
    open expensiveItems for tempItem with preparedStatement;
  9. Visualice los resultados de la consulta: 
    forEach (tempItem)
  SysLib.writeStdout(tempItem.Name :: " " ::
    tempItem.Description :: " $" :: tempItem.Price);
end
    La función completa tiene este aspecto: 
      function printItemRange(minPrice Price? in, maxPrice Price? in)
    
    selectStatement STRING = "select * from EGL.ITEM where ";
    
    if (minPrice == NULL && maxPrice == NULL)
      // Dos parámetros vacíos;
      // devolver todas las filas con PRICE mayor que cero.
      selectStatement ::= "EGL.ITEM.PRICE > 0 ";
    else
    
      if (minPrice != NULL && maxPrice != NULL)
        // Se han especificado ambos parámetros.
        selectStatement ::= "EGL.ITEM.PRICE between " :: minPrice :: " and " :: maxPrice :: " ";
      else
        // Sólo se ha especificado un parámetro.
        if (minPrice == NULL)
          // Se ha especificado el máximo.
          selectStatement ::= "EGL.ITEM.PRICE <= " :: maxPrice :: " ";
        else
          // Se ha especificado el mínimo.
          selectStatement ::= "EGL.ITEM.PRICE >= ":: minPrice :: " ";
        end
      end
      
    end
    
    // Independientemente de los parámetros, ordenar por precio.
    selectStatement ::= "order by EGL.ITEM.PRICE";
    
    // Visualizar código SQL completado.
    SysLib.writeStdout("Consulta completada: "::selectStatement);
    
    // Preparar la sentencia que debe utilizarse.
    tempItem Item;
    prepare preparedStatement 
      from selectStatement
      for tempItem;
    
    // Utilizar la sentencia preparada en lugar de SQL explícito.
    open expensiveItems for tempItem with preparedStatement;
    
    // Visualizar los resultados en la consola.
    forEach (tempItem)
      SysLib.writeStdout(tempItem.Name :: " " ::
        tempItem.Description :: " $" :: tempItem.Price);
    end
    
  end
  10. Llame a la función desde la función main del programa: 
      function main()
    minPrice, maxPrice Price?;
    minPrice = 50;
    maxPrice = 500;
    try
      printItemRange(minPrice, maxPrice);
    onException(exception SQLException)
      handleDBException(exception);
    end
  end
  11. Genere y ejecute el programa.
Resultados
Con los valores de parámetro del ejemplo anterior, la función visualiza los artículos cuyo precio está entre 50 y 500:
Consulta completa: select * from EGL.ITEM where EGL.ITEM.PRICE
  between 50.00 and 500.00 order by EGL.ITEM.PRICE
Serial Mouse Great little mouse.  All kinds'a applications. $65.22
Keyboard Got QWERTY?  If not, go get this awesome flat keyboard. $78.99
Watch Keep time - and look rakish! $99.01
Shredder Don't let that politically-sensitive document fall into the wrong hands! $198.99
Combination Safe Keep your valuables safe and secure.  Put 'em in here. $222.22
Puede editar los valores de parámetro y probar rangos diferentes, incluidos valores nulos, como en este ejemplo: 
function main()
  minPrice, maxPrice Price?;
  minPrice = 500;
  maxPrice = null;
  try
    printItemRange(minPrice, maxPrice);
  onException(exception SQLException)
    handleDBException(exception);
  end
end
Si se pasan valores de 500 y nulo, se generan resultados como estos: 
Consulta completa: select * from EGL.ITEM where
EGL.ITEM.PRICE >= 500.00 order by EGL.ITEM.PRICE
Desktop PC Lots'a power, at the right price.  Includes monitor. $888.55
Laptop PC Great little machine.  Take it anywhere with you. $1111.11

Este es el código completo del archivo selectItems.egl. Si ve errores marcados con símbolos X rojos en el archivo, asegúrese de que el código coincide con el código de este archivo: Archivo selectItems.egl completado después de la lección 4A.

Lección 4B: Variables de lenguaje principal en sentencias prepare

La sentencia SQL del ejemplo anterior estaba limitada en el sentido de que las variables de la cláusula WHERE era fijas. Es posible crear una sentencia preparada antes de conocer qué variables se utilizarán en la sentencia. Este tipo de sentencia es más complicada, pero puede proporcionar flexibilidad si desea preparar una sentencia y especificar los detalles más adelante.
Por qué y cuándo se efectúa esta tarea
En el ejemplo anterior, debía resolver los valores de las variables para poder concatenarlos con el resto de la serie de consulta: 
selectStatement STRING = "select * from EGL.ITEM where ";
...
selectStatement ::= "EGL.ITEM.PRICE between " :: minPrice :: " and " :: maxPrice :: " ";
Al crear una sentencia preparada, también puede situar signos de interrogación (?) en la sentencia, indicando que los valores se especificarán más adelante:
selectStatement2 STRING = "select * from EGL.ITEM where " ::
  "EGL.ITEM.PRICE between ? and ? " ::
  "order by EGL.ITEM.PRICE";

tempItem Item;
prepare preparedStatement2 
  from selectStatement2
  for tempItem;
Ahora, la sentencia preparada tiene lugar para dos variables de lenguaje principal, representadas por los signos de interrogación de la cláusula BETWEEN. Cuando esté preparado para utilizar la sentencia preparada, inserte los valores con la sentencia using. Por ejemplo, para buscar las filas cuyo precio esté entre 5 y 500, pase los literales 5 y 500 a la sentencia:
open expensiveItems2 for tempItem with preparedStatement2
  using 5, 500;
Evidentemente, también puede utilizar variables junto con la palabra clave using:
open expensiveItems2 for tempItem with preparedStatement2
  using minPrice, maxPrice;
La utilización de variables de lenguaje principal en una sentencia preparada mediante este procedimiento puede ser de utilidad si desea ensamblar una sentencia una sola vez y llamarla varias veces con valores diferentes. Por ejemplo, esta función utiliza tres veces la misma sentencia preparada para visualizar los valores de tres rangos diferentes: 
function printRanges()
  
  tempItem Item;
  selectStatement string = "select * from EGL.ITEM where " ::
    "EGL.ITEM.PRICE between ? and ? " ::
    "order by EGL.ITEM.PRICE";

  prepare preparedStatement from selectStatement for tempItem;

  SysLib.writeStdout("\nVisualizando valores entre 0 y 200");
  open expensiveItems for tempItem with preparedStatement using 0, 200;
  forEach(tempItem)
    SysLib.writeStdout(tempItem.Name :: " " :: tempItem.Description ::
      " $" :: tempItem.Price);
  end

  SysLib.writeStdout("\nVisualizando valores entre 200 y 500");
  open expensiveItems for tempItem with preparedStatement using 200, 500;
  forEach(tempItem)
    SysLib.writeStdout(tempItem.Name :: " " :: tempItem.Description ::
      " $" :: tempItem.Price);
  end

  SysLib.writeStdout("\nVisualizando valores entre 500 y 1.000");
  open expensiveItems for tempItem with preparedStatement
    using 500, 1000;
  forEach(tempItem)
    SysLib.writeStdout(tempItem.Name :: " " :: tempItem.Description ::
      " $" :: tempItem.Price);
  end

end

Utilizando variables de lenguaje principal de ese modo, es posible reescribir el programa de la sección anterior para que utilice una sola sentencia preparada, en lugar de modificar la sentencia para cada posible caso:

  1. En el archivo selectItems.egl, añada una función nueva que acepte un parámetro máximo y uno mínimo, igual que en la sección anterior: 
    function printItemRange2(minPrice Price? in, maxPrice Price? in)

end
    En esta función, preparará primero la sentencia e insertará las variables en ella más adelante.
  2. Cree una variable STRING para que contenga la sentencia preparada y luego utilice la sentencia prepare para prepararla: 
    selectStatement STRING = "select * from EGL.ITEM where " ::
  "EGL.ITEM.PRICE between ? and ? " ::
  "order by EGL.ITEM.PRICE";

tempItem Item;
prepare preparedStatement 
  from selectStatement
  for tempItem;
    Pasará dos parámetros a esta sentencia preparada, que representan los límites de la búsqueda. Si no se especifica el precio máximo, necesitará conocer el límite superior de la búsqueda, ya que no podrá convertir la cláusula EGL.ITEM.PRICE between ? and ? a EGL.ITEM.PRICE >= ? una vez preparada la sentencia. Por tanto, debe determinar el precio más elevado de la tabla para utilizarlo como límite superior. Puede determinar fácilmente este límite superior con la función SQL MAX().
  3. Cree una variable Item y recupere el precio del artículo más caro de la base de datos, utilizando la función MAX() para colocarlo en el campo Price de la variable de registro: 
    mostExpensiveItem Item;
get mostExpensiveItem 
  into mostExpensiveItem.Price
  with
    #sql{
      select
        max(EGL.ITEM.PRICE)
      from EGL.ITEM
    };
    Ahora, el registro mostExpensiveItem contiene el previo más elevado de la tabla Items.
  4. Determine los parámetros que se han especificado y utilice la sentencia preparada de acuerdo con ellos: 
    if (minPrice == NULL && maxPrice == NULL)
  // Dos parámetros vacíos;
  open expensiveItems for tempItem with preparedStatement 
    using 0, mostExpensiveItem.Price;
else

  if (minPrice != NULL && maxPrice != NULL)
    // Se han especificado ambos parámetros.
    open expensiveItems for tempItem with preparedStatement 
      using minPrice, maxPrice;
  else
    // Sólo se ha especificado un parámetro.
    if (minPrice == NULL)
      // Se ha especificado el máximo.
      open expensiveItems for tempItem with preparedStatement 
        using 0, maxPrice;
    else
      // Se ha especificado el mínimo.
      open expensiveItems for tempItem with preparedStatement 
        using minPrice, mostExpensiveItem.Price;
    end
  end
  
end
  5. Utilice forEach para visualizar los resultados: 
    forEach (tempItem)
  SysLib.writeStdout(tempItem.Name :: " " ::
    tempItem.Description :: " $" :: tempItem.Price);
end
    La función completa tiene este aspecto: 
    function printItemRange2(minPrice Price? in, maxPrice Price? in)
  
  selectStatement STRING = "select * from EGL.ITEM where " ::
    "EGL.ITEM.PRICE between ? and ? " ::
    "order by EGL.ITEM.PRICE";
  
  // Preparar la sentencia que debe utilizarse
  tempItem Item;
  prepare preparedStatement 
    from selectStatement
    for tempItem;
  
  mostExpensiveItem Item;
  get mostExpensiveItem 
    into mostExpensiveItem.Price
    with
    #sql{
      select
        max(EGL.ITEM.PRICE)
      from EGL.ITEM
    };
    
  if (minPrice == NULL && maxPrice == NULL)
    // Dos parámetros vacíos;
    open expensiveItems for tempItem with preparedStatement 
      using 0, mostExpensiveItem.Price;
  else
  
    if (minPrice != NULL && maxPrice != NULL)
      // Se han especificado ambos parámetros.
      open expensiveItems for tempItem with preparedStatement 
        using minPrice, maxPrice;
    else
      // Sólo se ha especificado un parámetro.
      if (minPrice == NULL)
        // Se ha especificado el máximo.
        open expensiveItems for tempItem with preparedStatement 
          using 0, maxPrice;
      else
        // Se ha especificado el mínimo.
        open expensiveItems for tempItem with preparedStatement 
          using minPrice, mostExpensiveItem.Price;
      end
    end
    
  end
  
  // Visualizar los resultados en la consola.
  forEach (tempItem)
    SysLib.writeStdout(tempItem.Name :: " " ::
      tempItem.Description :: " $" :: tempItem.Price);
  end
  
end
  6. Modifique la función main para que llame a la función nueva en lugar de a la función antigua: 
    function main()
  minPrice, maxPrice Price?;
  minPrice = 50;
  maxPrice = 500;
  try
    printItemRange2(minPrice, maxPrice);
  onException(exception SQLException)
    handleDBException(exception);
  end
end
  7. Guarde, genere y ejecute el programa nuevo con valores diferentes de minPrice y maxPrice, incluidos valores nulos. El resultado es el mismo que en la función anterior, pero la función ha llegado a él de modo diferente.
Resultados
Este es el código completo del archivo selectItems.egl. Si ve errores marcados con símbolos X rojos en el archivo, asegúrese de que el código coincide con el código de este archivo: Archivo selectItems.egl completado después de la lección 4B.

Punto de comprobación de lección

La creación de sentencias preparadas válidas puede ser complicada, pero las sentencias resultantes son más flexibles que las sentencias codificadas manualmente en SQL explícito. En una lección posterior, aprenderá a establecer el código SQL predeterminado para un componente SQLRecord, que ofrece otro modo de personalizar sentencias SQL.
Para obtener más información acerca de las sentencias preparadas, consulte la sección dedicada a la sentencia prepare.
< Anterior | Siguiente >
Comentarios