Step 4. Add Search Logic to xEmployeeDataServer Server Module

In this step, you will add two new srvroutines to the iiixEmployeeDataServer server module, to perform searches over the employee table (xEmployee) using the SELECT_SQL command.

SELECT_SQL Command The SELECT_SQL command is read only and uses SQL instead of calling the LANSA OAM. The table 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 validation rules, virtual fields or triggers. Using SELECT_SQL requires you to define an SQL statement which SELECT_SQL will perform. The best way to check your SQL logic is to use interactive SQL for the required deployment platform, to ensure that your code will work as expected with that database (for example DB/2, SQL Server or Oracle). LANSA performs minimal checks on your SQL statement. There are two forms of SELECT_SQL available. In the basic SELECT_SQL command, the SQL logic is defined in a 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 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 components and functions. For full information about SELECT_SQL, refer to the Technical Reference Guide. You should study this section of the Technical Reference Guide before attempting to use SELECT_SQL in your own applications.

1.  Open your iiixEmployeeDataServer Server Module in the editor.

2.  Create a srvroutine, named SQLName. Define a srvroutine which maps in fields iiiSearchName and STD_NAME (holds search type) and outputs list xEmployeeList and field IO$STS:

     Your code should look like the following:

Srvroutine Name(SQLName)

Field_Map For(*INPUT) Field(#iiiSearchName)

Field_Map For(*INPUT) Field(#std_name) Parameter_Name(SearchType)

List_Map For(*OUTPUT) List(#xEmployeeList)

Field_Map For(*OUTPUT) Field(#IO$STS)

Endroutine

3.  Complete the srvroutine's initial logic, based on the following:

Clear the list xEmployeeList

Within a CASE loop for field STD_NAME

When = BEGIN

Add the '%' wild card character following the value passed in the field iiiSearchName.

When = CONTAINS

Add the '%' wild card character before and after the value passed in field iiiSearchName.

End CASE loop.

Your code should now look like the following:

Srvroutine Name(SQLName)

Field_Map For(*INPUT) Field(#iiiSearchName)

Field_Map For(*INPUT) Field(#std_name) Parameter_Name(SearchType)

List_Map For(*OUTPUT) List(#xEmployeeList)

Field_Map For(*OUTPUT) Field(#IO$STS)

Clr_List Named(#xEmployeeList)

Case Of_Field(#std_name)

When (= BEGIN)

#iiiSearchName := #iiiSearchName.trim + '%'

When (= CONTAINS)

#iiiSearchName := '%' + #iiiSearchName.trim + '%'

Endcase

 

Endroutine 

4.  The search value in field xEmployeeSurname must be surrounded by single quotes in the SQL statement.

• The SQL statement must define the fields to be returned using their real names (no # prefix).

• The SELECT_SQL's Fields() parameter must list the same columns which the SQL SELECT retrieves, meaning "populate these variables in LANSA". As usual, these LANSA fields are prefixed with a # character.

• QUOTE is a Repository field with a default value of a single quote character.

• Use the free format SELECT_SQL with your SQL statement defined in the Using() parameter.

     Complete the SQLName srvroutines based on the following:

Assign field STD_STRNG to the value QUOTE plus iiiSearchName plus QUOTE.

Extend the definition of STD_STRNG to define the SQL SELECT statement which returns the required fields from table xEmployee, using a Where() clause which compares field xEmployeeSurname with STD_STRNG.trimmed.

Execute SELECT_SQL

For each returned record:

Add an entry to list xEmployeeList

End SELECT_SQL

     Your complete SQLName srvroutine should now look like the following. New code is highlighted.

Srvroutine Name(SQLName)

Field_Map For(*INPUT) Field(#iiiSearchName)

Field_Map For(*INPUT) Field(#std_name) Parameter_Name(SearchType)

List_Map For(*OUTPUT) List(#xEmployeeList)

Field_Map For(*OUTPUT) Field(#IO$STS)

Clr_List Named(#xEmployeeList)

Case Of_Field(#std_name)

When (= BEGIN)

#iiiSearchName := #iiiSearchName.trim + '%'

When (= CONTAINS)

#iiiSearchName := '%' + #iiiSearchName.trim + '%'

Endcase

#std_strng := #quote + #iiiSearchName.AsNativeString + #quote

#std_strng := 'SELECT xEmployeeIdentification, xEmployeeSurname, xEmployeeGivenNames FROM XDEMOLIB.xEmployee WHERE("xEmployeeSurname" LIKE ' + #STD_STRNG.trim + ')'

Select_Sql Fields(#xEmployeeIdentification #xEmployeeSurname #xEmployeeGivenNames) Io_Error(*next) Using(#std_strng)

Add_Entry To_List(#xEmployeeList)

Endselect

Endroutine 

     Important Note: In the SQL statement provided above, the table name is qualified as in XDEMOLIB.xEmployee where XDEMOLIB is the schema or collection name which qualifies the table xEmployee. This syntax varies depending on the DBMS and must therefore be written for the target platform. The example shown is for MS SQL Server. In the LANSA Repository, a table definition specifies the table library. Due to restrictions in schema length (8 long), LANSA renames library in SQL Server. For example, a library name of DC@DEMOLIB becomes a schema name in SQL Server of XDEMOLIB. The first three characters have been replaced with 'X'. DC@DEMOLIB is the default table library for the DEM partition in an Independent Workstation installation. You can quickly check the table library name for your system, by selecting the table and using Properties from the context menu on the Repository tab. For example:

     SQL Server Management Studio will also enable you to examine the table definitions in the database for the Visual LANSA system being used for training purposes.

5.  Create a Srvroutine, named SQLDate, with the following:

Define a Field_Map for input for field iiiSearchMonth.

Define a List_Map for output for the list xEmployeeList

Define a Field_Map for output for field IO$STS.

     Your code should look like the following:

Srvroutine Name(SQLDate)

Field_Map For(*INPUT) Field(#iiiSearchMonth)

List_Map For(*OUTPUT) List(#xEmployeeList)

Field_Map For(*OUTPUT) Field(#IO$STS)

Endroutine

6.  This Srvroutine will use the SQL MONTH function to compare iiiSearchMonth with employee Date of Birth. For example, using the SQL statement:

SELECT xEmployeeIdentification, xEmployeeSurname, xEmployeeGivenNames, xEmployeeDateOfBirth FROM XDEMOLIB.XEMPLOYEE WHERE MONTH(xemployeedateofbirth) = iiiSearchMonth.asstring 

7.  Complete the SQLDate Srvroutine based on the following:

Clear list xEmployeeList

Assign STD_STRNG to:

'SELECT xEmployeeIdentification, xEmployeeSurname, xEmployeeGivenNames, xEmployeeDateOfBirth FROM XDEMOLIB.XEMPLOYEE WHERE MONTH(xemployeedateofbirth) = ' + #iiiSearchMonth.asstring

Read table using SELECT_SQL for required Fields() and Using(#STD_STRNG)

Add each entry to xEmployeeList 

EndSelect

     Your completed srvroutine should look like the following:

Srvroutine Name(SQLDate)

Field_Map For(*INPUT) Field(#iiiSearchMonth)

List_Map For(*OUTPUT) List(#xEmployeeList)

Field_Map For(*OUTPUT) Field(#IO$STS)

Clr_List Named(#xEmployeeList)

#STD_STRNG := 'SELECT xEmployeeIdentification, xEmployeeSurname, xEmployeeGivenNames, xEmployeeDateOfBirth FROM XDEMOLIB.XEMPLOYEE WHERE MONTH(xemployeedateofbirth) = ' + #iiiSearchMonth.asstring

Select_Sql Fields(#xEmployeeIdentification #xEmployeeSurname #xEmployeeGivenNames) Using(#std_strng)

Add_Entry To_List(#xEmployeeList)

Endselect

 

Endroutine 

8.  Compile your iiixEmployeeDataServer Server Module.