Luc Sierens <[log in to unmask]> asks:
 
> The thing WE are worried about is: what locking level is used by
> Powerbuilder using Image/SQL? Would it coexist happily with BBXL
> programs locking at item level? Anyone got experience with this?
 
Experience?
My fingers have recovered from the burn marks so I can now reply.
 
The questions could perhaps be re-phrased as :
  What locking level should I use when accessing Image/SQL via SQL?
   The same applies for ISQL as well as PowerBuilder or anything.
 
Image/SQL is not relational and even though the Image Lab has done a
great job providing access to Image through ALLBASE, the locking
concepts are still fundamentally different.
 
Most RDBMSes have various isolation levels, ie RR-Read Repeatable,
CS-Cursor Stability, etc. These can provide sharable read locks and
all kinds of other fun stuff.
 
The only isolation levels passed through to Image are RR-Read
Repeatable, the most restrictive, and RU-Read Uncommitted, the least
restrictive.
 
RR does a DBLOCK on the dataset as soon as you read
a record(that's right, locks the dataset on read) until you do a
COMMIT or ROLLBACK.
RU does no locking and is sometimes referred to as a 'dirty read'.
(NOTE: I've heard that  ALLBASE G1, now in beta,  will do
predicate level locking).
 
So, the approach that we and others have taken with PowerBuilder is
essentially the 3GL approach. That is:
  - read record
  - store in a 2nd buffer.
  - user modifies 1st buffer.
  - when user is done, re-read record with lock and check
     to see it matches the prev stored 2nd buffer.
 - if so, update, if not bail out.
 - unlock.
 
PowerBuilder actually handles a lot of this for you and is one of the
better tools for this.
On connect, set isolation level to 'RU'.
(SQLCA.Lock = "RU", works with ODBC, didn't with Gupta although this
may have changed).
 
What you do is define the Update characteristics of the datawindow to
update based on all updatable columns.
This in effect creates a SQL UPDATE statement with the WHERE
clause consisting of ALL the (updatable)columns in your table
(fields in your dataset).
 
If something has changed while user is editing you will get an error
like 'selection has changed between read and update' or something
like that.
 
This should be compatable with your other program.
 
Bob
 
 
 
--=--=--
Bob Walker, Computer Centre  --  [log in to unmask]
Capilano College, North Vancouver, BC, CANADA.
-- Windows '95 - making your PC work like a Mac.