1 2 Previous Next 16 Replies Latest reply on Apr 28, 2016 11:03 AM by Ramesh Reddy

    Integrating with Sharepoint

    dajester2011 Newbie

      I am trying to read Project Server 2010 (SharePoint) SOAP services.  To start with, I'm trying to import from the ReadProject service.

       

      I've imported the WSDL successfully, and got to this point, and cannot proceed:

       

      It isn't recognizing columns from the WSDL for the result set - even though I've looked - it's all there.

       

      I'd write it all manually, but this is all very confusing to me.  Can someone point me in the right direction?

        • 1. Re: Integrating with Sharepoint
          Ramesh Reddy Master

          so you can not expand the sequence shown above to select columns? Can you provide the WSDL file for testing?

          • 2. Re: Integrating with Sharepoint
            dajester2011 Newbie

            Attached is the WSDL file.

            • 3. Re: Integrating with Sharepoint
              dajester2011 Newbie

              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:

               

                "default" ...

               

                "no" ...

               

                <STRINGVAL> ...

              • 4. Re: Integrating with Sharepoint
                Steven Hawkins Master

                > 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.

                • 5. Re: Integrating with Sharepoint
                  Ted Jones Master

                  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.

                   

                  Thanks,

                  Ted

                  • 6. Re: Integrating with Sharepoint
                    Ramesh Reddy Master

                    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>
                    

                     

                    with VDB

                     

                    share-point.xml

                    <vdb name="sharepoint" version="1">
                        <model visible="true" name="m">
                            <source name="s" translator-name="ws" connection-jndi-name="java:/sp"/> 
                        </model>
                    </vdb>
                    
                    • 7. Re: Integrating with Sharepoint
                      dajester2011 Newbie

                      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.

                      • 8. Re: Integrating with Sharepoint
                        Ramesh Reddy Master

                        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?

                        • 9. Re: Integrating with Sharepoint
                          dajester2011 Newbie

                          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):

                          • ReadProjectList
                          • ReadProjectTeam
                          • ReadProjectStatus
                          • ReadProjectImpacts
                          • ReadProjectEntities

                           

                          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

                          • 10. Re: Integrating with Sharepoint
                            Ramesh Reddy Master

                            Jesse,

                             

                            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.

                             

                            HTH

                             

                            Ramesh..

                            • 11. Re: Integrating with Sharepoint
                              dajester2011 Newbie

                              Ramesh,

                               

                              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.

                              • 12. Re: Integrating with Sharepoint
                                dajester2011 Newbie

                                Ramesh,

                                 

                                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!

                                • 13. Re: Integrating with Sharepoint
                                  Ramesh Reddy Master

                                  Jesse,

                                   

                                  Great!!!

                                   

                                  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.

                                   

                                  Ramesh..

                                  • 14. Re: Integrating with Sharepoint
                                    dajester2011 Newbie

                                    Ramesh,

                                     

                                    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.

                                     

                                    Jesse

                                    1 2 Previous Next