var var_sql = "UPDATE tbl_offers SET tbl_offer_goods_count = ( \
SELECT AMOUNTGOODS \
FROM ( \
SELECT COUNT(TEMP.offer) AS AMOUNTGOODS \
, TEMP.offer AS OFFERID \
FROM ( \
SELECT DISTINCT ON (tbl_offer_cargo.tbl_offer_cargo_good_id) \
tbl_offer_cargo.tbl_offer_cargo_offerid as offer \
FROM public.tbl_offer_cargo, public.tbl_offers \
WHERE tbl_offers.tbl_offer_id = tbl_offer_cargo.tbl_offer_cargo_offerid \
) TEMP \
WHERE TEMP.offer = ? \
GROUP BY offer \
) TEMP2 \
) WHERE tbl_offers.tbl_offer_id = ?";
var var_params = [tbl_offer_cargo_offerid, tbl_offer_cargo_offerid];
var var_result = plugins.rawSQL.executeSQL("transport_db","tbl_offers",var_sql, var_params);
if(var_result) {
plugins.rawSQL.flushAllClientsCache("transport_db","tbl_offers");
}
1) pass the id as parameter to the query, rather than concatenate.
This will make PG see each or these queries as 'similar' but with different params, rather than numerous unique queries.
From a security point of view, using params will prevent sql injection.
martijn wrote:I get a false result with these changes
var var_sql = "SELECT AMOUNTGOODS \
FROM ( \
SELECT COUNT(TEMP.offer) AS AMOUNTGOODS \
, TEMP.offer AS OFFERID \
FROM ( \
SELECT DISTINCT ON (tbl_offer_cargo.tbl_offer_cargo_good_id) \
tbl_offer_cargo.tbl_offer_cargo_offerid as offer \
FROM public.tbl_offer_cargo, public.tbl_offers \
WHERE tbl_offers.tbl_offer_id = tbl_offer_cargo.tbl_offer_cargo_offerid \
) TEMP \
WHERE TEMP.offer = ? \
GROUP BY offer";
var var_params = [tbl_offer_cargo_offerid];
var var_result = databaseManager.getDataSetByQuery("transport_db",var_sql, var_params,-1);
foundset.tbl_offer_goods_count = var_result.getValue(1,1);
databaseManager.saveData();
Return to Programming with Servoy
Users browsing this forum: No registered users and 12 guests