Results 1 to 13 of 13

Thread: SQLITE - UPDATE query problem

  1. #1
    Join Date
    Jul 2010
    Location
    Poland
    Posts
    184
    Thanks
    70
    Thanked 7 Times in 6 Posts
    Qt products
    Qt4 Qt/Embedded
    Platforms
    Unix/X11 Windows

    Default SQLITE - UPDATE query problem

    Hello!

    I've got piece of code:

    Qt Code:
    1. QString zapytanie;
    2. QTextStream out(stdout);
    3. bool ok;
    4.  
    5. zapytanie="update sensors set localization='";
    6. zapytanie.append(ui->localizationLine->text());
    7. zapytanie.append("'");
    8. zapytanie.append(" where devices_id='");
    9. zapytanie.append(QString::number(IDLicznika[ui->dostepneLiczniki->currentIndex()]));
    10. zapytanie.append("'");
    11.  
    12. QSqlDatabase bdb = QSqlDatabase::addDatabase("QSQLITE");
    13.  
    14. bdb.setDatabaseName("mybase.db");
    15. ok = bdb.open();
    16.  
    17. if (ok)
    18. {
    19. out << endl << "Otworzylem baze!" << endl ;
    20. } else {
    21. out << "Nie udalo sie otworzyc bazy!" << endl;
    22. }
    23.  
    24. QSqlQueryModel *queryModel = new QSqlQueryModel;
    25. queryModel->setQuery(zapytanie, bdb);
    26.  
    27. bdb.close();
    28.  
    29. out << zapytanie << endl;
    To copy to clipboard, switch view to plain text mode 

    and it doesn't work! Any idea why? Every tabel/colum exists. Last line show in console correct query.

    thanks in advance
    best regards
    Tomasz

  2. #2
    Join Date
    Jan 2006
    Location
    Germany
    Posts
    4,380
    Thanks
    19
    Thanked 1,005 Times in 913 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows Symbian S60
    Wiki edits
    5

    Default Re: SQLITE - UPDATE query problem

    First check if you sql is correct and you might want to use QSqlQuery::prepare() since it is saver. Also a QSqlQueryModel is a absolutely overkill for your task. Just use a simple QSqlQuery and execute it.

  3. #3
    Join Date
    Jul 2010
    Location
    Poland
    Posts
    184
    Thanks
    70
    Thanked 7 Times in 6 Posts
    Qt products
    Qt4 Qt/Embedded
    Platforms
    Unix/X11 Windows

    Default Re: SQLITE - UPDATE query problem

    Ok. I've made QSqlQuery:

    Qt Code:
    1. QSqlQuery query;
    2. query.prepare("update sensors set localization='abc' where devices_id='1'");
    3. query.exec();
    To copy to clipboard, switch view to plain text mode 

    and it doesn't work too. I've executed that query in command line and it works fine. Any idea? I always close database in all functions I use in my application, bu I get warnings when I use database (maybe it's important):

    Qt Code:
    1. QSqlDatabasePrivate::addDatabase: duplicate connection name 'qt_sql_default_connection', old connection removed.
    To copy to clipboard, switch view to plain text mode 

    When I do SELECT instead UPDATE it works fine too - I've checked execution with 'ok' bool variable.

    thanks in advance
    best regards
    Tomasz
    Last edited by Tomasz; 24th August 2010 at 15:56.

  4. #4
    Join Date
    Jan 2006
    Location
    Germany
    Posts
    4,380
    Thanks
    19
    Thanked 1,005 Times in 913 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows Symbian S60
    Wiki edits
    5

    Default Re: SQLITE - UPDATE query problem

    There is no need to close them. On startup open the database one time and everywhere you need it get the connection via the static QSqlDatabase::database(). If you wish give a name to the connection so you can have connections the different databases.

    And what does query.lastError() return after the unsuccessful execution?

  5. #5
    Join Date
    Jul 2010
    Location
    Poland
    Posts
    184
    Thanks
    70
    Thanked 7 Times in 6 Posts
    Qt products
    Qt4 Qt/Embedded
    Platforms
    Unix/X11 Windows

    Default Re: SQLITE - UPDATE query problem

    lastError() returns:

    Qt Code:
    1. attempt to write a readonly database Unable to fetch row
    To copy to clipboard, switch view to plain text mode 

    but in command line everything works fine.

  6. #6
    Join Date
    Jan 2006
    Location
    Germany
    Posts
    4,380
    Thanks
    19
    Thanked 1,005 Times in 913 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows Symbian S60
    Wiki edits
    5

    Default Re: SQLITE - UPDATE query problem

    maybe you set a write lock somewhere or initiate it double times or your application has not the right to write to the file or or or.

  7. The following user says thank you to Lykurg for this useful post:

    Tomasz (25th August 2010)

  8. #7
    Join Date
    Jul 2010
    Location
    Poland
    Posts
    184
    Thanks
    70
    Thanked 7 Times in 6 Posts
    Qt products
    Qt4 Qt/Embedded
    Platforms
    Unix/X11 Windows

    Default Re: SQLITE - UPDATE query problem

    My stupid mistake - database was readonly for all users except root. I've changed permissions.

    best regards
    Tomasz

  9. #8
    Join Date
    Jul 2010
    Location
    Poland
    Posts
    184
    Thanks
    70
    Thanked 7 Times in 6 Posts
    Qt products
    Qt4 Qt/Embedded
    Platforms
    Unix/X11 Windows

    Default Re: SQLITE - UPDATE query problem

    Quote Originally Posted by Lykurg View Post
    On startup open the database one time and everywhere you need it get the connection via the static QSqlDatabase::database(). If you wish give a name to the connection so you can have connections the different databases.
    What do You mean? I always do:

    Qt Code:
    1. QSqlDatabase bdb = QSqlDatabase::addDatabase("QSQLITE");
    2.  
    3. bdb.setDatabaseName(_PATH_TO_MY_DB_);
    4. ok = bdb.open();
    To copy to clipboard, switch view to plain text mode 

    should I bdb.open() at the begining? Won't this lock up my database?

    thanks in advance
    best regards
    Tomasz

  10. #9
    Join Date
    Jan 2006
    Location
    Germany
    Posts
    4,380
    Thanks
    19
    Thanked 1,005 Times in 913 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows Symbian S60
    Wiki edits
    5

    Default Re: SQLITE - UPDATE query problem

    Just as a small demonstration:
    Qt Code:
    1. main() {
    2. QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE", "MySettings");
    3. db.setDatabaseName("settings.db");
    4. db.open(); // assuming it opens
    5.  
    6. db = QSqlDatabase::addDatabase("QSQLITE", "MyValues");
    7. db.setDatabaseName("values.db");
    8. db.open(); // assuming it opens
    9.  
    10.  
    11. MainWindow w;
    12. w.show();
    13.  
    14. //...
    15. }
    16.  
    17. void Foo::bar() {
    18. // access your settings db
    19. QSqlDatabase db = QSqlDatabase::database("MySettings");
    20. QSqlQuery q(db);
    21. //...
    22. }
    23.  
    24. void Foo::barbar() {
    25. // access your values db
    26. QSqlDatabase db = QSqlDatabase::database("MyValues");
    27. QSqlQuery q(db);
    28. //...
    29. }
    To copy to clipboard, switch view to plain text mode 
    No open() nor anything else.

  11. The following user says thank you to Lykurg for this useful post:

    Tomasz (27th August 2010)

  12. #10
    Join Date
    Jul 2010
    Location
    Poland
    Posts
    184
    Thanks
    70
    Thanked 7 Times in 6 Posts
    Qt products
    Qt4 Qt/Embedded
    Platforms
    Unix/X11 Windows

    Default Re: SQLITE - UPDATE query problem

    I've got UPDATE problem again. This time I'm doing in one of my slots something like this:

    Qt Code:
    1. ok = bdb.open();
    2.  
    3. if(ok)
    4. {
    5. zapytanie.clear();
    6. zapytanie="UPDATE mesg SET cmp='1'";
    7. zapytanie.append(" WHERE id='");
    8. zapytanie.append(QString::number(rec.value(0).toInt()));
    9. zapytanie.append("'");
    10.  
    11. query.clear();
    12. query.prepare(zapytanie);
    13. ok = query.exec();
    14. query.clear();
    15.  
    16. bdb.close();
    17. }
    To copy to clipboard, switch view to plain text mode 

    At the beginning of the program I've got:

    Qt Code:
    1. databaseName="mybase.db";
    2. bdb = QSqlDatabase::addDatabase("QSQLITE");
    3. bdb.setDatabaseName(databaseName);
    To copy to clipboard, switch view to plain text mode 

    If I give a name to database:
    Qt Code:
    1. bdb = QSqlDatabase::addDatabase("QSQLITE","databaseName");
    To copy to clipboard, switch view to plain text mode 

    I can read from database but the update code above gives:
    Qt Code:
    1. QSqlQuery::prepare: database not open
    To copy to clipboard, switch view to plain text mode 

    When I remove name everything is OK. What's wrong? I know You've given me code and said that I can use It without open() but I need to close base after use because other programs uses the same database. And I want to give that database name.

    thanks in advance
    best regards
    Tomasz

  13. #11
    Join Date
    Jan 2006
    Location
    Germany
    Posts
    4,380
    Thanks
    19
    Thanked 1,005 Times in 913 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows Symbian S60
    Wiki edits
    5

    Default Re: SQLITE - UPDATE query problem

    with your posted code examples it is hard to say. One general advice: don't store queries as member variables. Declare them always on need in place.

    You likely put a wrong database to query.

  14. The following user says thank you to Lykurg for this useful post:

    Tomasz (5th September 2010)

  15. #12
    Join Date
    Jul 2010
    Location
    Poland
    Posts
    184
    Thanks
    70
    Thanked 7 Times in 6 Posts
    Qt products
    Qt4 Qt/Embedded
    Platforms
    Unix/X11 Windows

    Default Re: SQLITE - UPDATE query problem

    Quote Originally Posted by Lykurg View Post
    Declare them always on need in place.
    You likely put a wrong database to query.
    You are right. I declared it in place where I needed it and gived as an argument my database and it works. Now I'm just curious about something. I've wrote something like this, just to see what will happen (function that should read one thing from database and then close it):

    Qt Code:
    1. bool MainWindow::readFromDB()
    2. {
    3. bool ok;
    4. QTextStream out(stdout);
    5.  
    6. QSqlQueryModel queryModel;
    7.  
    8. bdb = QSqlDatabase::addDatabase("QSQLITE","vb");
    9. bdb.setDatabaseName(databaseName);
    10.  
    11. ok = bdb.open();
    12.  
    13. if (ok)
    14. {
    15. queryModel.clear();
    16. queryModel.setQuery("SELECT * FROM table WHERE name='var1'", bdb);
    17.  
    18. rec = queryModel.record(0);
    19. notificationInterval = (rec.value(1).toInt())*1000;
    20.  
    21. rec.clear();
    22. queryModel.clear();
    23. bdb.close();
    24.  
    25. } else {
    26. out << "Nie udalo sie otworzyc bazy!" << endl;
    27. }
    28.  
    29. QSqlDatabase::removeDatabase("vb");
    30.  
    31. return ok;
    32. }
    To copy to clipboard, switch view to plain text mode 

    And it does what it should but gives in console:

    Qt Code:
    1. QSqlDatabasePrivate::removeDatabase: connection 'vb' is still in use, all queries will cease to work.
    To copy to clipboard, switch view to plain text mode 

    Is is normal behavior?

    thanks in advance
    best regards
    Tomasz

  16. #13
    Join Date
    Jan 2006
    Location
    Germany
    Posts
    4,380
    Thanks
    19
    Thanked 1,005 Times in 913 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows Symbian S60
    Wiki edits
    5

    Default Re: SQLITE - UPDATE query problem

    Quote Originally Posted by Tomasz View Post
    Is is normal behavior?
    Yes it is. It is also described in the docs. And once again: Do not use QSqlQueryModel for such a simple task, just use QSqlQuery!

    Qt Code:
    1. // WRONG
    2. QSqlDatabase db = QSqlDatabase::database("sales");
    3. QSqlQuery query("SELECT NAME, DOB FROM EMPLOYEES", db);
    4. QSqlDatabase::removeDatabase("sales"); // will output a warning
    5.  
    6. // "db" is now a dangling invalid database connection,
    7. // "query" contains an invalid result set
    To copy to clipboard, switch view to plain text mode 

    The correct way to do it:

    Qt Code:
    1. {
    2. QSqlDatabase db = QSqlDatabase::database("sales");
    3. QSqlQuery query("SELECT NAME, DOB FROM EMPLOYEES", db);
    4. }
    5. // Both "db" and "query" are destroyed because they are out of scope
    6. QSqlDatabase::removeDatabase("sales"); // correct
    To copy to clipboard, switch view to plain text mode 

  17. The following user says thank you to Lykurg for this useful post:

    Tomasz (5th September 2010)

Similar Threads

  1. Replies: 0
    Last Post: 21st April 2010, 17:25
  2. SQL slow query - table update
    By lasher in forum Newbie
    Replies: 4
    Last Post: 22nd October 2009, 00:12
  3. Update query
    By apffal in forum Qt Programming
    Replies: 4
    Last Post: 7th August 2009, 17:57
  4. Problem with A SQLite Query
    By maveric in forum Qt Programming
    Replies: 1
    Last Post: 24th June 2008, 12:15
  5. [QT4][SQLITE] Database and query
    By agent007se in forum Newbie
    Replies: 10
    Last Post: 12th July 2006, 23:16

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.