Kbase 20065: ADM2. Fetching Records in a Query Using GetQueryPosition
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
9/3/2008 |
|
Status: Verified
GOAL:
Example of how to use GetQueryPosition for fetching records in a specific query and to control the returned values. It clarifies the concept of fetching records and testing all the conditions returned by the GetQueryPosition() function.
FACT(s) (Environment):
Progress 9.x
SYMPTOM(s):
ADM2
FIX:
The example makes different queries into a table using an SDO. To follow the example, you need:
1) Progress Version V9.1A or later.
2) A SmartWindow (SW).
3) A SmartDataObject (SDO).
4) A SmartDataBrowser (SDB).
5) A SmartDataViewer with 5 buttons (SDV).
6) A SimpleSmartObject with 1 button (SSO).
7) A Smartlink (SL) between the SDO and the SSO.
8) A connection to Sports2000 Database.
Follow these steps to create a window to execute:
1) Open the AppBuilder.
2) Connect to the Sports2000 Database.
3) Create an SDO in the AppBuilder.
4) Define the query on the Customer Table.
5) Add the fields; Custnum, Name, Phone.
6) Save the SDO with name mysdo.w and close it.
7) Create a SDB linked to SDO mysdo.w.
8) Add all the fields available in the SDO (Custnum, Name, Phone).
9) Finish and save the SDB with name mybrowser.w.
10) Close the SDB.
11) Create a SDV based on SDO mysdo.w.
12) Add field Name.
13) Finish.
14) Add 5 buttons to SDV you just created.
15) On first button change the label to 'One Row'.
16) On Second button change the label to 'Two Rows'.
17) On Third button change the label to 'Three Rows'.
18) On Fourth button change the label to 'Four Rows'.
19) On Fifth button change the label to 'All Rows'.
20) On button 'One Row' click on the Edit Code option and write
the following in the DEFINITION section:
DEFINE VARIABLE hDataSource AS HANDLE NO-UNDO.
DEFINE VARIABLE cWhere AS CHARACTER NO-UNDO INITIAL ''.
21) On button 'One Row' go to the TRIGGERS SECTION and put the following code in the 'ON CHOOSE' trigger:
ASSIGN hDataSource = DYNAMIC-FUNCTION('getDataSource')
cWhere = 'country = "finland" AND '
cWhere = cWhere + 'name = "urpon frisbee"'.
DYNAMIC-FUNCTION('setQueryWhere' IN hDataSource, cWhere).
DYNAMIC-FUNCTION('openQuery' IN hDataSource).
22) On button 'Two Rows' go to the TRIGGERS SECTION and put the following code in the 'ON CHOOSE' trigger:
ASSIGN hDataSource = DYNAMIC-FUNCTION('getDataSource')
cWhere = 'country = "finland" AND '
cWhere = cWhere + '(name = "urpon frisbee" OR '
cWhere = cWhere + 'name = "biljardi ja tennis")'.
DYNAMIC-FUNCTION('setQueryWhere' IN hDataSource, cWhere).
DYNAMIC-FUNCTION('openQuery' IN hDataSource).
23) On button 'Three Rows' go to the TRIGGERS SECTION and put the following code in the 'ON CHOOSE' trigger:
ASSIGN hDataSource = DYNAMIC-FUNCTION('getDataSource')
ASSIGN cWhere = 'country = "finland" AND '
cWhere = cWhere + '(name = "urpon frisbee" OR '
cWhere = cWhere + 'name = "biljardi ja tennis" OR '
cWhere = cWhere + 'name = "super golf center")'.
DYNAMIC-FUNCTION('setQueryWhere' IN hDataSource, cWhere).
DYNAMIC-FUNCTION('openQuery' IN hDataSource).
24) On button 'Four Rows' go to the TRIGGERS SECTION and put the following code in the 'ON CHOOSE' trigger:
ASSIGN hDataSource = DYNAMIC-FUNCTION('getDataSource')
cWhere = 'country = "finland" AND '
cWhere = cWhere + '(name = "urpon frisbee" OR '
cWhere = cWhere + 'name = "biljardi ja tennis" OR '
cWhere = cWhere + 'name = "super golf center" OR '
cWhere = cWhere + 'name = "aerobics valine ky")'.
DYNAMIC-FUNCTION('setQueryWhere' IN hDataSource, cWhere).
DYNAMIC-FUNCTION('openQuery' IN hDataSource).
25) On button 'All Rows' go to the TRIGGERS SECTION and put the following code in the 'ON CHOOSE' trigger:
ASSIGN hDataSource = DYNAMIC-FUNCTION('getDataSource')
cWhere = ''.
DYNAMIC-FUNCTION('setQueryWhere' IN hDataSource, cWhere).
DYNAMIC-FUNCTION('openQuery' IN hDataSource).
26) Save .the SDV with name mysdv.w and close it.
27) Create a new SSO.
28) Add a button inside and change his label to 'Fetch Records'.
29) Go to the code editor for this button and in the DEFINITION section put the following code:
DEFINE VARIABLE cPos AS CHARACTER NO-UNDO.
DEFINE VARIABLE cSDO AS CHARACTER NO-UNDO.
DEFINE VARIABLE hSDO AS HANDLE NO-UNDO.
30) In the TRIGGER SECTION for the CHOOSE event of the button add the following code:
ASSIGN cSDO = DYNAMIC-FUNCTION('linkHandles', 'Print-Source')
hSDO = WIDGET-HANDLE(cSDO).
IF VALID-HANDLE(hSDO) = FALSE THEN
DO:
MESSAGE 'No Print Link Found' VIEW-AS ALERT-BOX.
RETURN NO-APPLY.
END.
ASSIGN cPos = DYNAMIC-FUNCTION('getQueryPosition' IN hSDO).
REPEAT WHILE LOOKUP(cPos,'LastRecord,OnlyRecord') = 0:
MESSAGE cPos VIEW-AS ALERT-BOX.
RUN fetchNext IN hSDO.
ASSIGN cPos = DYNAMIC-FUNCTION('getQueryPosition' IN hSDO).
END.
MESSAGE cPos VIEW-AS ALERT-BOX.
31) Save the SSO with name mysso.w and close it.
32) Create a new SmartWindow (SW).
33) Add the SDO mysdo.w into the window.
34) Add the SDB mybrowser.w into the window.
35) Click Accept (OK) at the message 'Add, Create link from SDO h_mysdo..'.
36) Click Accept (OK) at the message 'Add, Create link from SDO h_mysdo..'.
37) Add to the window the SDV mysdv.w.
38) Click Accept (OK) at the message 'Add, Create link from SDO h_mysdo..'.
39) Add to the window SSO mysso.w.
40) Create a NEW Smartlink (SL) between h_sdo and h_mysso called 'Print'.
41) Save the SW with name mywindow.w.
42) Run the window.
Notice the four returned values for the:
DYNAMIC-FUNCTION("getQueryPosition" IN hSDO)
- FirstRecord
- OnlyRecord
- NotFirstorLast
- LastRecord
It is important to understand that when you fetch a record:
1) Once the query returns the rows, you are positioned in the first row of the query.
2) You must test the values returned by the getQueryPosition function because there are 4 possible values that can be returned.
3) The fetchNext call must be called when the current query position is either 'FirstRecord' or 'NotFirstOrLast'..