Skip to Content
0
Aug 28, 2023 at 12:28 PM

Calling Stored Procedure from SAP BODS throwing error

51 Views

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