[SOLVED] Help with QSqlQueryModel

holygirl

Hi!

The following query works fine in Sqlite but not in Qt creator.The creator hangs on running the App. After stopping the build it gives a error message _exited with code 62097_

Code snippet below:

@
Select a.name, d.title, b.start_time
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
Group By a.name, d.title
@

mcosta

Hi,

Qt Creator is only a IDE; so the problem is related to Qt not Creator.
However in your query I see a GROUP BY instruction and no grouping function like COUNT, MAX, and so on

holygirl

Thanks for your reply mcosta.

     Even if I don't use the *GROUP BY* instruction, I'm unable to execute the query in Qt. Is it because Qt doesn't support it or is it due to some other reason?

mcosta

HI,

I think you Query has a bad syntax and it's strange you say it works in SQlite. Are you sure of that?

SGaist

Hi,

Did you check the error message from the model ?

holygirl

mcosta

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

SGaist

There's no particular error message. The app runs but I don't see the UI window. That's when I stop the app and it gives exited with code 62097 in the output pane

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