Re: [Firebird-docs] Help

[Thad H. <th...@mi...>]

On Thu, 30 Jan 2003 14:09:19 -0500, Helen Borrie wrote:

> At 05:02 PM 30/01/2003 -0500, you wrote:
>>The most significant difference I've found is transaction control where
>>Firebird wins hands down!  For example, Postgres does not support nested
>>transactions.  Firebird does--you just have to name them.
> 
> No, Firebird doesn't support nested transactions.   It does supported
> nested stored procedures.  Firebird 2 will have named savepoints.

Maybe I didn't choose my words correctly.  PostgreSQL docs seems to call
it nested when you start another transaction before committing or rolling
back the current transactions. (I believe the book where it said
'PostgreSQL does not handle embedded transactions' was "Postgresql:
Developer's Handbook" by Geschwinde, Schoenig, and Schonig.)  Maybe I
should say "simultaneous"?  If I've used the term incorrectly, sorry.
Please let me know what I should call it.

Whatever it's called (and please correct me), it was the brick wall I hit
when I first tried porting from Oracle to PostgreSQL.  In PostgreSQL,
committing any transaction closes all transactions.  In the embedded SQL I
just I just ported, there are many instances where I run one or more named
transactions inside another--or, at least, while another one is executing.

For example, in the .e file that accesses the database, I have two
functions.  The first opens a cursor into a table and fetches record. The
second closes it.  (Since we use other databases, I also have Oracle *.pc,
Sybase *.cp, and Informix *.ep version of this, each tailored to their own
peculiar embedded SQL implementation.)

----------------- .e file ----------------------

static XSQLDA   *out_sqlda;		/* XSQLDA struct for all output params	*/
static isc_tr_handle    ent_trans;  /*  transaction for get_dbrec_ent() */
int entflag = FALSE;
...

int get_dbrec_ent( dbrec r )
{
  EXEC SQL BEGIN DECLARE SECTION;
  char		thesql[1024];
  EXEC SQL END DECLARE SECTION;
  EXEC SQL WHENEVER SQLERROR GO TO e_error; 
  EXEC SQL WHENEVER NOT FOUND GO TO e_notfound;

  if ( entflag == FALSE ) {
    memset( thesql, 0, sizeof thesql );
    strcpy( thesql, "SELECT " );
    ...
    /* allocate XSQLDA */

    ent_trans = (isc_tr_handle) NULL;
    EXEC SQL SET TRANSACTION NAME ent_trans READ ONLY; 
    EXEC SQL PREPARE TRANSACTION ent_trans statement FROM :thesql; 
    EXEC SQL DESCRIBE OUTPUT statement INTO SQL DESCRIPTOR out_sqlda; 
    ...
    /* allocate XSQVARS */

    EXEC SQL DECLARE scrnc CURSOR FOR statement; 
    EXEC SQL OPEN TRANSACTION ent_trans cursor;

    entflag = TRUE;
  }
  
  EXEC SQL FETCH cursor USING SQL DESCRIPTOR out_sqlda; 
  ...
  /* convert to  my dbrec structure */

  return 0;

  e_notfound:
  EXEC SQL WHENEVER NOT FOUND CONTINUE; 
  return E_NOREC;

  e_error:
  EXEC SQL WHENEVER SQLERROR CONTINUE;
  return DB_ERROR;
}

void end_o_dbrec_ent()
{
  entflag	= FALSE;
  EXEC SQL CLOSE cursor;
  ...
  /* free XSQLVARS and XSQLDA */
  /* commit ent_trans transaction */
}

----------------- .e file ----------------------

Now in my *.c file, I access get_dbrec_ent() in a loop.  This loop may
well call funtions in other *.e files, functions which create, execute,
and commit/rollbacktheir own transactions.

while( get_dbrec_ent( &mydbrec ) == 0 ) {
  ...
}
end_o_dbrec_ent();

I have tested cases of five and more transactions running at the same
time with Firebird.  This was not possible with PostgreSQL because the
first commit/rollback closed down everything.
-- 
------------------------------------------------------------------------
Thad Humphries              "...no religious test shall ever be required
Web Development Manager      as a qualification to any office or public
Phone: 540/675-3015, x225    trust under the United States." -Article VI