hi,
i use pg 8.0 with odbc 8.0.0.4 for some test stuff.
My application tries to insert many records from a ascii-file into a
db-table.
I use a transaction to speed up this process.
The application logic is the following :
1. create savepoint
2. try to update an existing record with the same key
3. if the update fails, do an rollback to savepoint and go to the next
record in file
4. if SQLNumResultCols returns 0 try an insert
5. if the insert fails, do an rollback to savepoint and go to the next
record in file
in any case do an release savepoint before creating a new savepoint.
the problem :
if an error occur while doing an update or insert the transaction will be
abortet
look at the following :
[744]in QR_Constructor
[744]exit QR_Constructor
[744]read 122, global_socket_buffersize=4096
[744]send_query: got id = 'P'
[744]send_query: got id = 'E'
[744]send_query: 'E' - ERROR: insert or update on table "rueckmeldungen"
violates foreign key constraint "rueckmeldungen_ursache_fkey"
[744]read 1, global_socket_buffersize=4096
[744]send_query: got id = 'Z'
[744]send_query(): conn=18824880, query='ROLLBACK'
[744]send_query: done sending query
[744]in QR_Constructor
[744]exit QR_Constructor
[744]read 11, global_socket_buffersize=4096
[744]send_query: got id = 'C'
[744]send_query: ok - 'C' - ROLLBACK
[744]send_query: setting cmdbuffer = 'ROLLBACK'
[744]send_query: returning res = 18852384
[744]send_query: got id = 'Z'
[744]CC_abort: sending ABORT!
[744]QResult: in DESTRUCTOR
[744]QResult: free memory in, fcount=0
[744]QResult: free memory out
[744]QResult: exit DESTRUCTOR
[744]STATEMENT ERROR: func=SC_execute, desc='', errnum=7, errmsg='Error
while executing the query'
[744]CONN ERROR: func=SC_execute, desc='', errnum=110, errmsg='ERROR:
insert or update on table "rueckmeldungen" violates foreign key constraint
"rueckmeldungen_ursache_fkey"'
[744]PGAPI_ExecDirect: returned -1 from PGAPI_Execute
[744][[SQLGetDiagFieldW]] Handle=(3,11f89f0) Rec=1 Id=4
The rollback is being executed (automatically) before I can do any own
error recovery !!!
How can I solve this problem.
greetings
tom