Roy,

I am old school in this issue...

When I worked with systems like that in COBOL, it has locking control and it was piece of cake.

When I needed to do that again was with Delphi with Paradox and after DBISAM, both have locking control, surely only DBISAM I could have peace of mind.

Now I am going to more based SQL, based on ORM. The idea is to be database agnostic, but I choose ElevateDB for all that good reasons we have: simple to install, simple to use, no trouble.

Right now the system is running a chain of stores. But the main server is based on a Log system. So each stores send the operations by log and they are consumed by a task manager. So mostly it is the task manager that is doing the stock changes.

But I have now to extended that to windows desktops and mobiles and then the problem can happen... I am not very aware how it is done on the real world.

For me locking always sounded good... just keep waiting some seconds until release and then update...


Roy Lambert wrote:



Eduardo


That is an entirely different question, and can be a lot more complex. I know some systems that allow negative stock pretty much for this reason. Part of the answer lies in having a separate "in use" flag sort of like Adam suggests but I prefer a separate table into which you write the stock code of the item being updated. The process would go along the lines of:

1. Check stock levels
2. Check if the stock code is in the InUse table if so stop
3. If not there add it
4. Check stock levels again, if they've changed worry
5. If stock levels acceptable then update/downdate
6. Delete stock code from InUse table
7. Cup of tea


Roy Lambert

Roy Lambert wrote:

> > to design the system so that multiple users can be allocating stock
> > at the same time.
>
> <full pedant mode ON>
>
> I'm just felling a bit awkward so I'm going to take issue with anyone
> allocating stock at the same time. This does not map to the real
> world and should never even be considered. You should never ever even
> consider updating / downdating stock simultaneously, it has to be a
> controlled sequential operation.
>
> I write as not only a pedant but also someone who has had to sort out
> the actual physical mess in the stores and stock take vs perpetual
> inventory <VBG>
>
> <full pedant mode OFF>

Accepted - I'd never want to design such a system. But it may be needed
in situations where your database is distributed. And if you can
control the supply, so back-ordering isn't an issue. Or you allocate,
and then check and confirm allocation.

But no one would like such a system.

--

Matthew Jones

Eduardo

>I am old school in this issue...
>
>When I worked with systems like that in COBOL, it has locking control and it was piece of cake.
>
>When I needed to do that again was with Delphi with Paradox and after DBISAM, both have locking control, surely only DBISAM I could have peace of mind.

Pessimistic locking which pretty much enforces flow control


>Now I am going to more based SQL, based on ORM. The idea is to be database agnostic, but I choose ElevateDB for all that good reasons we have: simple to install, simple to use, no trouble.

Optimistic locking - hmmm

>Right now the system is running a chain of stores. But the main server is based on a Log system. So each stores send the operations by log and they are consumed by a task manager. So mostly it is the task manager that is doing the stock changes.
>
>But I have now to extended that to windows desktops and mobiles and then the problem can happen... I am not very aware how it is done on the real world.

That makes it sound as though you're trying to alter the paradigm on which the system works. Why can't the central server with a log be extended to other "terminals"?

>For me locking always sounded good... just keep waiting some seconds until release and then update...

I don't know if I'm old school or just plain awkward - I suspect the latter.

SQL was a poor query language (look at ENGLISH on PICK if you want to see a good one) which was upgraded to be a batch processing language and is now touted as the answer to everything. It does do some nice things and (generally) gets closer to the metal than navigational methods. It is still inherently a batch/set processing system and not really suited to transactional use.


Roy Lambert (in rant mode)

Eduardo

>I am using an ORM layer to persist my memory objects on ElevateDB. And it is working just fine.

How well does the ORM layer integrate with ElevateDB's stored procedures / external functions?

I'm just wondering if a mixture of SQL and external functon could solve the problem.

Roy Lambert

Not sure Roy.

I am not a SQL guy by nature, I always dealt with the database layer directly.

Eventually is a solution, do you want to give me a hand on that?

I do have conditions to direct manipulate de SQL when needed. I try not to do so, for many reasons, one is that I want that my software to be portable.

I am using Aurelius (by the way I recommend to everyone) and it is SQL-transparent and portable for a lot of other database engines, but my need right now is to keep compatibility between ElevateDB and SQLite3 that is the Android built in SQL.

However, I can encapsulate and according to the database just not use the stored procedure. Since my need is to control locking with ElevateDB only. If eventually I need with other then I can write something for that.

What is your idea? How can I solve this issue with Stored Procedure?

Thanks


Roy Lambert wrote:

Eduardo

>I am using an ORM layer to persist my memory objects on ElevateDB. And it is working just fine.

How well does the ORM layer integrate with ElevateDB's stored procedures / external functions?

I'm just wondering if a mixture of SQL and external functon could solve the problem.

Roy Lambert

Eduardo

>I am not a SQL guy by nature, I always dealt with the database layer directly.

Neither am I  I started using SQL more when I moved to ElevateDB and I still prefer tables for lots of things.

My idea was roughly along the lines of using ElevateDB's abilities to use custom user written functions. The way Tim's set things up its fairly simple to write a program in Delphi and then call that from within SQL

You could have SQL along the lines of

UPDATE stocktable SET onhand = OnlyUpdateIfLocked(quantity)


OnlyUpdateIfLocked would be something like:


function OnlyUpdateIfLocked(updatevalue:integer):integer;
var
Locked:boolean;
begin
create database, table etc (or you could do this in the initialisation section for the dll

Locked:=False;
while not Locked do begin
try
table.edit;
table.fieldbyname('onhand').AsInteger :=  table.fieldbyname('onhand').AsIntege + updatevalue;
table.post;
Locked:=True;
except
end;
end;
result := table.fieldbyname('onhand').AsInteger;
end;

It means that you would be setting the value twice, but it might be possible to do something with a select statement but that's something I've never tried.

The other way is one that's already been touched on - use a separate lock table of your own and use SQL/PSM (Tim's programming language) to manage it

If you want to try that route and need some help let me know and I'll what I can cook up.

Roy Lambert