When I run the script from perl, it is throwing the below error:
SELECT create_bDBD::Pg::st execute failed: ERROR: cannot insert multiple commands into a prepared statement at /opt/ETL/perlLib/dlLib.pl line 77. dcprodidx();
It was working fine in another environment with the same code. Only difference was the perl version is 5.008005 and on the new server where it is failing is 5.010001.
sub executeSQL
{
my $dbHandle = shift;
my $sql = shift;
$query = $dbHandle->prepare( ${sql} )or throw Error::Simple "[ERROR] (database): Cannot prepare SQL statement: ${sql}. $DBI::errstr\n";
$result = $query->execute or throw Error::Simple "[ERROR] (database): Data load error - Cannot execute SQL statement: ${sql}. $DBI::errstr\n" ;
$query->finish();
# return 1; ## return gracefully. Ungracefully will be "return 0"
return $result;
}
SQL query that is failing:
SELECT drop_bdcprodidx();
/* Update existing data */
update bdc_products
set
description=b.description,
page_type=b.page_type,
keyf_category_home=b.keyf_category_home,
giftwrappable=b.giftwrappable,
action_publish=b.action_publish,
action_viewable=b.action_viewable,
keyf_syndicate=b.keyf_syndicate,
sss_base_fee_exempt_flag=b.sss_base_fee_exempt_flag,
sss_surcharge_fee=b.sss_surcharge_fee,
dvm_d=b.dvm_d,
dvm_v=b.dvm_v,
division=b.division,
keyf_images_primary=b.keyf_images_primary,
option4_flag=b.option4_flag,
keyf_producttype=b.keyf_producttype,
keyf_project=b.keyf_project,
option3_flag=b.option3_flag,
xclass=b.xclass,
archive_flag=b.archive_flag,
pricing_ticket_firstsenddate=b.pricing_ticket_firstsenddate,
pricing_override_14day=b.pricing_override_14day,
final_approval_copy=b.final_approval_copy,
final_approval_photo=b.final_approval_photo,
final_approval_data=b.final_approval_data,
final_approval_merchant=b.final_approval_merchant,
final_approval_buyer=b.final_approval_buyer,
pricing_override_savings=b.pricing_override_savings,
option2_flag=b.option2_flag,
gmm=b.gmm,
pricing_override_onsaleratio=b.pricing_override_onsaleratio,
aol_featured=b.aol_featured,
aol_flag=b.aol_flag,
aol_isvalid=b.aol_isvalid,
inc_lastsenddate=b.inc_lastsenddate,
firstlivedate=b.firstlivedate,
SCENE7 = b.SCENE7,
AOL_ITEM_CATEGORIES = b.AOL_ITEM_CATEGORIES,
DVM_M = b.DVM_M,
action_publish_flag_wc=b.action_publish_flag_wc,
ACTION_VIEWABLE_WC=b.ACTION_VIEWABLE_WC,
item_number=b.item_number,
SUBCLASS = cast (b.SUBCLASS as int4),
CADENCE= b.CADENCE,
date_turn_off = b.date_turn_off,
date_turn_on = b.date_turn_on,
PRICING_TICKETESTABLISHED_DATE= b.PRICING_TICKETESTABLISHED_DATE,
FINAL_APPROVAL_FULL= b.FINAL_APPROVAL_FULL,
KEY_ITEM_FLAG= b.KEY_ITEM_FLAG,
last_update = CURRENT_TIMESTAMP
from staging.staging_bdc_products b where
b.keyp_products = bdc_products.keyp_products ;
/* Insert new data */
INSERT INTO bdc_products(
keyp_products, description, page_type,
keyf_category_home, giftwrappable, action_publish,
action_viewable, keyf_syndicate, sss_base_fee_exempt_flag,
sss_surcharge_fee, dvm_d,
dvm_v, division, keyf_images_primary,
option4_flag, keyf_producttype, keyf_project,
option3_flag, xclass, archive_flag,
pricing_ticket_firstsenddate, pricing_override_14day,
pricing_today_retail,
final_approval_copy, final_approval_photo,
final_approval_data, final_approval_merchant,
final_approval_buyer,
pricing_override_savings, option2_flag,
gmm, pricing_override_onsaleratio,
aol_featured,aol_flag,aol_isvalid,inc_lastsenddate,
firstlivedate, SCENE7, AOL_ITEM_CATEGORIES, DVM_M, action_publish_flag_wc,
ACTION_VIEWABLE_WC, item_number,
SUBCLASS, CADENCE,
date_turn_off, date_turn_on,
PRICING_TICKETESTABLISHED_DATE, FINAL_APPROVAL_FULL, KEY_ITEM_FLAG,
last_update
)
SELECT b.keyp_products, b.description, b.page_type,
b.keyf_category_home, b.giftwrappable, b.action_publish,
b.action_viewable, b.keyf_syndicate, b.sss_base_fee_exempt_flag,
b.sss_surcharge_fee, b.dvm_d,
b.dvm_v, b.division, b.keyf_images_primary,
b.option4_flag, b.keyf_producttype, b.keyf_project,
b.option3_flag, b.xclass, b.archive_flag,
b.pricing_ticket_firstsenddate, b.pricing_override_14day,
b.pricing_today_retail,
b.final_approval_copy, b.final_approval_photo,
b.final_approval_data, b.final_approval_merchant,
b.final_approval_buyer,
b.pricing_override_savings, b.option2_flag,
b.gmm, b.pricing_override_onsaleratio,
b.aol_featured,b.aol_flag,b.aol_isvalid,b.inc_lastsenddate,
b.firstlivedate, b.SCENE7, b.AOL_ITEM_CATEGORIES, b.DVM_M,b.action_publish_flag_wc,
b.ACTION_VIEWABLE_WC, b.item_number,
cast (b.SUBCLASS as int4), b.CADENCE,
b.date_turn_off, b.date_turn_on,
b.PRICING_TICKETESTABLISHED_DATE, b.FINAL_APPROVAL_FULL, b.KEY_ITEM_FLAG
,current_timestamp
FROM staging.staging_bdc_products b
LEFT OUTER JOIN bdc_products p on b.keyp_products = p.keyp_products
WHERE p.keyp_products is null;
SELECT create_bdcprodidx();
UPDATE bdc_products
set pricing_daysonsale_currentfy = totaldays
FROM product_onsalesdays
WHERE keyf_products = keyp_products
;`
Could you please help on how to fix this issue? DO we need to split the sql?
Thanks Ajay
