PowerBuilder ‘Gotcha’ – Datawindow retrieval arguments
So I’m working on a scheduling process and I have a datawindow object which takes a number of arguments.
I have them set up as al_pro_id (long), adt_end (datetime), adt_start (datetime), al_resource_id[] (long array), and al_dept_id (long).
To save from maintaining two separate objects, I used this object for a datawindow as well as for datastores which check things behind the scene. Nothing here is unusual so far.
In my code I inadvertently reversed the end date and the start date in one of my retrieves. Not surprising the retrieve returned no rows. So I check the sqlca, make sure the assignment of the datawindow object is correct, and validate the SQL from the object in a SQL session with the arguments put in place – all were fine.
When I put a debug in the transaction object sqlpreview event this is what I got:
Date parms in reverse order
select rs.resource_id , rs.resource_ds , rs.bok_qy , qty_bok = ( select count(*) from RR_EVENT_RESOURCE_SCHED ers WHERE ers.resource_id = rg.resource_id AND ers.evt_start_ts < {ts '2010-07-16 09:15:00.000'} AND ers.evt_stop_ts > {ts '2010-07-16 10:15:00.000'} AND ers.evt_resource_sch_id not in (89407)) , rrg.num_req_qty , pr.proc_name , chosen = CASE WHEN (select count(*) from RR_EVENT_RESOURCE_SCHED ersd WHERE ersd.resource_id = rg.resource_id AND ersd.evt_resource_sch_id in ({ts '2010-07-16 09:15:00.000'})) > 0 THEN 'Y' ELSE 'N' END from RR_PROCESS pr join RR_REQUIRED_RESOURCE_GROUP rrg on pr.pro_id = rrg.pro_id join RR_SCHEDULING_GROUP sg on rrg.sch_grp_id = sg.sch_grp_id join RR_RESOURCE_GROUP rg on sg.sch_grp_id = rg.sch_grp_id join RR_RESOURCE rs on rg.resource_id = rs.resource_id left outer join RR_ROOM rm on rg.resource_id = rm.resource_id and rm.row_status_cd = 'A' and rm.or_dpt_id = {ts '2010-07-16 10:15:00.000'} left outer join RR_PERSON p on rs.persn_id = p.persn_id and p.row_status_cd = 'A' where IsNull(rm.resource_id,0) = 0 and IsNull(p.persn_id,0) = 0 and p.pro_id = 89407
Date parms in correct order
select rs.resource_id , rs.resource_ds , rs.bok_qy , qty_bok = ( select count(*) from RR_EVENT_RESOURCE_SCHED ers WHERE ers.resource_id = rg.resource_id AND ers.resource_evt_start_ts < {ts '2010-07-16 10:15:00.000'} AND ers.resource_evt_stop_ts > {ts '2010-07-16 09:15:00.000'} AND ers.evt_resource_sch_id not in (89407)) , rrg.num_req_qy , pr.proc_name , chosen = CASE WHEN (select count(*) from RR_EVENT_RESOURCE_SCHED ersd WHERE ersd.resource_id = rg.resource_id AND ersd.evt_resource_sch_id in (89407)) > 0 THEN 'Y' ELSE 'N' END from RR_PROCESS pr join RR_REQUIRED_RESOURCE_GROUP rrg on pr.pro_id = rrg.pro_id join RR_SCHEDULING_GROUP sg WITH on rrg.sch_grp_id = sg.sch_grp_id join RR_RESOURCE_GROUP rg on sg.sch_grp_id = rg.sch_grp_id join RR_RESOURCE rs on rg.resource_id = rs.resource_id left outer join RR_ROOM rm on rg.resource_id = rm.resource_id and rm.row_status_cd = 'A' and rm.or_dpt_id = 1 left outer join dbo.RR_PERSON p on rs.persn_id = p.persn_id and p.row_status_cd = 'A' where IsNull(rm.resource_id,0) = 0 and IsNull(p.persn_id,0) = 0 and p.pro_id = 8202
Notice the mis-assignment of the arguments in the first statement. The only reason this didn’t throw an error is because the timestamp values are converted to numeric in MS SQLServer. I don’t really have an explanation as to why Powerbuilder was submitting the SQL in the first statement. I guess just make sure you validate the order of the arguments you send in a retrieve.
Updated March 2021
You might also be interested in