我无法在我的程序中解释SQLException

Question

我有一个简单的c++程序。存在"；而"；循环，其中正在对数据库进行查询。我已经排除了读取值时出现问题的任何潜在可能性——如果提前处理，程序失败的值将正常处理！所以，这让我认为MYSQL有任何限制。由于mySQL在调试版本中不起作用，该程序在Release中运行。

程序在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

准备好的语句的全部意义在于只创建一次，然后重用它们。您可以将准备好的语句视为服务器端的预解析查询，其中有需要填充的漏洞(问号(。

所以你先准备一次：

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(?, ?, ?, ?, ?, ?)");

然后像使用prep一样使用它们，否则：

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

使用std::unique_ptr使您不必自己调用delete。您也应该在其他资源中采用它，因为您的代码delete st, re删除了st，但保留了re。