Results 1 to 2 of 2

Thread: Probelm with QSqlQuery

  1. #1
    Join Date
    Jun 2009
    Posts
    2
    Qt products
    Qt4
    Platforms
    MacOS X

    Default Probelm with QSqlQuery

    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

  2. #2
    Join Date
    Apr 2009
    Posts
    206
    Thanks
    34
    Thanked 2 Times in 2 Posts

    Default Re: Probelm with QSqlQuery

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

Similar Threads

  1. Replies: 1
    Last Post: 20th May 2009, 21:36
  2. Replies: 9
    Last Post: 6th May 2009, 11:09
  3. QSqlQuery error
    By skuda in forum Qt Programming
    Replies: 2
    Last Post: 2nd November 2007, 09:43
  4. How can I get the number of columns in QSqlQuery.
    By fengtian.we in forum Qt Programming
    Replies: 1
    Last Post: 21st May 2007, 12:04
  5. Problems with QSqlQuery update
    By whoops.slo in forum Qt Programming
    Replies: 4
    Last Post: 28th August 2006, 08:17

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Digia, Qt and their respective logos are trademarks of Digia Plc in Finland and/or other countries worldwide.