Calling a Stored Procedure From Database Using XI.

[former_member644654]

Hi,

Please help in writing a UDF for these fields in mapping, which I need for the object Iu2019m doing currently.

The fields are like this:

1) batch_no = "TRUNC((GetMaxObjid('x_txn_sap_parts')-POWER(2,28))/5000)+1",

2) lot_id = "TRUNC((GetMaxObjid('x_txn_sap_parts')-POWER(2,28))/500)+1",

3) How to use JDBC connection factory class in the UDF.

Some logic I can provide which I know i.e. Power (2, 28) means 2 to the power of 28 (2 multiplied 28 times), Trunc means truncate, X_TXN_SAP_Parts is a database table.The Target fields are Batch_no, lot_id & Objid.Actually, objid is mapped initially to a source field i.e. Object ID and in this function it is only being used for the calculation of the batch_no.

I've tried to use this code but still I'm unable to execute the mapping.

import java.util.*;

import com.sap.aii.mapping.lookup.*;

import com.sap.aii.mapping.lookup.*;

DataBaseAccessor accessor = null;

DataBaseResult JDBCOutPayload = null;

String BusinessSystem="clarify_dev_bizsys";

//give your business system having channel name

String CommunicationChannel="JDBC_TO_CDEV";

//give your channel name

String InputPayload= " select X_TXN_PRE_SITE_XFACE.nextval from dual;";

//give your sql query

try {

Channel channel =

LookupService.getChannel(BusinessSystem,CommunicationChannel);

accessor = LookupService.getDataBaseAccessor(channel);

DataBaseResult resultSet = accessor.execute(InputPayload);

for(Iterator rows = resultSet.getRows();rows.hasNext();){

Map rowMap = (Map)rows.next();

Object cValue = rowMap.get("batchno");

//field name of field required , as in database

}

catch (Exception e) {}

finally {

if (accessor != null)

accessor.close();

}

result.addValue((String)cValue);

}

--> I don't know what are the parameters to be used and how to be used in the UDF because this is the first time I'm writing a UDF.

--> The problem in using this query is that both OBJID & BatchNo. are on the target side and the value for the OBJID is retrieved by a SELECT query from the database.

Kindly help me how to resolve this query of mine.

Thanks in Advance.

Sreedhar.

[former_member644654]

opening a new thread

[Former Member]

give the details of your exace scenario, I have done many scenarios of this kind with s.p.-jdbc,

[former_member644654]

Hi,

The scenario is like I've to get a field (OBJID) value after using a SELECT statement from the database and use the same value for calculating another field values which is BATCHNO & LOTID.

OBJID, BATCHNO and LOTID are from the same database.

Thats all I was provided as the input.

Do let me know if you need anymore information. I'll try to give as much input to the extent I know.

Sreedhar.

[former_member644654]

Hi,

Is it possible to call a Stored Procedure from XI during Message Mapping?