Powerbuilder – Changing Datawindow object source to stored procedure

Posted on Friday, October 29th, 2010 at 8:20 pm in

So I had to update a report which deals with item prices in an application. Very familiar territory here. However, this report had a very large SQL statment consisting of five unions with many sub selects, case statements, and etc. The kind of code which, at first glance, looks like its held together with chewing gum and bailing wire (what the English would refer to as ‘bodged’). Anyway, my task was to make this whole thing ‘date aware’ which means that instead of just taking the current price of an item, the report needs to show the price in effect at the time the item was used. The price data already had an effective date column so the issue wasn’t that the information wasn’t captured, its just that it was not being taken into account in the report.

I decided to replace the SQL in the datawindow object with a stored procedure which will return the same result set as currently used in the report. This is very important since I did not want to spend a lot of time recreating the report after I had the stored procedure in place.

Step one is to create the procedure which returns a result set EXACTLY the same (column names and order) as the SQL in the datawindow. By column names I mean the column database names as shown in the Data Source view in the Datawindow Painter. Hopefully the original designer of the datawindow object ‘fixed’ any default column names (which take the form of .) to just reflect the columnname. If not you should take the time to do this in the existing datawindow object (and test/fix the display stuff) prior to attempting the change to a stored procedure. You also must make sure the column data types and sizes are the same as in the original (you can change this if absolutely necessary but I’m not going into this topic at this time).

Step two is to create a ‘throwaway’ datawindow object using the new stored procedure as the datasource. Make sure to designate any retrieval arguments needed. Once this is saved use the option to ‘Edit Source’ (or export the object and edit it in a text editor). Find the section which looks like this:

 procedure="1 execute dbo.usp_op_charge_rpt;1 @cas_int_id = :cas_int_id" arguments=(("cas_int_id", number)) sort="chg_sort A pri_fg A pro_nm A charge_desc A " )

Copy this into a text document and then Edit Source on the existing datawindow object. Look for the section like:

retrieve="SELECT	pro_nm = 
		case
		 when ( T_DEPARTMENT.sdy_lvl_int_id  = 0) then 'Highest Level Charge'
		 else ( SELECT pro_nm
				FROM   T_PROC 
				WHERE  T_PROC.pro_int_id  = T_CASE_PROC.sch_pro_int_id )
			 end,
		 charge_desc = T_CHARGE_LVL.lvl_ds,
//
// Lots of stuff here
//		

(T_CASE_HEADER.cas_int_id  = :cas_int_id) AND
(T_CHG_MST.row_sta_cd  = 'A') AND
( T_ITEM_USED.cap_itm_cst_fg = 'N' OR T_ITEM_USED.cap_itm_cst_fg IS NULL )
" arguments=(("cas_int_id", number))  sort="chg_sort A pri_fg A pro_nm A charge_desc A " )

Step three is to replace the existing section above with the ‘procedure’ script you saved in step two. Now save the datawindow object and, if editing an export, re-import it back.

With a bit of luck you are now in business and don’t have to rebuild the datawindow from scratch.

You might also be interested in

1 Comment

Add your comment

  1. cricketer - May 28, 2013 at 7:56 am

    Can you please let me know the process to create datawindow dynamically using oracle stroed procedure with parameters.

    I am using following syntax but I am getting this error – “Can not get parameters of procedure”

    Any suggestions will be greatly appreciated.

    sql_syntax = “execute starsdba.SP_PROVIDER_LIST; as_Sql= From Users”

    presentation_str = “style(type=grid)”

    presentation_str = &
    “style( type=Grid &
    Horizontal_spread = 25 &
    Header_bottom_margin = 15 &
    Header_top_margin = 15 ) &
    datawindow( units=2 &
    Color= 67108864) &
    column( Font.Face=’system’ &
    Font.Height=-10 &
    Font.Weight=700) &
    text( Font.Face=’system’ &
    Font.Height=-10 &
    Font.Weight=700 &
    Border=6)”

    dwsyntax_str = SQLCA.SyntaxFromSQL(sql_syntax, &
    presentation_str, ERRORS)

    IF Len(ERRORS) > 0 THEN
    MessageBox(“Caution”, &
    “SyntaxFromSQL caused these errors: ” + ERRORS)
    RETURN

    END IF

Leave a Reply

Top