What's in a For Loop?

In this article, we'll delve into a specifics of using a For loop, inserting data into the database, and use of transaction plus some error handling too.

for(int i = 0; i <= this.parmRecordCount(); i++)
{
    ttsbegin;
    SalesPool.SalesPoolId = int2Str(i);
    SalesPool.Name = int2Str(i);
    SalesPool.insert();
    ttscommit;
}

Loop Structure

An x++ for-loop is very similar to any other for-loop out this in terms of its idea. This is a for-loop, initiating with i set to 0 and running until i is greater than the number returned by this.parmRecordCount(). This loop controls the number of times records are inserted into the SalesPool table. This is essentially the same as any for-loop in most other 4th generation languages.

Transaction Handling

The ttsBegin; and ttsCommit; statements manage database transactions. ttsbegin marks the start of a transaction, and ttscommit commits the transaction to the database. This ensures data integrity and consistency. You can nest transactions in other transactions. ttsbegin increments a counter called ttslevel by 1 and ttscommit decrements that counter by 1. When ttslevel is zero, all transactions have been committed (if no errors thrown and unhandled). You can read more here if you're interested.

Record Insertion

The SalesPool.insert(); segment inserts a new record into the SalesPool table with the given SalesPoolId and Name. In our example we're assuming that no record level or primary key level validation will be an issue. When you declare a table buffer (like SalesPool salesPool;) and set its fields, you're preparing a record to be inserted into the database. This buffer holds the data that will be written to the database. This method is responsible for adding the new record into the table that the buffer represents.  Field-level validation methods, such as validateField(), are executed to ensure that each field in the record meets the business logic requirements. The validateWrite() method is called on the table buffer. This method can contain custom validation logic that applies to the entire record. If all validations pass, the record is then inserted into the database. This is where the record physically becomes a part of the table.

Analysis and Discussion

The code's primary function is to populate the SalesPool table with records. Each record's SalesPoolId and Name are set to the current iteration index of the loop, converted to a string. Our example we're just generating data as part of a series of tests. The use of transactions (ttsbegin and ttscommit) in each iteration can be considered both a pro and a con. While it ensures the integrity of each record insertion, it may lead to performance overhead, especially if parmRecordCount() returns a large number. In such cases, batch processing or other optimization techniques might be more efficient. Also if the method that gets your loop upper limit is doing a lot of work, that can slow your loop down. The loop will run that method every iteration to check its current value even if its not changing, like in our case because its a simple parm method. We'll give the snipped a rewrite later with some of these ideas in mind. The loop will insert and commit 1 record at a time so if it fails at record 501, 1 through 500 will have committed. There is no error handling on this database interaction which will cause the transaction presenting with an error to rollback. The code's scalability depends on the implementation of this.parmRecordCount() and the database's capability to handle numerous insertions in a short time. The code is straightforward and easy to maintain. However, it lacks error handling and logging, which might be necessary for debugging and maintenance in a production environment.

A Better Way

Below is an improved snipped that acheives the same outcome but does it in a much more safe way. First, we have basic error handling for things that could go wrong. Near 100% of the time things to not go wrong but if they do, we have some exception handling to attempt to address and recover from the error and if we can't, a place to log it. Next, we're opening the transaction, sending a bunch of data over then committing the transaction. A database commit is a fairly slow operation, relative to everything else, so if wait until we have sent all data over, we won't incur the slow down related to committing a transaction. That "slow down" may be tiny amount but at scale a million  units of "tiny" add up. 

ttsbegin;
try
{
    int recordCount = this.parmRecordCount();
    for(int i = 0; i <= recordCount; i++)
    {
        SalesPool.SalesPoolId = int2Str(i);
        SalesPool.Name = int2Str(i);
        SalesPool.insert();
    }
    ttscommit;
}
catch (Exception::DuplicateKeyException)
{
    ttsabort;
    // Handle the duplicate key exception
}
catch (Exception::Error)
{
    ttsabort;
    // Handle the error 
}
catch
{
    ttsabort;
    // Log the error or handle it as needed
}

The Overkill Way

Those that know me know I really enjoyed the game Turbo Overkill, so with that in mind, let's look at the code below

#OCCRetryCount

int i;
int recordCount = this.parmRecordCount(); // Fetch the count once outside the loop
int retryCount = 0;

try
{
    ttsbegin; // Start the transaction outside the loop

    for (i = 0; i < recordCount; i++) // Use less than '<' since loop starts at 0
    {
        SalesPool salesPool; // Declare the table buffer inside the loop

        salesPool.SalesPoolId = int2Str(i + 1); // Assuming IDs should start from 1
        salesPool.Name = int2Str(i + 1);

        salesPool.insert();
    }

    ttscommit; // Commit the transaction after the loop
}
catch (Exception::Deadlock)
{
    // Handle deadlock exception
    ttsabort; // Abort the current transaction before retrying
    if (appl.ttsLevel() == 0)
    {
        if (xSession::currentRetryCount() >= #RetryNum)
        {
            throw Exception::Deadlock;
        }
        else
        {
            retry;
        }
    }
    else
    {
        throw Exception::MaximumRetryCountExceeded;
    }
}
catch (Exception::DuplicateKeyException)
{
    // Handle duplicate key exception
    ttsabort;
    info("Duplicate key encountered at iteration: " + int2Str(i));
}
catch (Exception::Error)
{
    // Handle general errors
    ttsabort; // Abort the transaction
    error("An error occurred at iteration: " + int2Str(i));
}
finally
{
    if (appl.ttsLevel() > 0)
    {
        ttsabort; // Ensure to abort transaction if not committed or already aborted
    }
    // Code to execute regardless of whether an error occurred
}
 

This code handles nearly all of the common or semi-common errors that can occur plus handles if there is any database issue. This will also retry if there is a dead lock exception thrown meaning if the database throws a specific dead-lock error, the code will suppress it and try again up to 3 times.