Choosing columns for display in an QSqlTableModel

[gabor53]

Hi,
I display the data from a database using the following code:

	model = new QSqlTableModel(this);
    model->setTable ("Items");
    model->select ();
    ui->tableView->setModel (model);

How can I choose which columns to be displayed and how can I display images from the database (stored in a BLOB column in the database)?
Thank you.

[mrjj]

Hi
Im not sure you can select columns
with QSqlTableModel as its mapping whole table.

Here is example of save & load image to db
https://wiki.qt.io/How_to_Store_and_Retrieve_Image_on_SQLite

[gabor53]

@mrjj
Is it possible to display the image (stored as a BLOB) through tablemodel?
Thank you

[mrjj]

@mrjj said:

QSqlTableModel

If you look at docs there is sample
QSqlTableModel model;
model.setTable("employee");
model.select();
int salary = model.record(4).value("salary").toInt(); << you would use toByteArray() or something like that.

You can do the same with your blob to get the field with the blob
and then use the code from.
How_to_Store_and_Retrieve_Image_on_SQLite

[gabor53]

Hi,
I came up with the following code:

 QSqlQuery query2 ("SELECT Count(*) FROM Items");
    query2.first ();
    row1 = query2.value (0).toInt ();

	 QStandardItemModel *smodel = new QStandardItemModel;

	ui->tableView->setModel (smodel);
    ui->tableView->setColumnHidden (0,true);

    for(int i = 4; i < 14; i++)
    {
        ui->tableView->setColumnHidden (i,true);
    }
    row1 = 0;

    while(query.next ())
    {
		QStandardItem *Item1 = new QStandardItem();
        QStandardItem *Item2 = new QStandardItem();
        QStandardItem *Item3 = new QStandardItem();

        Item1->setData (NameD = query.value(0).toString (),Qt::DisplayRole);

        byteArray = query.value (1).toByteArray ();
        Pixmap.loadFromData (byteArray);
        Pixmap = Pixmap.scaled (100,100,Qt::KeepAspectRatio);

        Item2->setData (QVariant(Pixmap),Qt::DecorationRole );
      smodel->setItem (row1++,1,Item2);
        Item3->setData (DescrD = query.value (2).toString (),Qt::DisplayRole);
    }

Now it displays the images, but not the texts. What did I miss? Thank you for your help.

[VRonin]

No need to use the QStandardItem here

 for(;query.next ();++row1)
    {
        smodel->setData (smodel->index(row1,0), NameD = query.value(0).toString ());
        Pixmap.loadFromData (query.value (1).toByteArray (););
        smodel->setData (smodel->index(row1,1),Pixmap,Qt::DecorationRole );
        smodel->setData (smodel->index(row1,2), DescrD = query.value (2).toString ());
    }

[gabor53]

@VRonin
Thank you. I have the following:

	 QStandardItemModel *smodel = new QStandardItemModel;

    row1 = 0;

	for(;query.next (); row1++)
    {
        smodel->setData (smodel->index(row1,0), NameD = query.value(0).toString ());
        qDebug() << "row1: " << row1;
        qDebug() << "NameD: " << NameD;
        Pixmap.loadFromData (query.value (1).toByteArray ());
        smodel->setData (smodel->index(row1,1),Pixmap,Qt::DecorationRole );
        smodel->setData (smodel->index(row1,2), DescrD = query.value (2).toString ());
        qDebug() << "DescrD: " << DescrD;
    }

	ui->tableView->show ();

qDebug() shows that the variables have the right values, but nothing shows in the table. What did I miss?

[mrjj]

Hi
Maybe
smodel->setData (xx for DescrD needs Qt::DisplayRole
like u do for Qt::DecorationRole ?

[gabor53]

@mrjj
Unfortunately

        smodel->setData (smodel->index(row1,0), NameD = query.value(0).toString (),Qt::DisplayRole);

did nothing. The table is still empty.

[mrjj]

@gabor53
and u are 100000% sure that it adds NameD ?
and its not empty?

[VRonin]

the code you posted is missing the necessary smodel->insertRows() smodel->inserColumns() are you doing it?

[gabor53]

@VRonin
Hi
I don't have it. Whete is this supposed to go?
Thank you

[SGaist]

Hi,

Why not just use a QSqlQueryModel and a QStyledItemDelegate ?

If you want to have specific names for the columns you are selecting then use the SELECT myColumn AS NewName construct.

[VRonin]

QStandardItemModel *smodel = new QStandardItemModel(this); // maybe give it a parent to avoid memory leaks

for(int row1=0;query.next (); ++row1)
    {
if(row1==0){
const QSqlRecord qRec=query.record();
smodel->insertColumns(0,qRec.count());
Q_ASSERT(query.size()>=0) // if NO COUNT is set in the database then you have to calculate the size manually
smodel->inserRows(0,query.size());

// Optional: set headers
for(int i=0;i<qRec.count();++i)
smodel->setHeaderData(i,Qt::Horizontal,qRec.fieldName(i));
}
        smodel->setData (smodel->index(row1,0), NameD = query.value(0).toString ());
        qDebug() << "row1: " << row1;
        qDebug() << "NameD: " << NameD;
        Pixmap.loadFromData (query.value (1).toByteArray ());
        smodel->setData (smodel->index(row1,1),Pixmap,Qt::DecorationRole );
        smodel->setData (smodel->index(row1,2), DescrD = query.value (2).toString ());
        qDebug() << "DescrD: " << DescrD;
    }

[gabor53]

@VRonin
Thank you very much for the code. Now it shows the column headers, but still no data.
I added

ui->tableView->setModel (smodel);

to view it in tableView.Any Idea why no records show?
Thank you.

[VRonin]

Looks like rows are still mot inserted, try this:

for(int row1=0;query.next (); ++row1)
    {
if(row1==0){
const QSqlRecord qRec=query.record();
smodel->insertColumns(0,qRec.count());

// Optional: set headers
for(int i=0;i<qRec.count();++i)
smodel->setHeaderData(i,Qt::Horizontal,qRec.fieldName(i));
}

smodel->inserRow(row1);
        smodel->setData (smodel->index(row1,0), query.value(0).toString ());
        Pixmap.loadFromData (query.value (1).toByteArray ());
        smodel->setData (smodel->index(row1,1),Pixmap,Qt::DecorationRole );
        smodel->setData (smodel->index(row1,2), query.value (2).toString ());
    }

[gabor53]

@VRonin
Thank you. This one displays data from the database but only one record (1 line). Trying to figure out why.

[gabor53]

Any idea why this code displays only 1 record from the database?

[SGaist]

Check your query and your database content.

[gabor53]

@SGaist Thank you. I was missing a }.