Using Qt with SQLite

Printable View

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:

Code:

#ifndef NEWVENDORFORM_H
#define NEWVENDORFORM_H
 
#include <QDialog>
#include <QSqlTableModel>
#include <QSqlRelationalTableModel>
#include <QFontMetrics>
#include <QSqlRecord>
#include "dblineedit.h"
#include "dbplaintextedit.h"
 
namespace Ui {
    class NewVendorForm;
}
 
class NewVendorForm : public QDialog
{
    Q_OBJECT
 
public:
    explicit NewVendorForm(int id, QWidget *parent = 0);
    ~NewVendorForm();
 
private:
    Ui::NewVendorForm *ui;
    DBLineEdit *le;
    int rec_id;
    DBPlainTextEdit *pte;
    QSqlRelationalTableModel *table_mod;
 
private slots:
    void slot_new_phone(QPoint);
};
 
#endif // NEWVENDORFORM_H

The .cpp file:

Code:

#include "newvendorform.h"
#include "ui_newvendorform.h"
#include "sqliteinpstring.h"
#include <QSqlError>
#include <QSqlField>
#include <QDebug>
 
NewVendorForm::NewVendorForm(int id, QWidget *parent) :
    QDialog(parent),
    ui(new Ui::NewVendorForm)
{
    rec_id = id;
    ui->setupUi(this);
    ui->tableView_phone->resizeRowsToContents();
    ui->tableView_phone->setEditTriggers(QTableView::AllEditTriggers);
    le = new DBLineEdit(rec_id, "vendor", "name", this);
    pte = new DBPlainTextEdit(rec_id, "vendor", "address", this);
    le->move(160, 35);
    le->setFixedWidth(130);
    pte->move(10, 155);
    pte->setFixedWidth(140);
    pte->setFixedHeight(140);
    table_mod = new QSqlRelationalTableModel(this);
    table_mod->setTable("phone_number");
    table_mod->setFilter("vendor_id == " + QString::number(rec_id));
    table_mod->setEditStrategy(QSqlRelationalTableModel::OnFieldChange);
    table_mod->select();
    table_mod->setHeaderData(3, Qt::Horizontal, "Type");
    table_mod->setHeaderData(4, Qt::Horizontal, "Country Code");
    table_mod->setHeaderData(5, Qt::Horizontal, "Area Code");
    table_mod->setHeaderData(6, Qt::Horizontal, "Number");
    table_mod->setRelation(3, QSqlRelation("phone_type", "id", "term"));
    ui->tableView_phone->setModel(table_mod);
    ui->tableView_phone->update();
    connect(ui->tableView_phone, SIGNAL(customContextMenuRequested(QPoint)),
            this, SLOT(slot_new_phone(QPoint)));
    ui->tableView_phone->setColumnHidden(0, true);
    ui->tableView_phone->setColumnHidden(1, true);
    ui->tableView_phone->setColumnHidden(2, true);
    ui->tableView_phone->resizeColumnsToContents();
    ui->tableView_phone->resizeRowsToContents();
}
 
void NewVendorForm::slot_new_phone(QPoint) {
    qDebug() << "slot_new_phone";
    QSqlRecord tmp_record;
    tmp_record = table_mod->record();
    qDebug() << tmp_record;
    tmp_record.setGenerated(0, false);
    tmp_record.setGenerated(1, false);
    tmp_record.setGenerated(3, false);
    tmp_record.setGenerated(4, false);
    tmp_record.setGenerated(5, false);
    tmp_record.setValue("vendor_id", rec_id);
    tmp_record.setValue("number", "555-5555");
    qDebug() << tmp_record;
    table_mod->select();
    qDebug() << "Insert was successful " << table_mod->insertRecord(-1, tmp_record);  [COLOR="red"]// Here is my current headache.[/COLOR]
    qDebug() << table_mod->query().lastError().text();
    table_mod->select();
    ui->tableView_phone->update();
    ui->tableView_phone->resizeRowsToContents();
    ui->tableView_phone->resizeColumnsToContents();
}
 
NewVendorForm::~NewVendorForm() {
    le->destroying();
    pte->destroying();
    delete le;
    SQLiteInpString fix_str;
    QSqlQuery query;
    QString qry_str;
    qry_str = "SELECT address FROM vendor WHERE id == " + QString::number(rec_id);
    query.prepare(qry_str);
    query.exec();
    query.next();
    fix_str = query.value(0).toString();
    fix_str = fix_str.replace("\n\r", ", ");
    fix_str = fix_str.replace("\n", ", ");
    query.clear();
    qry_str = "UPDATE vendor SET address = '" + fix_str;
    qry_str += "' WHERE id == " + QString::number(rec_id);
    query.prepare(qry_str);
    query.exec();
    query.next();
    delete pte;
    delete ui;
}

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:

Code:

CREATE TABLE [phone_number] (
  [id] INTEGER NOT NULL ON CONFLICT FAIL PRIMARY KEY ON CONFLICT FAIL AUTOINCREMENT, 
  [client_id] INTEGER CONSTRAINT [client_id] REFERENCES [client_info]([id]) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, 
  [vendor_id] INTEGER CONSTRAINT [vendor_id] REFERENCES [vendor]([id]) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, 
  [type] INTEGER CONSTRAINT [type] REFERENCES [phone_type]([id]) DEFERRABLE INITIALLY DEFERRED, 
  [country_code] TEXT NOT NULL ON CONFLICT REPLACE DEFAULT ('001'), 
  [area_code] TEXT NOT NULL ON CONFLICT REPLACE DEFAULT ('828'), 
  [number] TEXT NOT NULL ON CONFLICT FAIL, 
  CONSTRAINT [has_owner] CHECK(client_id IS NOT NULL OR vendor_id IS NOT NULL));

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

Code:

INSERT INTO phone_number (vendor_id, number) VALUES (40, '555-5555')

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

3rd July 2011, 08:46
ChrisW67

Re: Using Qt with SQLite

Quote:

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.
3rd July 2011, 14:46
lxman

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:
Code:

void NewVendorForm::slot_new_phone(QPoint) { qDebug() << "slot_new_phone"; QSqlRecord tmp_record; tmp_record = table_mod->record(); qDebug() << tmp_record; tmp_record.setGenerated(0, false); tmp_record.setGenerated(1, false); tmp_record.setGenerated(3, false); tmp_record.setGenerated(4, false); tmp_record.setGenerated(5, false); tmp_record.setValue("vendor_id", rec_id); tmp_record.setValue("number", "555-5555"); qDebug() << tmp_record; table_mod->select(); qDebug() << table_mod->get_selectStatement(); qDebug() << "Insert was successful " << table_mod->insertRecord(-1, tmp_record); qDebug() << table_mod->get_selectStatement(); qDebug() << table_mod->query().lastError().text(); table_mod->select(); ui->tableView_phone->update(); ui->tableView_phone->resizeRowsToContents(); ui->tableView_phone->resizeColumnsToContents(); }
And here is the complete debugging output from running through this code.

Quote:

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) "
" "
3rd July 2011, 19:52
norobro

Re: Using Qt with SQLite

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