Probelm with QSqlQuery

[AndreaCe]

Hy, i'm using QSqlDatabase with SQLite...
I created 2 tables:

CREATE TABLE COUNTRY(
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
latitude NUMBER NOT NULL,
longitude NUMBER NOT NULL,
CONSTRAINT unique_lat_long UNIQUE (latitude,longitude)
);

CREATE TABLE REGION(
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
latitude NUMBER NOT NULL,
longitude NUMBER NOT NULL,
country_id INTEGER NOT NULL,
CONSTRAINT fk_COUNTRY_id FOREIGN KEY (country_id) REFERENCES COUNTRY (id) ON DELETE CASCADE,
CONSTRAINT unique_lat_long UNIQUE (latitude,longitude)
);

For fill these two table i'll read the data from two files:
- File_Country = country_name,latitude,longitude (ex: USA,7.5231456,8.75213)
- File_Region = regione_name,latitude,longitude,country_name (ex: Washington,8.512369,9.874560,USA)

Here the code for fill tables:
COUNTRY:

void country(){
QFile file(QString("country"));
if (!file.open(QFile::ReadOnly | QFile::Text)) {
// DEBUG +++++++++++++++++++++++++++++++++++++++++++
cout << "Can not open file: continent." << endl;
// +++++++++++++++++++++++++++++++++++++++++++++++++
return;
}
QTextStream in(&file);
QVector<QStringList> lines;
while (!in.atEnd()) {
QString line = in.readLine();
if(!line.isNull() && line.length() > 0)
lines.append(line.split(";"));
}
QSqlQuery query;
for (int i=0; i<lines.size(); i++) {
query.prepare("INSERT INTO COUNTRY (id,name,lat,lon) VALUES (null, ?, ?, ?)");
query.addBindValue(lines[i].at(0));
query.addBindValue(lines[i].at(1));
query.addBindValue(lines[i].at(2));
if(query.exec()){
// DEBUG +++++++++++++++++++++++++++++++++++++++++++
cout << "Query OK!" << endl;
// +++++++++++++++++++++++++++++++++++++++++++++++++
}else {
// DEBUG +++++++++++++++++++++++++++++++++++++++++++
cout << "Query Failed! Error = " << query.lastError().text().toStdString() << endl;
// +++++++++++++++++++++++++++++++++++++++++++++++++
}
query.finish();
}
// DEBUG +++++++++++++++++++++++++++++++++++++++++++
cout << "COUNTRY FINISH! -------------------" << endl;
// +++++++++++++++++++++++++++++++++++++++++++++++++
file.close();
}

REGION:

void region(){
QFile file(QString("regioon"));
if (!file.open(QFile::ReadOnly | QFile::Text)) {
// DEBUG +++++++++++++++++++++++++++++++++++++++++++
cout << "Can not open file: country." << endl;
// +++++++++++++++++++++++++++++++++++++++++++++++++
return;
}
QTextStream in(&file);
QVector<QStringList> lines;
while (!in.atEnd()) {
QString line = in.readLine();
if(!line.isNull() && line.length() > 0)
lines.append(line.split(";"));
}
QSqlQuery query1;
QSqlQuery query2;
int country_id;
for (int i=0; i<lines.size(); i++) {
query1.prepare("SELECT id FROM COUNTRY WHERE COUNTRY.name = '" + lines[i].at(3) + "'");
if(query1.exec()){
// DEBUG +++++++++++++++++++++++++++++++++++++++++++
cout << "Query (1) OK!" << endl;
// +++++++++++++++++++++++++++++++++++++++++++++++++
}else {
// DEBUG +++++++++++++++++++++++++++++++++++++++++++
cout << "Query (1) Failed! Error = " << query1.lastError().text().toStdString() << endl;
// +++++++++++++++++++++++++++++++++++++++++++++++++
}
// ================================================== ====
// MOVE ON THE FIRST RECORD
// ================================================== ====
query1.first();
// ================================================== ====
// Check If The Query Is Active
// (If The Query Is Positioned On A Valid Record)
if(query1.isValid()){
// If The Number Of Rows Affected Is Different From 1
// Set FK To NULL Value
if(query1.numRowsAffected() != 1){
query2.prepare("INSERT INTO REGION (id,name,lat,lon,country_id) VALUES (null, ?, ?, ?, null)");
query2.addBindValue(lines[i].at(0));
query2.addBindValue(lines[i].at(1));
query2.addBindValue(lines[i].at(2));
if(query2.exec()){
// DEBUG +++++++++++++++++++++++++++++++++++++++++++
cout << "Query (2.1) OK!" << endl;
// +++++++++++++++++++++++++++++++++++++++++++++++++
}else {
// DEBUG +++++++++++++++++++++++++++++++++++++++++++
cout << "Query (2.1) Failed! Error = " << query2.lastError().text().toStdString() << endl;
// +++++++++++++++++++++++++++++++++++++++++++++++++
}
}else {
// Else Get The Id Result And Set FK
country_id = query1.value(0).toInt();
query2.prepare("INSERT INTO REGION (id,name,lat,lon, country_id) VALUES (null, ?, ?, ?, ?)");
query2.addBindValue(lines[i].at(0));
query2.addBindValue(lines[i].at(1));
query2.addBindValue(lines[i].at(2));
query2.addBindValue(country_id);
if(query2.exec()){
// DEBUG +++++++++++++++++++++++++++++++++++++++++++
cout << "Query (2.2) OK!" << endl;
// +++++++++++++++++++++++++++++++++++++++++++++++++
}else {
// DEBUG +++++++++++++++++++++++++++++++++++++++++++
cout << "Query (2.2) Failed! Error = " << query2.lastError().text().toStdString() << endl;
// +++++++++++++++++++++++++++++++++++++++++++++++++
}
}
}else {
// DEBUG +++++++++++++++++++++++++++++++++++++++++++
cout << "Query NOT Positioned On A Valid Record" << endl;
// +++++++++++++++++++++++++++++++++++++++++++++++++
// If The Qurey Is Not Positioned On A Valid Record
// Set FK To NULL Value
query2.prepare("INSERT INTO REGION (id,name,lat,lon, country_id) VALUES (null, ?, ?, ?, null)");
query2.addBindValue(lines[i].at(0));
query2.addBindValue(lines[i].at(1));
query2.addBindValue(lines[i].at(2));
if(query2.exec()){
// DEBUG +++++++++++++++++++++++++++++++++++++++++++
cout << "Query (2.3) OK!" << endl;
// +++++++++++++++++++++++++++++++++++++++++++++++++
}else {
// DEBUG +++++++++++++++++++++++++++++++++++++++++++
cout << "Query (2.3) Failed! Error = " << query2.lastError().text().toStdString() << endl;
// +++++++++++++++++++++++++++++++++++++++++++++++++
}
}
query1.finish();
query2.finish();
}
// DEBUG +++++++++++++++++++++++++++++++++++++++++++
cout << "REGION FINISH! -------------------" << endl;
// +++++++++++++++++++++++++++++++++++++++++++++++++
file.close();
}

The Problem is that when, in function region(), i try to INSERT a REGION that is already present in the REGION TABLE (latitude & longitude UNIQUE), the query (query2) fail (and that is correct), but the next SELECT query (query1) doesn't work. The number of rows affected from SELECT query (query1) is 0.

For example if I try to enter a region in the sixth cycle, but this REGION is already present in REGION TABLE (in particular, the coordinates of this region are already present -> UNIQUE on longitue, latitude), at seventh cycle the number of rows affected from SELECT query is 0 (but the SELECT query is correct)!!!! Then at eighth cycle everything works!!! WHY?????

That is really strange. I don't know why?

Someone has any idea?

P.S
I am really sorry for my english... i don't speak very well english

[codeman]

Please use code tags my eyes burn.........