Using Qt with SQLite

[lxman]

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:

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


#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::OnFieldC hange);
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); // Here is my current headache.
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:


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:


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

[ChrisW67]

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.

[lxman]

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:


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.


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) "
" "

[norobro]

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