Registration

Dear SAP Community Member,
In order to fully benefit from what the SAP Community has to offer, please register at:
http://scn.sap.com
Thank you,
The SAP Community team.
Skip to end of metadata
Go to start of metadata

Since there is no support of JDBC for VC 7.1 SP6, one needs to create Web Services to be able to use it. Unfortunately, exposing the stored procedures as web service directly from MS SQL 2005 is also not an option.

Here's How to (using NWDS 7.1):

1. Download the sqljdbc.jar

2. In NWA (NetWeaver Administration) http://<servername>:50000/webdynpro/dispatcher/sap.com/tc~lm~itsam~co~ui~nwa~localnavigation~wd/NWAApp -> Select Configuration management -> Infrastructure -> Application Resources -> Create New Resource -> Deploy New JDBC Driver (screenshot attached for JDBC Custom Datasource) then add the downloaded driver.

2. In NWA (NetWeaver Administration) http://<servername>:50000/webdynpro/dispatcher/sap.com/tc~lm~itsam~co~ui~nwa~localnavigation~wd/NWAApp -> Select Configuration management -> Infrastructure -> Application Resources -> Create New Resource -> New JDBC Custom Datasource

3. Fill in the details for the custom datasource. Make sure you have rights to the MSSQL database to be able to connect. Also, give the alias name in the JDBC Datasource aliases. Click on save. Make sure the state is green.

4. Now going to NWDS, Create a new EJB Project (File->New->Other -> EJB)

5. New EJB Project wizard Next-> Next-> deselect the check box "Create an EJB client jar ........" click finsh

6. Create a package - "connection" -> in ejbModule -> then Create a class -> DAOFactory (This is your connection to the JDBC driver ->MSSQL)

public class DAOFactory {
public static final String DATASOURCENAME="jdbc/MY_PRODIN_DB"; // This the datasource alias
private static DAOFactory instance = null;

private DataSource ds;

/* Constructor for initialization
*/
private DAOFactory(){
}

public synchronized static DAOFactory getInstance()throws SystemException{
 if(instance == null){
instance = new DAOFactory();
 }
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; return instance;
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp; }
&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp; public Connection getConn() throws Exception {
&nbsp;&nbsp; &nbsp; Connection con = null;
&nbsp;&nbsp; &nbsp;try {
&nbsp;&nbsp; &nbsp;&nbsp;
&nbsp;&nbsp; &nbsp;&nbsp;javax.naming.InitialContext ctx = new javax.naming.InitialContext();
&nbsp;&nbsp; &nbsp;&nbsp;ds = (javax.sql.DataSource) ctx.lookup(DATASOURCENAME);
&nbsp;&nbsp; &nbsp;&nbsp;//out.println("Getting connection<br>");
&nbsp;&nbsp; &nbsp;&nbsp;con = ds.getConnection();
&nbsp;&nbsp; &nbsp;&nbsp;
&nbsp;&nbsp; &nbsp;&nbsp;} catch (Exception e) {
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;System.err.println("SQLException: Error in Connection Exception " + e.getMessage());
&nbsp;&nbsp; &nbsp;&nbsp;}
&nbsp;&nbsp; &nbsp;&nbsp;
&nbsp;&nbsp; &nbsp;&nbsp;return con;

&nbsp;&nbsp; }
}


7. Create a package - "artifacts" -> in ejbModule ->then  Create a class ->   Parts  (This is your table with the fields  in the MS SQL which you wish to access via a stored procedure)

public class Parts implements Serializable {

&nbsp;/*\*
&nbsp; *
&nbsp; \*/
&nbsp;private static final long serialVersionUID = 1L;
&nbsp;private String partid;
&nbsp;private String articleid;
&nbsp;private String engineid;
&nbsp;private String shipid;
&nbsp;private String desc;
&nbsp;
&nbsp;public Parts() {
&nbsp;&nbsp;super();
&nbsp;}
&nbsp;public String getPartid() {
&nbsp;&nbsp;return this.partid;
&nbsp;}

&nbsp;public void setPartid(String partid) {
&nbsp;&nbsp;this.partid = partid;
&nbsp;}

&nbsp;public String getArticleid() {
&nbsp;&nbsp;return this.articleid;
&nbsp;}

&nbsp;public void setArticleid(String articleid) {
&nbsp;&nbsp;this.articleid = articleid;
&nbsp;}

&nbsp;public String getEngineid() {
&nbsp;&nbsp;return this.engineid;
&nbsp;}

&nbsp;public void setEngineid(String engineid) {
&nbsp;&nbsp;this.engineid = engineid;
&nbsp;}

&nbsp;public String getShipid() {
&nbsp;&nbsp;return this.shipid;
&nbsp;}

&nbsp;public void setShipid(String shipid) {
&nbsp;&nbsp;this.shipid = shipid;
&nbsp;}


&nbsp;public String getDesc() {
&nbsp;&nbsp;return this.desc;
&nbsp;}

&nbsp;public void setDesc(String desc) {
&nbsp;&nbsp;this.desc = desc;
&nbsp;}


&nbsp;public Parts(String shipId, String engineId, String partId, String articleId, String Desc) {
&nbsp;&nbsp;shipid = shipId;
&nbsp;&nbsp;&nbsp;&nbsp; engineid = engineId;
&nbsp;&nbsp;partid = partId;
&nbsp;&nbsp;articleid = articleId;
&nbsp;&nbsp;desc = Desc;
&nbsp;}\\

}





8. Create a package - "service" -> in ejbModule ->  then Create a Stateless session bean, with the options remote and local checked

In the session bean:

&nbsp;

public class SQLSMSBean implements SQLSMSRemote, SQLSMSLocal {
&nbsp;private java.sql.Connection&nbsp; conn = null;
&nbsp;
&nbsp;
&nbsp;public Parts\[\] getParts(final String pSqlDb, final String pShipId, final String pEngineId, final String pPartId, final String pDesc) throws Exception {
&nbsp;&nbsp;&nbsp;
&nbsp;
&nbsp;&nbsp; ArrayList<Parts> list = new ArrayList<Parts>();

&nbsp;&nbsp;&nbsp;&nbsp; ResultSet rs = null;
&nbsp;&nbsp;&nbsp;&nbsp; PreparedStatement stmt= null;

&nbsp;&nbsp;&nbsp;&nbsp; try{
&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;DAOFactory factory = DAOFactory.getInstance();
&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;conn = factory.getConn();
&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;} catch (Exception e){
&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;System.err.println("Connection in Parts failed: " \+e.getMessage());
&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;}

&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;stmt = conn.prepareStatement(
&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; "SELECT shipid, engineid, partid, articleid, \[desc\] FROM \[dbo\].\[xxx_parts\]" +
&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; "WHERE (shipid LIKE ?) AND (engineid LIKE ?) AND (partid LIKE ?) AND " +
&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; "(\[desc\] LIKE ?)");
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; try {

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; String Ship= "%"+pShipId+"%";
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; String Engine ="%"+pEngineId+"%";
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; String Part;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; if (pPartId==null){
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;Part = "%";
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; } else {
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;Part =&nbsp; pPartId+"%";
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; String Desc;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; if (pDesc==null){
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;Desc = "%";
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }else {&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;Desc = "%"+pDesc+"%";
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; stmt.setString(1, Ship );
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; stmt.setString(2, Engine );
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; stmt.setString(3, Part);
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; stmt.setString(4, Desc);

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; rs = stmt.executeQuery();

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; try {

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; while (rs.next()) {
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Parts data = new Parts(
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; rs.getString(1),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; rs.getString(2),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; rs.getString(3),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; rs.getString(4),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; rs.getString(5)
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; );

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; list.add(data);
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; } finally {
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; rs.close();

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; return (Parts\[\]) list.toArray(new Parts\[\]&nbsp; {
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; });
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; } finally {
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; stmt.close();
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; conn.close();
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; conn=null;
&nbsp;&nbsp;&nbsp; }
&nbsp;}

9. After creating methods that you would like to expose as web service -> Add the methods to the local and remote interfaces

10. right click the bean and select Web services -> create web service -> Bring the slider to develop -> select the option "specify exiting interface" -> You would see the URL of the WSDL-> select the remote interface of your bean -> you would see the method selected -> finish

11. Add your project to the server for deployment, after successful deployment , you can test the WSDL and use it in VC.

Good Luck,

Dharmi

p.s any comments or pointers of improvement are welcomed at dharmi.tanna@gmail.com

  • No labels