The easiest way for a very simple database with tables?

Niagarer

Hi!
I need to store (and load) some attributes in different tables (in one database) in my program.
With this data, I want to recreate some objects with the stored attributes in the database.
I thought that a normal SQL database (SQLite) would do the job (it surely does somehow), but I noticed, that it will get a bit complicated (to evaluate the stored data)... I there an easier way in QT to achieve all that?
Thanks for answers!

J.Hilk

hi @Niagarer ,
do you know of the SQL-Browser- Example? It does pretty much what you describe.

jsulm

@Niagarer What about SQLite? You can easily use it in Qt. And you do not need a SQL server.

Niagarer

@jsulm
Well, I can't find any useful docs or tutorials or examples about SQLite in C++ that is complete and does not use any third party plugins and that is portable.

@J-Hilk
Thanks, I will take a look.

mrjj

@Niagarer
What you mean ?
Both Qt + sqlite has many docs and the sqllite itself have good docs also.
If its the actual SQL syntax, its not included as
thats out of scope in same way as Qt docs dont try to teach you c++.

The minimal example is

bool createConnection() {
  QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
  db.setDatabaseName(":memory:");
  if (!db.open()) {
    QMessageBox::critical(0, qApp->tr("Cannot open database"), "Click Cancel to exit.", QMessageBox::Cancel);
    return false;
  }
  QSqlQuery query;
  qDebug() << "table:" <<   query.exec("create table person (id int primary key, "
                                       "firstname varchar(20), lastname varchar(20), num int )");
  query.exec("insert into person values(101, 'Dennis', 'Young','1')");
  query.exec("insert into person values(102, 'Christine', 'Holand','2')");
  query.exec("insert into person values(103, 'Lars junior', 'Gordon','4')");
  query.exec("insert into person values(104, 'Roberto', 'Robitaille','5')");
  query.exec("insert into person values(105, 'Maria', 'Papadopoulos','3')");
  return true;
}

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

  QSqlQuery query;
  int ok = query.prepare(("INSERT INTO person (id, firstname, lastname) VALUES (:name, :first, :last)"));
  query.bindValue(":id", 4);
  query.bindValue(":first", "Lars junior");
  query.bindValue(":last", "Gordon");
  query.exec();

Niagarer

@mrjj
Yes, well in the mean time I found some more useful docs on the sqlite site. Anyway, the docs of SQLite, I can do something with them.... once I know everything else about SQLite that's not explained by them.
I just wondered, wheather there is an easier way of storing and loading minimal static data in tables in a database or at least in a sql-like db style but with easier tools to evaluate it. Doesn't seem to be the case. But with QSQLite it seems to be feasible.
Thanks for the example, it really helps!

mrjj

@Niagarer
Hi
Most use of database is somewhat the same as its SQL that does it.
Also if you need sql-like db, so you can easy select subsets of the data, its
most likely the most easy way using sqllite.

Niagarer

update
Well, there does not seem to be another popular and easy way to go than using SQL.
SQLite turned out as an easy and fun way. The SQL-Browser- Example shows good, how it works.
To sum it up (for SQLite):

To open an existing database (or create a new one, if it doesn't exist yet)

QSqlDatabase db = QSqlDatabase::addDatabase("QSQLIITE");
db.setDatabaseName("G:/databases/myDB.db"); // path to your database
if(!db.open())
    // error
// now it's opened

To read or write data, we need a query. To execute SQLite-commands, call query.exec("commands")

QSqlQuery query;
query.exec("SELECT * FROM Student");
while(query.next()){
    QString name = query.value("Name").toString;
    int age = query.value("Age").toInt();
    // and so on
}

Creating table

query.exec("CREATE TABLE 'Student' ( \
               'ID'	INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, \
               'Name'	TEXT, \
               'Age'	INTEGER ); \
             ");

inserting new data into a table

query.exec("INSERT INTO Person(Name, Age) VALUES ('"+name+"', '"+age+"');");

please also look at the minimal example by @mrjj above and SQL-Browser- Example

to see what was happening, I found the DB Browser for SQLite very useful.

Hope it helps :)