Powerbuilder ‘Gotcha’ – Datawindow retrieval arguments

Posted on Friday, July 16th, 2010 at 8:22 pm in

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 WITH (NOLOCK)
			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 WITH (NOLOCK)
			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 WITH (NOLOCK)
join RR_REQUIRED_RESOURCE_GROUP rrg WITH (NOLOCK) on pr.pro_id = rrg.pro_id
join RR_SCHEDULING_GROUP sg WITH (NOLOCK) on rrg.sch_grp_id = sg.sch_grp_id 
join RR_RESOURCE_GROUP rg WITH (NOLOCK) on sg.sch_grp_id = rg.sch_grp_id
join RR_RESOURCE rs WITH (NOLOCK) on rg.resource_id = rs.resource_id
left outer join RR_ROOM rm WITH (NOLOCK) 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 WITH (NOLOCK) 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 WITH (NOLOCK)
			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 WITH (NOLOCK)
			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 WITH (NOLOCK)
join RR_REQUIRED_RESOURCE_GROUP rrg WITH (NOLOCK) on pr.pro_id = rrg.pro_id
join RR_SCHEDULING_GROUP sg WITH (NOLOCK) on rrg.sch_grp_id = sg.sch_grp_id 
join RR_RESOURCE_GROUP rg WITH (NOLOCK) on sg.sch_grp_id = rg.sch_grp_id
join RR_RESOURCE rs WITH (NOLOCK) on rg.resource_id = rs.resource_id
left outer join RR_ROOM rm WITH (NOLOCK) 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 WITH (NOLOCK) 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.

You might also be interested in

Add your comment

Leave a Reply

Top