DVL Software: PowerBuilder Tips - n_cst

PowerBuilder Tips

n_cst_dwsrv_multitable extension

Back to the Tips

News Flash - 6 July 1998

It appears there is much more to the binding problem than I anticipated.

I'm still working on a solution, but until then, I recommend that you do not use this service unless you have binding disabled. That is, put DisableBind=1 on the DBParm field of your transaction object before you connect.

Keep checking here for updates.

News Flash - 23 June 1998

Unfortunately, we've found a bug. It's to do with binding. Instead of creating SQL like this:
UPDATE my_table SET cola = :1;
it create SQL like this:
UPDATE my_table SET cola = 'coca';
That's not very good. I'm working on a fix. Come back in a week or so. Sorry about this. Insufficient testing. :(

What is n_cst_dwsrv_multitable?

According to the PFC help, the n_cst_dwsrv_multitable service is "a collection of functions that facilitate the updating of DataWindows that contain rows from more than one table". That is true. But as the service comes out of the box, it assumes that all tables will have the same operations performed on each table (for each table, an INSERT or UPDATE or DELETE is executed). However, this is not always desirable.

For example, in an outer join, one of the tables may be update only, and you don't want to delete existing rows or insert new rows. It was because of this limitation, that I added an extension which allows you to specify a table as Update, Insert, Delete, or All.

How do I use the new functionality?

I have not created a working example for this extension. But I have included code from an existing application which uses this service. The code which is specific to this extension is in blue. For each call to of_AddToUpdate, you can optionally call of_SetUpdateType. If you do not call of_SetUpdateType, then the default action is ii_Update_Type_All. The valid parameters to of_SetUpdateType are:
ii_Update_Type_Insert#
ii_Update_Type_Delete#
ii_Update_Type_Update#
ii_Update_Type_All#	(the default action if you don't call of_AddToUpdate).
The above values are defined as constants on the n_cst_dwsrv_multitable object.. They may be added together to obtain the combination you need. For example, to allow updates and inserts, but not deletes, you would make the following call:
this.inv_multitable.of_SetUpdateType(this.inv_MultiTable.ii_Update_Type_Update# + &
                                     this.inv_MultiTable.ii_Update_Type_Insert#)
What about master-detail type datawindows?

With respect to a master-detail type datawindow, I'm actually referring to a join on two tables. One of these tables contains relatively permanent information whereas the other contains relatively temporary information. In this scenario, you do not want the default actions of the multitable object. Instead, you want this extension.

For an example, I'll use the entry and result tables as shown in the example code below. An entry will always exists. A result is added sometime later. The datawindow for our example looks like this:

In this datawindow, Bib # and Finish are stored in the result table. Start is stored in the entry table. When the user enters a bib number, the corresponding details are loaded into the datawindow from the entry table. If this action is performed on a newly inserted row (which has not yet been saved to the database), the existing multitable object would perform an insert on both the entry and the result table. Clearly, we don't want that to happen here. The new extension solves this problem by converting an Insert to an Update. Please refer to the code example below for further detail.

What if you don't want that?

There is the possibility that you do not want Inserts converted to Updates but you do want Updates. If so, then please contact the author and I'll make the changes for you.

Example code

The following code example demonstrates how to use this extension. The code which is specific to this extension is in blue.
/* how to use the multi-table extension */
string	ls_Entry_Columns[]= {'start_time'}
string	ls_Entry_Keys	[]= {'entry_id'}

string	ls_Result_Columns[]= {'id', 'event_id', 'finish_time', 'race_number', 'edit_flag'}
string	ls_Result_Keys	 []= {'id'}
this.of_SetMultiTable(TRUE)

this.inv_multitable.of_AddToUpdate("entry", ls_Entry_Keys, ls_Entry_Columns, TRUE, 0)
this.inv_multitable.of_SetUpdateType(this.inv_MultiTable.ii_Update_Type_Update#)

this.inv_multitable.of_AddToUpdate("result", ls_Result_Keys, ls_Result_Columns, TRUE, 0)
this.inv_multitable.of_SetUpdateType(this.inv_MultiTable.ii_Update_Type_All#)
Installation instructions

In order to use this extension, you will need to to do the following:

Download multitable.zip.

Unzip the contents of this file. You will find it contains n_cst_dwsrv_multitable.sru. This is the exported object.

Import n_cst_dwsrv_multitable into your application. Be careful not to overwrite any existing extensions you may have. If you do have code within your existing object, you'll have to merge these two files. I often do a copy/paste session with notepad.

Add the following code to u_dw::sqlpreview
/*

Purpose:	If the multitable service is invoked, then give it the chance
		to modify the SQL.

Parameters:	std for this event

Results:	std for this event.

	DVL Software - http://www.dvl-software.com
	You may use, distribute, or modify this code provided you retain this header.

*/

int	li_RC = 0

/* Call the multi-table sqlpreview if applicable */
IF IsValid (this.inv_multitable) THEN 
	li_rc = this.inv_multitable.of_SqlPreview(request, sqltype, sqlsyntax, buffer, row)
END IF

return li_rc
Your views

As with everything on this website, if you can think of any suggestions for this extension, please feel free to contact me.

Back to the Tips