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;
}

Please add the error message you get. Also you may want to change your title. While yes, MySQL has limitations, there is about a 0.016779% chance that your problem is due to one of those limitations and not because of a bug in your code. — Solarflare, Aug 12 '22 at 13:04
The message: Unhandled exception at 0x00007FF93438474C in Project.exe: Microsoft C++ exception: sql::SQLException at memory location 0x000000BADA4FF5B0. — Karol Rosina, Aug 12 '22 at 13:22
You need to get the details of the sql exception, otherwise it will be difficult (if not impossible) to help you — Shadow, Aug 12 '22 at 13:50
Maybe you want to add a try / catch and see what the message is. There is sample code here to do so: [https://github.com/anhstudios/mysql-connector-cpp/blob/master/examples/exceptions.cpp](https://github.com/anhstudios/mysql-connector-cpp/blob/master/examples/exceptions.cpp) — drescherjm, Aug 12 '22 at 13:51
@Solarflare It looks that the 0.016779% chance of being the problem due to MySQL limitation is my case. I added a try catch block and a catched exception message is: Can't create more than max_prepared_stmt_count statements. — Karol Rosina, Aug 12 '22 at 13:58
... so the solution is clear to me - close preparedStatements. — Karol Rosina, Aug 12 '22 at 14:05
Related: [https://serverfault.com/questions/990731/how-to-set-max-prepared-stmt-count-permanently-on-mysql-server](https://serverfault.com/questions/990731/how-to-set-max-prepared-stmt-count-permanently-on-mysql-server) — drescherjm, Aug 12 '22 at 14:07
@KarolRosina `delete st, re;` -- Explain what this line of code is supposed to do. Whatever it is, it doesn't do what you think it does. It doesn't do a `delete` on those individual items. You must specify `delete st; delete re;`. If `st` and `re` do resource cleanup on destruction, then possibly all of your issues have to do with not properly writing the `delete` calls, and nothing to do with MYSQL. — PaulMcKenzie, Aug 12 '22 at 14:46
@KarolRosina If you reach 16k open prepared statements to execute 10 different queries, you may want to consider it a bug in your code, not a limitation of MySQL. Otherwise, it would be like saying that the solution to a memory leak would be to buy more ram. But glad you figured out the problem, no matter what you call it :-) — Solarflare, Aug 12 '22 at 15:36

Botje · Answer 1 · 2022-08-12T15:13:13.903

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.

You should also point out the obvious issue with the `delete` calls in the code. — PaulMcKenzie, Aug 12 '22 at 15:06

I can not explain SQLException in my program

1 Answers1