< Previous | Next >

Lesson 4: Constructing queries dynamically with prepare

The EGL prepare statement lets you construct SQL statements dynamically.
In the previous lesson, you created a function that selected items from a database that had a price greater than or equal to a parameter:
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
What if you wanted to return items that had a price less than or equal to the parameter? Or, what if you wanted to return items between a maximum and minimum price? Using explicit SQL in the open statement, you would have to code three functions, one for each possibility, because the comparisons in the WHERE clause of the SQL statement are hard-coded.
A more flexible way to create a SELECT statement is to use the EGL prepare statement to assemble the query at run time. When using prepare, you first create a STRING variable that contains the text of the SQL code:
selectStatement STRING = 
  "select * from EGL.ITEM where EGL.ITEM.PRICE >= 500";
Then you use the prepare statement to convert the string into an SQL statement. You must also specify a name for the statement and optionally, a record variable to indicate which SQLRecord part the statement will operate on:
tempItem Item;
prepare preparedStatement from selectStatement for tempItem;
Then, you can use the prepared statement in place of explicit SQL in an EGL get, open, or execute statement (you will learn more about execute in a later lesson):
open expensiveItems for tempItem with preparedStatement;

Common errors in prepared statements

Creating an SQL statement from a string in this way is an advanced technique for programmers who are familiar with SQL. EGL does not check the SQL statement for accuracy at design time, so you must ensure that the string will resolve to a correct SQL statement at run time. Here is a list of common mistakes in working with prepared statements:
Host variables
You cannot use host variables in prepared statements in the same way as in explicit SQL. Take the following example:
tempItem Item;
maxPrice Price = 500;
selectStatement STRING = 
  "select * from EGL.ITEM where EGL.ITEM.PRICE >= :maxPrice";
prepare preparedStatement from selectStatement for tempItem;
In this case, the actual SQL statement will include the string :maxPrice exactly as it is, without using it as a host variable and replacing it with the value of the maxPrice variable. The result is an incorrect SQL statement.
Instead, you must insert the values of the variables into the string:
tempItem Item;
maxPrice Price = 500;
selectStatement STRING = 
  "select * from EGL.ITEM where EGL.ITEM.PRICE >= " :: maxPrice;
prepare preparedStatement from selectStatement for tempItem;
In this case, the value of the maxPrice variable is resolved when EGL creates the string, creating the following SQL statement:
select * from EGL.ITEM where EGL.ITEM.PRICE >= 500
Later in this lesson, you will learn an alternate way of using variables in prepared statements.
Spacing
Be careful to separate keywords in the SQL statement with spaces. For example, take the following SQL statement, which is prepared by concatenating several string values with the concatenation operator (::=):
incorrectSQL STRING;
incorrectSQL = "select * from EGL.ITEM where";
incorrectSQL ::= "EGL.ITEM.PRICE >= :maxPrice";
incorrectSQL ::= "order by EGL.ITEM.PRICE";
This example creates the following incorrect SQL statement:
select * from EGL.ITEM whereEGL.ITEM.PRICE >= :maxPriceorder by EGL.ITEM.PRICE
This statement needs a space after WHERE and another before ORDER BY.
Correct and appropriate statements
EGL does not validate prepared statements, so it is up to you to ensure that your statement will be correct SQL.

Also, prepared SQL statements, like explicit SQL, must be appropriate for the EGL statement with which they are used. For example, the EGL get and open statements expect a result set. To use a prepared statement with either of these statements, the SQL code must return a result set. For this reason, you cannot prepare an SQL INSERT or DELETE statement and then use that statement with get or open.

Lesson 4A: Using a prepared statement

In this lesson, you will alter the program you created in the previous exercise to accept two parameters, either or both of which can be null. The function will create a query based on which parameters are not null, returning items within a certain range of prices.
  1. Open the selectItems.egl file.
  2. Add a function named printItemRange that receives two nullable Price parameters: 
    function printItemRange(minPrice Price? in, maxPrice Price? in)

end

    The question mark (?) after the parameter type indicates that the variable can receive a null value. This way, the function will be able to accept either or both of a minimum and maximum for the range of results.

  3. Create a STRING variable to hold the SQL code and insert the beginning of a SELECT statement into it: 
    selectStatement STRING = "select * from EGL.ITEM where ";
    The next step is to determine what the appropriate WHERE clause should be. There are four possible situations:
    • Both parameters are null. In this case, the function will print all of the items with a price greater than or equal to zero, so the WHERE clause should be where EGL.ITEM.PRICE > 0.
    • Both parameters are specified. In this case, the function will use the SQL BETWEEN keyword to print values between or equal to the parameters. The WHERE clause should be where EGL.ITEM.PRICE between :minPrice and :maxPrice.
    • Only the maximum parameter is specified. In this case, the function will print all of the items with a price less than or equal to the maximum: where EGL.ITEM.PRICE <= :maxPrice.
    • Only the minimum parameter is specified. In this case, the function will print all of the items with a price greater than or equal to the minimum: where EGL.ITEM.PRICE >= :minPrice.
    There are several ways to make this decision in EGL, but a simple way it so use three nested if statements.
  4. Add the following code to the function to complete the WHERE clause: 
    if (minPrice == NULL && maxPrice == NULL)
  // Two empty parameters;
  // return all rows with PRICE greater than zero.
  selectStatement ::= "EGL.ITEM.PRICE > 0 ";
else

  if (minPrice != NULL && maxPrice != NULL)
    // Both parameters were specified.
    selectStatement ::= "EGL.ITEM.PRICE between " :: minPrice :: " and " :: maxPrice :: " ";
  else
    // Only one parameter was specified.
    if (minPrice == NULL)
      // Maximum was specified.
      selectStatement ::= "EGL.ITEM.PRICE <= " :: maxPrice :: " ";
    else
      // Minimum was specified.
      selectStatement ::= "EGL.ITEM.PRICE >= ":: minPrice :: " ";
    end
  end
  
end
  5. Complete the statement with an ORDER BY clause: 
    selectStatement ::= "order by EGL.ITEM.PRICE";
  6. To make sure the SQL statement is correct, print it to the console: 
    SysLib.writeStdout("Completed query: "::selectStatement);
  7. Prepare the statement to be used, referring to a record created from the table that the prepared statement will access: 
    tempItem Item;
prepare preparedStatement 
  from selectStatement
  for tempItem;
  8. Use the prepared statement in place of explicit SQL in an open statement: 
    open expensiveItems for tempItem with preparedStatement;
  9. Print the results of the query: 
    forEach (tempItem)
  SysLib.writeStdout(tempItem.Name :: " " ::
    tempItem.Description :: " $" :: tempItem.Price);
end
    The completed function looks like this:
      function printItemRange(minPrice Price? in, maxPrice Price? in)
    
    selectStatement STRING = "select * from EGL.ITEM where ";
    
    if (minPrice == NULL && maxPrice == NULL)
      // Two empty parameters;
      // return all rows with PRICE greater than zero.
      selectStatement ::= "EGL.ITEM.PRICE > 0 ";
    else
    
      if (minPrice != NULL && maxPrice != NULL)
        // Both parameters were specified.
        selectStatement ::= "EGL.ITEM.PRICE between " :: minPrice :: " and " :: maxPrice :: " ";
      else
        // Only one parameter was specified.
        if (minPrice == NULL)
          // Maximum was specified.
          selectStatement ::= "EGL.ITEM.PRICE <= " :: maxPrice :: " ";
        else
          // Minimum was specified.
          selectStatement ::= "EGL.ITEM.PRICE >= ":: minPrice :: " ";
        end
      end
      
    end
    
    // Regardless of parameters, sort by price.
    selectStatement ::= "order by EGL.ITEM.PRICE";
    
    // Print completed SQL code.
    SysLib.writeStdout("Completed query: "::selectStatement);
    
    // Prepare the statement to be used.
    tempItem Item;
    prepare preparedStatement 
      from selectStatement
      for tempItem;
    
    // Use the prepared statement in place of explicit SQL.
    open expensiveItems for tempItem with preparedStatement;
    
    // Print the results to the console.
    forEach (tempItem)
      SysLib.writeStdout(tempItem.Name :: " " ::
        tempItem.Description :: " $" :: tempItem.Price);
    end
    
  end
  10. Call the function from the program's main function: 
      function main()
    minPrice, maxPrice Price?;
    minPrice = 50;
    maxPrice = 500;
    try
      printItemRange(minPrice, maxPrice);
    onException(exception SQLException)
      handleDBException(exception);
    end
  end
  11. Generate and run the program.
With the parameter values in the example above, the function prints the items with a price between 50 and 500:
Completed query: 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
You can edit the parameter values and try different ranges, including null values, as in this example:
function main()
  minPrice, maxPrice Price?;
  minPrice = 500;
  maxPrice = null;
  try
    printItemRange(minPrice, maxPrice);
  onException(exception SQLException)
    handleDBException(exception);
  end
end
Passing values of 500 and null yields results like these:
Completed query: 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

Here is the complete code of the selectItems.egl file. If you see any errors marked by red X symbols in the file, make sure your code matches the code in this file:Completed selectItems.egl file after lesson 4A.

Lesson 4B: Host variables in prepare statements

The SQL statement in the previous example was limited in that the variables in the WHERE clause were fixed. It's possible to created a prepared statement before you know which variables will be used in the statement. This type of statement is more complicated, but it can provide flexibility if you want to prepare a statement and fill in the details later.
In the previous example, you had to resolve the values of the variables in order to concatenate them with the rest of the query string:
selectStatement STRING = "select * from EGL.ITEM where ";
...
selectStatement ::= "EGL.ITEM.PRICE between " :: minPrice :: " and " :: maxPrice :: " ";
When you create a prepared statement, you can also place question marks (?) into the statement, indicating that the values will be filled in later:
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;
Now the prepared statement has places for two host variables, represented by the question marks in the BETWEEN clause. When you are ready to use the prepared statement, insert values with the using statement. For example, to search for rows with a price between 5 and 500, pass the literals 5 and 500 to the statement:
open expensiveItems2 for tempItem with preparedStatement2
  using 5, 500;
Of course, you can also use variables along with the using keyword:
open expensiveItems2 for tempItem with preparedStatement2
  using minPrice, maxPrice;
Using host variables in a prepared statement in this way can be useful if you want to assemble a statement once and call it several times with different values. For example, this function uses the same prepared statement three times to print values within three different ranges:
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("\nPrinting values between 0 and 200");
  open expensiveItems for tempItem with preparedStatement using 0, 200;
  forEach(tempItem)
    SysLib.writeStdout(tempItem.Name :: " " :: tempItem.Description ::
      " $" :: tempItem.Price);
  end

  SysLib.writeStdout("\nPrinting values between 200 and 500");
  open expensiveItems for tempItem with preparedStatement using 200, 500;
  forEach(tempItem)
    SysLib.writeStdout(tempItem.Name :: " " :: tempItem.Description ::
      " $" :: tempItem.Price);
  end

  SysLib.writeStdout("\nPrinting values between 500 and 1,000");
  open expensiveItems for tempItem with preparedStatement
    using 500, 1000;
  forEach(tempItem)
    SysLib.writeStdout(tempItem.Name :: " " :: tempItem.Description ::
      " $" :: tempItem.Price);
  end

end

Using host variables in this way, it's possible to rewrite the program from the previous section to use a single prepared statement, instead of altering the statement for each possible case:

  1. In the selectItems.egl file, add a new function that accepts a maximum and minimum parameter, just like in the previous section: 
    function printItemRange2(minPrice Price? in, maxPrice Price? in)

end
    In this function, you will prepare the statement first and insert variables into it later.
  2. Create a string variable to hold the prepared statement and then use the prepare statement to prepare it: 
    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;
    You will pass two parameters to this prepared statement, representing the bounds of the search. If the maximum price is not specified, you will need to know the upper boundary to search for, since you cannot convert the EGL.ITEM.PRICE between ? and ? clause into EGL.ITEM.PRICE >= ? once you have prepared the statement. Therefore, you need to determine the highest price in the table to use as the upper boundary. You can determine this upper boundary easily with the SQL MAX() function.
  3. Create an Item variable and retrieve the price of the most expensive item in the database, using the MAX() function to place it into the Price field of the record variable: 
    get mostExpensiveItem 
  into mostExpensiveItem.Price
  with
    #sql{
      select
        max(EGL.ITEM.PRICE)
      from EGL.ITEM
    };
    Now the mostExpensiveItem record contains the highest price in the Items table.
  4. Determine which parameters were specified and use the prepared statement accordingly: 
    if (minPrice == NULL && maxPrice == NULL)
  // Two empty parameters;
  open expensiveItems for tempItem with preparedStatement 
    using 0, mostExpensiveItem.Price;
else

  if (minPrice != NULL && maxPrice != NULL)
    // Both parameters were specified.
    open expensiveItems for tempItem with preparedStatement 
      using minPrice, maxPrice;
  else
    // Only one parameter was specified.
    if (minPrice == NULL)
      // Maximum was specified.
      open expensiveItems for tempItem with preparedStatement 
        using 0, maxPrice;
    else
      // Minimum was specified.
      open expensiveItems for tempItem with preparedStatement 
        using minPrice, mostExpensiveItem.Price;
    end
  end
  
end
  5. Use forEach to print the results: 
    forEach (tempItem)
  SysLib.writeStdout(tempItem.Name :: " " ::
    tempItem.Description :: " $" :: tempItem.Price);
end
    The completed function looks like this:
    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";
  
  // Prepare the statement to be used.
  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)
    // Two empty parameters;
    open expensiveItems for tempItem with preparedStatement 
      using 0, mostExpensiveItem.Price;
  else
  
    if (minPrice != NULL && maxPrice != NULL)
      // Both parameters were specified.
      open expensiveItems for tempItem with preparedStatement 
        using minPrice, maxPrice;
    else
      // Only one parameter was specified.
      if (minPrice == NULL)
        // Maximum was specified.
        open expensiveItems for tempItem with preparedStatement 
          using 0, maxPrice;
      else
        // Minimum was specified.
        open expensiveItems for tempItem with preparedStatement 
          using minPrice, mostExpensiveItem.Price;
      end
    end
    
  end
  
  // Print the results to the console.
  forEach (tempItem)
    SysLib.writeStdout(tempItem.Name :: " " ::
      tempItem.Description :: " $" :: tempItem.Price);
  end
  
end
  6. Modify the main function to call the new function instead of the old function: 
    function main()
  minPrice, maxPrice Price?;
  minPrice = 50;
  maxPrice = 500;
  try
    printItemRange2(minPrice, maxPrice);
  onException(exception SQLException)
    handleDBException(exception);
  end
end
  7. Save, generate, and run the new program with different values of minPrice and maxPrice, including null values. The results are the same as the previous function, but the function arrived at the results in a different way.
Here is the complete code of the selectItems.egl file. If you see any errors marked by red X symbols in the file, make sure your code matches the code in this file:Completed selectItems.egl file after lesson 4B.

Lesson checkpoint

Creating valid prepared statements can be complicated, but the resulting statements are more flexible than statements hard-coded in explicit SQL. In a later lesson, you will learn to set the default SQL code for a SQLRecord part, which offers another way of customizing SQL statements.
For more information on prepared statements, see prepare.
< Previous | Next >
Feedback