SQL Views within QSqlTableModel

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 ?