Making QT Work with SQLite View

[Baasie]

Goodday fellow QT'ers,

at the moment i am making a program where i use a database.

The database is SQLite and i made several tabels in it and 2 views.

Now here is my problem i got 1 view wich works good with QSQLQuerymodel and the statement is SELECT * FROM view

now there is a 2nd view i made where i need to get data out and wich i inner joined. In this view i need to compare the ID with an int i put it and it needs to give me the value on the same row as that ID.

but here is my problem i cannot seem to get QT work with the view to get the data out. the * works but when i put it SELECT ID, Value FROM view it just won't take it. i am 100% sure the syntax is correct.

when i use the error handling it tells me no such column.... but that is inpossible.

anyone has any ideas how i need to assign the tabels in a SQLite view ?

thanx in advance

[wysota]

Without seeing any code there is not much we can do about it. But maybe QRelationalTableModel applies to you? If the int is a foreign key from another table, you should be able to use it.

[Baasie]

Wel the code itself is not accualy that hard. Anyways let me begin with the database.. SQLite does not support FK's afaik menaing no contraints. But it does support views where you can JOIN tables together and use something similiar (only you cannot edit a view but that cna be fixed agian using triggers.)

anyway i have multiple table describing differetn values with comes together in 1 table. meaing:

Table: Artist
ID_Artist
Name

Table: track
ID_Track
Track

Table: Path
ID_Path
Path

Main
ID_Main
ID_Artist
ID_Track
ID_Path

Now these are my tables (i got a lot more but none necesarry here), i made a view that combines the ID_Main and join the ID_Path so you will have a seperated ID_Main for each track and you can show directly the path in a view. In this example i got the ID from another view of the track i want to hear, and trough this view i can locate the path of the file trough the DB.


Now for the coding part, it;s relaly simple,:

openDatabase();
QSqlQuery query(db);
query.exec("SELECT ID_Main, Path FROM view");

qDebug() << query.lastError().text();
while(query.next())
{
qDebug() << "value = " << query.value(0).toString();
}
closeDatabase();

the qDebug is just to see if i get something out of it, but when i run this i get the following error:

"no such column: ID Unable to execute statement"

wich means offcourse he cannot find the columns wich is strange because when i load it into a QSqlTableView with the SELECT * FROM view query it shows me those tables. Also using the query SELECT * FROM view wil get me both number but in this case i wnat to make a query wich goes like: SELECT ID, path FROM view WHERE ID=4 else i need to take care of it in my code wich is no problem but why making more code if it cna be easier in this case (hopefully!!).

anyways thanx in advance

[wysota]

SQLite does not support FK's afaik menaing no contraints.
There is no such thing as support for foreign keys. Foreign key is just a concept of putting a key of one table into another. It's true that SQLite doesn't have constraints but constraints and foreign keys are two different concepts.


the qDebug is just to see if i get something out of it, but when i run this i get the following error:

"no such column: ID Unable to execute statement"
Does it work if you execute the same statement from SQLite console? I'd assume SQLite treats underscore as a separator and you need to wrap the column name into some kind of quotes (double, single or backquotes - it depends on the sql engine). Seems that Qt has nothing to do with it.

[Baasie]

I know about the contraits a bit, not a pro in this.

anyways already thanx for the quick help and reply. I tried multiple things like:

SELECT ID FROM view and it does not give me back anything. The strange thing i still find is that the asterix does work but not just ID etc.

i have worked around this by just fetching everything with the * and comparing but this is jsut temporary for me since checking all the that comes from that database sometimes takes a while, Plus it;s nto whiseto use the *.

about the SQLite console, i use SQLite2009pro for my database and trough this i make my views and cna make queries. the query he makes is this one:

SELECT
ID,
PlayPath
FROM
user_getPlayPath

WHERE
(ID=1)


this time i just choised 1 as ID but that one is the variable i will input myself offc.
i tried to put this exact same thing in but does not work

ID, PlayPath are the columns, the user_getPlayPath is the view i made.

it is just strange i can load the view trough:

model.setQuery(query);

where the query is: SELECT * FROM user_getPlayPath

and get the table names on top in a tableview...

also i tried to use PRAGMA table_info(user_getPlayPath) and from this i just get the tables back Main.ID, Playpath.Playpath in my query. Wich i also tried to use but did not get it to work.

so something really strange is going on wich i cannot explain.

and sorry for my crappy grammar, in my native language it's the same so hope you can follow me on this one ^^

[Baasie]

Some update, i used the same method now with a mysql database, and here it does exacly what i want.

SELECT id, user FROM view (an example)

and it shows me what i want.