The easiest way for a very simple database with tables?



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



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


  • Moderators

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



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


  • Qt Champions 2017

    @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();
    
    


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


  • Qt Champions 2017

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



  • 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

    Hope it helps :)


Log in to reply
 

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