Step 3 Add Search Logic

Step 3. Add Search Logic

FRM085 - Update from a Grid

In this step you will create Click events for both search buttons and complete the search logic using SELECT_SQL to populate the EMPLOYS List View.

1.  Select the SEARCH_MONTH push button. Change its Enabled property to False and create a Click event.

2.  The SQL will have a WHERE clause, which compares STARTDTER with a work field containing a mask '__nn__', where nn is the character value for the month selector field iiiMONTH.

     Define a work field (inside the SEARCH_MONTH.Click event) as follows:

 Define Field(#iiiDATE6) Type(*char) Length(6)

     STARTDTER is a signed, length 6 field,

3.  The date mask value can be created in field iiiDATE6 using the asString and RightAdjust intrinsic functions.

     Hint: Use the F2 Feature help on field #iiiMONTH to discover the intrinsic functions (methods) available.

     Note: On this occasion you are first using the function asString from the numeric field iiiMONTH and then using a RightAdjust function which is available once it is a string. You can use F2 Feature help on any Alpha or String field to discover the RightAdjust function. Then use F1 help on an intrinsic function (method) to display help text and examples.

     The code required is:

#iiiDATE6 := '__' + #iiiMONTH.asDisplayString.RightAdjust( 2, "0" ) + '__'

     The RightAdjust pads the result to length 2, with a pad character of 0.

Note:

     In the next step you will add the SELECT_SQL logic to add rows to list view EMPLOYS.

     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 file directly and is not subject to LANSA validation, virtual field or triggers.

     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.

     For full information about SELECT_SQL, refer to the Technical Reference Guide. You should do a detailed study of this Technical Reference information before attempting to use SELECT_SQL.

4.  The SQL statement to retrieve employees with a start date in the required month is as follows:

SELECT EMPNO, SURNAME, GIVENAME, STARTDTER FROM XDEMOLIB.PSLMST WHERE ("STARDTER LIKE '__mm__')

   Where mm = the month number required.

   Complete the SEARCH_MONTH Click event routine as shown below:

Evtroutine Handling(#SEARCH_MONTH.Click)
#iiiDATE6 := '__' + #iiiMONTH.asDisplayString.rightAdjust( 2, "0" ) + '__'

Clr_List Named(#EMPLOYS)
#std_strng := 'WHERE (' + '"STARTDTER"' + ' LIKE ' + #quote + #iiiDATE6 + #quote + ')'

#std_strng := 'SELECT EMPNO, SURNAME, GIVENAME, STARTDTER FROM XDEMOLIB.PSLMST ' + #std_strng.trim
Select_Sql Fields(#EMPNO #SURNAME #GIVENAME #startdter) Using(#std_strng)

#fullname := #SURNAME + ', ' + #GIVENAME
Add_Entry To_List(#EMPLOYS)

Endselect
#EMPNO #SURNAME #GIVENAME := *default

Endroutine

Note: The schema name used for file PSLMST in the SELECT_SQL will depend on your partition file library name. Visual LANSA creates the SQL table by shortening the library name. For example DCXDEMOLIB, becomes XDEMOLIB as used in the supplied RDML above. Use SQL Management Tools to access the DB and check the schema name being used.

5.  Create a Changed event for field iiiMONTH. Add code to this event to enable the SEARCH_MONTH button. Your code should look like the following:

Evtroutine Handling(#IIIMONTH.Changed) Options(*NOCLEARMESSAGES *NOCLEARERRORS)
#SEARCH_MONTH.enabled := true
Endroutine

6.  Compile your form and test it. You should be able to select a month. The search button should populate the list view. Hint: Try a number of different months.

7.  Select the SEARCH_NAME button, change its Enabled property to false and create a Click event for it.

8.  The SQL statement required to retrieve employees selecting by SURNAME based on in input search value such as 'S%' or %smi%' is as follows:

SELECT EMPNO, SURNAME, GIVENAME FROM XDEMOLIB.PSLMST WHERE (#SURNAME" LIKE 'XX')

Where XX = the search criteria.

 Note: Once again you need to know the schema name for table PSLMST in your local SQL Server database.    

Your completed code should look like the following:

Evtroutine Handling(#SEARCH_NAME.Click)
Clr_List Named(#EMPLOYS)

#std_strng := #quote + #IIISRCNME + #quote
#std_strng := 'SELECT EMPNO, SURNAME, GIVENAME FROM XDEMOLIB.PSLMST where ("surname" like ' + #std_strng.trim + ')'

Change Field(#EMPNO) To(*blank)
Select_Sql Fields(#EMPNO #SURNAME #GIVENAME) Using(#std_strng)

#fullname := #SURNAME + ', ' + #GIVENAME
Add_Entry To_List(#EMPLOYS)

Endselect
#EMPNO #SURNAME #GIVENAME := *blanks

Endroutine

9.  Create a Changed event for the field iiiSRCNME. Complete this event routine to enabled the SEARCH_NAME button. Your code should look like the following:

Evtroutine Handling(#IIISRCNME.Changed) Options(*NOCLEARMESSAGES *NOCLEARERRORS)
#SEARCH_NAME.enabled := true
Endroutine
 

10. Compile your form and test it. Check the SEARCH_NAME logic using values such as SM% and %Y%.