SQL Views within QSqlTableModel

mikeosoft

I am trying to use a SQL VIEW to display some information in a table view using the QSqlTableModel.

The Database I am using is SQLite.

I am using this, because the SQL VIEW I use is relational, so the SQL VIEW pulls its data from a number of tables, but also uses the strftime to format a date field and to create an additional column with a derived field (which holds the day of the week, derived using strftime from the date column).

The SQL VIEW, creates in the database and is usable from other SQLite tools, so that aspect is working.

The code below, gives me a straight table of the Events table within the database, without the relational lookups and the required SQL SELECTion, though I realise I could do that bit with a filter.

@m_model = new QSqlTableModel(m_db);
m_model->setTable("Events");
m_model->select();

ui->Diary_Table->setModel(m_model);
ui->Diary_Table->resizeColumnToContents(0);
ui->Diary_Table->setWordWrap(true);
ui->Diary_Table->resizeRowsToContents();
ui->Diary_Table->setSortingEnabled(true);@

I realise that I could also use a relational model to carry out the relational lookups, but I am not sure how else I could create the additional columns and reformat the data using a single table or relational model.

The SQL view is created elsewhere in the code and resides within the database once it is created.

@sql_AddValue->prepare("CREATE VIEW Result_Diary AS SELECT User_Info_Key, case cast (strftime('%w', Date) AS integer) when 0 then 'Sun' when 1 then 'Mon' when 2 then 'Tue' when 3 then 'Wed' when 4 then 'Thu' when 5 then 'Fri' else 'Sat' end as Day, strftime("%d-%m-%Y",Date,'localtime') AS Date, Time, Event_Type.Event_Name AS Event, Reason_Type.Reason_Type_Name AS Reason, Value FROM Events JOIN Event_Type ON Events.Event_Type_Key = Event_Type.Event_Type_Key JOIN Reason_Type ON Events.Reason_Type_Key = Reason_Type.Reason_Type_Key WHERE (Events.Event_Type_Key = :EventType_BT OR Events.Event_Type_Key = :EventType_Carb OR Events.Event_Type_Key = :EventType_Basal_Inj OR Events.Event_Type_Key = :EventType_Bolus_Inj) ORDER BY Date, Time");

    sql_AddValue->bindValue(":EventType_Carb", get_Event_Key("Carbohydrate"));
    sql_AddValue->bindValue(":EventType_BT", get_Event_Key("Blood Test"));
    sql_AddValue->bindValue(":EventType_Basal_Inj", get_Event_Key("Injection Basal"));
    sql_AddValue->bindValue(":EventType_Bolus_Inj", get_Event_Key("Injection Bolus"));
    sql_AddValue->exec&#40;&#41;;@

The database is created elsewhere within the code, within a Class and that class is passed to the dialogue class which I am using to create this table view.

If I try the code below:
@ m_model = new QSqlTableModel(m_db);
QSqlQuery *query = new QSqlQuery(QString("SELECT * FROM Result_Diary WHERE User_Info_Key = %1").arg(m_Current_User));
m_model->setQuery(*query);@

Which is followed by the ui code in the earlier (working) example, I get an error message:
@/home/mike/Qt/5.1.0/gcc/include/QtSql/qsqltablemodel.h:135: void QSqlTableModel::setQuery(const QSqlQuery&) is Protected@

I had this problem, with QT4, 5.0 and 5.1.

I'm new to QT and have tried searching for this, but had little success in finding anything because searching for QT and views got me a lot of information on views within QT, but not the use of SQL VIEWS within QT views.

Thanks.

SGaist

Hi,

You should use setTable and setFilter (you are mixing QSqlQueryModel and QSqlTableModel)

On a side note, it's Qt, QT stands for Apple QuickTime :)

mikeosoft

Thanks for the suggestion, are you suggesting that the following should work:

@m_model = new QSqlTableModel(m_db);
m_model->setTable("Result_Diary"); // The SQL VIEW name
QString myfilter = QString("User_Info_Key = %1").arg(m_Current_User); // The WHERE bit of the SQL used with the SQL VIEW
m_model->setFilter(myfilter);
m_model->select();

ui->Diary_Table->setModel(m_model);@

I don't get any errors with this, just a blank frame within the window.

The Filter displays what I would expect if I try it with the "Events" Table.

SGaist

Check the lastError() returned by m_model after the select() call.

I'm not sure right now but I think your filter should be something like:

@QString("User_Info_Key = '%1'").@

mikeosoft

Unfortunately the lastError() doesn't return anything and adding the single quotes to the QString doesn't make any differents

I tried the lastError() with and without the single quotes.

SGaist

Strange, another idea: you can get the query built by the QSqlTableModel and run it in a mysql shell to see what you should obtain.

mikeosoft

I can execute the query against the SQL VIEW in the SQLite Firefox add-in and it works perfectly. I did my SQL building in that tool as it was complex to build with the case statement and the strftime time elements.

SGaist

Hoooo wait, just thought of something... When opening the connection, do you provide the full path to your database file ? If not, you might well be creating a new empty database with the same name as yours.

mikeosoft

It does use a path that it loads from a configuration file. The database file is only created, if it doesn't exist and the start up builds a user profile with the location and other information stored within a file.

The SQL VIEW is created as part of this initial creation and so is part of the database structure.

If I change the code to look at the "Events" Table which the SQL VIEW gets most of it's data from (it gets the relational lookups from elsewhere), it works perfectly, it is only when I use the SQL VIEW it fails to display anything.

mikeosoft

I tried the lastError() on the model (I accidently did it on the database when you suggested it before - sorry SGaist - problems with cutting and pasting) and got the error message "Unable to find table Result_Diary".

I guess this is because "Result_Diary" is a SQL VIEW not a SQL Table.

A SQL VIEW is a virtual table, not an actual table, but it would appear Qt doesn't see it in that way.

"SQL Views - w3schools.com":http://www.w3schools.com/sql/sql_view.asp

I need to be able to use a SQL VIEW because, because the SQL VIEW I use is relational, so the SQL VIEW pulls its data from a number of tables, but also uses the strftime to format a date field and to create an additional column with a derived field (which holds the day of the week, derived using strftime from the date column).

I don't thing the Relational Table model would let me derive fields based on other fields or display the data in the fields formated differently?

Can Qt cope with SQL VIEWS or how can I achieve what I want to do?

SGaist

It's been a long time since I used VIEWs but I thought Qt would see them also as tables.

Anyway, you can get a class lower and use the QSqlQueryModel with the query you first posted

mikeosoft

I'm afraid QSqlQueryModel doesn't recognise SQL Views either, I'll try skipping the view and just building the QTableView with SQL.

It looks like the SQLite element of Qt doesn't include SQL VIEWS.

Can anyone tell me if the other Database libaries can use SQL VIEWS?

SGaist

Did you try by just running your queries with QSqlQuery ?