1 Reply Latest reply on Oct 24, 2018 5:25 PM by Steven Hawkins

    How to fetch data from SOAP wsdl ?

    Srinivas Varma Newbie

      HI all ,

      i am trying to fetch data from wsdl using Teiid Soap Webservice in JAVA . but no data is getting .. below is My JAVA Class method i am using

       

      private static void Method1() {

       

      String procedure2="CREATE VIRTUAL PROCEDURE SoapService_request() RETURNS TABLE (xml_out xml)\r\n" +

      " AS\r\n" +

      " BEGIN\r\n" +

      " SELECT XMLELEMENT(NAME \"tns:FullCountryInfoAllCountries\", XMLNAMESPACES( 'http://www.oorsprong.org/websamples.countryinfo' AS tns) ) AS xml_out;\r\n" +

      " END\r\n" +

      " \r\n" +

      " CREATE VIRTUAL PROCEDURE SoapService_response(xml_in xml NOT NULL) RETURNS TABLE ( sISOCode VARCHAR(2000), sName VARCHAR(2000), sCapitalCity VARCHAR(2000), sPhoneCode INTEGER, sContinentCode VARCHAR(2000), sCurrencyISOCode VARCHAR(2000), sCountryFlag VARCHAR(2000), Languages VARCHAR(2000))\r\n" +

      " AS\r\n" +

      " BEGIN\r\n" +

      " SELECT t.* FROM XMLTABLE(XMLNAMESPACES( 'http://www.oorsprong.org/websamples.countryinfo' AS ns2), '/FullCountryInfoAllCountriesResponse/FullCountryInfoAllCountriesResult' PASSING xml_in COLUMNS  sISOCode STRING PATH 'tCountryInfo/sISOCode/text()', sName STRING PATH 'tCountryInfo/sName/text()', sCapitalCity STRING PATH 'tCountryInfo/sCapitalCity/text()', sPhoneCode INTEGER PATH 'tCountryInfo/sPhoneCode/text()', sContinentCode STRING PATH 'tCountryInfo/sContinentCode/text()', sCurrencyISOCode STRING PATH 'tCountryInfo/sCurrencyISOCode/text()', sCountryFlag STRING PATH 'tCountryInfo/sCountryFlag/text()', Languages STRING PATH 'tCountryInfo/Languages/text()' ) AS t; \r\n" +

      " END\r\n" +

      " \r\n" +

      " CREATE VIRTUAL PROCEDURE OMethod(IN endpoint string NOT NULL ) RETURNS TABLE ( sISOCode VARCHAR(2000), sName VARCHAR(2000), sCapitalCity VARCHAR(2000), sPhoneCode INTEGER, sContinentCode VARCHAR(2000), sCurrencyISOCode VARCHAR(2000), sCountryFlag VARCHAR(2000), Languages VARCHAR(2000))\r\n" +

      " AS\r\n" +

      " BEGIN\r\n" +

      " DECLARE xml xml_in = (EXEC SoapService_request());\r\n" +

      " DECLARE xml xml_out = (EXEC invoke('SOAP11', null, VARIABLES.xml_in, endpoint, TRUE));\r\n" +

      " \r\n" +

      " IF (xml_out IS NULL)\r\n" +

      " BEGIN\r\n" +

      "                RAISE SQLEXCEPTION 'The SOAP Service return null';\r\n" +

      "        END\r\n" +

      " \r\n" +

      " SELECT t.* FROM TABLE(EXEC SoapService_response(VARIABLES.xml_out)) AS t;\r\n" +

      " END";

      logger.debug(""+procedure2);

      EmbeddedServer server = new EmbeddedServer();

       

       

      WSExecutionFactory factory = new WSExecutionFactory();

      try {

      factory.start();

       

       

      server.addTranslator("translator-ws", factory);

          WSManagedConnectionFactory wsmcf = new WSManagedConnectionFactory();

      server.addConnectionFactory("java:/CustomerRESTWebSvcSource",

      wsmcf.createConnectionFactory());

       

       

      server.start(new EmbeddedConfiguration());

       

       

      ModelMetaData mmd1 = new ModelMetaData();

      mmd1.setName("translater_ws");

      // mmd1.setProperties(properties1);

      mmd1.addSourceMapping("webservice", "translator-ws", "java:/CustomerRESTWebSvcSource");

       

       

      ModelMetaData mmd2 = new ModelMetaData();

      mmd2.setName("procedure");

      mmd2.setModelType(org.teiid.adminapi.Model.Type.VIRTUAL);

      mmd2.addSourceMetadata("DDL", procedure2);

      logger.debug(""+(new Gson().toJson(mmd2)));

      server.deployVDB("test", mmd1,mmd2);

       

       

      Connection connection = server.getDriver()

      .connect("jdbc:teiid:test;useJDBC4ColumnNameAndLabelSemantics=false;showplan=DEBUG", null);

       

      String query="EXEC OMethod('http://webservices.oorsprong.org/websamples.countryinfo/CountryInfoService.wso')";

       

       

      System.out.println("SQL: " + query); //$NON-NLS-1$

            

              Statement stmt = null;

              ResultSet rs = null;

              try {

            

                  stmt = connection.createStatement();

                

                  //boolean hasResults = stmt.execute(query);

                  //if (hasResults) {

                      rs = stmt.executeQuery(query);

                      ResultSetMetaData metadata = rs.getMetaData();

                      int columns = metadata.getColumnCount();

                      logger.debug("Columsn :  "+columns);

                      boolean isData=false;

                      int row=1;

                      while(rs.next()) {

                      isData=true;

                          System.out.print(row + ": ");

                          for (int i = 0; i < columns; i++) {

                              if (i > 0) {

                                  System.out.print(", ");

                              }

                              try {

                              Object data = rs.getObject(i+1);

                              if(data instanceof SQLXMLImpl) {

                              SQLXMLImpl ob = (SQLXMLImpl)rs.getObject(i+1);

                              System.out.print(ob.getString());

                              }else if (data instanceof BlobImpl) {

                              BlobImpl bl = (BlobImpl)data;

                              System.out.println(""+convertStreamToString(bl.getBinaryStream()));

                              }else {

                              System.out.print(data);

                              }

                              }catch (Exception e) {

                                System.out.print(rs.getString(i+1));

      }

                          }

                          row++;

                          System.out.println();

                      }

                    

                      if(!isData) {

                      System.err.println("No Data Avaliable");

                      }

                // }

              } catch (SQLException e) {

                  e.printStackTrace();

              } finally {

              if (null != rs) {

                      rs.close();

                      rs = null;

                  }

                

                  if(null != stmt) {

                      stmt.close();

                      stmt = null;

                  }

                

                  if(null != connection) {

                  connection.close();

                  connection = null;

                  }

              }

      } catch (Exception e) {

      // TODO Auto-generated catch block

      e.printStackTrace();

      }

      }

      static String convertStreamToString(java.io.InputStream is) {

          java.util.Scanner s = new java.util.Scanner(is).useDelimiter("\\A");

          return s.hasNext() ? s.next() : "";

      }

        • 1. Re: How to fetch data from SOAP wsdl ?
          Steven Hawkins Master

          It helps to obtain the response document for reference.  Which you can do through the server logs as well.

           

          In this case you are declaring a namespace, but not using it in the xpath expressions, and you choose your iteration point to be lower - '/FullCountryInfoAllCountriesResponse/FullCountryInfoAllCountriesResult/tCountryInfo' rather than '/FullCountryInfoAllCountriesResponse/FullCountryInfoAllCountriesResult':

           

          SELECT t.* FROM XMLTABLE(XMLNAMESPACES(default 'http://www.oorsprong.org/websamples.countryinfo'), '/FullCountryInfoAllCountriesResponse/FullCountryInfoAllCountriesResult/tCountryInfo' PASSING xml_in COLUMNS  sISOCode STRING PATH 'sISOCode/text()', sName STRING PATH 'sName/text()', sCapitalCity STRING PATH 'sCapitalCity/text()', sPhoneCode INTEGER PATH 'sPhoneCode/text()', sContinentCode STRING PATH 'sContinentCode/text()', sCurrencyISOCode STRING PATH 'sCurrencyISOCode/text()', sCountryFlag STRING PATH 'sCountryFlag/text()', Languages STRING PATH 'Languages/text()' ) AS t

           

          Note the default keyword for the namespace so that it will be used for all elements.