Re: commit work not working?
- From: "jda" <adamski@xxxxxxxxxxxxx>
- Date: 21 Jul 2006 07:22:38 -0700
Thanks everyone for your replies, I passed them on to the developer and
here is his reply. It seems to me a difference of interpretation of
what the manuals say on begin-commit work and/or what actually happens
in IDS 9.4. Because he is using straight sql, I'm not sure how to
check for errors, so asking the for help and passing on his email to
me.
John
Thanks for all the replies but I still need more help.
First here is a section of the "IBM Informix Guide to SQL: Syntax" for
9.4 page 2-84
-------------------------- begin snipit ------------------------
Example of BEGIN WORK
The following code fragment shows how you might place statements within
a transaction. The transaction is made up of the statements that occur
between the BEGIN WORK and COMMIT WORK statements. The transaction
locks the stock table (LOCK TABLE), updates rows in the stock table
(UPDATE), deletes rows from the stock table (DELETE), and inserts a row
into
the manufact table (INSERT).
BEGIN WORK;
LOCK TABLE stock;
UPDATE stock SET unit_price = unit_price * 1.10
WHERE manu_code = 'KAR';
DELETE FROM stock WHERE description = 'baseball bat';
INSERT INTO manufact (manu_code, manu_name, lead_time)
VALUES ('LYM', 'LYMAN', 14);
COMMIT WORK;
The database server must perform this sequence of operations either
completely or not at all. When you include all of these operations
within a
single transaction, the database server guarantees that all the
statements are
completely and perfectly committed to disk, or else the database is
restored
to the same state that it was in before the transaction began.
-------------------------- end snipit ------------------------
The documentation implies that I don't need to decide if I should
rollback or commit, it implies that the database server will do that
for me (it actually guarantees it). So why doesn't the database server
automatically rollback when an error occurs?
Second since the documentation is obviously wrong I will need to find a
work around.
The code that I am running is passed to dbaccess from perl so dbaccess
is not running interactively.
It is very simple code (4 statements) and is standard SQL (not SPL)
begin work;
load from file insert tableA;
insert tableB;
commit work;
Can anyone tell me how to do the error checking in standard SQL?
I suppose from Perl I could break it apart into multiple SQLs passed to
dbaccess
load from file insert tableA;
check for error
if no error then insert tableB
check for error
My problem then becomes, if the insert on tableB fails how do I know
what rows to remove from tableA? Do I have to parse the file
attempting to pull out the key fields so that I can remove the correct
rows from the table? There surely has to be an easier way to do this.
Any suggestions?
TIA
Denis
.
- Follow-Ups:
- Re: commit work not working?
- From: Art S. Kagel
- Re: commit work not working?
- References:
- commit work not working?
- From: jda
- commit work not working?
- Prev by Date: Re: Updates to utils2_ak and myexport
- Next by Date: RE: commit work not working?
- Previous by thread: Re: commit work not working?
- Next by thread: Re: commit work not working?
- Index(es):
Relevant Pages
|
