[SOLVED]How fill a Table view with MySQL database connexion

Marco_105

Hello,

Desesperate in the way to understanding how to get a gui with view colomn and row from a MySQL database table...

what the best way for that, i saw everywhere implement the opening database in the construct of the MainWindow gui ?

It is not better ti have file beside with the connexion database rules ?

SGaist

Hi, in the examples, there's a file dedicated to that (though using a SQLite database but the principe is the same)

Where the database setup is done is up to you, where you think it's best suited for you application logic i.e. :

You can do it in the main.cpp, show an error message if the connection fails and exit your application.
You can do it in the Mainwindow, disabling the control related to the database until a connection has been properly setup
etc...

If you want to make a quick test, go to the tablemodel example sources and grab what you need, update the connection code to use your MySQL database and you should be good to go.

Hope it helps

Marco_105

Ok, i try some code now:

file.pro
@#-------------------------------------------------

Project created by QtCreator 2013-04-10T15:33:20

#-------------------------------------------------

QT += core gui sql

greaterThan(QT_MAJOR_VERSION, 4): QT += widgets

TARGET = DataBaseTableEditor
TEMPLATE = app

SOURCES += main.cpp
mainwindow.cpp

HEADERS += mainwindow.h

FORMS += mainwindow.ui@

main.cpp
@#include "mainwindow.h"
#include <QApplication>

int main(int argc, char *argv[])
{
QApplication a(argc, argv);
MainWindow w;
w.show();

return a.exec&#40;&#41;;

}@

file.h
@#ifndef MAINWINDOW_H
#define MAINWINDOW_H

#include <QMainWindow>

#include <qsqltablemodel.h>

namespace Ui {
class MainWindow;
}

class MainWindow : public QMainWindow
{
Q_OBJECT

public:
explicit MainWindow(QWidget *parent = 0);
~MainWindow();

private:
Ui::MainWindow *ui;
QSqlDatabase db;
QSqlTableModel *model;
};

#endif // MAINWINDOW_H@

file.cpp
@#include "mainwindow.h"
#include "ui_mainwindow.h"

#include <QSql>
#include <QSqlTableModel>
#include <QMessageBox>
#include <QtSql>

MainWindow::MainWindow(QWidget *parent) :
QMainWindow(parent),
ui(new Ui::MainWindow)
{
ui->setupUi(this);
this->setCentralWidget(ui->tableView);

//ouverture BDD
QString myCurrDrv = "QMYSQL";
db = QSqlDatabase::addDatabase(myCurrDrv);

db.setHostName("localhost");
db.setPort(3306);
db.setUserName("root");
db.setPassword("");
db.setDatabaseName("fgh_database");

QMessageBox msgBox;

if(!db.open())
{
    //problem with connexion
    msgBox.setIcon(QMessageBox::Warning);
    msgBox.setWindowTitle("Opening status: ");

    msgBox.setText("Connexion FAILED____________________________\n" +
                   db.lastError().databaseText());

    QStringList availableDriver(db.drivers());

    //db.lastError().driverText() + db.drivers();
    msgBox.setDetailedText("*******status du driver*******\n" +
                           db.lastError().driverText()+ "\n\n" +

                           "*******Is driver available*******\n" +
                           QString(db.isDriverAvailable(myCurrDrv)?"true":"false") + "\n\n" +

                           "*******driver available*******\n" +
                           availableDriver.join(" "));
    msgBox.exec&#40;&#41;;

}else{
    
    //Database is connected 
    QMessageBox::information(this,tr("Opening status: "),db.hostName()); 
}

//get the table
model = new QSqlTableModel(this,db);

model->setTable("person");
model->setEditStrategy(QSqlTableModel::OnManualSubmit);
model->select();
model->setHeaderData(0,Qt::Horizontal,"Nom");
model->setHeaderData(1,Qt::Horizontal,"Adresse");


ui->tableView->setModel(model);

}

MainWindow::~MainWindow()
{
db.close();
delete ui;
}
@

I got following msgBox:

Unknown MySQL server host // QMYSQL driver is available // all driver are available

So, from the MainWindow constructor my connexion seems impossible !

SGaist

Are you sure that your database as a person table and that it contains something ?
You can also check the errors from the model

panosk

Btw, you should fix your if statement in order to actually any errors
@
if(!db.open())
{
...
@

Marco_105

I try check what's happened with:

@qDebug() << model->lastError().text();@

that give: "Unable to find table person"

I check for the name, ther was a mistake because i switch english/french (table: personne)

So, correct the line 38:
@model->setTable("personne");@

but i get same result (recomplile and clean before):
"Unable to find table personne"

Is it a permission problem ?

When i do some transaction (on this table) into a main.cpp sample there is no problem...

Marco_105

I try enhance the code by that:
@if(model->select())
{
model->setHeaderData(0,Qt::Horizontal,"Nom");
model->setHeaderData(1,Qt::Horizontal,"Adresse");

    ui->tableView->setModel(model);
}else{
    QMessageBox::warning(this,tr("Access table: "),db.lastError().text());
}@

so now i can see the message box saying, Access table: "Unknown MySQL server host 'localhost' (0) QMYSQL: Unable to connect

That mean the db is closed before statement, so so fun...

panosk

Is the MySQL server actually running? Can you connect to the database from the command line using the mysql client and the same arguments you are using in your code? Can you work with the specific table after you connect from the command line?

Marco_105

You mean with mysql.exe CLI ?

mysql> use test;
mysql> select * from personne; //listing the table

From CLI all transaction with table are Ok !

Sorry, but if you followed this post i've updated it since yesterday, so we see now that the connexion from the MainWindow constructor was never been but from the main.cpp that was Ok...

Is a possible wrong folder composant (driver, lib) problem ?

SGaist

Is it normal that you are using the database test from the cli and fgh_database from Qt ?

Marco_105

Yes i try for to database but no worry when i copy the update that look mixed but was not on my post.

I try to understand what's happend and i saw that :
connect with driver MySQL possible only on a console app config.

So, when i have a file.pro for GUI config the connexion seems impossible...

Marco_105

Following response for it...":http://qt-project.org/forums/viewthread/27169/