Ah ha, I didn't know that, and it fits the scenario that we have been
experiencing. I thought that the OLE DB connection itself would lock me out
of the file until it ended the connection.
As Ron and Chris suggested, I may have to re-think this process. For the
moment there is a clumsy workaround since the stores themselves are not
actually submitting the order but rather by a staff person on-site.
Eventually though our plan is to allow some (or all) of the stores do the
submissions.
Jerry C. Adams
IBM i Programmer/Analyst
He's an intellectual from Yale, but he's very intelligent. -Pete Rose on
Bart Giamatti, Baseball Commissioner
--
A&K Wholesale
Murfreesboro, TN
615-867-5070
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Morgan, Paul
Sent: Tuesday, February 21, 2012 9:59 AM
To: Midrange Systems Technical Discussion
Subject: RE: Object Allocation Problem - or Something Else?
Jerry,
Does OLE DB use a separate SQL INSERT statement for each row added to
DTOORDERS? I believe SQL will only lock a table while a statement is
running. If each row is a separate SQL statement your ALCOBJ can lock the
file in the middle of an order upload.
Paul Morgan
Principal Programmer Analyst
IT Supply Chain/Replenishment
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jerry C. Adams
Sent: Tuesday, February 21, 2012 10:37 AM
To: Midrange-L
Subject: Object Allocation Problem - or Something Else?
Our company has 20+ retail outlets. Until recently, they were sending
replenishment orders to us in a rather convoluted manner (which I'd rather
not get into).
The company that provides our Point-of-Sales applications and I teamed up so
that the stores could send an order directly to the System i. I don't know
much about the POS side, but the programmer there said he used OLE DB to
connect, which we both thought was great because I could create an SQL DDL
file on the System i into which the orders would be dumped.
On my end I wrote a process that runs continuously from about 0700 - 1600
and checks for new orders. The actual order processing takes only about a
minute or less, and then goes to sleep for 10 minutes.
Before my process starts processing the order bucket file, it ALCOBJ the
file. I know that, if I allocate the file *before* a store tries to send me
an order, the POS errors out because I once had the program in debug and
forgot to hold the process on the job queue. The actual command is:
ALCOBJ OBJ((DTOORDERS *FILE *EXCL)) WAIT(60) SCOPE(*JOB)
The problem that we have been experiencing is that, occasionally, I will get
only part of the order, say 10 lines out of 100 (it varies). I have a check
at my end (the store's purchase order number) to be sure that the store
managers (not the sharpest knives in the drawer) don't try to send the same
order a second (or more) time. But on the next cycle the rest of the order
is there and, naturally, my program rejects it as being a duplicate.
What I don't know, and have been unable to test, is whether or not the
ALCOBJ actually works if the store has already made the connection to my
bucket file when I allocate the file and start processing. This seems to be
what is happening, but is it possible? I do know that the orders are not
being submitted twice.
Thanks.
Jerry C. Adams
IBM i Programmer/Analyst
Brooks Robinson belongs in a higher league. -Pete Rose
--
A&K Wholesale
Murfreesboro, TN
615-867-5070
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
