Issue in JDBC Receiver with Optional Parameter/Default value in Stored Proc

[Former Member]

I have an Stored Procedure [SQL 2005] that will insertu2019s the Employee No, Name and Age into the table. This stored procedure having 3 parameters named @Empid,@Name,@Age and all are optional Parameter/ having default value. Please refer the stored procedure below

Stored Procedure:

Create Procedure SP_SAPPI_EMPDETAILS(@Empid varchar(20)='102',@Name varchar(50)='Kannan',@Age int=25)

as

Begin

insert into SAPPI_Empdet values(@empid,@Name,@Age)

End

In SAP PI [Using SQL 2005]

Note : we are using SAP PI 7.1

We are using JDBC Receiver channel with Stored Procedure for inserting the source data to the SQL Table . refer the mapping table below

SP_SAPPI_EMPDETAILS

Empid 0-1 Empid 0-1

Name 0-1 Name 0-1

Age 0-1 Age 0-1

Source Message:

<ns0: MT_EmpDetails xmlns:ns0="http://infosys.com/is/poc2">

<Details>

<Empid>123</Empid>

<Name>ram</Name>

<Age>26</Age>

</Details>

<Details>

<Name>Muthu</Name>

<Age>28</Age>

</Details>

<Details>

<Age>27</Age>

</Details>

</ns0:MT_POC2_0004_DETAILS>

Result:

Empid Name Age

123 Ram 26

Muthu 28 25

27 Kannan 25

In first Details I am passing all the 3 elements to the target , so the result is coming properly.

In second Details

<Details>

<Name>Muthu</Name>

<Age>28</Age>

</Details>

I am not passing the Empid element, so It should take the default value for that in the SP, but the second parameter value is going to the first parameter and also third for second, so the third is getting inserted as default value.

In Third Details

<Details>

<Age>27</Age>

</Details>

I am passing only the Age element , so it should take default value for 1nd and 2rd but its inserting the 3rd parameter value In 1st and all other taking their default value.

Please any one can help to resolve this issue?

Thanks in advance!!!

[Former Member]

Kannan,

In mapping use the function "MapWithDefault". In the properties of this function enter the default value if there is no value coming from source side.

[Former Member]

Hi Jaishankar,

Thanky you, but i want to specify the default value in the Stored Procedure level. Refer the Stored Procedure. if the source element is not there then it should take the default value that is specified in the stored procedure.

The problem is if we are not passing the source element for the first parameter , the second parameter values is assigned to the first parameter in SQL. last one is taking default value.

[Former Member]

Do you mean you need help changing your SP? If so, I guess I could be fo little help. Am not very good in DB

[Former Member]

Jaishankar,

SP is correct and the PI calling the SP with Optional parameter is not in the same way. please refer my soruce message and also result for the same.

[Former Member]

Kannan,

Am sorry. I misread your post. You use MapWithDefault funtion. This will ensure when the source has no incoming values, default values are correctly assigned. You need to use this function before mapping each node.

Eg:

empid (source) -->MapwithDefault -->empid (target)

Here give the default value for empid in the properties of MapWithDefault.

Repeat the steps for other nodes too..

Regards,

Jaishankar

[Former Member]

Jaishankar,

we dont want to specify the default value at PI level, we need it in the SP [SQL] level, refer the SP

Create Procedure SP_SAPPI_EMPDETAILS (@Empid varchar(20)='102',@Name varchar(50)='Kannan',@Age int=25) as

Begin

insert into SAPPI_Empdet values(@empid,@Name,@Age)

End

I have mentioned the Default value for the parameters, if the parameter is not coming then it will take default value for that.

[Former Member]

can u try this way

in ur source strcut make all 3 elements 1...1 and do the mapping so in ur target strcut u always

get all the 3 elements

for e.g.

<Empid></Empid>

<Name></Name>

<Age>33</Age>