Updating multiple Tables from a DataWindow

[Mark Brown]

Hello,
I have two tables that I want to update from one datawindow. I have only
seen the option to update one table at a time. Is there anyway to update
multiple tables at one time ?? I am using PB 7.02 Build 8045.

Thanks,
Mark B.

[Vladimir Gendler]

Hi Mark,

Yes and no. You can not do it by selecting something in the painter.
But you can do it either in a stored procedure if you will use the stored
procedure option or write generic script in the SQLPreview event of
a DW. In the last case you can find the complete solution in PFC.

Regards, Vladimir.

[Philip Salgannik]

Yes - straight from PB Help example (for Modify function which is suggested
thru the See Also button in the Update topic) :

Updating more than one table An important use of Modify is to make it
possible to update more than one table from one DataWindow object. The
following script updates the table that was specified as updatable in the
DataWindow painter; then it uses Modify to make the other joined table
updatable and to specify the key column and which columns to update. This
technique eliminates the need to create multiple DataWindow objects or to
use embedded SQL statements to update more than one table.

In this example, the DataWindow object joins two tables: department and
employee. First department is updated, with status flags not reset. Then
employee is made updatable and is updated. If all succeeds, the Update
commands resets the flags and COMMIT commits the changes. Note that to make
the script repeatable in the user's session, you must add code to make
department the updatable table again:

integer rc

string err

/* The SELECT statement for the DataWindow is:

SELECT department.dept_id, department.dept_name,

employee.emp_id, employee.emp_fname,

employee.emp_lname FROM department, employee ;

*/

// Update department, as set up in the DW painter

rc = dw_1.Update(TRUE, FALSE)

IF rc = 1 THEN
//Turn off update for department columns.
dw_1.Modify("department_dept_name.Update = No")
dw_1.Modify("department_dept_id.Update = No")
dw_1.Modify("department_dept_id.Key = No")

// Make employee table updatable.
dw_1.Modify( &
"DataWindow.Table.UpdateTable = ~"employee~"")

//Turn on update for desired employee columns.
dw_1.Modify("employee_emp_id.Update = Yes")
dw_1.Modify("employee_emp_fname.Update = Yes")

dw_1.Modify("employee_emp_lname.Update = Yes")
dw_1.Modify("employee_emp_id.Key = Yes")

//Then update the employee table.
rc = dw_1.Update()
IF rc = 1 THEN
COMMIT USING SQLCA;
ELSE
ROLLBACK USING SQLCA;
MessageBox("Status", &
+ "Update of employee table failed. " &
+ "Rolling back all changes.")
END IF
ELSE
ROLLBACK USING SQLCA;
MessageBox("Status", &
+ "Update of department table failed. " &

+ "Rolling back changes to department.")
END IF

--
pbm_thisusuallydoesnothelp :-))

"Mark Brown" <mbr...@bbn.com> wrote in message
news:$542kRR7$GA....@forums.sybase.com...

[John Olson [TeamSybase]]

Rather than write your own generic code take a look at the multitable
service in the PFC. The object to look at is pfc_n_cst_dwsrv_multitable.

Regards,
John Olson [TeamSybase]
Developower, Inc.
www.developower.com
Mark Brown wrote in message <$542kRR7$GA....@forums.sybase.com>...