[SOLVED] Help with QSqlQueryModel



  • 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
    @



  • 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



  • 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?


  • HI,

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


  • Lifetime Qt Champion

    Hi,

    Did you check the error message from the model ?



  • 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


  • Lifetime Qt Champion

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



  • 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.



  • 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

    ESPN | NBA Basketball | UTC Time
    ESPN | Up to the minute | UTC Time
    Family Net | The cry of the owl | UTC Time


  • Lifetime Qt Champion

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



  • 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.


  • Lifetime Qt Champion

    How do you write the last one in your code ?



  • 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();
    @


  • Lifetime Qt Champion

    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'@


  • Lifetime Qt Champion

    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



  • 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 :)


  • Lifetime Qt Champion

    You're welcome !

    SQL syntax is very sensitive to these kind of details


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.