SDK: Access WIP DB from custom web-service

[Former Member]

Hi,

I am writing a custom web-service that needs to write SQL Query directly against the WIP database.
I need to find parent SFC for a sub-level SFC and that functionality does not seem to be available from supported API's.

Is it possible to get a hint on how the database connection and querying should be done?

Best regards,

Johan

[tim_drury · Accepted solution]

com.sap.me.production.SfcStateService

method: readSfc() returns SfcConfiguration which contains a List<SfcIdHistory> which tells you about the SFC's parents and the reason (split, serialize, etc.)

[Former Member · Accepted solution]

Hi Tim,

Thanks for the answer, but it does not seem to work in this case.

When I search for an SFC with ReadSFC the SFCIdHistory only shows the SERIALIZE activity and also the SFC is the same as the one that I have searched for.

When I say parent, I mean the parent that a sub-level SFC has been assembled into a top-level SFC.

I am working in SAP ME 6.0.4.12.

Br,

Johan

Soap request sent:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:mep="mepapi:com:sap:me:production">

   <soapenv:Header/>

   <soapenv:Body>

      <mep:readSfc>

         <!--Optional:-->

         <mep:Site>SEVA1</mep:Site>

         <!--Optional:-->

         <mep:Request>SFCBO:SEVA1,008HUH</mep:Request>

      </mep:readSfc>

   </soapenv:Body>

</soapenv:Envelope>

Returned result:

<SOAP-ENV:Envelope xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

   <SOAP-ENV:Body>

      <ns3:readSfcResponse xmlns:ns4="http://www.sap.com/me/common" xmlns:ns2="http://www.sap.com/me/production" xmlns:ns3="mepapi:com:sap:me:production">

         <ns3:Response>

            <ns2:sfcRef>SFCBO:SEVA1,008HUH</ns2:sfcRef>

            <ns4:site>SEVA1</ns4:site>

            <ns4:sfc>008HUH</ns4:sfc>

            <ns2:itemRef>ItemBO:SEVA1,3EST000211-8565,08</ns2:itemRef>

            <ns2:shopOrderRef>ShopOrderBO:SEVA1,251944</ns2:shopOrderRef>

            <ns2:statusRef>StatusBO:SEVA1,408</ns2:statusRef>

            <ns2:qty>0</ns2:qty>

            <ns2:qtyDone>0</ns2:qtyDone>

            <ns2:qtyScrapped>0</ns2:qtyScrapped>

            <ns2:priority>500</ns2:priority>

            <ns2:actualCompletionDate>2013-10-31T09:47:07.000+01:00</ns2:actualCompletionDate>

            <ns2:rma>false</ns2:rma>

            <ns2:sfcIdHistoryList>

               <ns2:sfcRef>SFCBO:SEVA1,008HUH</ns2:sfcRef>

               <ns2:sequence>1</ns2:sequence>

               <ns2:reason>RELEASE</ns2:reason>

            </ns2:sfcIdHistoryList>

         </ns3:Response>

      </ns3:readSfcResponse>

   </SOAP-ENV:Body>

</SOAP-ENV:Envelope>

[Former Member · Accepted solution]

Dear Johan,

Can you give it a try with below code.

But please remember thats not the supported way, and u have to keep an eye on this all the time.

SystemBase systemBase = SystemBase.createDefaultSystemBase();
DynamicQuery dynaQuery = DynamicQueryFactory.newInstance();
String query ="your SQL-Statement";

dynaQuery.append(query);

int succeed = systemBase.executeUpdate(dynaQuery);

if (succeed > 0) {

     //success

}

Regards, Ehswar.

[Former Member · Accepted solution]

Hi Ehswar,

Thank you for the suggestion. I already started at looking at the dynamicQuery.

One question I have though.

How do I get the wip db connection string?

Best regards,

Johan

[Former Member · Accepted solution]

Hi Johan,

You can perform standard JNDI lookup to get data source and use standard jdbc calls using Statement and ResultSet.

This approach would be more correct as the Java API will not change, while DynamicQuery and related classes can potentially be changed.

Thanks,
Bakhtiyar

[Former Member · Accepted solution]

Hi Bakhtiyar and thank you for the suggestion,

I have managed to get it working using Statement and Resulset.

I still have to figure out how the code should look for JNDI lookup of WipPool datasource. When I get it to work I have written a string for the data source connection string, but I need code example on how to do the lookup from the Netweaver WipPool data source.

Would it be to much to ask for a small code example to help me in the right way? I am not an experienced java programmer and are basically struggling with every code line

Best regards,

Johan

[Former Member · Accepted solution]

Hi Johan,

Hope this will help:

public void getSites() {
  Connection con = null;
  Statement st = null;
  ResultSet rs = null;
  try {
   DataSource ds = getDataSource();
   con = ds.getConnection();
   st = con.createStatement();
  
   rs = st.executeQuery("SELECT * FROM SITE");
   while (rs.next()) {
    String site = rs.getString("SITE");
    location.debugT("Here is the site from DB " + site);
   }
  

  } catch (NamingException e) {
   location.errorT(e.getMessage());
  } catch (SQLException e) {
   location.errorT(e.getMessage());
  } finally {
   if (rs != null) {
    try {
     rs.close();
    } catch (SQLException e1) {
     location.errorT(e1.getMessage());
    }
   }
   if (st != null) {
    try {
     st.close();
    } catch (SQLException e1) {
     location.errorT(e1.getMessage());
    }
   }
   if (con != null) {
    try {
     con.close();
    } catch (SQLException e1) {
     location.errorT(e1.getMessage());
    }
   }
  }
}


private DataSource getDataSource() throws NamingException {
  Context ctx = new InitialContext();
     DataSource dataSource = (DataSource) ctx.lookup("jdbc/wipPool");
     return dataSource;
}

Thanks,

Bakhtiyar

[Former Member · Accepted solution]

Thank you Bakhtiyar,

Worked perfect to use the WipPool lookup.

Best Regards,

Johan