PostgreSQL, ODBC and transaction strange behavior
-
I've a serious problem :(( in my software while inserting data in PostgreSQL tables. I'm using VC6.0 sp6, the last PostgresSQL release and the last associated psqlODBC driver. I can not find anything in msdn help, so I post my problem and I except that someone could help me.:rolleyes: That is the way I insert my data: 1. I create a database connection to my database, using psqlODBC driver 2. I open a recordset (with appendOnly and bulkrowadd options) 3. Start a transaction with CDatabase::BeginTrans() 3. I insert lots new records to my table (hundreds of records) 4. Commit transaction with CDatabase::CommitTrans(); 5. Close database connection But, when an insert fails (a CDBExcpetion is thrown in Update() function), then all previously non commited records are lost !!!:~ I just want only bad insert to be lost but not all my records updated since BeginTrans() Here is my code :
CDatabase * pDatabase = new CDatabase(); pDatabase->OpenEx(); //ask user to select a DSN CRecordsetST myRecordset(pDatabase); myRecordset.Open(AFX_DB_USE_DEFAULT_TYPE, NULL, CRecordset::optimizeBulkAdd | CRecordset::appendOnly); if(!myRecordset.IsOpen()) { pDatabase->Close(); return; } pDatabase->BeginTrans(); while( //file is not EOF ) { try { myRecordset.AddNew(); //fill recordset fields with file data //..... //..... //..... //..... myRecordset.Update(); } catch(CDBException *e) { //if a CDBException is catched, then //cancelupdate to pass current record //and continue to next data without //doing rollback //cancel update to prepare next record myRecordset.CancelUpdate(); //trace some message OutputDebugString(e->m_strError+_T("\n")); e->Delete(); //here I do not want to rollback my transaction, but just cancel current update //and continue my process with keeping all previously added records (good ones) } catch(...) { //if an unknown exception if catched then rollback all myRecordset.CancelUpdate(); //trace some message OutputDebugString(_T("FATAL error while inserting data\n")); //perform rollback pDatabase->Rollback(); pDatabase->Close(); return; } //read next line in file } pDatabase->CommitTrans(); pDatabase->Close(); ...I've tried to commit current transaction in first catch, but records are not saved. I've searched for some options in CDatabase or CRecordset, but I didn't find anything. I excepect that you could help me. thanks for all.