G'day

Having a few problems with transactions in embedded sql:

C++Builder / FB1

I declare 3 transaction handles, 1 for select (read only), 1 for the
primary key generation, and on for inserts. These are initialised to NULL
on connection.

EXEC SQL BEGIN DECLARE SECTION;
//other host vars....
isc_tr_handle HtransIns, HtransSel, Htrans_genId;
EXEC SQL END DECLARE SECTION;

int TFrmAccDtls::insertNewOwner()
{
int ret = 1;

strcpy(HsnameO, EdOwnerSurname->Text.Trim().c_str());
//assign values from other TEdit fields as above into Host vars...


HaddridO = getInsertPK(TABLE_ADDR);
ShowMessage("HaddridO = " + IntToStr(HaddridO));

EXEC SQL SET TRANSACTION NAME HtransIns;
EXEC SQL
INSERT TRANSACTION HtransIns INTO addr(id, addr1, addr2, addr3, suburb,
state, cntry, pcode, ph_home, ph_work)
VALUES(:HaddridO, :Haddr1O, :Haddr2O, :Haddr3O, :HsuburbO, :HstateO,
:HcntryO, :HpcodeO, :Hph_homeO, :Hph_workO);
if(SQLCODE)
{
sprintf(tmp,"INSERT INTO addr...(SQLCODE %i) - \n", SQLCODE);
isc_sql_interprete((short)SQLCODE, err, sizeof(err));
strcat(tmp, err);
Application->MessageBox(tmp, "TFrmAccDtls::insertNewOwner()", MB_OK
| MB_ICONERROR);
ret = 0;
EXEC SQL ROLLBACK HtransIns;
goto abortINO;
}
Hownerlu_id = getInsertPK(TABLE_NAMELU);
ShowMessage("Hownerlu_id = " + IntToStr(Hownerlu_id));

EXEC SQL
INSERT TRANSACTION HtransIns INTO namelu(id, id_type, sname, fnames,
addl, title, addr_id, name_type)
VALUES(:Hownerlu_id, :Hid_type, :HsnameO, :HfnamesO, :HaddlO, :HtitleO,
:HaddridO, :HnameType);
if(SQLCODE)
{
sprintf(tmp,"INSERT INTO namelu...(SQLCODE %i) - \n", SQLCODE);
isc_sql_interprete((short)SQLCODE, err, sizeof(err));
strcat(tmp, err);
Application->MessageBox(tmp, "TFrmAccDtls::insertNewOwner()", MB_OK
| MB_ICONERROR);
ret = 0;
EXEC SQL ROLLBACK HtransIns;
goto abortINO;
}

EXEC SQL COMMIT HtransIns;

abortINO:
return(ret);
}

int getInsertPK(int table)
{
EXEC SQL SET TRANSACTION NAME Htrans_genId;
switch(table)
{
case TABLE_NAMELU:
EXEC SQL
SELECT TRANSACTION Htrans_genId CAST(GEN_ID(namelu_gen,1) AS
INTEGER)
INTO :HpkId FROM dummy;
break;

case TABLE_ADDR:
EXEC SQL
SELECT TRANSACTION Htrans_genId CAST(GEN_ID(addr_gen,1) AS INTEGER)

INTO :HpkId FROM dummy;
break;

//other case statements for other tables
default:
EXEC SQL ROLLBACK Htrans_genId;
return(-9);
}
if(! SQLCODE)
{
EXEC SQL COMMIT Htrans_genId;
return(HpkId);
}
else
{
EXEC SQL ROLLBACK Htrans_genId;
return(-1);
}
}

int connect(char *Hdb, char *Husr, char *Hpwd)
{
EXEC SQL CONNECT :Hdb USER :Husr PASSWORD :Hpwd;

Htrans_genId = NULL;
HtransIns = NULL;
HtransSel = NULL;
return(SQLCODE);
}


The rows appear to be added, in that any attempt to insert a row from isql
using a PK as created by getInsertPK() causes a "violation of PRIMARY or
UNIQUE KEY constraint" error. Despite this, the rows are not visible. Shut
everything down, restart the server, still not visible, run gfix -sweep,
still no joy. So what am I doing wrong?

Any _comprehensive_ sample code for embedded sql with IB/FB around?

I run gpre with the -m switch as all transactions are now named - perhaps
this was a mistake?

Regards