PowerBuilder – Refactoring Code to Reduce Database Calls
So I’m working on a fairly large enhancement to an application which is about twelve years old. In a nutshell it involves allowing users to see data for multiple sites and multiple locations within each site. Originally the app was created so the user could only view a single site at a time. Which sites (and locations) are visible to any given user is based on security settings.
The existing windows normally had your typical ‘search’ datawindows which defaulted to the ‘default’ site assigned to the user and then they could select a location from a drop down datawindow.
Example ‘post open’ event on window code:
datawindowchild ldwc long ll_rows dw_search.getchild('loc_code', ldwc) ldwc.settransobject(SQLCA) ll_rows = ldwc.retrieve(gs_user_id) dw_search.object.loc_code = gs_default_site
Example ‘itemchanged’ event on dw_search code
this.getChild("site_to", ldwc) ldwc.setTransObject(SQLCA) If ldwc.Retrieve(long(data)) = 0 then ldwc.insertrow(0) End if
So each time the user changed the location value another retrieve was sent to the database.
I needed to add a site column to the search datawindow which would also be a dddw. This meant that even more calls to the database would occur as the user changed the value in the new column.
My approach involved creating an nvo with two datastores, one for the sites and the other for the locations. Each were populated when the window was opened (populated based on the user’s security settings). Then when I needed to set up the drop down datawindow columns I used the following.
// all sites and locations for this user ls_sites = invo_multisite.ids_site.object.datawindow.data ls_locs = invo_multsite.ids_loc.object.datawindow.data dw_search.getchild('site_code', ldwc) ldwc.importstring(ls_sites) dw_search.object.site_code = is_site_code ll_find = ldwc.Find('site_code = ' + string(is_site_code), 1, ldwc.RowCount() + 1) dw_search.getchild('loc_code', ldwc) ldwc.importstring(ls_locs) ldwc.setfilter("site_code = '" + is_site_code + "' AND status = 'Active'" ldwc.filter() // only show locations associated with the chosen site
You need to remember to turn off Autoretrieve on the DDDW columns you are using for this. You also need to have the datawindow object used in the nvo datastores the same as that used in the other datawindow dddw columns. If you need to account for user access being changed during the sesssion, set up a timer in the nvo and periodically refresh the datastores.
You might also be interested in