Results 1 to 4 of 4

Thread: Using Qt with SQLite

  1. #1
    Join Date
    Jan 2011
    Posts
    32
    Thanks
    6
    Qt products
    Qt4
    Platforms
    Unix/X11

    Default Using Qt with SQLite

    Okay, folks, I've been busting my head for about two weeks with this one. I am now reaching out for help.

    The relevant project hits about 7000 lines of code at this point, so I won't post it all, but I think this particular form should paint the basic picture.

    The name of the form is NewVendorForm.

    The header:
    Qt Code:
    1. #ifndef NEWVENDORFORM_H
    2. #define NEWVENDORFORM_H
    3.  
    4. #include <QDialog>
    5. #include <QSqlTableModel>
    6. #include <QSqlRelationalTableModel>
    7. #include <QFontMetrics>
    8. #include <QSqlRecord>
    9. #include "dblineedit.h"
    10. #include "dbplaintextedit.h"
    11.  
    12. namespace Ui {
    13. class NewVendorForm;
    14. }
    15.  
    16. class NewVendorForm : public QDialog
    17. {
    18. Q_OBJECT
    19.  
    20. public:
    21. explicit NewVendorForm(int id, QWidget *parent = 0);
    22. ~NewVendorForm();
    23.  
    24. private:
    25. Ui::NewVendorForm *ui;
    26. DBLineEdit *le;
    27. int rec_id;
    28. DBPlainTextEdit *pte;
    29.  
    30. private slots:
    31. void slot_new_phone(QPoint);
    32. };
    33.  
    34. #endif // NEWVENDORFORM_H
    To copy to clipboard, switch view to plain text mode 

    The .cpp file:

    Qt Code:
    1. #include "newvendorform.h"
    2. #include "ui_newvendorform.h"
    3. #include "sqliteinpstring.h"
    4. #include <QSqlError>
    5. #include <QSqlField>
    6. #include <QDebug>
    7.  
    8. NewVendorForm::NewVendorForm(int id, QWidget *parent) :
    9. QDialog(parent),
    10. ui(new Ui::NewVendorForm)
    11. {
    12. rec_id = id;
    13. ui->setupUi(this);
    14. ui->tableView_phone->resizeRowsToContents();
    15. ui->tableView_phone->setEditTriggers(QTableView::AllEditTriggers);
    16. le = new DBLineEdit(rec_id, "vendor", "name", this);
    17. pte = new DBPlainTextEdit(rec_id, "vendor", "address", this);
    18. le->move(160, 35);
    19. le->setFixedWidth(130);
    20. pte->move(10, 155);
    21. pte->setFixedWidth(140);
    22. pte->setFixedHeight(140);
    23. table_mod = new QSqlRelationalTableModel(this);
    24. table_mod->setTable("phone_number");
    25. table_mod->setFilter("vendor_id == " + QString::number(rec_id));
    26. table_mod->setEditStrategy(QSqlRelationalTableModel::OnFieldChange);
    27. table_mod->select();
    28. table_mod->setHeaderData(3, Qt::Horizontal, "Type");
    29. table_mod->setHeaderData(4, Qt::Horizontal, "Country Code");
    30. table_mod->setHeaderData(5, Qt::Horizontal, "Area Code");
    31. table_mod->setHeaderData(6, Qt::Horizontal, "Number");
    32. table_mod->setRelation(3, QSqlRelation("phone_type", "id", "term"));
    33. ui->tableView_phone->setModel(table_mod);
    34. ui->tableView_phone->update();
    35. connect(ui->tableView_phone, SIGNAL(customContextMenuRequested(QPoint)),
    36. this, SLOT(slot_new_phone(QPoint)));
    37. ui->tableView_phone->setColumnHidden(0, true);
    38. ui->tableView_phone->setColumnHidden(1, true);
    39. ui->tableView_phone->setColumnHidden(2, true);
    40. ui->tableView_phone->resizeColumnsToContents();
    41. ui->tableView_phone->resizeRowsToContents();
    42. }
    43.  
    44. void NewVendorForm::slot_new_phone(QPoint) {
    45. qDebug() << "slot_new_phone";
    46. QSqlRecord tmp_record;
    47. tmp_record = table_mod->record();
    48. qDebug() << tmp_record;
    49. tmp_record.setGenerated(0, false);
    50. tmp_record.setGenerated(1, false);
    51. tmp_record.setGenerated(3, false);
    52. tmp_record.setGenerated(4, false);
    53. tmp_record.setGenerated(5, false);
    54. tmp_record.setValue("vendor_id", rec_id);
    55. tmp_record.setValue("number", "555-5555");
    56. qDebug() << tmp_record;
    57. table_mod->select();
    58. qDebug() << "Insert was successful " << table_mod->insertRecord(-1, tmp_record); [COLOR="red"]// Here is my current headache.[/COLOR]
    59. qDebug() << table_mod->query().lastError().text();
    60. table_mod->select();
    61. ui->tableView_phone->update();
    62. ui->tableView_phone->resizeRowsToContents();
    63. ui->tableView_phone->resizeColumnsToContents();
    64. }
    65.  
    66. NewVendorForm::~NewVendorForm() {
    67. le->destroying();
    68. pte->destroying();
    69. delete le;
    70. SQLiteInpString fix_str;
    71. QSqlQuery query;
    72. QString qry_str;
    73. qry_str = "SELECT address FROM vendor WHERE id == " + QString::number(rec_id);
    74. query.prepare(qry_str);
    75. query.exec();
    76. query.next();
    77. fix_str = query.value(0).toString();
    78. fix_str = fix_str.replace("\n\r", ", ");
    79. fix_str = fix_str.replace("\n", ", ");
    80. query.clear();
    81. qry_str = "UPDATE vendor SET address = '" + fix_str;
    82. qry_str += "' WHERE id == " + QString::number(rec_id);
    83. query.prepare(qry_str);
    84. query.exec();
    85. query.next();
    86. delete pte;
    87. delete ui;
    88. }
    To copy to clipboard, switch view to plain text mode 

    I am running connected to a small SQLite database connected with the QSQLITE driver.

    Firstly, if I run the same code and switch back over to QSqlTableModel instead of QSqlRelationalTableModel the code works. What I am trying to accomplish is a (basically) real-time view into the database where the user can simply point, click, double-click and edit his way through to create the records he/she wants (within the constraints of the DB schema, of course.)

    For the sake of discussion, here is the DDL to create the relevant table in the database:

    Qt Code:
    1. CREATE TABLE [phone_number] (
    2. [id] INTEGER NOT NULL ON CONFLICT FAIL PRIMARY KEY ON CONFLICT FAIL AUTOINCREMENT,
    3. [client_id] INTEGER CONSTRAINT [client_id] REFERENCES [client_info]([id]) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
    4. [vendor_id] INTEGER CONSTRAINT [vendor_id] REFERENCES [vendor]([id]) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
    5. [type] INTEGER CONSTRAINT [type] REFERENCES [phone_type]([id]) DEFERRABLE INITIALLY DEFERRED,
    6. [country_code] TEXT NOT NULL ON CONFLICT REPLACE DEFAULT ('001'),
    7. [area_code] TEXT NOT NULL ON CONFLICT REPLACE DEFAULT ('828'),
    8. [number] TEXT NOT NULL ON CONFLICT FAIL,
    9. CONSTRAINT [has_owner] CHECK(client_id IS NOT NULL OR vendor_id IS NOT NULL));
    To copy to clipboard, switch view to plain text mode 

    Now, I know that from an SQLite command line, I can enter the following:

    Qt Code:
    1. INSERT INTO phone_number (vendor_id, number) VALUES (40, '555-5555')
    To copy to clipboard, switch view to plain text mode 

    and it works.

    So, is there some way to see the specific SQL command that QSqlRelationalTableModel is passing when I do an insertRecord()?

    That is my first question. Now I have one other.

    I am using the latest Qt under Wine on my Linux box. (I know, there are other ways to do this, but this is the way that works for me.) I have noticed lately that the Qt debugger (Python gdb?) crashes when I try to do any debugging. That doesn't annoy me so much. I just put a bunch of qDebug()'s in the code and I can see what is going on.

    I am just curious if others have observed this behavior and might have any clues/configuration tips, etc.

    Thanks for the time,

    Michael

  2. #2
    Join Date
    Mar 2009
    Location
    Brisbane, Australia
    Posts
    7,552
    Thanks
    13
    Thanked 1,579 Times in 1,508 Posts
    Qt products
    Qt4 Qt5
    Platforms
    Unix/X11 Windows
    Wiki edits
    17

    Default Re: Using Qt with SQLite

    I am using the latest Qt under Wine on my Linux box. (I know, there are other ways to do this, but this is the way that works for me.) I have noticed lately that the Qt debugger (Python gdb?) crashes when I try to do any debugging. That doesn't annoy me so much. I just put a bunch of qDebug()'s in the code and I can see what is going on.
    Why on Earth would you choose to burden yourself with a whole raft of unrelated things that can go wrong under Wine when you have a perfectly good native Linux option?

    I appreciate that you have told us that your insert doesn't work. You haven't told us what the debug output at line 59 is: often these things are useful.

    At line 25, 73, and 82 of the cpp file you use "==" in a context expecting an SQL fragment. SQLite will accept "==" but the standard equality test operator in SQL is "=" so if you ever intend to use the code against a different backend you might like to change these.

  3. #3
    Join Date
    Jan 2011
    Posts
    32
    Thanks
    6
    Qt products
    Qt4
    Platforms
    Unix/X11

    Default Re: Using Qt with SQLite

    The debug output at line 59 is " "


    Added after 24 minutes:


    I have now subclassed QSqlRelationalTableModel, which I found gives me access to selectStatement(). This tells me the syntax of the select statement used to create the model/view.

    The relevant change in my code from the .cpp file:

    Qt Code:
    1. void NewVendorForm::slot_new_phone(QPoint) {
    2. qDebug() << "slot_new_phone";
    3. QSqlRecord tmp_record;
    4. tmp_record = table_mod->record();
    5. qDebug() << tmp_record;
    6. tmp_record.setGenerated(0, false);
    7. tmp_record.setGenerated(1, false);
    8. tmp_record.setGenerated(3, false);
    9. tmp_record.setGenerated(4, false);
    10. tmp_record.setGenerated(5, false);
    11. tmp_record.setValue("vendor_id", rec_id);
    12. tmp_record.setValue("number", "555-5555");
    13. qDebug() << tmp_record;
    14. table_mod->select();
    15. qDebug() << table_mod->get_selectStatement();
    16. qDebug() << "Insert was successful " << table_mod->insertRecord(-1, tmp_record);
    17. qDebug() << table_mod->get_selectStatement();
    18. qDebug() << table_mod->query().lastError().text();
    19. table_mod->select();
    20. ui->tableView_phone->update();
    21. ui->tableView_phone->resizeRowsToContents();
    22. ui->tableView_phone->resizeColumnsToContents();
    23. }
    To copy to clipboard, switch view to plain text mode 

    And here is the complete debugging output from running through this code.

    slot_new_phone
    QSqlRecord( 7 )
    " 0:" QSqlField("id", int, generated: yes) ""
    " 1:" QSqlField("client_id", int, generated: yes) ""
    " 2:" QSqlField("vendor_id", int, generated: yes) ""
    " 3:" QSqlField("type", int, generated: yes) ""
    " 4:" QSqlField("country_code", QString, generated: yes) ""
    " 5:" QSqlField("area_code", QString, generated: yes) ""
    " 6:" QSqlField("number", QString, generated: yes) ""
    QSqlRecord( 7 )
    " 0:" QSqlField("id", int, generated: no) ""
    " 1:" QSqlField("client_id", int, generated: no) ""
    " 2:" QSqlField("vendor_id", int, generated: yes) "47"
    " 3:" QSqlField("type", int, generated: no) ""
    " 4:" QSqlField("country_code", QString, generated: no) ""
    " 5:" QSqlField("area_code", QString, generated: no) ""
    " 6:" QSqlField("number", QString, generated: yes) "555-5555"
    "SELECT phone_number."id", phone_number."client_id", phone_number."vendor_id", relTblAl_3.term, phone_number."country_code", phone_number."area_code", phone_number."number" FROM phone_number, phone_type relTblAl_3 WHERE (phone_number."type" = relTblAl_3.id) AND (vendor_id == 47) "
    Insert was successful false
    "SELECT phone_number."id", phone_number."client_id", phone_number."vendor_id", relTblAl_3.term, phone_number."country_code", phone_number."area_code", phone_number."number" FROM phone_number, phone_type relTblAl_3 WHERE (phone_number."type" = relTblAl_3.id) AND (vendor_id == 47) "
    " "
    Last edited by lxman; 3rd July 2011 at 14:46.

  4. #4
    Join Date
    Feb 2008
    Posts
    491
    Thanks
    12
    Thanked 142 Times in 135 Posts
    Qt products
    Qt4 Qt5
    Platforms
    Unix/X11

    Default Re: Using Qt with SQLite

    Just a WAG, but try putting a value in your foreign key column: phone_number.type

Similar Threads

  1. [Qt][SQLite] Two problems with SQLite.
    By Xandareva in forum Newbie
    Replies: 6
    Last Post: 6th April 2010, 23:06
  2. Help Sqlite + QT
    By vinny gracindo in forum Newbie
    Replies: 4
    Last Post: 5th December 2009, 07:33
  3. SQLite where are you
    By baray98 in forum Installation and Deployment
    Replies: 1
    Last Post: 20th June 2009, 15:37
  4. xml + sqlite
    By eleanor in forum Qt Programming
    Replies: 1
    Last Post: 7th May 2009, 17:06
  5. sqlite
    By spx2 in forum Qt Programming
    Replies: 9
    Last Post: 19th December 2006, 22:01

Tags for this Thread

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.