Results 1 to 3 of 3

Thread: Problem with QSqlQuery::prepare and "WHERE ... IN (...)" postgresql statments

  1. #1
    Join Date
    Jan 2008
    Posts
    107
    Thanks
    36
    Thanked 2 Times in 2 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11

    Question Problem with QSqlQuery::prepare and "WHERE ... IN (...)" postgresql statments

    The code:

    Qt Code:
    1. quint16 packet_list_hex[]=
    2. {FMI_ID_ENABLE, FMI_ID_PRODUCT_ID_SUPPORT_RQST, FMI_ID_UNICODE_REQUEST, FMI_ID_SERVER_OPEN_TXT_MSG,
    3. FMI_ID_SERVER_OK_ACK_TXT_MSG, FMI_ID_SERVER_YES_NO_CONFIRM_MSG, FMI_A604_OPEN_TEXT_MSG, FMI_SET_CANNED_RESPONSE,
    4. FMI_DELETE_CANNED_RESPONSE, FMI_SET_CANNED_MSG, FMI_DELETE_CANNED_MSG, FMI_ID_A603_STOP, FMI_ID_SORT_STOP_LIST,
    5. FMI_ID_ETA_DATA_REQUEST, FMI_ID_AUTO_ARRIVAL, FMI_ID_DATA_DELETION, FMI_ID_USER_INTERFACE_TEXT,FMI_MSG_THROTTLING_QUERY,
    6. FMI_SET_DRIVER_STATUS_LIST_ITEM, FMI_DELETE_DRIVER_STATUS_LIST_ITEM
    7. };
    8. QString packets_list;
    9. quint8 size = sizeof(packet_list_hex) / sizeof(packet_list_hex[0]);
    10. for (int n=0; n< size; n++) {
    11. packets_list += QString::number( packet_list_hex[n] );
    12. if (n<size-1)
    13. packets_list+=",";
    14. }
    To copy to clipboard, switch view to plain text mode 

    Qt Code:
    1. sql ="SELECT packet_id, fmi_packet_id, msg_id, txt_container, unique_id, st_x(destination) as lon, st_y(destination) as lat, eta_time, eta_dist_to_dest FROM garmin_packets "
    2. "WHERE unit_imei=? AND ack=FALSE AND fmi_packet_id IN (?)";
    3. query.prepare(sql);
    4. query.bindValue(0, data.imei.data());
    5. query.bindValue(1, packets_list);
    6. query.exec();
    To copy to clipboard, switch view to plain text mode 

    Postgresql log spits this out:

    Qt Code:
    1. ERROR invalid input syntax for integer: "0,1,4,33,34,35,42,48,49,80,82,257,272,512,544,560,576,594,2048,2049" at character 43
    To copy to clipboard, switch view to plain text mode 

    So QSqlQuery::bindValue should be putting those surrounding quotes there...

    Any hints b4 I revert to using sprintf() ?

    BR

  2. #2
    Join Date
    Mar 2009
    Location
    Brisbane, Australia
    Posts
    7,729
    Thanks
    13
    Thanked 1,610 Times in 1,537 Posts
    Qt products
    Qt4 Qt5
    Platforms
    Unix/X11 Windows
    Wiki edits
    17

    Default Re: Problem with QSqlQuery::prepare and "WHERE ... IN (...)" postgresql statments

    As far as I know there is no way to use bound parameters to inserts a list in the manner you were expecting: one parameter one value.

    Given that the items in the IN() list are integers and not user sourced there's little danger in using a string construction for that part of the query (look at QStringList::join() too). I would continue to use parameter binding for user-sourced inputs to the query, I would not revert to snprintf() because there are cleaner/safer tools in the Qt libraries (QString::arg())
    Qt Code:
    1. QList<quint16> packet_list_hex = QList<quint16>() << 1 << 2 << 3;
    2. QStringList entries;
    3. foreach(quint16 i, packet_list_hex)
    4. entries << QString::number(i);
    5.  
    6. sql = QString( "SELECT packet_id, fmi_packet_id, msg_id, txt_container, unique_id, st_x(destination) as lon, "
    7. "st_y(destination) as lat, eta_time, eta_dist_to_dest FROM garmin_packets "
    8. "WHERE unit_imei=? AND ack=FALSE AND fmi_packet_id IN (%1)" )
    9. .arg(entries.join(", "));
    10. query.prepare( sql );
    11. ...
    To copy to clipboard, switch view to plain text mode 

  3. The following user says thank you to ChrisW67 for this useful post:

    pdoria (19th March 2012)

  4. #3
    Join Date
    Jan 2008
    Posts
    107
    Thanks
    36
    Thanked 2 Times in 2 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11

    Default Re: Problem with QSqlQuery::prepare and "WHERE ... IN (...)" postgresql statments

    Useful and elegant solution Chris! Thank you very much!

Similar Threads

  1. Replies: 1
    Last Post: 7th April 2010, 22:46
  2. Replies: 3
    Last Post: 25th August 2009, 14:03
  3. The PostgreSQL plugin is not loaded in "SQL Browser" example
    By Scratch in forum Installation and Deployment
    Replies: 1
    Last Post: 18th November 2008, 02:30
  4. Translation QFileDialog standart buttons ("Open"/"Save"/"Cancel")
    By victor.yacovlev in forum Qt Programming
    Replies: 4
    Last Post: 24th January 2008, 20:05
  5. QFile Problem~ "Unknow error" in "open(QIODevice::ReadWrite)"
    By fengtian.we in forum Qt Programming
    Replies: 3
    Last Post: 23rd May 2007, 16:58

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Digia, Qt and their respective logos are trademarks of Digia Plc in Finland and/or other countries worldwide.