so you can not expand the sequence shown above to select columns? Can you provide the WSDL file for testing?
Just by way of showing what else I've done, I tried adding columns manually, and this is what I get:
Click the Add button, get this message:
Clicked Yes, then tried to edit it manually:
I had to make an assumption on the path because I'm not exactly sure what it should be... In any case, I hit OK, and this is the SQL generated:
That doesn't look right - the path is all screwed up... Anyways, I click Finish. And now I have this error in my view xmi on the ReadProjectList_response procedure:
ERROR: Error in parsing of sql - Encountered " ")" ") "" at line 3, column 40.
Was expecting one of:
> ERROR: Error in parsing of sql - Encountered " ")" ") "" at line 3, column 40.
Teiid does not expect an empty XMLNAMESPACES declaration. This will need a Teiid Designer issue.
The schema processor is not handling the ANY type element. It should be more informative, but it just sort of stops building the tree. Can you log a Jira for this please?
The only work around will be to build the procedure manually. I will try to help you out with that.
I tried loading this WSDL into Teiid "ws" translator based metadata processor, even there it did not load the metadata due to other reasons (ExtensibilityElements), which needs to be looked at also. I did
<resource-adapter id="sp"> <module slot="main" id="org.jboss.teiid.resource-adapter.webservice"/> <transaction-support>NoTransaction</transaction-support> <connection-definitions> <connection-definition class-name="org.teiid.resource.adapter.ws.WSManagedConnectionFactory" jndi-name="java:/sp" enabled="true" use-java-context="true" pool-name="teiid-swagger-ds"> <config-property name="EndPoint"> http://petstore.swagger.io/v2 </config-property> <config-property name="Wsdl"> /home/rareddy/testing/project-asmx.wsdl </config-property> <config-property name="ServiceName"> Project </config-property> <config-property name="EndPointName"> ProjectSoap12 </config-property> <config-property name="NamespaceUri"> http://schemas.microsoft.com/office/project/server/webservices/Project/ </config-property> </connection-definition> </connection-definitions> </resource-adapter>
<vdb name="sharepoint" version="1"> <model visible="true" name="m"> <source name="s" translator-name="ws" connection-jndi-name="java:/sp"/> </model> </vdb>
Is it safe to say that Teiid does not support this SOAP service at the moment? If so, I can look at writing a proxy service to interface between Teiid and SharePoint for the time being.
Not quite yet, the Designer tooling is not generating desired models based on WSDL, nor translator. But you can still do this manually. Which service endpoint you are trying to reach in this WSDL? May be we can help do this?
Ramesh, I would sincerely appreciate that! These are the ones I need to read (I think though, if you could help me walk through one, I should be able to figure out the others):
Here is some documentation about the actual methods, if it helps: https://msdn.microsoft.com/en-us/library/office/websvcproject.project_di_pj14mref_methods(v=office.14).aspx
Here is the Teiid Designer project for above methods. I have no way of testing, so I am not totally sure if this works yet, may need further tweaks. If you have external server somewhere I can execute against, then I can test. This is what I did
I replaced all the <s:any ..> in your WSDL with something dummy like
<s:element minOccurs="0" maxOccurs="1" name="any" type="s:string" />
Then I ran through the WSDL (SOAP) wizard, and created connection profile to your WSDL file, then selected your methods defined above to expose, then choose their input/output columns. Now that "any" is defined as "string" column Teiid Designer did not complain. I finished generating the all the procedures, then I manually edited all the the "methodxxx_response" procedures, updated the return from "string" to "xml" then updated the transformation to return the whole xml fragment which is returned as part of "any" xml fragment. Also updated "methodXXX" return type XML.
So basically when deploy the VDB based on this project, and execute the virtual store procedure, the procedure will return you a XML fragment that is returned as part of the ANY. Now it is your responsibility parse that XML into meaningful construct using the XMLTABLE. For before you do that, make sure you are getting the right result, then move further.
Note if the endpoint in the WSDL is not correct you would also need to update that in the procedures. You also need to create web-service resource adapter in the server with JNDI name "project" (I am sure you are aware these other Teiid runtime configuration mechanics).
We need still need to fix the NTLM portion of it, but you should run this to validate without NTLM first.
SharePoint.zip 102.0 K
Thank you very much for helping me move down the road a bit further. I may still have a public server available, but I'm not sure. I will try to find out that information.
As far as your example, I was able to import and deploy that project, but I got an exception when trying to call the function:
TEIID30068 The function 'sharepoint.ProjectView.ReadProjectList()' is an unknown form. Check that the function name and number of arguments is correct.'. Originally QueryResolverException ResolverVisitor.java:623. Enable more detailed logging to see the entire stacktrace.
I tried running a few different queries, but no luck. I even tried a SELECT 1 as column, but I got that same exception.
I am past all that - everything looks right on the Teiid side. I had to fix some connection properties as far as auth info goes - I turned on basic auth on my sharepoint server. Now I get a weird error - it appears to be a problem on the MS side:
HTTP response '415: Cannot process the message because the content type 'application/soap+xml; action="http://schemas.microsoft.com/office/project/server/webservices/Project/ReadProjectList"; charset=UTF-8' was not the expected type 'text/xml; charset=utf-8'.' when communicating with http://pcsii_project/_vti_bin/PSI/Project.asmx
I'm investigating it further, but it appears you got me past the point of getting connected, thank you!
What I see on searching is you are invoking SOAP11 based endpoint, where as Teiid is setup for SOAP12. Either change the endpoint, or change the type in Teiid to SOAP11.
One thing I would try is, using SoapUI issue the same query and take look at the request response in detail log, and make sure it is working. I would even turn off HTTPBasic during this initial functional testing. Then you can use Teiid to execute same. If you can (allowed) to use Wireshark kind of tool then you monitor what Teiid has been sending as payload and match to the differences that of SoapUI.
Thanks for the link to that SoapUI. I found a couple more problems with the way I was trying to execute it. I was trying to execute queries against the server, and not a specific Project site. Once I fixed that, I started getting data back.
I haven't tried from Teiid yet, but wanted to share this with you, this is an example response packet from the service (ReadProjectList, specifically). It doesn't quite look like I would have expected it, I'll see if it works with Teiid and let you know. In any case, I've attached it so you can see what the expected response is.
By the way, I am very impressed by the level of support you have offered via this forum. I've dealt with projects where the members were frankly jerks in their replies to noob questions, and other projects where getting any response was rare. You have helped very much and I'm looking forward to using this project more.