Re: Not in Transaction Error Message -255
>From: Ruchi_Patel@avid.avid.com (Ruchi Patel)
>Date: 9 Mar 1995 20:49:39 GMT
>X-Informix-List-Id: <news.12087>
>
>I am working with online 6.0 version on HP 9000 platform. The database
>uses unbuffered transaction logging. The 4gl program gives -255 error
>message when I am trying to open a cursor that is defined for UPDATE.
>Here is a test program.
>
>PREPARE updt_stmt FROM
>SELECT * FROM T WHERE ROWID = ? FOR UPDATE>DECLARE upd_curs CURSOR FOR updt_stmt
>OPEN upd_curs USING ROWID
>
>I need to convert applications written for SE to work with ONLINE using
>COMPILER. The SE uses NO Transaction logging. The ONLINE uses
>UNBUFFERED TRANSACTION LOGGING. What choices do I have?
>
>If I use BEGIN WORK before OPEN statement then it works fine. According
>to Informix I don't need to re-write all my application.
I attach a document I wrote in 1988 on the subject of handling transactions
in I4GL code for databases which may, or may not, have transactions. I
removed a few names but the content remains basically valid.
Note that since you are (I trust) using at least version 4.00, you can
automatically detect whether you are working with OnLine or SE, with
transactions or not, and with a MODE ANSI database or not, by looking at
the SQLCA.SQLAWARN flags (in I4GL; a different name in ESQL/C) immediately
after the database is opened.
Note that you also need to worry about table locking. If you have a no
transactions, you use LOCK TABLE and UNLOCK TABLE; if you have them, you
use LOCK TABLE inside a transaction and COMMIT WORK. If you need to code
for both, use a pair of functions lock_table() and unlock_table() to handle
these deviances.
I also attach a document on whether to use a transaction log or not from
the same era. It doesn't take into account OnLine as Turbo was then not
readily available, let alone OnLine. The bug in ROLLFORWARD DATABASE it
mentions has been fixed too.
Pretending you can switch between logged and unlogged databases without
code changes and without careful planning is ludicrous. That is a personal
opinion, but it is based on a non-neglible amount of experience.
Yours,
Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>
===========================================================================
Handling Transactions in Informix-4GL code
1. The need for a method of handling transactions
There are two problems with handling transactions in an Informix-4GL
program: first, it is necessary to decide where to create the transaction
boundaries; and second, it is necessary to insert code to handle the
transaction. Also, transaction logs can be made to come and go - it isn't
always easy to make the transaction log go, but it can be done - so another
problem occurs if there is any possibility that the transaction log may not
exist; executing a BEGIN WORK statement when there is no log causes an
error.
There is no simple cure for the problem of deciding where transaction
boundaries should be created, but the other problems can be eased.
2. A simple method of handling transactions
To make the transaction handling code robust (not dependent on the presence
or absence of the transaction log) the code should be written on the
assumption that there is a log, but instead of writing the BEGIN WORK,
COMMIT WORK or ROLLBACK WORK statements, write a call to a routine which
only executes the transaction statement if the log is present. The
routines are:
CALL begin_work() { Conditionally BEGIN WORK }
CALL commit_work() { Conditionally COMMIT WORK }
CALL rollback_work() { Conditionally ROLLBACK WORK }
By calling these routines where the corresponding statement would be
written, the code is made robust. An additional help is the routine called
end_work; it takes an integer argument which indicates whether the
transaction was successful or not, and executes either COMMIT WORK or
ROLLBACK WORK depending on the value in the flag. If the value is 0
(zero), the transaction is successful and is committed; otherwise, it fails
and is rolled back. CALL end_work(STATUS) These routines make use of
another routine called translog which tests whether there is a transaction
log on the database; it returns TRUE if there is a transaction log, and
FALSE if there is not. This routine only tests the database once, so it is
not an expensive function to use, but in general, the other 4 routines are
the only ones that need to use it.
3. The actual routines
{
@(#)translog.4gl 1.1
@(#)JLSS Informix Tools: General Library
@(#)Handle transactions whether database has log or not
@(#)Author: JL
}
{ Global to this file -- not accessible outside }
DEFINE logstatus INTEGER
{ 0 => State of log unknown, 1 => Log absent, 2 => Log present }
{ Determine whether there is a transaction log }
FUNCTION translog()
DEFINE
junk INTEGER
IF logstatus = 0 THEN
SELECT Tabid
INTO junk
FROM Systables
WHERE Systables.Tabtype = 'L'
IF STATUS = NOTFOUND THEN
LET logstatus = 1 { Log absent }
ELSE
LET logstatus = 2 { Log present }
END IF
END IF
RETURN (logstatus = 2)
END FUNCTION {translog}
{ Begin a transaction if there is a log }
FUNCTION begin_work()
IF translog() THEN
BEGIN WORK
END IF
END FUNCTION {begin_work}
{ Commit a transaction if there is a log }
FUNCTION commit_work()
IF translog() THEN
COMMIT WORK
END IF
END FUNCTION {commit_work}
{ Rollback transaction if there is a log }
FUNCTION rollback_work()
IF translog() THEN
ROLLBACK WORK
END IF
END FUNCTION {rollback_work}
{ Terminate a transaction }
FUNCTION end_work(state)
DEFINE
state INTEGER
IF state != 0 THEN
CALL rollback_work()
ELSE
CALL commit_work()
END IF
END FUNCTION {end_work}
Jonathan Leffler
Sphinx Ltd.
22nd February 1988
===========================================================================
To log or not to log, that is the question
(With a limited apology to the Immortal Bard and the Prince of Denmark.)
1. Using a transaction log
Should the operational version of the database have a transaction log, and
does it have to be decided immediately? The answer to both questions is
"Yes"; the two sections which follow justify these answers, and the
remaining sections discuss consequences of these answers.
2. Why use a transaction log?
There are at least three points in favor of using a transaction log.
1. A transaction log makes backups easier. After a full backup, only the
log needs to be backed up until the next full backup because the log
records all the changes made to the database.
2. There are a number of parts of the application where changes must be
made to several tables, and unless all the changes are successful, the
database must be restored to it