Thanks for the help to date and as a result I've made some progress using
psql. But I'm still no further forward with my C++ application!
I wrote a plpgsql function that executed the insert then the update
functions; I then executed the test function using:
begin transaction ;
select testing() ;
commit ;
This worked as one would expect. Now, this proves that the functions are
themselves syntactically and gramatically sound. But when these functions
are called in the application the first succeeds as it returns the new row
id. This row id is then passed to the update function but still fails.
I know the logic of the approach is sound since it works in psql, so again
I've got a big question mark above my head as to why this doesn't work in
C++ land.
The only thing that comes to mind is the "begin transaction" statement is
the key to psql's success. So how would I implement this via the ODBC.
There is to my knowledge no explicit BeginTransaction method in the API,
although the MSDN does produce results for the ADO and RDO. But I'm not
using them!
There is one thing I have noticed (or failed to mention earlier) is that
when the second (update) function fails the transaction is rolled back.
When the transaction is rolled back I get errors saying that there is a
Function Sequence error. So the implication here is that there was no
transaction in the first place.
According to the documents when the SQL_ATTR_AUTOCOMMIT is turned off the
next statement issued starts a transaction. When I turn off auto commit I
don't get an error.
No wonder I'm bald!
--
Regards,
Wm. G. Urquhart
Custom Software Solutions
http://www.wurquhart.co.uk