How to connect to Salesforce using Teiid?

[tarifezaz]

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

[shawkins]

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.

[tarifezaz]

Thanks a lot Steven, you made my day!

[tarifezaz]

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?

[shawkins]

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?

[tarifezaz]

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

[tarifezaz]

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?

[rareddy]

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.

[tarifezaz]

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.

[tarifezaz]

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

[tarifezaz]

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

[sanjeev.gour]

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

[shawkins]

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.

[tarifezaz]

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.

[tarifezaz]

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