Example of using SELECT_SQL in a remote procedure

Date:9 September 2002
Product/Release:Visual LANSA V9.1 and above
Abstract:Example code to invoke a server function using SELECT_SQL that can return up to 9999 entries to a client system
Submitted By:LANSA Technical Support
Last Review:November 2010

Following is example code to use SELECT_SQL that can return up to 9999 entries to a client system. To use, simply copy/paste and then compile in any system that has the standard demo system installed.

* **************************
* Part 1 : TSTFORM : The VL Test Form
* **************************

FUNCTION OPTIONS(*DIRECT)
Begin_Com Role(*EXTENDS #PRIM_FORM) Height(429) Left(402) Top(108) Width(452) 
Define_Com Class(#PRIM_PHBN) Name(#PHBN_1) Caption('Execute SELECT_SQL') Displayposition(1) Left(16) Parent(#COM_OWNER) Tabposition(1) Top(7) Width(416)
Define_Com Class(#PRIM_LTVW) Name(#EMP_VIEW) Displayposition(2) Fullrowselect(True) Height(290) Left(14) Parent(#COM_OWNER) Tabposition(2) Top(45) Width(425)
Define_Com Class(#PRIM_LVCL) Name(#LVCL_1) Displayposition(1) Parent(#EMP_VIEW) Source(#EMPNO) Width(17)
Define_Com Class(#PRIM_LVCL) Name(#LVCL_2) Displayposition(2) Parent(#EMP_VIEW) Source(#SURNAME) Width(28)
Define_Com Class(#PRIM_LVCL) Name(#LVCL_3) Displayposition(3) Parent(#EMP_VIEW) Source(#GIVENAME) Width(30)
Define_Com Class(#PRIM_LVCL) Name(#LVCL_4) Displayposition(4) Parent(#EMP_VIEW) Source(#SALARY) Width(20) Widthtype(Remainder)
Define_Com Class(#PRIM_STBR) Name(#STBR_1) Displayposition(3) Height(22) Left(0) Messageposition(1) Parent(#COM_OWNER) Tabposition(3) Tabstop(False) Top(380) Width(444)

* Define the "action" field used to control function
Define #Action Reffld(#STD_OBJ)
Define #Ret_Code *char 2
Def_List Name(#BUF_LIST) Fields(#EMPNO #GIVENAME #SURNAME #SALARY) Type(*WORKING) Entrys(627)

EVTROUTINE HANDLING(#PHBN_1.Click)

* Clear the current list view of employees
CLR_LIST NAMED(#EMP_VIEW)

* Now call TSTFUNQ on the server until all data received
CHANGE FIELD(#ACTION) TO(SELECT)
DOUNTIL COND('#ACTION = END')
CLR_LIST NAMED(#BUF_LIST)
EXCHANGE FIELDS(#ACTION)
USE BUILTIN(CALL_SERVER_FUNCTION) WITH_ARGS(*SSERVER_SSN TSTFUNQ Y Y #BUF_LIST) TO_GET(#RET_CODE)
LEAVE IF('#RET_CODE *NE OK')
SELECTLIST NAMED(#BUF_LIST)
ADD_ENTRY TO_LIST(#EMP_VIEW)
ENDSELECT
ENDUNTIL
ENDROUTINE
END_COM


* **************************
* Part 2 : TSTFUNQ : The SELECT_SQL Operation on the Server
* **************************

Function Options(*HEAVYUSAGE *DIRECT) Rcv_List(#RET_LIST)

* The set of fields that will be returned to the client
Group_By Name(#XG_RECORD) Fields(#EMPNO #GIVENAME #SURNAME #SALARY)

* The byte length of the fields that will be returned
Define Field(#XG_RECLEN) Reffld(#STD_NUM)
Change Field(#XG_RECLEN) To('5 + 20 + 20 + 6')

* Define the list to be returned to the client.
Define Field(#RET_COUNT) Reffld(#STD_NUM) Desc('Count of entries in #RET_LIST')
Def_List Name(#RET_LIST) Fields(#XG_RECORD) Counter(#RET_COUNT) Type(*WORKING) Entrys(9999)

* #RET_LIMIT defines the maximum number of entries that can be returned.
Define Field(#RET_LIMIT) Reffld(#STD_NUM) Desc('Limit of entries allowed in #RET_LIST')
Change Field(#RET_LIMIT) To('(32000 / #XG_RECLEN) - 1')

* Define the list used to hold the SELECT_SQL results
Define Field(#SQL_COUNT) Reffld(#STD_NUM) Desc('Count of entries in #SQL_LIST')
Define Field(#SQL_SENDC) Reffld(#STD_NUM) Desc('Entries sent to client from #SQL_LIST')
Def_List Name(#SQL_LIST) Fields(#XG_RECORD) Counter(#SQL_COUNT) Type(*WORKING) Entrys(9999)

* Define the "action" field used to control function
Define Field(#ACTION) Reffld(#STD_OBJ)

* If the action is "SELECT" then select the rows into the SQL list
If Cond('#Action = SELECT')
Clr_List Named(#SQL_LIST)
Change Field(#SQL_SENDC) To(0)
Select_Sql Fields(#EMPNO #GIVENAME #SURNAME #SALARY) From_Files((PSLMST)) Where('SALARY > 0')
Add_Entry To_List(#SQL_LIST)
Endselect
Endif

* Now return as many records as is possible in the return working list ....
Clr_List Named(#RET_LIST)
Begin_Loop
If Cond('#SQL_SENDC >= #SQL_COUNT')
Use Builtin(SET_FOR_LIGHT_USAGE)
Change Field(#ACTION) To(END)
Leave
Endif
If Cond('#RET_COUNT >= #RET_LIMIT')
Use Builtin(SET_FOR_HEAVY_USAGE)
Change Field(#ACTION) To(MORE)
Leave
Endif
Change Field(#SQL_SENDC) To('#SQL_SENDC + 1')
Get_Entry Number(#SQL_SENDC) From_List(#SQL_LIST)
Add_Entry To_List(#RET_LIST)
End_Loop

* Finished. At this point #ACTION will always contain "END" or "MORE"
Exchange Fields(#ACTION)
Return