Calling a Stored Procedure From Database Using XI.

[former_member644654]

Hi,

With reference of my previous I would like to give an example.

I've a stored procedure like this:

CREATE OR REPLACE FUNCTION GetObjid (tableName IN VARCHAR2)

RETURN NUMBER

AS

PRAGMA AUTONOMOUS_TRANSACTION;

nextObjid NUMBER;

typeID NUMBER;

BEGIN

SELECT type_id INTO typeId FROM ADP_TBL_NAME_MAP WHERE type_name = tableName;

UPDATE ADP_TBL_OID SET obj_num = obj_num + 1 WHERE type_id = typeId ;

SELECT obj_num + POWER(2,28) INTO nextObjid FROM ADP_TBL_OID WHERE type_id = typeId;

COMMIT;

RETURN nextObjid;

EXCEPTION WHEN OTHERS THEN

RAISE_APPLICATION_ERROR(-20001,'Error generating objid...' );

END;

/

I just wanted to know how can we call this stored procedure in the JDBC receiver.

Thanks in Advance.

Sreedhar.

[former_member644654]

Problem is with the API

[former_member187339]

Hi,

The receiver Data type should be

<StatementName>
<storedProcedureName action=u201D EXECUTEu201D>
    <table>GetObjid </table>
<param1 isInput=u201Dtrueu201D type=VARCHAR>tableName</param1>
</storedProcedureName > 
</StatementName>

You don't have any OUT parameter, if you are expecting any output from SP you need to use OUT parameter too

check this link http://help.sap.com/erp2005_ehp_04/helpdata/EN/22/b4d13b633f7748b4d34f3191529946/frameset.htm

Regards

suraj

[former_member644654]

Hi,

Thank you for your reply.

Could you provide me the steps how to do it as I've never called Stored procedures. Is there anything to be done both in the Design & Configuration. Kindly provide the steps to call the stored procedure.

Sreedhar.

[former_member187339]

Hi Sreedhar,

It is pretty much same as calling a DB table.

The only difference is in the data type for receiver. Make sure you have a data type like that and you can go ahead

Lot of blogs are available for your help

http://www.sdn.sap.com/irj/scn/weblogs;jsessionid=%28J2EE3417100%29ID0608857850DB0080670479342076069...

http://www.sdn.sap.com/irj/scn/weblogs;jsessionid=%28J2EE3417100%29ID0608857850DB0080670479342076069...

http://www.sdn.sap.com/irj/scn/weblogs;jsessionid=%28J2EE3417100%29ID0608857850DB0080670479342076069...

Regards

suraj

[former_member644654]

Hi Suraj,

The problem in my case is I've only 1 field for which a stored procedure is provided and rest of the fields are having 1-to1 mapping. Now my doubt while creating the data type how do I create it?

Sreedhar.

[former_member187339]

Hi Sreedhar,

I didnt get your doubt, but there was slight change in structure

<StatementName>

<storedProcedureName action=u201D EXECUTEu201D>

<table>GetObjid </table>

<tableName isInput=u201Dtrueu201D type=VARCHAR></tableName>

</storedProcedureName >

</StatementName>

And in mapping you will

1. map the attribute action to Constant EXECUTE

2. Map field table with constant GetObjid

3. Map attribute isInput of field tableName to constant true

4. Map field tableName with value (whic you are getting from source)

Regards

Surja

[former_member644654]

Hi Suraj,

My Doubt is how to create the data type for the fields when only one field OBJID is called from a stored procedure.

My fields are

X_CARRIER_NO

X_CARRIER

X_SALES_OFFICE

X_SALES_GROUP

OBJID

X_CURRENCY

X_PRICE_GROUP

X_INCOTERM

X_FRIEGHT_DESC

X_FRIEGHT_LOCATION

X_CUSTOMER_CREDIT

Out of all these fields only OBJID is retrieved from a stored procedure but rest of the fields are not.

So how do I create the data type for the rest of the fields?

I hope you understood my doubt.

Sreedhar.

[former_member187339]

Hi Sreedhar,

I guess all these fields are coming from source side? If yes then make the DT as I mentioned and map only the OBJID field

if you have already defined a target sture with all the field then i will suggest you to use two mappings in Interface mapping,

One which will transform the source fields to target and second which will take map only OBJID to DB structure

BTW your SP is in receiver side right????

IF not then all this will not work.. Also you need to tell me from where you are getting the other fields!!!!

Regards

suraj

[former_member644654]

Hi Suraj,

All the fields are received from the RFC and only OBJID is getting the putput from the Receiver i.e. JDBC .

Sreedhar.

[Former Member]

hi sreedhar,

can you explain your requirement little bit clearly?

i mean what i the sender and what is the receiver and little knowledge about your mapping requirement

Thanks & regards

srinivas

[former_member644654]

Hi Srinivas,

The requirement is like I've a set of 10 fields which I receive from the RFC apart from that there is one more field i.e. OBJID which generates the sequence from the database by selecting the table. They want that I should also populate that field OBJID in the output along with the other fields.

For retrieving that field they have provided 2 options either calling that field using a select statement or by calling a stored procedure.

1st Option:

I've written a UDF for the select statement but it is showing an error i.e. "Exception during the payload. Problem when calling an adapter by using communcation channel JDBC_TO_CDEV (Party: Service:) XI AF API call failed.

2nd Option:

Calling the Stored Procedure: I don't know how to call and create the data type for all the fields including the OBJID in the Data Type and where to call the Stored Procedure provided to me.

My client wants me to use the 1st option only.

UDF which I've written is:

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("clarify_dev_bizsys","JDBC_TO_CDEV");

accessor = LookupService.getDataBaseAccessor(channel);

DataBaseResult resultSet = accessor.execute(InputPayload);

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

{

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

}

result.addValue("rowMap.next()");

}

catch(Exception e)

{

result.addValue(e.getMessage()) ;

}

I hope you understood my requirement.

Sreedhar.

Edited by: Sreedhar Av on Oct 8, 2009 3:24 PM

[former_member187339]

Hi Sreedhar,

first approach of doing a lookup using JDBC adapter was the correct method. Don't go for Stored procedures.

Check the link which Manjusha has given you in the previous thread that will help you

Regards

Suraj

[former_member644654]

Hi Suraj,

When I tried to use the code like this:

String Query = " ";

Channel channel = null;

DataBaseAccessor accessor = null;

DataBaseResult resultSet = null;

// Build the Query String

Query = "select X_TXN_PRE_SITE_XFACE.nextval from dual;";

try{

//Determine a channel, as created in the Configuration

channel = LookupService.getChannel("clarify_dev_bizsys","JDBC_TO_CDEV");

//Get a system accessor for the channel. As the call is being made to an DB, an DatabaseAccessor is obtained.

accessor = LookupService.getDataBaseAccessor(channel);

//Execute Query and get the values in resultset

resultSet = accessor.execute(Query);

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

{

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

result.addValue((String)rowMap.get("rowMap.next()"));

}

}

catch(Exception ex){

result.addValue(ex.getMessage())

}

finally{

try{

if (accessor!=null) accessor.close();

}

catch(Exception e){

result.addValue(e.getMessage())

}

}

It is giving some errors like:

1) /usr/sap/XIX/DVEBMGS03/j2ee/cluster/server0/./temp/classpath_resolver/Map1a4f4560b41111debe02001560de7906/source/com/sap/xi/tf/_MM_CLARIFY_MODELS_.java:147: ';' expected }

2) /usr/sap/XIX/DVEBMGS03/j2ee/cluster/server0/./temp/classpath_resolver/Map1a4f4560b41111debe02001560de7906/source/com/sap/xi/tf/_MM_CLARIFY_MODELS_.java:155: ';' expected }

3) /usr/sap/XIX/DVEBMGS03/j2ee/cluster/server0/./temp/classpath_resolver/Map1a4f4560b41111debe02001560de7906/source/com/sap/xi/tf/_MM_CLARIFY_MODELS_.java:160: illegal start of expression public static void main(String[] args) throws Exception{/!_$ClNRep_/_MM_CLARIFY_MODELS_ st = new /!_$ClNRep_/_MM_CLARIFY_MODELS_(); st.testExecute(); }

4) /usr/sap/XIX/DVEBMGS03/j2ee/cluster/server0/./temp/classpath_resolver/Map1a4f4560b41111debe02001560de7906/source/com/sap/xi/tf/_MM_CLARIFY_MODELS_.java:161: ';' expected }

5) /usr/sap/XIX/DVEBMGS03/j2ee/cluster/server0/./temp/classpath_resolver/Map1a4f4560b41111debe02001560de7906/source/com/sap/xi/tf/_MM_CLARIFY_MODELS_.java:162: '}' expected ^

What should I do rectify these errors?

Sreedhar.

[former_member187339]

Hi Sreedhar,

Please copy this code and replace with your old one and see. I have done a few changes in this

String Query = " ";
Channel channel = null;
DataBaseAccessor accessor = null;
DataBaseResult resultSet = null;

// Build the Query String
Query = "select X_TXN_PRE_SITE_XFACE.nextval from dual";

try{

//Determine a channel, as created in the Configuration
channel = LookupService.getChannel(clarify_dev_bizsys,JDBC_TO_CDEV);

//Get a system accessor for the channel. As the call is being made to an DB, an DatabaseAccessor is obtained.
accessor = LookupService.getDataBaseAccessor(channel);

//Execute Query and get the values in resultset
resultSet = accessor.execute(Query);

for(Iterator rows = resultSet.getRows();rows.hasNext();) {
Map rowMap = (Map)rows.next();
result.addValue((String)rowMap.get(rowMap.next()));
 }
}
catch(Exception ex){
result.addValue(ex.getMessage());
}

finally{
try{
if (accessor!=null) accessor.close();
}
catch(Exception e){
result.addValue(e.getMessage())
}
}

Regards

Suraj

Edited by: S.R.Suraj on Oct 8, 2009 9:52 AM

[former_member644654]

Hi Suraj,

Thank you very much for correcting the errors.

I've changed the code and now it is showing compilation success but in the output of the field it is showing Eception during the processing the payload. Problem when calling an adapter using communication channel XI API Adapter failed.

What has to be done to rectify this error?

Sreedhar.

[former_member187339]

Hi Sreedhar,

>>Thank you very much for correcting the errors.

Great that I was helpful in correcting your error. I would like to say one more thing can you please close all the previous thread by marking them as answered or pointing to this thread. so that in future if someone get this type of error they can directly see this post and get it resolved

Now coming to your next problem. Can you tell how you got this error? Did you get while testing the message mapping or end to end scenario testing?

Also what does the jdbc receiver adapter log say? Hope you are using receiver jdbc adapter

Regards

Suraj

[former_member644654]

Hi Suraj,

I've closed this thread already.

And I got the error while testing in Message Mapping.

Sreedhar.

[Former Member]

Hi Suraj,

I am also trying to do a DB look up and facing the same problem. I am getting one error same as Sreedhar. The error is........

/usr/sap/XIX/DVEBMGS03/j2ee/cluster/server0/./temp/classpath_resolver/Map1a4f4560b41111debe02001560de7906/source/com/sap/xi/tf/_MM_XXXXXXXXXXXXX_.java:160: illegal start of expression public static void main(String[] args) throws Exception{/!_$ClNRep_/_MM_CLARIFY_MODELS_ st = new /!_$ClNRep_/_MM_XXXXXXXXXXXXX_(); st.testExecute(); }

Please help me out in this regard.

It's really urget......

Thanks :

Ram.