7 Replies Latest reply on May 27, 2006 12:01 AM by 464026

    Interconnect with Microsoft

    Sammy Hopkins
      I realize there is not adapter for MS SQL Server.
      So, now. What is the technology that allows me to send complex XML message to a windows server that will be picked up by perhaps a listeining application?Does MSMQ play in this? If so, is there an adapter that talks to MSMQ? I do not want to use the FTP adapter.

      Give me a case study or something to read and perhaps setup a proof of concept.

        • 1. Re: Interconnect with Microsoft

          I had the same problem. We did use the ftp adapter and let the application import files.
          Bu lately I discovered that Oracle fusion Middleware has a jdbc-adapter that can connect to several datbase, like db2 and MSSQLserver.

          the only issue is that this jdbc adapter is the DB-adapter of the BPEL-environment. and is not portable yet (!) to interconnect.
          According to an Oracle-contact there should be an interconnect-adapter build by oracle consultancy with some adapter toolkit, that has the same functionality as the jdbc adapter. I don't want to investigate this, so I have no details on that

          Another solution could be the ORacle transparant gateway. You can make dblinks between oracle an MSSQL database. Use a db-adapter on the oracle dtabase and put triggers on your table to push it to MSSQL tables
          This was proposed a year ago by Oracle consultants as a solution.

          Feel free to contact me
          • 2. Re: Interconnect with Microsoft
            Sammy Hopkins
            Thanks for the dialog.

            Connecting straight to SQLServer scares me. The only options I'm seeing with this method is to do straight inserts for incomming messages and read/delete for outbound messages. To me, this implies an inherent limitation in that I am going to be bound by the maximum size of an SQLServer column size. I want to be able to drop off a very complex XML message with multiple headers and repeating groups.

            I'm beginning to wonder if connecting straight to SQLServer is the right approach. If it were an Oracle database, it would be no problem, we would simply use AQ. However, as we all know, Microsoft does not have a similar "object" within SQLServer. The decision is made, the database being developed against is SQLServer, so, I have no choice but to use this on the "satelite" platform. The "central" platform is the Oracle Business Suite 11i10, with Interconnect doing the distribution of message to multiple different "sateites" besides this new application being developed.

            Again, does MSMQ help in this areana? Is there a proof of concept case study out there?

            What about HTTP? Is there a proof of concept casestudy?

            Here is a picture
            <---?---> MSSqlServer1
            Oracle 11i10 <--AQ adapter---> Oracle Interconnect <---?---> MSSqlServer2
            <---?---> MSSqlServer3

            • 3. Re: Interconnect with Microsoft
              Hmm,... interesting case.
              I'm am not a "real" developer so I can't go into details.

              But I can give some ideas we introduced in general, including ideas that are still ideas.

              1. About your remark that you don't want to connect to SQLServer directly;
              We (almost) never connect to the schema/db directly. We always use a staging area. We dump the message in a staging table and with triggers we call the application API or database procedure to handle the message.
              advantages; a) you have always a message-history, b) Sometimes API's/procedures already exist or need to be build by the application-supplier. They know the datamodel. integration becomes a 2-stage implementation, but is very easy to support.

              2. about http; instead of using a staging are you can develop/buy a webservice on top of the application (notice the similarity with an API) that can handle your xml-message. use an HTTP-adapter to deliver the message. We have no experience in this one. So i also cannot tell if this is the right option.

              3. In general the message is not leading in the integration, the processes are. And from your question it is hard to understand the process. therefore my suggestions could easily miss the answers you are looking for.

              Make sure you have an integration architecture that helps you to build your integrations correctly. If you don't have one, it might be a good opportunity to start developing...
              • 4. Re: Interconnect with Microsoft
                Use InterConnect and it's new JCA adapter - it allow you to use any JCA-compatible adatper, including BPEL jdbc DBadapter.

                Furthermore, this jdbc DBadapter (JCA) included in this release of InterConnect (but this is not documented).
                Folow the example, that is in JCA adapter documentation and you'll get what you want!
                • 5. Re: Interconnect with Microsoft
                  Do you have a link where I can find the JCA adapter documentation?
                  • 6. Re: Interconnect with Microsoft
                    Simply go to OTN (otn.oracle.com) - it's the first place for all Oracle Documentation.

                    Follow direct link:

                    Adapter for JCA Installation and User's Guide

                    Message was edited by:
                    • 7. Re: Interconnect with Microsoft
                      I got custom JDBC adapter that can read xml stored in SQL server(or any RDBMS) table into interconnect or write xml into SQL server table from interconnect. For writing it can also call stored procedure and pass xml as string. in sql server the data type should be text/ntext/xml. There are functionality in sql server that can read xml and put data to tables and vice versa. if this solves your purpose, you can go for either writing the custom adapter using this idea or buy it from my company.