datawindow stored procedure update 的使用介绍

This document explains how to implement the new feature of the DataWindow which uses stored procedures to insert, update, and delete data from a database

Overview

In previous versions of PowerBuilder, it was possible to use a stored procedure as a datasource
for a DataWindow, however to use a stored procedure for update, insert or delete, the SQLPreview
event had to be used to substitute the generated SQL statement for a stored procedure call.

PowerBuilder 7.0 adds direct support for stored procedures for update, insert and delete actions.
This makes it possible to design a DataWindow entirely on stored procedures within the painter.

Specifying Update Properties

When a DataWindow is based on a stored procedure, the DataWindow is by default not updateable. When stored procedures are used for all three updating actions, it is not necessary to change the properties the Specify Update Properties dialog, but the taborder of the columns to be updated must be set to a non-zero, positive, number.

In the following example a set of simple ASA stored procedures are given for updating the EAS Demo DB department table. The stored procedure sp_select_departments can be used as a datasource for the DataWindow as usual, however the other three procedures must be associated using the Stored Procedure Update menu item.

//Note : The tilde character "~" represents the terminator character.
drop procedure sp_select_departments ~
drop procedure sp_insert_department ~
drop procedure sp_update_department ~
drop procedure sp_delete_department ~

create procedure sp_select_departments()
result (dept_id int, dept_name char(40), dept_head_id int)
begin
select dept_id, dept_name , dept_head_id
from department
end~

create procedure sp_insert_department(in id int, in name char(40), in head_id int)
begin
insert into department
values (id, name, head_id)
end~

create procedure sp_update_department(in old_id int, in new_id int, in name char(40), in head_id int)
begin
update department
set dept_id = new_id,
dept_name = name ,
dept_head_id= head_id
where dept_id = old_id
end~

create procedure sp_delete_department(in id int)
begin
delete from department where dept_id = id
end~


The stored procedure arguments must match the columns of the DataWindow. For each argument, you can specify to use the original value or the current value. The original value is typically used in the where clause. For example in the procedure sp_update_department you would check Use Original for the old_id argument and uncheck it for the new_id, name and head_id arguments.





Working with different databases

The Stored Procedure Update functionality is not dependent on the particular database used, although the database itself can pose certain restrictions. Similar procedure sets can be defined for other databases like Sybase ASE or Oracle.

// ASE syntax
drop procedure sp_select_departments ~
drop procedure sp_insert_department ~
drop procedure sp_update_department ~
drop procedure sp_delete_department ~

create procedure sp_select_departments
as
begin
select dept_id, dept_name , dept_head_id
from department
end~

create procedure sp_insert_department(@dept_id int, @dept_name char(40), @dept_head_id int)
as
begin
insert into department
values (@dept_id, @dept_name, @dept_head_id)
end~

create procedure sp_update_department(@old_dept_id int, @new_dept_id int, @dept_name char(40), @dept_head_id int)
as
begin
update department
set dept_id = @new_dept_id,
dept_name = @dept_name ,
dept_head_id= @dept_head_id
where dept_id = @old_dept_id
end~

create procedure sp_delete_department(@dept_id int)
as
begin
delete from department where dept_id = @dept_id
end~

Either a stored procedure or a sql-statement can perform each of the separate SQL actions Update Insert or Delete. But if the DataWindow is not updateable, only those actions with type Stored Procedure will be carried out.

At run-time it can be verified by checking the value of the dw_control.Object.DataWindow.Table.SqlAction.Type
in which sqlaction can be either Update Insert or Delete. Also the Method (stored procedure) and the Arguments are available at run-time and can be modified after the Type has been set to SP.

The stored procedure will usually be prefixed by its owner. Most databases don't require an owner name before the stored procedure name, however you will have to verify this with your database vendor (ASE requires the owner name to prefix the stored procedure i.e. dbo.sp_update.)

The support for the stored procedure datasource hasn't been changed in PB7, so that the same script rules apply as in earlier versions.

In the following Oracle example the stored procedure for retrieval is rewritten using the PBDBMS package.

// Oracle syntax using PBDBMS
drop procedure sp_select_departments ~
drop procedure sp_insert_department ~
drop procedure sp_update_department ~
drop procedure sp_delete_department ~

create procedure sp_select_department
as
begin
PBDBMS.Put_Line('select dept_id, dept_name , dept_head_id ');
PBDBMS.Put_Line('from department');
end~


create procedure sp_insert_department(id in int, name in varchar2, head_id in int)
as
begin
insert into department
values (id, name, head_id);
end~


create procedure sp_update_department(old_id in int, new_id in int, name in varchar2, head_id in int)
as
begin
update department
set dept_id = new_id,
dept_name = name ,
dept_head_id= head_id
where dept_id = old_id;
end~


create procedure sp_delete_department(id in int)
as
begin
delete from department where dept_id = id;
end~

select * from all_errors~

A package with suitable stored procedures could also have been used, in which case the method must be prefixed by the package name (see also Tech Doc 44450 and 44553).

Note:
PowerBuilder does not retrieve a list of stored procedures that are contained within packages so that they cannot be selected in the painter. But the stored procedures can be executed at run-time.

An example script

Suppose similar sets of stored procedures exists within and ASA, ASE and Oracle Server; and the Oracle update procedures have been defined in the package SYSTEM.PKG_SPDWC. The following script fragment gives an example on how the DataWindow can call different stored procedures dependant on the database server the application is connected to. The script can be generalized in many ways, for example the names of the stored procedures don't have to be hard-coded but could also have been stored in an ini file or even in the database.

// current server
string gs_server
// list of servers
string gs_servers[] = {"my_ASA_server", "my_O84_server", "my_SYC_server")

// list of stored procedures for the different databasesdatabases.
string is_spupdates[] = {"sp_update_department", "SYSTEM.PKG_SPDWC.sp_update_department", "dbo.sp_update_department"}
string is_spinsertes[] = {"sp_insert_department", "SYSTEM.PKG_SPDWC.sp_insert_department", "dbo.sp_insert_department"}
string is_spdeletes[] = {"sp_delete_department", "SYSTEM.PKG_SPDWC.sp_delete_department", "dbo.sp_delete_department"}
string is_spselect[] = {"sp_select_department", "SYSTEM.sp_select_department", "dbo.sp_select_department"}

// list of stored procedures the DataWindow was created upon
string ls_default_update = "dbo.sp_update_department"
string ls_default_insert = "dbo.sp_insert_department"
string ls_default_delete = "dbo.sp_delete_department"
string ls_default_select = "dbo.sp_select_department"


// Replace example script :
string ls_update_method, ls_insert_method, ls_delete_method, ls_select_method
integer l_upper, li_i = 0
boolean b_found = false

//check which is the current server
li_upper = UpperBound(gs_servers)
for li_i = 1 to li_upper
if gs_servers[li_i] = gs_server then
b_found = true
exit
end if
next

//replace the names of the stored procedures
if b_found then
ls_update_method = dw_1.Object.DataWindow.Table.UPDATE.Method
ls_insert_method = dw_1.Object.DataWindow.Table.INSERT.Method
ls_delete_method = dw_1.Object.DataWindow.Table.DELETE.Method
ls_select_method = dw_1.object.DataWindow.Table.Procedure
dw_1.Object.DataWindow.Table.UPDATE.Method = gf_substitute(ls_update_method, ls_default_update, is_spupdates[li_i])
dw_1.Object.DataWindow.Table.INSERT.Method = gf_substitute(ls_insert_method, ls_default_insert, is_spinsertes[li_i])
dw_1.Object.DataWindow.Table.DELETE.Method = gf_substitute(ls_delete_method, ls_default_delete, is_spdeletes[li_i])
dw_1.object.DataWindow.Table.Procedure = gf_substitute(ls_select_method, ls_default_select, is_spselect[li_i])
else
MessageBox("Error Unknown Server", "No Match : " + gs_server)
end if


//global substitute string function
string function gf_substitute(as_1, as_2, as_3)
integer li_pos, li_1, li_2, li_3
string ls

li_pos = pos(as_1, as_2)
li_1 = len(as_1)
li_2 = len(as_2)
li_3 = len(as_3)

if li_pos > 0 and li_3 > 0 then
ls = left(as_1, li_pos - 1) + as_3 + right(as_1, li_1 - li_2 - li_pos + 1)