--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...> wrote:

>
> At 03:02 PM 30/12/2007, you wrote:
>
> >--- Helen Borrie <helebor@...> wrote:
> >
> >>
> >> At this point, transaction #2 is active and is interested in the

completed

> >> (committed or rolled-back) transaction #1.
> >
> >why is it still active while it has been (hard-)committed?
>
> Silly question, I hope - but are you watching the database from

another application (such as isql or some other tool)?

Helen Borrie and Edwin Pratomo ---

Since I started this thread back in November 2007, I have a keen
interest in the topic. I now think that Edward's ``DBD::InterBase''
Perl module is properly written and reveals either a flaw in Firebird
(2.0.3) or perhaps in the API documentation.

Distilling what Edwin provided, here's a very simple example that runs
cleanly under both Linux (gcc with all warnings enabled) and Windows
XP (using Borland C++ Builder 5) illustrating the increment of the
``Next Transaction'' counter only. The code is intentionally terse and
free of comments so that it can be cut and pasted from this page,
compiled and executed. It's a very much simplified version of
``api3.c'' and Edwin's ``single.c''.

Just run the program several times and keep an eye on the transaction
counters. Only ``Next transaction'' increments. Even when
``ics_start_transaction()'' is given a ``tpb'' with
``isc_tpb_read_committed'' defind, the problem remains.

As Edwin pointed out, the only remedy I've found is to create a second
(dummy) transaction and not associate it with any statement handle.

------------- <single_query.c> -----------------------------

#include <assert.h>
#include <ibase.h>
#include <stdio.h>
#include <stdlib.h>

#define CHECK_RESULT() do { \
isc_print_status(status); \
if(result) exit(result); } \
while(0)

int main(void)
{
ISC_STATUS_ARRAY status;
ISC_STATUS result;
isc_db_handle db = NULL;
isc_tr_handle tr = NULL;
isc_stmt_handle st = NULL;
XSQLDA *da;
long count;
short ind = 0;
const char *dbname = "localhost:employee.fdb";
const char *query = "SELECT count(*) FROM country";

result = isc_attach_database(status, 0, dbname, &db, 0, NULL);
CHECK_RESULT();

result = isc_start_transaction(status, &tr, 1, &db, 0, NULL);
CHECK_RESULT();

result = isc_dsql_allocate_statement(status, &db, &st);
CHECK_RESULT();

da = malloc(XSQLDA_LENGTH(1));
assert(da != NULL);

da->sqln = 1;
da->sqld = 1;
da->version = SQLDA_VERSION1;
da->sqlvar[0].sqldata = (char *)&count;
da->sqlvar[0].sqltype = SQL_LONG + 1;
da->sqlvar[0].sqlind = &ind;

result = isc_dsql_prepare(status, &tr, &st, 0, query, 1, da);
CHECK_RESULT();

result = isc_dsql_execute(status, &tr, &st, 1, NULL);
CHECK_RESULT();

result = isc_dsql_fetch(status, &st, 1, da);
CHECK_RESULT();
printf("COUNT: %ld\n", count);

result = isc_dsql_free_statement(status, &st, DSQL_close);
CHECK_RESULT();

result = isc_commit_transaction(status, &tr);
CHECK_RESULT();

result = isc_detach_database(status, &db);
CHECK_RESULT();

free(da);

return EXIT_SUCCESS;
}