SearchLibrary
package libraries;
library SearchLibrary type BasicLibrary
end
function NameAndStateSearch_And(lname STRING in,
state CHAR(2) in, customer Customer[])
get customer;
end
Right now, this function will retrieve every record in the database. In the next few steps, you edit the SQL statement generated by this function so that only the records matching the search terms lname and state are returned.
Strictly speaking, nothing has changed in your code. EGL has merely exposed the default SQL code that it creates when it encounters the code get customer. Now that this SQL code is explicitly shown on the page, you can edit it to make it behave differently. In this case, you want to change the statement from retrieving every customer record to retrieving only the customer records with a matching last name and state.
where LASTNAME like :lname
and "STATE" = :state
The code looks like this:

The code you've added is not EGL but SQL. LASTNAME is the complete name of a field in the sample database your project is using. If you look at the Record parts in the package eglderbydb.data, you will see that the records such as the Customer record also refer to these fields. The code :lname and :state are called host variables, which in this context are EGL variables that you use in SQL code. The STATE is enclosed in quotes to indicate that "state" is the name of the table, not the SQL reserved word.
EGL provides different ways to create and generate SQL statements. In an earlier EGL tutorial, you retrieved a specific database record by specifying a particular customer ID number. This clause created a SQL where statement similar to the where statement you just added. You could also use a defaultSelectCondition to perform the same task.
searchTerms Customer;
searchResults Customer[0];
resultMessage CHAR(80);
numberOfResults INT;
Next, you need to create a function
to be called from the web page. This function will pass the searchResults
variable and the necessary fields from the searchTerms variable to
the function in the library. function searchFunction()
searchTerms.LastName = searchTerms.LastName::"%";
SearchLibrary.NameAndStateSearch_And(
searchTerms.LastName,
searchTerms.State, searchResults);
resultMessage = " customer(s) found.";
numberOfResults = searchResults.getSize();
end
Ignore any red Xs for now. function onPrerender()
if (searchResults.getSize() == 0)
resultMessage = "No customers found or no search criteria entered.";
end
end
Here is some information about the page code you just added:
Here is the complete code of the customersearch.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 customersearch.egl file after lesson 2
This search page is difficult to use because the user must know both the customer's state and the first letter of the customer's last name. It would be better if the user were able to choose between an AND search and an OR search. In the next lesson, you will add this option to the page. In a later lesson, you will change the State input field to a combo box that lists all of the valid states used in the database.
In addition, there is a problem with "customer(s) found" display; on output, the space before "customer(s)" was lost. In the next exercise, you will change the cascading style sheet (CSS) for the page to fix this problem.
In this exercise you will change the more general template.
padding-right:5px;

