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



  • 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 ?


  • Lifetime Qt Champion

    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



  • 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 !


  • Lifetime Qt Champion

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



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



  • 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...



  • 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...



  • 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?



  • 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 ?


  • Lifetime Qt Champion

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



  • 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...



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


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.