Hi all,
I am trying to call a store procedure from SAP BODS . Below is the script that i am using
sql('D365ReplicaDB','exec hsl.usp_LEGACY_REPORTING_FEED_BeginProcess');
the stored proc is written in mysql developer
<<
DELIMITER $$
CREATE DEFINER=`cdm_ds`@`%` PROCEDURE `PE_SYNC_DELETE`( IN reference_record_id_IN INT, IN attribute_name VARCHAR(100),IN attribute_to_value VARCHAR(100),IN src_table VARCHAR(100),IN global_audit_id_IN INT,IN TABLE_NAME_IN VARCHAR(100),IN COLUMN_NAME_IN VARCHAR(100),IN CHILD_TABLE_NAME_IN VARCHAR(100),IN CHILD_TABLE_COLUMN VARCHAR(100))
BEGIN
## This procedure will take care of all soft deletes that happens to PARENT,LCTN,ACCT_KEY tables.
SET @var = NULL;
## THis sql to find whether the parent table have any child or not.
SET @sql_text = concat('select count(*) INTO @var from ',CHILD_TABLE_NAME_IN,' where ',CHILD_TABLE_COLUMN ,'=',reference_record_id_IN,' and (delete_flag is NULL or delete_flag=0)');
PREPARE stmt FROM @sql_text;
EXECUTE stmt;
SELECT @var;
DEALLOCATE PREPARE stmt;
If (attribute_to_value=1)
THEN
if(@var =0) THEN
##These are the set of sqls needs to execute if parent table is having no child tables.
SET @sql_text = concat('update ',TABLE_NAME_IN,' set delete_flag=1 where ',COLUMN_NAME_IN,'=',reference_record_id_IN );
PREPARE stmt FROM @sql_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
insert into cdm_ops.global_audit(ss_audit_id ,attribute_name ,attribute_from_value ,attribute_to_value ,changed_dttm ,chngd_by ,chngd_type ,src_table ,reference_record_id ,created_dttm ,creatd_by ,updated_dttm ,upd_by ,version ,source ,load_date ,modify_date ,deleted,record_type )
select hana_global_audit_id,attribute_name ,attribute_from_value ,attribute_to_value ,changed_dttm ,chngd_by ,chngd_type ,src_table ,reference_record_id ,created_dttm ,creatd_by ,updated_dttm ,upd_by ,version ,source ,load_date ,modify_date ,deleted,record_type from cdm_st.hana_change_log where
hana_global_audit_id=global_audit_id_IN and reference_record_id=reference_record_id_IN;
insert into CDM_ST.HANA_CHANGE_ARCHIVE ( select * from CDM_ST.HANA_CHANGE_LOG where hana_global_audit_id=global_audit_id_IN and reference_record_id=reference_record_id_IN);
DELETE from CDM_ST.HANA_CHANGE_LOG where HANA_GLOBAL_AUDIT_ID=global_audit_id_IN and reference_record_id=reference_record_id_IN;
ELSE
##These are the set of sqls needs to execute if there is any child
insert into CDM_ST.HANA_CHANGE_ARCHIVE (select * from CDM_ST.HANA_CHANGE_LOG where reference_record_id=reference_record_id_IN and HANA_GLOBAL_AUDIT_ID=global_audit_id_IN);
delete from CDM_ST.HANA_CHANGE_LOG where reference_record_id=reference_record_id_IN and HANA_GLOBAL_AUDIT_ID=global_audit_id_IN;
END IF;
ELSE
SET @sql_text = concat('update ',TABLE_NAME_IN,' set delete_flag=',attribute_to_value,' where ',COLUMN_NAME_IN,'=',reference_record_id_IN );
PREPARE stmt FROM @sql_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
insert into cdm_ops.global_audit(ss_audit_id ,attribute_name ,attribute_from_value ,attribute_to_value ,changed_dttm ,chngd_by ,chngd_type ,src_table ,reference_record_id ,created_dttm ,creatd_by ,updated_dttm ,upd_by ,version ,source ,load_date ,modify_date ,deleted,record_type )
select hana_global_audit_id,attribute_name ,attribute_from_value ,attribute_to_value ,changed_dttm ,chngd_by ,chngd_type ,src_table ,reference_record_id ,created_dttm ,creatd_by ,updated_dttm ,upd_by ,version ,source ,load_date ,modify_date ,deleted,record_type from cdm_st.hana_change_log where
hana_global_audit_id=global_audit_id_IN and reference_record_id=reference_record_id_IN;
insert into CDM_ST.HANA_CHANGE_ARCHIVE ( select * from CDM_ST.HANA_CHANGE_LOG where hana_global_audit_id=global_audit_id_IN and reference_record_id=reference_record_id_IN);
DELETE from CDM_ST.HANA_CHANGE_LOG where HANA_GLOBAL_AUDIT_ID=global_audit_id_IN and reference_record_id=reference_record_id_IN;
End IF;
END$$
DELIMITER ;
>>but when i am executing my job it is failing with the error message .can someone help me where i am going wrong.SQL submitted to ODBC data source <CDM_AMAZON_QA_5.3_Unicode_Driver> resulted in error <[MySQL][ODBC 8.0(w)
Driver][mysqld-5.7.12-log]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version
for the right syntax to use near 'exec cdm_ops.PE_SYNC_DELETE' at line 1>. The SQL submitted is <exec cdm_ops.PE_SYNC_DELETE>.