I got too enthusistic in my snipping, it should be as below.

Are you saying that I just need to add a commit just before the EVENT
WAIT below so that there isn't an open transaction when the program is
waiting for the event?

EXEC SQL CONNECT :db_filename USER :db_username PASSWORD :db_password;

/* Go with read committed to see updates */
EXEC SQL SET TRANSACTION READ COMMITTED;

EXEC SQL SELECT basecontrolident INTO :basecontrolident FROM
TBLBASECONTROLLER; /* for filename */

EXEC SQL EVENT INIT ALERT_WAIT ('amsalert');

while (0 == 0) /* until killed */
{
rowcount = 0;
EXEC SQL SELECT COUNT (*) INTO :rowcount FROM TBLALERT;
if (rowcount == 0)
{
LOG("DS", LGREF "23","","waiting for alert");
EXEC SQL EVENT WAIT ALERT_WAIT;
if (SQLCODE != 0)
{
isc_print_sqlerror((short)SQLCODE, gds__status);
goto fatal_error;
}
LOG("DS", LGREF "24","","received alert");
}
handle event
commit
end while

On 07/11/2007, Martijn Tonies <m.tonies@...> wrote:
> Nick,
>
> > I have a program which just connects to the database and waits for an
> > event to occur, it then handles it and waits again (see code excert
> > below).
> >
> > The problem is that it seems to be holding the OIT, after a busy
> > period that didn't cause any events oldest & next transactions differ
> > by over 400,000, as soon as I caused an event oldest caught up.
> >
> > I figure I either need to connect with different params or some
> > different transaction parameters but I can't see what to change.
> >
> >
> > EXEC SQL CONNECT :db_filename USER :db_username PASSWORD
> :db_password;
> >
> > /* Go with read committed to see updates */
> > EXEC SQL SET TRANSACTION READ COMMITTED;
>
> Why are you starting a transaction here?
>
> > EXEC SQL EVENT INIT ALERT_WAIT ('amsalert');
> >
> > while (0 == 0) /* until killed */
> > {
> >
> > EXEC SQL EVENT WAIT ALERT_WAIT;
> > handle alert
> > }
> >
>
> Why not start the transaction once the event has arrived at the
> client, do your thing and commit the transaction?
>
> Martijn Tonies
> Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
> MS SQL Server
> Upscene Productions
> http://www.upscene.com
> My thoughts:
> http://blog.upscene.com/martijn/
> Database development questions? Check the forum!
> http://www.databasedevelopmentforum.com
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>