PowerBuilder – Refactoring Code to Reduce Database Calls

Posted on Wednesday, November 27th, 2013 at 4:11 pm in

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[1] = 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[1] = 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

Add your comment

Leave a Reply

Top