Re: Oracle & HTML
Date: 1996/04/10
Message-ID: <4kgskq$kgi_at_inet-nntp-gw-1.us.oracle.com>
"Roman L. Podshivalov" <romas_at_online.ru> wrote:
>Jared Hecker wrote:
>>
>> The Web Server doesn't do any locking. The package(s) associated with it
>> probably implement the standard Oracle locking schemes (select for update,
>> etc.). Should be in the docs...
>Look: in multiuser env you select data into html form wait 10 mins and want to update it...
>What can happend ???
>PS: separate packages make select & update as i undestand the idea of Web Server...
>Oracle goverment where are you ???????????
Correct, WebServer does not maintain locks across URL accesses. The htt-protocol does not support such a concept (yet). There is no such thing as a 'state'. HTTP is a completely stateless protocol.
So, how do people handle this? These are the most common ways I've seen it dealt with:
- collect information from the web and disseminate it. Don't update existing information. Avoid the problem alltogether. (only allow INSERTS to collect and SELECTS to disseminate, and perhaps DELETES to get rid of).
- When you select the information out of the database, not only would you put it in the text fields for updating but you would put it in hidden fields as well. For example:
for x in ( select rowid, a.* from emp a ) loop
hpt.formHidden( 'the_Rowid', x.rowid );
htp.p( 'Enter New Emp Name: ' );
htp.formHidden( 'ename_old', x.ename );
htp.formText( 'ename_new', cvalue => x.ename );
....
end loop;
And then your update routine will get the rowid, old values and new values. The update statement would look like:
update emp
set ename = new_ename,
.....
where rowid = THE_ROWID
and ename = old_ename and .......
if ( sql%rowcount = 0 ) then
htp.bold( 'Your data has been changed, please requery and try again' );
else
htp.p( 'Success....' );
end if;
- Do a variation on the above. Instead of having to send old and new values back and forth and having a complex update statement, create a 'timestamp' field on the table. For example:
create sequence timestamp_seq;
alter table scott.emp add timestamp number;
create trigger emp_ts
after insert or update on emp
for each row
begin
select timestamp_seq.nextval into :new.timestamp from dual;
end;
/
Now whenever a row is created or modified, a unique number will be attached to it. Your pl/sql code would look like:
for x in ( select rowid, a.* from emp loop )
htp.formHidden( 'the_Rowid', x.rowid );
htp.formHidden( 'the_Timestamp', x.timestamp );
htp.p( 'Enter New Emp Name: ' );
htp.formText( 'ename_new', cvalue => x.ename );
....
end loop;
And then your update routine will get the rowid, the old timestamp and new values. The update statement would look like:
update emp
set ename = new_ename,
.....
where rowid = THE_ROWID
and timestamp = THE_TIMESTAMP
if ( sql%rowcount = 0 ) then
htp.bold( 'Your data has been changed, please requery and try again' );
else
htp.p( 'Success....' );
end if;
- Or if you cannot, do not, want to modify the table structure as above but like
the concept of not having to send old and new values around, you could use a
checksum to determine of the row has changed since you queried it. For example
- Internet (1's complement) checksum: create or replace function checksum( p_buff in varchar2 ) return number is l_sum number default 0; l_n number; begin for i in 1 .. trunc(length(p_buff)/2) loop l_n := ascii(substr(p_buff, 1+(i-1)*2, 1))*256 + ascii(substr(p_buff, 2+(i-1)*2, 1)); l_sum := mod(l_sum+l_n,4294967296); end loop; while ( l_sum > 65536 ) loop l_sum := bitand( l_sum, 65535 ) + trunc(l_sum/65536); end loop; return l_sum; end checksum; /
And then you would code:
for x in ( select checksum(ename||dept||loc||....) checksum, rowid, a.*
from emp a ) loop
htp.formHidden( 'the_Rowid', x.rowid );
htp.formHidden( 'the_checksum', x.checksum );
htp.p( 'Enter New Emp Name: ' );
htp.formText( 'ename_new', cvalue => x.ename );
....
end loop;
And then your update routine will get the rowid, the old timestamp and new values. The update statement would look like:
update emp
set ename = new_ename,
.....
where rowid = THE_ROWID
and checksum(ename||dept||loc||...) = THE_CHECKSUM;
if ( sql%rowcount = 0 ) then
htp.bold( 'Your data has been changed, please requery and try again' );
else
htp.p( 'Success....' );
end if;
>>
>> hth -
>>
>> jhy
>>
>> Roman L. Podshivalov (romas_at_online.ru) wrote:
>> : Can any1 explain how locks working in Oracle Web server ????
>> : Or how Web server locks rows...
>>
>--
>Roman L. Podshivalov Sovam Teleport
><mailto:romas_at_online.ru> (+7-501) 258-4170
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
opinions and statements are mine and do not necessarily reflect the opinions of Oracle Corporation. Received on Wed Apr 10 1996 - 00:00:00 CEST