How to use QSqlTableModel and QSqlRelationalTableModel to create new tables on the database.



  • Hello Qt-Geeks,

    I am working on a program that uses a SQLite-Database (QT += core gui sql). I can create a database and use it. So far I create my tables with QSqlQuery::exec(). Works but is very uncomfortable and hard to evaluate by hand.

    I would like to use QSqlTableModel and QSqlRelationalTableModel to create my tables on the database. I found functions like addColumn and selet but I don't really know how to use addColumn() to create a new column on the database (how do I define the value type like INT, VARCHAR, etc?) and I can't find anything like createTable() to actually save the table to the database.

    Unfortunatly I can't find more details in the documentation. Can anyone provide a very short example of how to use those models to create a table and actually save it to the database(SQLite-file)?

    This is what I got so far:

    @
    m_db = QSqlDatabase::addDatabase( "QSQLITE" );
    m_db.setDatabaseName("testdb.sqlite");
    m_db.open();

    if(m_db.isOpen())
    {
        m_images = new QSqlRelationalTableModel(this, m_db);
        m_images->setTable("images");
        m_images->setEditStrategy(QSqlTableModel::OnFieldChange);
        
        // here is where I would like to add columns like for example name(VARCHAR) and number(INT)
        // then I would like to "execute" this model to the database like "CREATE TABLE"
    }
    

    @

    Thanks a lot for any help!!



  • Hello, ~Charmaman!

    I think instead of execution
    @
    m_images->setTable("images");
    @
    You should execute SQL-query to make this table in database
    @
    QSqlQuery q("CREATE TABLE testdb.images ...");
    if(q.exec()) {
    m_images->setTable("images");
    m_images->setEditStrategy(QSqlTableModel::OnFieldChange);
    }
    @

    I don't know how to do it in another way.



  • Thanks for your reply.

    As I wrote in my first comment. I already have it working with premade tables that I have created with QSqlQuery::exec(). That's exactly what I wanted to replace with (if any) qt-structs that do the work for me clean and save instead of having me adding SQL code by hand into my program.

    So QSqlTableModels work only with already created tables? I can change and edit entries but i can not create the table?
    If so, what are the implemented functions addColumn() addRow() for?



  • [quote author="Charmaman" date="1340795923"]Thanks for your reply.

    As I wrote in my first comment. I already have it working with premade tables that I have created with QSqlQuery::exec(). That's exactly what I wanted to replace with (if any) qt-structs that do the work for me clean and save instead of having me adding SQL code by hand into my program.

    So QSqlTableModels work only with already created tables? I can change and edit entries but i can not create the table?
    If so, what are the implemented functions addColumn() addRow() for?[/quote]

    AFAIK there are no any qt-way for your situation. You should make it in your way.



  • There are two* classes of SQL commands:

    DML: Data Manipulation Language is a set of commands that allows you to query and manipulate the data in your database.

    DDL: Data Definition Language is a set of commands that allows to you manipulate the structure of your database: add tables, manipulate indices, etc.

    Qt unfortunately only provides any level of support for the first, not for the second. It is possible to add this, but it is complicated. I wrote an extended set of Qt database drivers for MySql and for MS Access once that adds these features, as I used them a lot. These drivers added a lot of options to QSqlDriver::sqlStatement, as well as some other features. If you're interested: the code is available under GPL.


Log in to reply
 

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