ttsAbort to rollback all csv rows and make no changes in table?

(1) Share

Report

Posted on by Community Member

I want to insert data from csv to two tables.

If there is any invalid data in the csv, the process should be rollback and made no changes to tables.

E.g. The csv file contains 4 rows and the last row has an invalid data value. When the batch run and the invalid data is found... all rows must not be inserted and the process need to stop.

This is my code...

---

private void insertCustomTable(container _container)
{
CustomTable z_CustomTable;
NewTable z_NewTable;

ttsBegin;
try
{
// CustomTable
z_CustomTable.Name = conPeek(_container, 1);
z_CustomTable.Address = conPeek(_container, 2);

z_CustomTable.insert();

// NewTable
z_NewTable.clear();
z_NewTable.initValue();
z_NewTable.initFromCustomTable(z_CustomTable);
z_NewTable.Description = conPeek(_container, 3);
z_NewTable.ID = conPeek(_container, 4);

z_NewTable.insert();

ttsCommit;
}
catch(Exception::CLRError)
{
ttsAbort;
info(CLRInterop::getLastException().toString());
}

---

The problem is the process only stop at the error row and the correct rows get inserted into tables.

If 4th row has error, the 1,2,3rd rows are inserted.

If 1st row has error, the 2,3,4th rows are not inserted.

The process stopped only if it detects the error and the previous correct rows are inserted into tables.

Please Advice.

*This post is locked for comments

I have the same question (0)

All responses (9)

Answers (1)

Verified answer

Hariharans87 3 on at

Like (1)

Report
You started ttsbegin before try statement, so you need to ttscommit after the try statement.
You don't need to call the ttsabort in the catch statement. Because, by default the standard will call ttsabort when any error occur.

Try this code:

private void insertCustomTable(container _container) { CustomTable z_CustomTable; NewTable z_NewTable; ttsBegin; try { // CustomTable z_CustomTable.clear(); z_CustomTable.Name = conPeek(_container, 1); z_CustomTable.Address = conPeek(_container, 2); z_CustomTable.insert(); // NewTable z_NewTable.clear(); z_NewTable.initValue(); z_NewTable.initFromCustomTable(z_CustomTable); z_NewTable.Description = conPeek(_container, 3); z_NewTable.ID = conPeek(_container, 4); z_NewTable.insert(); } catch(Exception::CLRError) { info(CLRInterop::getLastException().toString()); } ttsCommit; }

Was this reply helpful? Yes No
Suggested answer

Martin Dráb 237,985 Most Valuable Professional on at

Like (1)

Report

Htoo Ko Ko: It seems that your code snippet is for handling a single line of CSV, which isn't the correct place for the transaction. If you have four lines, you need all four calls to insertCustomTable() in a single transaction, therefore you have to do it at a higher level, in the method calling insertCustomTable().

Hariharan: Because you can't catch exceptions inside transactions, the try/catch statement in your code won't have any effect.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hariharan: The correct csv data are still being inserted.

Thanks for the knowledge.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Martin Drab: Thanks for the knowledge. I will try again in method calling position.

Was this reply helpful? Yes No
Martin Dráb 237,985 Most Valuable Professional on at

Like (1)

Report

By the way, you don't need ttsAbort in catch - throwing (most) exceptions rollbacks transactions automatically.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Martin: The method which calls [insertCustomTable] method is as below...

private boolean readCsvFile() { TextIo csvFile, csvFilePurch; container readCon, readConPurch; int insCnt; boolean ret = true; readCon = conNull(); csvFile = new TextIo(pFileName, 'r', 65001); csvFile.inRecordDelimiter('\n'); csvFile.inFieldDelimiter(','); readConPurch = conNull(); csvFilePurch = new TextIo(pFileName, 'r', 65001); csvFilePurch.inRecordDelimiter('\n'); csvFilePurch.inFieldDelimiter(','); if (! csvFile ) { info (strFmt ("@ZZZ44", pFileName)); } try { if (csvFile) { readCon = csvFile.read(); readConPurch = csvFilePurch.read(); while (csvFilePurch.status() == IO_Status::OK) { readConPurch = csvFilePurch.read(); // No Data in CSV File if (! readConPurch) { break; } recordCount++; recordErr = 0; if (! this.ischeckValidFormats(readConPurch)) { errNum += 1; ret = false; break; } for(insCnt=1; insCnt <= recordCount; insCnt++ ) { this.insertCustomTable(readConPurch); norNum += 1; } recordCount = 0; } } } catch(Exception::CLRError) { info(CLRInterop::getLastException().toString()); } return ret; }

----------------------------------------------------------------------------------------------------------
I tried editing the code in "for(insCnt=1; insCnt <= recordCount; insCnt++ ) { }" section but I still can't figure out.

Was this reply helpful? Yes No
Hariharans87 3 on at

Like (1)

Report

Thanks Martin. I agree.

Was this reply helpful? Yes No
Hariharans87 3 on at

Like (1)

Report

You have to use transaction between try statement and while statement.

Note: Your for loop always execute one time.

for(insCnt=1; insCnt <= recordCount; insCnt++ )

I believe without for loop it will work.

Was this reply helpful? Yes No
Martin Dráb 237,985 Most Valuable Professional on at

Like (2)

Report
You have to add a transaction to readCsvFile().

If I omit your handling of CLR error (because I don't know why it's there), you code may look like this:

TextIo csvFile, csvFilePurch; container readCon, readConPurch; int insCnt; boolean ret = true; csvFile = new TextIo(pFileName, 'r', 65001); csvFile.inRecordDelimiter('\n'); csvFile.inFieldDelimiter(','); csvFilePurch = new TextIo(pFileName, 'r', 65001); csvFilePurch.inRecordDelimiter('\n'); csvFilePurch.inFieldDelimiter(','); if (csvFile) { ttsBegin; readCon = csvFile.read(); readConPurch = csvFilePurch.read(); while (csvFilePurch.status() == IO_Status::OK) { // No Data in CSV File if (! readConPurch) { break; } recordCount++; recordErr = 0; if (! this.ischeckValidFormats(readConPurch)) { errNum += 1; ret = false; break; } for (insCnt=1; insCnt <= recordCount; insCnt++) { this.insertCustomTable(readConPurch); norNum += 1; } recordCount = 0;
} ttsCommit; } else { info (strFmt("@ZZZ44", pFileName)); } return ret;

Nevertheless a lot of your code looks pretty suspicious to me; such as why you have the for loop in the while loop and why you check the existence of csvFile if you never use it. These seem to be bugs.

Was this reply helpful? Yes No