In this step, you will create a SearchByName method routine and add code to the SearchButton.Click event to perform a search routine based on field STD_OBJ.

• The search logic will use the SELECT_SQL command to search the table xEmployee.

SELECT_SQL Command

a.  The SELECT_SQL command is read only and uses SQL instead of calling the LANSA OAM. The OAM uses native I/O on the IBM i server and ODBC on other platforms. SELECT_SQL reads the table directly and does not support LANSA validations, virtual fields or triggers.

b.  The best way to check your SELECT_SQL logic is to use interactive SQL for the required deployment platform, to ensure that your logic will work as expected with that database (DB/2, MS SQL Server, Oracle).

     There are two forms of SELECT_SQL available:

• In the basic SELECT_SQL command, the SQL logic is defined in the SELECT_SQL Where() parameter. This means that there are some restrictions on the type of SQL code which you can implement.
• In the SELECT_SQL Free Format command, the SQL statement is written in the SELECT_SQL Using() parameter. This means that any SQL code which is supported by the database can be implemented in this form of SELECT_SQL. This form of SELECT_SQL can only be used in RDMLX enabled functions and components.

     Refer to the Technical Reference Guide for full information on SELECT_SQL command. You should do a detailed study of the Technical Reference information before attempting to use SELECT_SQL.

     Important Note - SQL Tables:

• The schema name used for table xEmployee in the SELECT_SQL will depend on your partition table library name. Visual LANSA creates the SQL table schema name by shortening the library name. For example, library name DCXDEMOLIB, becomes schema name XDEMOLIB. Use SQL Management Tools to access the DB and check the schema name being used.
• If you are running the SELECT_SQL on the IBM i server, the table name is qualified by the library name.

1.  Create a SearchByName method routine. The surname search will use a LIKE operator in the SQL statement. The search value will have "wild card" characters added, so that the search finds all employees with their surname beginning or containing the search value. Add logic to perform the following:

     Clear the list EmployeeList
     Case of field STD_OBJ /* search type */
     When = BEGIN
       Assign STD_STRNG to SearchName as uppercase (see code below)
       Assign STD_STRNG to QUOTE + STD_STRNG + % + QUOTE
     When = CONTAINS
       Assign STD_STRNG to QUOTE + % + SearchName.value.trim + % + QUOTE
     EndCase
     Assign STD_STRNG to SQL statement which retrieves fields Identification, Surname and Given Names from table XDEMOLIB.xEmployee where Surname LIKE STD_STRNG
     SELECT_SQL for fields Identification, Surname and GivenNames Using STD_STRNG
     Assign Fullname to surname.trim + ', ' + GivenNames.trim
     Assign SD_DESCS to xEmployeeIdentification
     Add each entry to EmployeeList
     End Select
     Assign xEmployeeIdentification, xEmployeeSurname and xEmployeeGivenNames to *null

     Your code should look like the following:

     Mthroutine Name(SearchByName)
     Clr_List Named(#EmployeeList)
     Case Of_Field(#STD_OBJ) /* search type */
     When (= BEGIN)
     * make search value uppercase
     #std_strng := #SearchName.value.replacesubstring( 1 1 #SearchName.value.uppercase.substring( 1 1 ) )
     #std_strng := #QUOTE + #STD_strng + '%' + #QUOTE
     When (= CONTAINS)
     #STD_STRNG := #QUOTE + '%' + #SearchName.value.trim + '%' + #QUOTE
     Endcase
     #STD_STRNG := 'SELECT xEmployeeIdentification, xEmployeeSurname, xEmployeeGivenNames FROM XDEMOLIB.XEMPLOYEE WHERE ("xEmployeeSurname" like ' + #std_strng.trim + ')'
     #xEmployeeIdentification := *null
     Select_Sql Fields(#xEmployeeIdentification #xEmployeeSurname #xEmployeeGivenNames) Using(#std_strng)
     #iiiFullName := #xEmployeeSurname.trim + ', ' + #xEmployeeGivenNames.trim
     #std_descs := #xEmployeeIdentification
     Add_Entry To_List(#EmployeeList)
     Endselect
     #xEmployeeIdentification #xEmployeeSurname #xEmployeeGivenNames := *null
     Endroutine

2.  Add code to the SearchButton.Click event to invoke SearchByName or SearchByMonth based on field STD_OBJ.

     Evtroutine Handling(#SearchButton.Click)
     Case (#STD_OBJ) /* search type */
     When ('= BEGIN' '= CONTAINS')
     #com_self.SearchByName
     When (= MONTH)
     #COM_SELF.SearchByMonth
     Endcase
     Endroutine

3.  Create a method routine SearchByMonth which will complete in a later step.

4.  Compile and test the form. You should be able to perform a surname search. Search beginning such as value 'Sa will find all surnames beginning Sa. A Search contains value 'ph' will find surnames such as Randolph and Shepherd. You could easily provide many more name search options.

5.  Complete the SearchByMonth routine. The SQL MONTH function returns the 2 digit month portion of a date field.

     Add code to perform the following:

     Clear list EmployeeList
     Assign STD_STRNG to SQL statement which retrieves fields Identification, Surname, Given Names and DateOfBirth from table XDEMOLIB.xEmployee where MONTH(DateofBirth) = ' + #SearchMONTH.AsString
     SELECT_SQL for fields Identification, Surname and GivenNames Using STD_STRNG
     Assign Fullname to surname.trim + ', ' + GivenNames.trim
     Assign STD_DESCS to xEmployeeIdentification
     Add each entry to EmployeeList
     End Select

     Your code should look like the following:

     Mthroutine Name(SearchByMonth)
     Clr_List Named(#EmployeeList)
     #STD_STRNG := 'SELECT xEmployeeIdentification, xEmployeeSurname, xEmployeeGivenNames, xEmployeeDateOfBirth FROM XDEMOLIB.XEMPLOYEE WHERE MONTH(xemployeedateofbirth) = ' + #iiiMonth.asstring
     #xEmployeeIdentification := *null
     Select_Sql Fields(#xEmployeeIdentification #xEmployeeSurname #xEmployeeGivenNames) Using(#std_strng)
     #iiiFullName := #xEmployeeSurname.trim + ', ' + #xEmployeeGivenNames.trim
     #STD_DESCS := #xEmployeeIdentification
     Add_Entry To_List(#EmployeeList)
     Endselect
     #xEmployeeIdentification #xEmployeeSurname #xEmployeeGivenNames := *null

     Endroutine

6.  Compile and test the form. You should now be able to perform a search by month and select any month to return all employees with Date of Birth containing the search month.