Hello!
I am working on a program that would eventually permit querying,
inserting, deleting and modifying some data stored in a table.
(I use postgresql 6.5.3, g++, ncurses, libpq on Debian GNU/Linux).
The program generates Sql commands according to the
user's actions ( insert into .. , delete from .. , etc. ) and sends
them for execution to the postgresql server with PQexec().
I want the user to be able to choose between commiting and
rollbacking the changes he/she has made to the table.
Therefore:
1. when the user presses KEY_END, I execute: PQexec(conn,"commit")
2. when the user presses KEY_HOME, I execute: PQexec(conn,"rollback")
Here comes the problem:
Imagine the user has inserted 5 new records. He enters the 6th record
but makes a mistake ( for example enters a bad date format ).
The program generates an INSERT command and executes it with PQexec.
An error is generated and the program prints it on the screen.
Because of the error, the transaction is compromised and has to be
aborted, and the user has to enter the first 5 records... again...
Very annoying.
The program should report the error and let the user correct the
typing mistake, without affecting the other records (although
there is only one transaction).
But, I don't know how to achieve this.
(I've noticed that even in psql if I enter an erroneous insert
command, the transaction is left in an unusual state , and queries
are no longer executed until "rollback" or "abort" is entered).
Please tell me, is there a way to abort only the last command of
a transaction? Or, is it possible to use "subtransactions"
inside a transaction?
Thanks in advance for any hint or idea.
Adrian Maier
(am@fx.ro)