How to fetch data from SOAP wsdl ?
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() : "";
}
