cancel
Showing results for 
Search instead for 
Did you mean: 

Create oData service using oracle Store procedure

CRVMANISH
Contributor
0 Kudos

Hello Experts,

I need to create oData service from oracle store procedure,

what are the steps to do it.

We are using SMP Integration gateway.

Please Suggest.

Regards

Manish

Accepted Solutions (0)

Answers (2)

Answers (2)

0 Kudos

Moved to SMP Developer Center from SAP for Mobile as more appropriate topic for that community.

Regards, Mike (Moderator)

CRVMANISH
Contributor
0 Kudos

Hello Experts,

Here is my code to join 2 tables , in debug it does not give error, but it gives on only one table output not merged output.

Please suggest.


function processRequestSQL(message) {

  //Import statements

  importPackage(com.sap.gateway.ip.core.customdev.util);

  importPackage(java.util);

  importPackage(org.apache.olingo.odata2.api.edm);

  importPackage(com.sap.gateway.core.ip.component.commons);

  importPackage(org.apache.olingo.odata2.api.uri);

  importPackage(com.sap.gateway.ip.core.customdev.logging);

  //Get the SQL statement contained in message body

  var sqlStatement = message.getBody();

  var uriInfo = message.getHeaders().get(ODataExchangeHeaderProperty.UriInfo.toString());

  var map = new HashMap();

  map = uriInfo.getCustomQueryOptions();

  //Create the query and sub query

  var JOIN_WO_WHERE = "INNER JOIN ZONE ON AREA.AREA_ID = ZONE.AREA_ID ";

  var WHERE = " WHERE ";

  var JOIN = " INNER JOIN ZONE ON AREA.AREA_ID = ZONE.AREA_ID ";

  //Modify the SQL statement

  var modifiedSqlStatement = "";

  var entitySet = uriInfo.getStartEntitySet();

  var odataMethod = message.getHeaders().get(ODataExchangeHeaderProperty.ODataMethod.toString());

  modifiedSqlStatement += sqlStatement;

  if (odataMethod.toString() == "GET_FEED") {

  entitySetName = entitySet.getName();

  if (entitySetName == "AREA") {

  if (sqlStatement.indexOf("WHERE") != -1

  || sqlStatement.indexOf("where") != -1) {

  modifiedSqlStatement += JOIN_WO_WHERE;

  } else {

  // modifiedSqlStatement += WHERE;

  modifiedSqlStatement += JOIN;

  }

  }

  }

  //Set the message body back with the modified SQL statement

  message.setBody(modifiedSqlStatement);

  //Logger

  log.logErrors(LogMessage.TechnicalError, "This is first log");

//Set the message body back with the modified SQL statement

  message.setBody(modifiedSqlStatement);

  //Logger

  log.logErrors(LogMessage.TechnicalError, "This is first log");

-------------------End Sample Implementation---------------------------------------

*/

  return message;

}

CRVMANISH
Contributor
0 Kudos


l

Any suggestion on this.


Regards

Manish

Jitendra_Kansal
Product and Topic Expert
Product and Topic Expert
0 Kudos

No stored procedure are currently supported.Check below ref

Regards,

JK

CRVMANISH
Contributor
0 Kudos

Thanks for reply Jitendra.

Can we create SOAP webservice and then will it be converted to oData after deployment.

I mean to say we will develop all logic to consume store procedure in SOAP and then consume using Integration gateway.

Regards

Manish

Jitendra_Kansal
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hi Manish,


Can we create SOAP webservice and then will it be converted to oData after deployment.

I mean to say we will develop all logic to consume store procedure in SOAP and then consume using Integration gateway.

I guess there should not be a problem then. It should work.

Looping to provide more information.

Regards,

JK

bjoernw
Participant
0 Kudos

Hi Manish,

I guess technically it should work. However, it does not seem like a very "lean" solution to me. Can you maybe elaborate what the stored procedure does? Maybe there is a different way to solve oyur needs?

Kind regards

Björn

CRVMANISH
Contributor
0 Kudos

Hello Bjorn,


There are two approaches table based and store procedure based.

1. If we use table based approach then number of database hits increases and load is more on mobile client for business logic as all table has to be queried separately using oData.

2. If we use store procedure based approach then load is more on back end as all the queries will be executed at once.

As store procedure cannot be used directly on SMP that is why we use SOAP.

Please suggest if our approach is correct.

Regards

Manish

CRVMANISH
Contributor
0 Kudos

Hello Experts,

Can you please reply on above question about the approach.

Regards

Manish

bjoernw
Participant
0 Kudos

Hi Manish,

the detail you provided isn't sufficient enough to provide advice for your question .

Just as an idea, using the custom scripting in SMP you can manipulate the select statements that are sent to your JDBC database. Maybe you can write some intelligent scripts that would allow you to query the data more effectively.

Kind regards

Björn

CRVMANISH
Contributor
0 Kudos

Hi Bjoern,

Thanks for reply.

I have 2 tables which are joined using association in oData modeling, I want to use $expand to show output of 2 tables in oData, i tried but it does not give desired output.

Do I need to write custom script to join 2 tables if yes can you give one example.

Regards

Manish