Hello Helen,

>OK, I think the first thing you have to let go is your belief that a lookup
>is the way to implement this 1:1 relationship. "Lookup" is really a
>desktop-database thing, a Paradoxy thing, that's very costly to
>client/server implementation. Although lookups have their uses in
>client/server, this aint one of them. I won't explain that here, it's a
>bridge you have yet to cross.
>
>
>

The lookup is simply a usability thing, so yep, it can be removed from
equation for now.

>There are plenty of real-world examples around but they won't mean much
>until you've got the multi-user, transaction-isolation aspects on board.
>
>
>

I think I've got the transaction isolation under my belt. Commited = no
one else can read the changes to data you make until you commit your
transaction. Consistant = Snapshot of the requested data, useful for
generating reports. Then there's the other one.
I'm also clear on difference between Commit and Post.

>So, let's look at this join. I'm totally guessing the columns in your
>Product and Stocksheet tables but I hope you'll get the picture.
>
>select p.productid, p,productname, s.stockcount
> from product p
> join stocksheet s
> on p.productid = s.productid
>where....(whatever search criteria you need to drill down with)
>
>This set has no lookups and no need for any.
>
>Yes,*explicit* locking is something to avoid most of the time because, most
>of the time, it's not necessary. Transactions have an attribute called
>isolation and another called LockWait. By choosing the right combination
>to suit what you need, you can get exactly the level of locking you need
>performed by the database engine.
>
>However, in this case, you *do* need an explicit lock -- one that gets
>applied immediately before the dataset allows you to begin editing. This
>is because "editing" isn't a database activity, it's a client
>activity. The database has no idea that a client is editing anything until
>AFTER the user has finished and actually posts the changes to the database.
>
>So what happens with the explicit lock (the PessimisticLocking property of
>the dataset set true) is that the dataset posts a dummy update to the
>database *before* the user starts editing the row. It's all done in the
>same, explicitly-controlled (not Autocommit) transaction. The dummy update
>causes the row (or rows, in the case of a set that's not naturally
>updatable) to be locked: no other transaction can then post an update to
>it. That's all it takes.
>
>Now, it's really important to understand that Post and Commit are two
>entirely separate things. Post creates a new record version on the server
>but it is invisible to other transactions. You can post many DML statements
>before you actually end the transaction. Commit, finally, makes all of
>that the work visible to all transactions; rollback reverts database state
>to what it was before the transaction began.
>
>Now, going back to our joined dataset. A joined dataset isn't naturally
>updatable but, in IBO, you can make it so. If you need to update more than
>one of the tables in the join, you write a parameterised, executable stored
>procedure to do it. If you only have to update one table (your case here)
>you set the dataset's KeyRelation property to be the table that is getting
>the update. You will need a parameterised stored procedure for the
>LockSQL. Basically, it takes the common key of the two tables and updates
>each one by setting the primary key = the primary key.
>
>Now, the dummy update will either succeed (because no other transaction has
>an update pending on that row) or fail (because another transaction has an
>update pending). You intercept the error in the latter case and tell the
>user she can't edit that record now because someone else is doing so.
>
>
>

That's okay for editing a record (I will delve into it over the
weekend), but IBO doesn't attempt any locking (custom or not) unless the
dataset is about to go into edit mode...so what happens when you create
a new record? Or for appending would you just display the current stock
count for the entered stocksheet, and attempt a commit with whatever
values the user enters, and then deal with any incorrect constraints
such as stock sold > stock count?

More of a "try and see" kind of way of doing things? ie Even though the
current stock count is 5 when you create a new sale entry, and you enter
in a value of 5 items to be sold, but whilst your creating this sale,
someone realizes the count on the stock sheet is incorrect and changes
the count to 4. Hence when the new sale entry of 5 items is posted, some
checking is done and then an error reported to the user.

If the above is the way to go about these things, then it's quite
non-user friendly...what if you've just promised 5 items to a chap over
the phone whilst you were creating the sale entry, then commited the
transaction after hanging up!?

>I understand your frustration. It's a very big leap. But there really is
>a penny waiting to drop. Work with it, it will happen.
>
>
>

I'm not going anywhere in a hurry...I'm determined. :)

>Ok, these are two totally different requirements. Totally preventing the
>count being modified is the easy one (described here); conditioning it on
>the results of others' pending changes is rocket science. I'd suggest
>that, if you can live with the pessimistic lock to begin with, the way to
>work the conditional situation will begin to evolve as the paint dries.
>
>
>

Pessimistic locking will be ok if I can get it to work :). I will try
your suggested tact over the weekend.

>Now, are you saying that the stockcount is calculated from multiple
>records, i.e. the relationship between the tables is 1:many and you're
>updating a master field from summing the details? And that the stockcount
>is modified by adding more records on the Many side?
>
>
>

No, the stock sheet contains a "delivered" qty if you like, and then (I
had in mind) the sum of the sales entries would give you the total sold.
To give some context to this, the turnover of stock (and hence sales) is
a weekly basis. eg stock comes in in the morning, gets entered into a
stock sheet, then through the course of the day (or over a day or so),
various sales are made against the stocksheets - eg raise an invoice
against stock sheet 1122, which happens to be 5 items of product A, and
enter an invoice qty less than the remaining qty, which would be 5 minus
the sum of current sales against stock sheet 1122.

>If so, the SQL for the join is a bit more complex and the LockSQL
>procedure, too. There are techniques in IBO to manage this, of course, but
>you need to do stuff on the database side, too, to achieve the protection
>you want. The water is quite deep in such places...
>
>
>

This seems to be the only end of the ocean I swim in :)

>I'm tempted to yatter on, but I've typed long enough here for today and
>maybe given you a few things to think about for now. Bedtime for me.
>
>
>

Thank you very much Helen, I will do my best to look at it with your
suggested join in mind...but I'm sure I'll squark again.

Mark