1 2 Previous Next 29 Replies Latest reply on Nov 19, 2014 2:37 AM by Tarif Ezaz

    How to connect to Salesforce using Teiid?

    Tarif Ezaz Newbie

      Guys,

       

      I have gone through the documentation a several times in order to figure out how to connect to a database of my existing Salesforce app with Teiid. I know that Teiid Translator acts as an abstraction between physical datasource and Teiid Query Engine. But since Teiid translator is loaded already with the start of a jboss server, I did not look much into it. I have also copied the Resource Adapter for Salesforce and pasted it to the standalone-teiid.xml My main problem at this moment is how to create Virtual Databases for Salesforce. There is a template on how to create a Dynamic VDB for any datasources with a plenty of options. But I'm not entirely sure which ones are going to be useful for me. So here are some of my questions:

       

      1) How do I connect to a particular Salesforce app database with Teiid using a VDB?

      2) How to define source models that maps to a specific table or relational models of a particular Salesforce app?

      3) Are there any extra codes that I need to add to my existing Resource Adapter? I am currently using the Resource Adapter for Salesforce that comes with the default Teiid installation.

       

      I have already tried to deploy a sample vdb for Saleforce, but since some necessary attributes were not present in that vdb, it has given some parse error while starting the Teiid server.

       

      I am using Teiid version 8.8.0 with Jboss Eap 6.1.

       

      I am stuck on this for a while, any help would be hugely appreciated.

       

      Regards

      Tarif

        • 1. Re: How to connect to Salesforce using Teiid?
          Steven Hawkins Master

          1) You can use Teiid Designer to get started in a graphical environment or use a dynamic vdb.  For example assuming that you setup a salesforce data source with the name sf, the dynamic vdb xml would look like:

           

          <vdb name="myVDB" version= "1">

              <model name = "salesforce">

                  <source name="salesforce" translator-name="salesforce" connection-jndi-name="java:/sf"/>

              </model>

          </vdb>

           

          2) Once you import from salesforce in designer or deploy the vdb above, you will have the appropriate source connectivity to salesforce.

           

          3) No additional code should be needed.  You'll at least need a user name and password/security token set.

          • 2. Re: How to connect to Salesforce using Teiid?
            Tarif Ezaz Newbie

            Thanks a lot Steven, you made my day!

            • 3. Re: How to connect to Salesforce using Teiid?
              Tarif Ezaz Newbie

              I have managed to connect successfully with my Salesforce account. I have edited the resource adapter accordingly with my username and password. Then I deployed the vdb that you have mentioned above. After this, I restarted the server and the vdb was set to active. It downloaded some metadata in /Home/jboss-eap-6.1/standalone/teiid-data/myVDB_1/myVDB_1_salesforce.ser/". But when I make a query with ODBC, I could not find any tables from Salesforce. I have already created some sample objects in Salesforce end for testing purpose. But they do not seem to load when I make a query for all the avaialble table names. How can I access all the tables from Salesforce?

              • 4. Re: How to connect to Salesforce using Teiid?
                Steven Hawkins Master

                If you can see the salesforce tables over Teiid JDBC, then they should be present in Teiid ODBC.  Have you tried with a JDBC client and/or a client that shows you a metadata tree?

                1 of 1 people found this helpful
                • 5. Re: How to connect to Salesforce using Teiid?
                  Tarif Ezaz Newbie

                  Thanks Steven, I'll try a JDBC client and will let you know.

                  • 6. Re: How to connect to Salesforce using Teiid?
                    Tarif Ezaz Newbie

                    I tried to connect to my vdb using the following code:

                     

                    import java.sql.* ;

                     

                    public class Driver {

                     

                        public static void main(String[] args) throws ClassNotFoundException {

                            String url = "jdbc:teiid:myVDB@mm://localhost:31000";

                            Class.forName("org.teiid.jdbc.TeiidDriver");

                            try {

                                Connection myConn = DriverManager.getConnection(url,"user","user");

                               

                                DatabaseMetaData md = myConn.getMetaData();

                                ResultSet rs = md.getTables(null, null, "%", null);

                                while (rs.next()) {

                                  System.out.println(rs.getString(3));

                                }

                            }

                           

                            catch (Exception exc) {

                                exc.printStackTrace() ;

                            }

                        }

                    }

                     

                    But I have been failing to connect to the database. What should be the correct port number for Teiid JDBC?

                    • 7. Re: How to connect to Salesforce using Teiid?
                      Ramesh Reddy Master

                      That looks correct. Are you getting connection refused exception. Did you add user? Depending upon the version that addition of the user has changed in 8.8. You can use some tool like SQuirreL too.

                      1 of 1 people found this helpful
                      • 8. Re: How to connect to Salesforce using Teiid?
                        Tarif Ezaz Newbie

                        Hello Ramesh,

                         

                        Yes I am getting connection refused exception. I haven't added any user yet. I will try to add users and will let you know.

                        • 9. Re: How to connect to Salesforce using Teiid?
                          Tarif Ezaz Newbie

                          I could not find the way how to add users in 8.8 documentation, could someone please help?

                          • 10. Re: How to connect to Salesforce using Teiid?
                            Tarif Ezaz Newbie

                            I have been getting the following error message:

                             

                            SEVERE: Could not create connection

                            org.teiid.jdbc.TeiidSQLException: TEIID20020 Error establishing socket to host and port: localhost:53458. Reason: Connection refused

                                at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:135)

                                at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:71)

                                at org.teiid.jdbc.SocketProfile.connect(SocketProfile.java:56)

                                at org.teiid.jdbc.TeiidDriver.connect(TeiidDriver.java:107)

                                at org.teiid.jdbc.TeiidDriver.connect(TeiidDriver.java:55)

                                at java.sql.DriverManager.getConnection(DriverManager.java:571)

                                at java.sql.DriverManager.getConnection(DriverManager.java:215)

                                at Driver.main(Driver.java:11)

                            Caused by: org.teiid.net.socket.SingleInstanceCommunicationException: TEIID20020 Error establishing socket to host and port: localhost:53458. Reason: Connection refused

                                at org.teiid.net.socket.SocketServerConnection.selectServerInstance(SocketServerConnection.java:161)

                                at org.teiid.net.socket.SocketServerConnection.<init>(SocketServerConnection.java:95)

                                at org.teiid.net.socket.SocketServerConnectionFactory.getConnection(SocketServerConnectionFactory.java:316)

                                at org.teiid.jdbc.SocketProfile.connect(SocketProfile.java:54)

                                ... 5 more

                            Caused by: java.net.ConnectException: Connection refused

                                at java.net.PlainSocketImpl.socketConnect(Native Method)

                                at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:339)

                                at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:200)

                                at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:182)

                                at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)

                                at java.net.Socket.connect(Socket.java:579)

                                at java.net.Socket.connect(Socket.java:528)

                                at org.teiid.net.socket.OioOjbectChannelFactory.createObjectChannel(OioOjbectChannelFactory.java:178)

                                at org.teiid.net.socket.SocketServerInstanceImpl.connect(SocketServerInstanceImpl.java:94)

                                at org.teiid.net.socket.SocketServerConnectionFactory.getServerInstance(SocketServerConnectionFactory.java:277)

                                at org.teiid.net.socket.SocketServerConnection.connect(SocketServerConnection.java:239)

                                at org.teiid.net.socket.SocketServerConnection.selectServerInstance(SocketServerConnection.java:125)

                                ... 8 more

                             

                            org.teiid.jdbc.TeiidSQLException: TEIID20020 Error establishing socket to host and port: localhost:53458. Reason: Connection refused

                                at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:135)

                                at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:71)

                                at org.teiid.jdbc.SocketProfile.connect(SocketProfile.java:56)

                                at org.teiid.jdbc.TeiidDriver.connect(TeiidDriver.java:107)

                                at org.teiid.jdbc.TeiidDriver.connect(TeiidDriver.java:55)

                                at java.sql.DriverManager.getConnection(DriverManager.java:571)

                                at java.sql.DriverManager.getConnection(DriverManager.java:215)

                                at Driver.main(Driver.java:11)

                            Caused by: org.teiid.net.socket.SingleInstanceCommunicationException: TEIID20020 Error establishing socket to host and port: localhost:53458. Reason: Connection refused

                                at org.teiid.net.socket.SocketServerConnection.selectServerInstance(SocketServerConnection.java:161)

                                at org.teiid.net.socket.SocketServerConnection.<init>(SocketServerConnection.java:95)

                                at org.teiid.net.socket.SocketServerConnectionFactory.getConnection(SocketServerConnectionFactory.java:316)

                                at org.teiid.jdbc.SocketProfile.connect(SocketProfile.java:54)

                                ... 5 more

                            Caused by: java.net.ConnectException: Connection refused

                                at java.net.PlainSocketImpl.socketConnect(Native Method)

                                at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:339)

                                at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:200)

                                at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:182)

                                at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)

                                at java.net.Socket.connect(Socket.java:579)

                                at java.net.Socket.connect(Socket.java:528)

                                at org.teiid.net.socket.OioOjbectChannelFactory.createObjectChannel(OioOjbectChannelFactory.java:178)

                                at org.teiid.net.socket.SocketServerInstanceImpl.connect(SocketServerInstanceImpl.java:94)

                                at org.teiid.net.socket.SocketServerConnectionFactory.getServerInstance(SocketServerConnectionFactory.java:277)

                                at org.teiid.net.socket.SocketServerConnection.connect(SocketServerConnection.java:239)

                                at org.teiid.net.socket.SocketServerConnection.selectServerInstance(SocketServerConnection.java:125)

                                ... 8 more

                            • 11. Re: How to connect to Salesforce using Teiid?
                              Sanjeev Gour Novice

                              You could use jboss\bin\add-user.bat

                              • 12. Re: How to connect to Salesforce using Teiid?
                                Steven Hawkins Master

                                Connection refused means that there is nothing listening at that host/port.  Have you changed the port?  The client is connecting to 53458 rather than the default of 31000.  You'll want to address the connectivity issue first, then if you haven't properly added a user you'll see a different exception.

                                • 13. Re: How to connect to Salesforce using Teiid?
                                  Tarif Ezaz Newbie

                                  Thank you Ramesh, Sanjeev and Steven. I have managed to connect to the Salesforce and query for data successfully by following your instructions. Right now, I require to change the default port from 31000 to a different value. Where can I change this?

                                   

                                  Thank you all again. You guys helped me a lot.

                                  • 14. Re: How to connect to Salesforce using Teiid?
                                    Tarif Ezaz Newbie

                                    I have found the place where to change the port number. You can do it in standalone-teiid.xml file here:

                                    <socket-binding-group name="standard-sockets" default-interface="public" port-offset="${jboss.socket.binding.port-offset:0}">

                                            <socket-binding name="management-native" interface="management" port="${jboss.management.native.port:9999}"/>

                                            <socket-binding name="management-http" interface="management" port="${jboss.management.http.port:9990}"/>

                                            <socket-binding name="management-https" interface="management" port="${jboss.management.https.port:9443}"/>

                                            <socket-binding name="ajp" port="8009"/>

                                            <socket-binding name="http" port="8080"/>

                                            <socket-binding name="https" port="8443"/>

                                            <socket-binding name="remoting" port="4447"/>

                                            <socket-binding name="txn-recovery-environment" port="4712"/>

                                            <socket-binding name="txn-status-manager" port="4713"/>

                                            <socket-binding name="teiid-jdbc" port="31000"/>

                                            <socket-binding name="teiid-odbc" port="35432"/>

                                            <outbound-socket-binding name="mail-smtp">

                                                <remote-destination host="localhost" port="25"/>

                                            </outbound-socket-binding>

                                        </socket-binding-group>

                                     

                                    Regards

                                    Tarif

                                    1 2 Previous Next