Problem with QSqlQuery::prepare and "WHERE ... IN (...)" postgresql statments
The code:
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
};
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+=",";
}
Code:
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:
Code:
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
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())
Code:
QList<quint16> packet_list_hex = QList<quint16>() << 1 << 2 << 3;
foreach(quint16 i, packet_list_hex)
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 );
...
Re: Problem with QSqlQuery::prepare and "WHERE ... IN (...)" postgresql statments
Useful and elegant solution Chris! ;) Thank you very much! :)