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

[pdoria]

The code:

quint16 packet_list_hex[]=
        {FMI_ID_ENABLE, FMI_ID_PRODUCT_ID_SUPPORT_RQST, FMI_ID_UNICODE_REQUEST, FMI_ID_SERVER_OPEN_TXT_MSG,
         FMI_ID_SERVER_OK_ACK_TXT_MSG, FMI_ID_SERVER_YES_NO_CONFIRM_MSG, FMI_A604_OPEN_TEXT_MSG, FMI_SET_CANNED_RESPONSE,
         FMI_DELETE_CANNED_RESPONSE, FMI_SET_CANNED_MSG, FMI_DELETE_CANNED_MSG, FMI_ID_A603_STOP, FMI_ID_SORT_STOP_LIST,
         FMI_ID_ETA_DATA_REQUEST, FMI_ID_AUTO_ARRIVAL, FMI_ID_DATA_DELETION, FMI_ID_USER_INTERFACE_TEXT,FMI_MSG_THROTTLING_QUERY,
         FMI_SET_DRIVER_STATUS_LIST_ITEM, FMI_DELETE_DRIVER_STATUS_LIST_ITEM
        };
    QString packets_list;
    quint8 size = sizeof(packet_list_hex) / sizeof(packet_list_hex[0]);
    for (int n=0; n< size; n++) {
        packets_list += QString::number( packet_list_hex[n] );
        if (n<size-1)
            packets_list+=",";
    }

quint16 packet_list_hex[]=
        {FMI_ID_ENABLE, FMI_ID_PRODUCT_ID_SUPPORT_RQST, FMI_ID_UNICODE_REQUEST, FMI_ID_SERVER_OPEN_TXT_MSG,
         FMI_ID_SERVER_OK_ACK_TXT_MSG, FMI_ID_SERVER_YES_NO_CONFIRM_MSG, FMI_A604_OPEN_TEXT_MSG, FMI_SET_CANNED_RESPONSE,
         FMI_DELETE_CANNED_RESPONSE, FMI_SET_CANNED_MSG, FMI_DELETE_CANNED_MSG, FMI_ID_A603_STOP, FMI_ID_SORT_STOP_LIST,
         FMI_ID_ETA_DATA_REQUEST, FMI_ID_AUTO_ARRIVAL, FMI_ID_DATA_DELETION, FMI_ID_USER_INTERFACE_TEXT,FMI_MSG_THROTTLING_QUERY,
         FMI_SET_DRIVER_STATUS_LIST_ITEM, FMI_DELETE_DRIVER_STATUS_LIST_ITEM
        };
    QString packets_list;
    quint8 size = sizeof(packet_list_hex) / sizeof(packet_list_hex[0]);
    for (int n=0; n< size; n++) {
        packets_list += QString::number( packet_list_hex[n] );
        if (n<size-1)
            packets_list+=",";
    }

To copy to clipboard, switch view to plain text mode 

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 "
         "WHERE unit_imei=? AND ack=FALSE AND fmi_packet_id IN (?)";
    query.prepare(sql);
    query.bindValue(0, data.imei.data());
    query.bindValue(1, packets_list);
    query.exec();

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 "
         "WHERE unit_imei=? AND ack=FALSE AND fmi_packet_id IN (?)";
    query.prepare(sql);
    query.bindValue(0, data.imei.data());
    query.bindValue(1, packets_list);
    query.exec();

To copy to clipboard, switch view to plain text mode 

Postgresql log spits this out:

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

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

[ChrisW67]

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())

QList<quint16> packet_list_hex = QList<quint16>() << 1 << 2 << 3;
QStringList  entries;
foreach(quint16 i, packet_list_hex)
    entries << QString::number(i);
 
sql = QString( "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 "
   "WHERE unit_imei=? AND ack=FALSE AND fmi_packet_id IN (%1)" )
   .arg(entries.join(", "));
query.prepare( sql );
...

QList<quint16> packet_list_hex = QList<quint16>() << 1 << 2 << 3;
QStringList  entries;
foreach(quint16 i, packet_list_hex)
    entries << QString::number(i);

sql = QString( "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 "
   "WHERE unit_imei=? AND ack=FALSE AND fmi_packet_id IN (%1)" )
   .arg(entries.join(", "));
query.prepare( sql );
...

To copy to clipboard, switch view to plain text mode 

[pdoria]

Useful and elegant solution Chris! Thank you very much!