[SOLVED] Help with QSqlQueryModel

SGaist

If you don't set the query, does you ui show ?

mcosta

bq. Yes I’ve tried it in SQlite and it works very well.

What is the result? I think your query has a incorrect syntax.

holygirl

SGaist

Yes the UI shows if my query is something like

@
select name from channel
@

That's why I was wondering if Qt supports complex SQL queries.

mcosta

The result is the name, title and start_time fields like

NAME | TITLE | START_TIME

SGaist

In that case, print what your model.lastError().text() returns. It should give you an idea why it doesn't work.

holygirl

Hi SGaist

The lastError returns 0. I tried a variation of the code I originally posted and it worked. Here's the code

@
Select d.title
From channel a
Join BROADCAST_SCHEDULE b on b.channel_id = 30
Join CONTENT_INSTANCE c on b.instance_id =c.instance_id
Join CONTENT d on d.content_id = c.content_id
Group By b.start_time
@

However, I needed to use a where clause to specify a condition. But when used with this clause, the result is nil. There's no error either.

@
Select d.title
From channel a
Join BROADCAST_SCHEDULE b on a.channel_id = b.channel_id
Join CONTENT_INSTANCE c on b.instance_id =c.instance_id
Join CONTENT d on d.content_id = c.content_id
where a.name like "HBO"
Group By b.start_time
@

Does Qt not support where clauses? Where am I going wrong? Thanks for your help.

SGaist

How do you write the last one in your code ?

holygirl

Like this

@
QSqlQuery *query = new QSqlQuery();
QString title = "HBO";
query->prepare (Select d.title
From channel a
Join BROADCAST_SCHEDULE b on a.channel_id = b.channel_id
Join CONTENT_INSTANCE c on b.instance_id =c.instance_id
Join CONTENT d on d.content_id = c.content_id
where a.name like %1
Group By b.start_time).arg(title);
query->exec();
@

SGaist

This will lead to:

@where a.name like HBO@

and IIRC it's not a valid like statement

try with:

@where a.name like '%1'@

SGaist

Another thing, is HBO the complete name ? Or is it in the name ? Like "something HBO" ?

In the later case you have to add % to your like statement, see "this":http://www.w3schools.com/sql/sql_like.asp

holygirl

Thanks SGaist for the w3schools.com link. It was very useful to me to learn how to find a particular pattern from a column in the database. Anyhow, pertaining to the problem I was trying to solve, I used

@
where a.name like '%1'
@

instead of

@
where a.name like %1
@

and it works perfectly well. Thank you very much for correcting my syntax. I didn't know the single quotes were necessary. Thanks again :)

SGaist

You're welcome !

SQL syntax is very sensitive to these kind of details