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_QUER Y,
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+=",";
}


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

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

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

Any hints b4 I revert to using sprintf() ? :D

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 );
...

[pdoria]

Useful and elegant solution Chris! ;) Thank you very much! :)