Results 1 to 5 of 5

Thread: Sql questions

  1. #1
    Join Date
    Dec 2006
    Posts
    10
    Qt products
    Qt4
    Platforms
    Windows

    Default Sql questions

    I have some QSql related questions that I can't seem to find answered anywhere. Perhaps someone here can help out.

    1) My database gets updated externally at a very fast rate. What is the proper/best way to update any QSql*Models when their table is changed externally? Since SQLite is embedded I was kinda hoping it would happen automatically but that doesn't seem to be the case. I guess I could just have a timer and manually loop over the sql table and update the model rows/colums? Or is there a more elegant way using some database triggers or something? Calling select() repeatedly will update the data but any model selections & editing gets wiped out. Does MySql have any features that might help in this area?

    2) The QSqlRelation* classes say they support foreign keys. If a database table has a column specified as a foreign key will QSqlRelationalTableModel "know" it automatically? Or will I still have to specifically use it's setRelation() method on all the columns manually? (I know SQLite doesn't enforce them but you can still specify them.)

    3) Did I do something wrong or does SQLite's ":memory:" database not support multiple threads? When I switched it to using a file, updates and selects from multiple threads began working without problems. (Yes, I made unique db connections for each thread)

    Thanks in advance,

    RobF

  2. #2
    Join Date
    Jan 2006
    Location
    Warsaw, Poland
    Posts
    33,359
    Thanks
    3
    Thanked 5,015 Times in 4,792 Posts
    Qt products
    Qt3 Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows Android Maemo/MeeGo
    Wiki edits
    10

    Default Re: Sql questions

    Quote Originally Posted by Nb2Qt View Post
    1) My database gets updated externally at a very fast rate. What is the proper/best way to update any QSql*Models when their table is changed externally? Since SQLite is embedded I was kinda hoping it would happen automatically but that doesn't seem to be the case. I guess I could just have a timer and manually loop over the sql table and update the model rows/colums? Or is there a more elegant way using some database triggers or something? Calling select() repeatedly will update the data but any model selections & editing gets wiped out. Does MySql have any features that might help in this area?
    With these models you are limited to the sql language/protocol and it doesn't provide any means of checking for changes since a defined moment in the past, therefore the only certain way of getting all the changes is to refetch all data. Unfortunately this is connected to losing all data related to selections, etc. You can only store the selection before you reinitialise the model (by calling "select") and then restore the selection afterwards.

    2) The QSqlRelation* classes say they support foreign keys. If a database table has a column specified as a foreign key will QSqlRelationalTableModel "know" it automatically? Or will I still have to specifically use it's setRelation() method on all the columns manually? (I know SQLite doesn't enforce them but you can still specify them.)
    You can only substitute a column which is a foreign key with some other column from the table which the key belongs to and you have to explicitely name the columns (there is an example how to use it in QSqlRelationalTableModel.

    3) Did I do something wrong or does SQLite's ":memory:" database not support multiple threads? When I switched it to using a file, updates and selects from multiple threads began working without problems. (Yes, I made unique db connections for each thread)
    If you use ":memory:" then the database is created only for that single connection, therefore if you create another connection (i.e. in a different thread) again passing ":memory:" as the database name, a new database will be created for the other connection - they won't share data.

  3. #3
    Join Date
    May 2006
    Location
    Germany
    Posts
    108
    Thanks
    2
    Thanked 14 Times in 12 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: Sql questions

    Regarding your first question: At work (even though it's a plain vanilla win32api-app) we're using a timer to refresh our models and a locking mechanism to prevent two users from editing the same row. Other dbms (like mysql or pgsql) won't help you much there except for providing locking mechanisms you can use directly instead of having to invent your own.

  4. #4
    Join Date
    Mar 2006
    Posts
    140
    Thanks
    8
    Thanked 4 Times in 3 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: Sql questions

    How about writing a little service / daemon or even just another thread that monitors connections on the db port (Does SQLite use a port). Whenever a connection is established, then signal your main program to do an sql refresh.
    You might need to consider waiting a bit, or repolling until you know the connection is finished before signalling.

    Just a thought.

  5. #5
    Join Date
    Dec 2006
    Posts
    10
    Qt products
    Qt4
    Platforms
    Windows

    Default Re: Sql questions

    The other problem I'm having is that calling select() via a timer is not updating the contents of the table. It appears only to update if the #row or #columns has changed in the database. Is there a way of forcing QSqlQueryModel (and derivatives) to update the table *contents* without subclassing?

    Thanks again,

    RobF

Similar Threads

  1. SQL problem
    By nimmyj in forum Qt Programming
    Replies: 4
    Last Post: 25th December 2006, 19:43
  2. Replies: 1
    Last Post: 4th October 2006, 17:05
  3. Connection with MS SQL
    By manish_pesit in forum Qt Programming
    Replies: 4
    Last Post: 13th September 2006, 08:47
  4. 2 Questions about layouts
    By SkripT in forum Qt Programming
    Replies: 1
    Last Post: 26th February 2006, 14:54
  5. Qt related questions and thoughts about getting job
    By AlexKiriukha in forum General Discussion
    Replies: 4
    Last Post: 26th January 2006, 13:25

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.