Add a transaction to a multiple insert in a SQLIte3 database

Hi!

I’m developing a game with Unreal 5.2.1 with C++ and SQLite3, but I have a problem inserting several rows, one by one, on a SQLite3 table.

This is my code:

void UCatalogueGameInstanceSubsystem::InsertMyColourToDatabase(TArray<FMyColour> MyColour)
{
	FSQLiteDatabase* MyDb = new FSQLiteDatabase();

	if (MyDb->Open(*DbFileFullPath, ESQLiteDatabaseOpenMode::ReadOnly))
	{
		FSQLitePreparedStatement* PreparedStatement = new FSQLitePreparedStatement();

		if (PreparedStatement->Create(*MyDb, *UpdateMyColour, ESQLitePreparedStatementFlags::Persistent))
		{
			TArray<FString> Keys;
			Keys.Add("$redRGB");
			Keys.Add("$greenRGB");
			Keys.Add("$blueRGB");
			Keys.Add("$id");

			for (const FMyColour MyColor : MyColour)
			{
				TArray<FString> Values;
				Values.Add(FString::SanitizeFloat(MyColor.RGB.R));
				Values.Add(FString::SanitizeFloat(MyColor.RGB.G));
				Values.Add(FString::SanitizeFloat(MyColor.RGB.B));
				Values.Add(FString::FromInt(MyColor.id));

				if (BindUpdateStatement(PreparedStatement, Keys, Values))
				{
					if (!PreparedStatement->Execute())
					{
						UE_LOG(LogTemp, Warning, TEXT("Failed to update color: %s"), *MyDb->GetLastError());
						break;
					}
				}
			}
		}

		PreparedStatement->Destroy();
		delete PreparedStatement;
	}

	MyDb->Close();
	delete MyDb;
}

bool UCatalogueGameInstanceSubsystem::BindUpdateStatement(
	class FSQLitePreparedStatement* Statement,
	const TArray<FString> Keys,
	const TArray<FString> Values) const
{
	for (int i = 0; i < Keys.Num(); i++)
	{
		if (!Statement->SetBindingValueByName(*Keys[i], *Values[i]))
			return false;
	}

	return true;
}

const FString UpdateMyColour = "UPDATE MyTable "
		"SET Red_RGB = $redRGB, Green_RGB = $greenRGB, Blue_RGB = $blueRGB "
		"WHERE ID = $id";

But it halts the game.

I think the problem is about the number of rows to update. It there are less rows, it doesn’t halt the game. So, I’ve thought to use TRANSACTIONS.

Do you know how I can add a transaction to the method UCatalogueGameInstanceSubsystem::InsertMyColourToDatabase?

Thanks!