I can not explain SQLException in my program

Question

I have a simple c++ program. There is "while" loop in which is being done query to database. I have excluded any potential possibility being the problem in reading values - the value on which program fails is processed normally if being processed earlier! So, that leads me to opinion there is any MYSQL limitation. Since mySQL doesn't work in Debug version the program run in Release.

The program fails on prepareStatement ("SELECT region_id..."):

rapidcsv::Document doc = rapidcsv::Document("***.csv", rapidcsv::LabelParams(0, -1), rapidcsv::SeparatorParams(';'));
short rowIndex = 0;
short rows = doc.GetRowCount();
while (rowIndex<rows)
{
    short country_id;
    sql::PreparedStatement* prep;
    sql::ResultSet* res;
    prep = this->con->prepareStatement("SELECT country_id FROM countries WHERE name LIKE ?");
    prep->setString(1, doc.GetRow<std::string>(rowIndex)[0]);
    res=prep->executeQuery();
    if (res->next()) {
        country_id = res->getInt(1);
    }
    else {
        sql::Statement* st;
        sql::ResultSet* re;
        st = this->con->createStatement();
        re = st->executeQuery("SELECT MAX(country_id) FROM countries");
        re->next();
        country_id = re->getInt(1) + 1;
        delete st, re;
        prep = this->con->prepareStatement("INSERT INTO countries VALUES(?, ?)");
        prep->setInt(1, country_id);
        prep->setString(2, doc.GetRow<std::string>(rowIndex)[0]);
        prep->executeUpdate();
    }
    short region_id;
    prep = this->con->prepareStatement("SELECT region_id FROM regions WHERE country_id = ? AND name LIKE ?");
    prep->setInt(1, country_id);
    prep->setString(2, doc.GetRow<std::string>(rowIndex)[1]);
    res = prep->executeQuery();
    if (res->next()) {
        region_id = res->getInt(1);
    }
    else {
        sql::Statement* st;
        sql::ResultSet* re;
        st = this->con->createStatement();
        re = st->executeQuery("SELECT MAX(region_id) FROM regions");
        re->next();
        region_id = re->getInt(1) + 1;
        delete st, re;
        prep = this->con->prepareStatement("INSERT INTO regions VALUES(?, ?, ?)");
        prep->setInt(1, region_id);
        prep->setInt(2, country_id);
        prep->setString(3, doc.GetRow<std::string>(rowIndex)[1]);
        prep->executeUpdate();
    }
    short district_id;
    prep = this->con->prepareStatement("SELECT district_id FROM districts WHERE name LIKE ? AND region_id = ? AND country_id = ?");
    prep->setString(1, doc.GetRow<std::string>(rowIndex)[2]);
    prep->setInt(2, region_id);
    prep->setInt(3, country_id);
    res=prep->executeQuery();
    if (res->next()) {
        district_id = res->getInt(1);
    }
    else {
        sql::Statement* st;
        sql::ResultSet* re;
        st = this->con->createStatement();
        re = st->executeQuery("SELECT MAX(district_id) FROM districts");
        re->next();
        district_id = re->getInt(1) + 1;
        delete st, re;
        prep = this->con->prepareStatement("INSERT INTO districts VALUES(?, ?, ?, ?)");
        prep->setInt(1, district_id);
        prep->setInt(2, region_id);
        prep->setInt(3, country_id);
        prep->setString(4, doc.GetRow<std::string>(rowIndex)[2]);
        prep->executeUpdate();
    }
    short munici_id;
    prep = this->con->prepareStatement("SELECT municipality_id FROM municipalities WHERE name LIKE ? AND region_id = ? AND country_id = ? AND district_id = ?");
    prep->setString(1, doc.GetRow<std::string>(rowIndex)[3]);
    prep->setInt(2, region_id);
    prep->setInt(3, country_id);
    prep->setInt(4, district_id);
    res = prep->executeQuery();
    if (res->next()) {
        munici_id = res->getInt(1);
    }
    else {
        sql::Statement* st;
        sql::ResultSet* re;
        st = this->con->createStatement();
        re = st->executeQuery("SELECT MAX(municipality_id) FROM municipalities");
        re->next();
        munici_id = re->getInt(1) + 1;
        delete st, re;
        prep = this->con->prepareStatement("INSERT INTO municipalities VALUES(?, ?, ?, ?, ?)");
        prep->setInt(1, munici_id);
        prep->setInt(2, district_id);
        prep->setInt(3, region_id);
        prep->setInt(4, country_id);
        prep->setString(5, doc.GetRow<std::string>(rowIndex)[3]);
        prep->executeUpdate();
    }
    prep = con->prepareStatement("INSERT INTO cadastral_areas VALUES(?, ?, ?, ?, ?, ?)");
    prep->setInt(1, ++rowIndex);
    prep->setInt(2, munici_id);
    prep->setInt(3, district_id);
    prep->setInt(4, region_id);
    prep->setInt(5, country_id);
    prep->setString(6, doc.GetRow<std::string>(rowIndex - 1)[4]);
    prep->executeUpdate();
    delete prep, res;
}

Answer 1

The whole point of prepared statements is that you create them once and then reuse them. You can think of a prepared statement as a pre-parsed query on the server side with holes to fill (the question marks).

So you prepare them once, up front:

std::unique_ptr<sql::PreparedStement> select_country = this->con->select_countryareStatement("SELECT country_id FROM countries WHERE name LIKE ?");
std::unique_ptr<sql::PreparedStement> insert_country = this->con->insert_countryareStatement("INSERT INTO countries VALUES(?, ?)");
std::unique_ptr<sql::PreparedStement> select_region = this->con->select_regionareStatement("SELECT region_id FROM regions WHERE country_id = ? AND name LIKE ?");
std::unique_ptr<sql::PreparedStement> insert_region = this->con->insert_regionareStatement("INSERT INTO regions VALUES(?, ?, ?)");
std::unique_ptr<sql::PreparedStement> select_district = this->con->select_districtareStatement("SELECT district_id FROM districts WHERE name LIKE ? AND region_id = ? AND country_id = ?");
std::unique_ptr<sql::PreparedStement> insert_district = this->con->insert_districtareStatement("INSERT INTO districts VALUES(?, ?, ?, ?)");
std::unique_ptr<sql::PreparedStement> get_municipality = this->con->get_municipalityareStatement("SELECT municipality_id FROM municipalities WHERE name LIKE ? AND region_id = ? AND country_id = ? AND district_id = ?");
std::unique_ptr<sql::PreparedStement> insert_municipality = this->con->insert_municipalityareStatement("INSERT INTO municipalities VALUES(?, ?, ?, ?, ?)");
std::unique_ptr<sql::PreparedStement> insert_cadastral_area = con->insert_cadastral_areaareStatement("INSERT INTO cadastral_areas VALUES(?, ?, ?, ?, ?, ?)");

and then use them like you would use prep otherwise:

insert_country->setInt(1, country_id);
insert_country->setString(2, doc.GetRow<std::string>(rowIndex)[0]);
insert_country->executeUpdate();

Using a std::unique_ptr frees you from having to call delete yourself. You should adopt it for the other resources as well, because your code delete st, re deletes st but leaves re untouched.