[ |../../index.htm#lansa/frmeng01_0372.htm]
You are here:

Step 4. Add Search Logic

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.
   
  [ |../../index.htm#lansa/frmeng01_0372.htm]