Connecting to Database in Qt

Swati777999

Hi All,

Hope you're doing well.

I have a doubt regarding connecting a DB to Qt.
In this link https://doc.qt.io/qt-5/sql-connecting.html

QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");

Which kind of file is "QMYSQL" written above?

I've data in Excel files. Once I get the answer to the above question, I will be able to convert .xsls file into the required typ e for processing.

Thanks in advance!

jsulm

@Swati777999 Take a look at http://katecpp.github.io/sqlite-with-qt/
SQLite uses a file to store the data, so there is no host name involved. Instead you specify the file:

m_db = QSqlDatabase::addDatabase("QSQLITE");
m_db.setDatabaseName(path);

jsulm

@Swati777999 said in Connecting to Database in Qt:

Which kind of file is "QMYSQL" written above?

This is not a file. This tells Qt to connect to a MySQL server.

Swati777999

@jsulm ohh.. is it possible to connect to SQLite in the same way as MYSQL ; written below
QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE"); ?

db.setHostName("bigblue"); // **where to get this name?**
db.setDatabaseName("flightdb"); 
db.setUserName("acarlson");
db.setPassword("1uTbSbAs");
bool ok = db.open();

Any help will appreciated!

jsulm

@Swati777999 Take a look at http://katecpp.github.io/sqlite-with-qt/
SQLite uses a file to store the data, so there is no host name involved. Instead you specify the file:

m_db = QSqlDatabase::addDatabase("QSQLITE");
m_db.setDatabaseName(path);

Swati777999

@jsulm Thanks for the help.

Strangely, I had bookmarked this page a few months ago, this link just slipped my mind!

Swati777999

@jsulm I want to show the result of a query in my centralWidget. Is it possible through the following codes?
QLabel *label = new QLabel(this);

QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName("C:/Users/swati/Desktop/TestData.db");
db.exec ("Select * from TestData");
label->setText("db.exec ("Select * from TestData")");
setCentralWidget(label);

How to display the result of SQL query in the centralWidget of the mainWindow?

Any help will be appreciated!
Regards
Swati

Pl45m4

@Swati777999 said in Connecting to Database in Qt:

How to display the result of SQL query in the centralWidget of the mainWindow?

Depends on what your centralWidget is...

The way you do this currently, is definitely wrong. It would show the text if your query as label.

Have a look here:

https://doc.qt.io/qt-5/sql-sqlstatements.html

Swati777999

@Pl45m4
I checked the documentation which

For viewing the data from SQL query in tabular form, what to use whether SQL Model Class or QTableview?

jsulm

@Swati777999 said in Connecting to Database in Qt:

what to use whether SQL Model Class or QTableview?

Both.
Please see how it is done here: https://doc.qt.io/qt-5/qsqltablemodel.html

Swati777999

@jsulm said in Connecting to Database in Qt:

@Swati777999 said in Connecting to Database in Qt:

what to use whether SQL Model Class or QTableview?

Both.
Please see how it is done here: https://doc.qt.io/qt-5/qsqltablemodel.html

I used this strategy but I am not getting anything in my main window. :(

jsulm

@Swati777999 Then you are doing something wrong and should provide more information to get meaningful answer.
Best would be related code.

Swati777999

@jsulm

#include "dbmanager.h"

DbManager::DbManager(const QString &path)
{ Q_UNUSED(path);
m_db = QSqlDatabase::addDatabase("QSQLITE");
m_db.setDatabaseName("C:/Users/ss/Desktop/TestData.db");

if (!m_db.open())
{
qDebug() << "Error: connection with database failed";
}
else
{
qDebug() << "Database: connection ok";
}
}
bool DbManager::addEntry(const QString &name)
{
bool success = false;
QSqlQuery query;
query.prepare("INSERT INTO TestData VALUES (:time)");
query.bindValue(":time, time);
if(query.exec())
{
success = true;
}
else
{
qDebug() << "addtemperature error:"
<< query.lastError();
}

return success;
qDebug() << query.isValid();

QSqlQueryModel model;
   model.setQuery("SELECT * FROM TestData");

 for (int i = 0; i < model.rowCount(); ++i) {
    int id = model.record(i).value("id").toInt();
   QString name = model.record(i).value("time").toString();
   qDebug() << id << time;
   }

   QLabel *labelExp = new QLabel(this);
   labelExp->setFrameStyle(QFrame::Panel | QFrame::Sunken);
   labelExp->setText("first line\nsecond line");
   labelExp->setAlignment(Qt::AlignBottom | Qt::AlignRight);

   //Named binding
   QSqlQuery query1;
   query1.prepare("INSERT INTO TestData (time,temperature) VALUES (:time, "
                  ":temperature)");
   query1.bindValue(":time","2020-11-31 10:19:38");
   query1.bindValue(":temperature", 27.4);
   query1.exec();

   //Positional Binding
   QSqlQuery query2;
   query2.prepare("INSERT INTO TestData (time,temperature) VALUES (?, ?, )");
   query2.addBindValue("2010-06-31 12:08:35");
   query2.addBindValue(30.6);
   query2.exec();

   QSqlQueryModel *model1 = new QSqlQueryModel;
      model1->setQuery("SELECT time, temperature FROM TestData");
      model1->setHeaderData(0, Qt::Horizontal, tr("Time"));
      model1->setHeaderData(1, Qt::Horizontal, tr("Temperature"));

      QTableView *view = new QTableView;
      view->setModel(model1);
      view->show();

      model1->setHeaderData(0, Qt::Horizontal, QObject::tr("Time"));
      model1->setHeaderData(1, Qt::Horizontal, QObject::tr("Temperature"));

}

jsulm

@Swati777999 said in Connecting to Database in Qt:

QSqlQueryModel model;

Your model is a local variable and is destroyed as soon as it goes out of scope.
And please format you code properly.

Pl45m4

@jsulm

For whatever reason there are two models... QSqlQueryModel model and QSqlQueryModel* model1

Swati777999

@jsulm I didn't get your point clearly and how to format the code; do you mean looking after the indentation/ improving the readability of the codes?

Swati777999

@Pl45m4 I was trying to see how QSqlQuery and QSqlQueryModel are different from each other, no other reason apart from that.

JonB

@Swati777999 said in Connecting to Database in Qt:

I didn't get your point clearly and how to format the code; do you mean looking after the indentation/ improving the readability of the codes?

When pasting code please select it all and use the the </> Code button:

so it comes out like this
with ``` above & below it in your post

As for your code. addEntry() at least does little --- most of the code is not executed --- since you have an unconditional return statement before most of the body. If people are supposed to look at this for you, you should really clear it up a lot before posting.

jsulm

@Swati777999 said in Connecting to Database in Qt:

I didn't get your point clearly

We are talking about absolute C++ basics now! Please learn those!

bool DbManager::addEntry(const QString &name)
{
...
QSqlQueryModel model;
...
}

In the code above "model" only exists inside DbManager::addEntry. As soon as DbManager::addEntry is done "mode" goes out of scope and is deleted!
Also, I see that you do "return success;" before you create your model - how can this work at all?!