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

Charmaman

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

tucnak

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.

Charmaman

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?

tucnak

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

andre

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.