Access Teiid Driver

[beginnerram]

Hi,

I am trying to connect to Teiid from Qlikview(http://www.qlikview.com/ ) a BI tool. I need teiid driver to be included into the qlikview to access information from teiid.It right now has only the standard ODBC drivers to connet to MYsql , Oracle and others. I would like to know how to install the given teiid driver jar as a driver in windowsxp.

Though i am not sure whether after installing teiid as a JDBC driver, It will show up in the available list. I want to check it. Please let me know if there is any other alternatives also.

Thanks,

Ramya

[rareddy]

Ramya,

This sounds very interesting to integrate Teiid with qlikview, however I am not familiar with qlinkview currently to answer your question correctly. The real question you need to find out is "does qlikview support the data sources using JDBC or just ODBC?"

As of Teiid 7.0, we did not offer ODBC support. So, if you are working with 7.0 or prior version of Teiid, JDBC is the only access mechanism.

If you have to use ODBC, then starting 7.1, we are going to offer ODBC access using the PostgreSQL driver. This version is scheduled to release in some time early next month. As a matter of fact I just committed my initial set of changes for ODBC support to the Teiid nightly build late last week. You can download the source for Teiid and build it and test with qlikview. If there are any issues, let us know, I will be gladly fix them and try to make them in 7.1 release.

Also, in 7.1 we will be providing a Web Service interface on top, so you can access the VDB using a SOAP calls.

Hope this helps.

Thanks.

Ramesh..

[beginnerram]

Thanks Ramesh. I am using Teiid 6.2. Yes, Qlikview only uses the system DSN to connect to the Database not using JDBC.  So i just came across this site where in they have used Openlink software which when installed act as ODBC to JDBC bridge to connect SAP to metamatrix database. I tried to do the same, But i face issues when i connect to Teiid given the follwing details:

JDBC Driver: org.teiid.jdbc.TeiidDriver

URL String: jdbc:teiid:try@mm://myIp:31000

where DB is "try"

Ipaddress is "MyIP"

also tried with Driver:com.metamatrix.jdbc.MMDriver

URL: jdbc:metamatrix:trymm://myIp:31000

I get the following error "[OpenLink][ODBC]Unable to allocate server handle". Please let me know how do i Connect using a n open ODBC-JDBC bridge. It will be really great if you could help me in this regard.

I have one more query. When i am accessing from my JDBCClient.Class, I can access it either using getDriverConnection or getDataSource connection.

/// code snippet

static

Connection getDriverConnection(String vdb) throws Exception {

String url =

"jdbc:metamatrix:"+vdb+"@C:\\teiid\\teiid-6.2.0\\deploy.properties";

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

return DriverManager.getConnection(url,"admin", "teiid");

}

This perfectly works fine if is from local machine. HOw do i execute using driver Connection from remote system. Where and how do i specify the IP address.

and is it mandatory to have deploy.properties in the client system as i get error if i dont mention this

ds.setEmbeddedBootstrapFile(

"C:\\ramya\\teiid\\teiid-6.2.0\\deploy.properties"); using DataSource method. Please do clarify me in this regard also.

Thanks.

[rareddy]

Ramya,

Good information. Couple issues.

1) You can take the my previous suggestion and upgrade to 7.1 nightly and have ODBC support to create the DSN you needed.

2) If you would like to take the approach you mentioned in the email with OpenLink ODBC driver, that if fine too. However, you would need to obtain (purchase) license for the OpenLink driver to begin with. Next, you are starting the Teiid in "embedded" mode.  You need to start and connect to Teiid in "Server" mode if your Client application and Teiid Server are not in the same VM. Here the Client application is OpenLink ODBC Driver.

Read more about different operating modes Teiid can be installed and started here. Using the server mode, you have URL is different where you do not need to specify the "deploy.properties", check out the links I have given.

Hope this helps.

Ramesh..

[beginnerram]

I have downloaded 7.1 will check it and get back.Could you proivde me with any document if any for ODBC connection.

I went throught the link you have provided. I am still not clear on how to access vdb from remote machine.Right now i am using 6.2 version and these are the steps i followed for deloying Teiid runtime.

1. Extracted Teiid6-2.0-dist.zip to a folder.

2. Dropped  the .VDB file created using Teiid designer in deploy folder

3.started run.bat

4. Accces the VDB from java class using the code previously mentioned.

I think this is running in server mode. is it? if not please let me know, what changes needs to be done as in Teiid Installation its mention deploy.properties needs to be configured but i am not sure what needs to be done.

[rareddy]

I have downloaded 7.1 will check it and get back.Could you proivde me with any document if any for ODBC connection

7.1 is not released, so you would have to download the source and build the project yourself. We did release 7.1 alpha-1, but ODBC feature is not in that release. We are going to release 7.1 alpha-2, by the end of the week or early next week, it will have the first cut of the ODBC. For document check the Client Developer's Guide for ODBC chapter.

I went throught the link you have provided. I am still not clear on how to access vdb from remote machine.Right now i am using 6.2 version and these are the steps i followed for deloying Teiid runtime.

1. Extracted Teiid6-2.0-dist.zip to a folder.

2. Dropped  the .VDB file created using Teiid designer in deploy folder

3.started run.bat

4. Accces the VDB from java class using the code previously mentioned.

I think this is running in server mode. is it? if not please let me know, what changes needs to be done as in Teiid Installation its mention deploy.properties needs to be configured but i am not sure what needs to be done.

Yes, up to the execution of the run.bat. Take look at the "teiid-examples" directory. In there there is an example called "simpleclient" which is generic JDBC client that connects to Teiid runtime using the TeiidDriver or TeiidDatasource.  You need to connect to your VDB in the similar way.

Hope this helps.

Ramesh..

[beginnerram]

Thanks. Ya the example you had meniond the "simpleclient" which is generic JDBC client perfectly works for Teiid 7.0 but i didnt know how to do it with Teiid 6.2.

[rareddy]

Ramya,

The same JDBC client will work with 6.2 version too. Just make sure you are using the correct jdbc-client libraries. The same example is also in the 6.2.

Ramesh..

[beginnerram]

Actualy Ramesh, When i tried to run the JDBC Client for server mode in Teiid 6.2. I get the following error:

JDBC call from remote machine results in

Aug 3, 2010 3:26:48 PM org.teiid.jdbc.SocketProfile connect

SEVERE: Could not create connection

com.metamatrix.common.comm.exception.SingleInstanceCommunicationException

: Error establishing socket to host and port: 172.20.178.44:31000. Reason: Read timed out

JDBC call from same machine results in this error

Aug 3, 2010 3:14:27 PM com.metamatrix.common.comm.platform.socket.client.SocketServerInstanceImpl exceptionOccurred

WARNING: Unable to read: unexpected exception

java.net.SocketException

: Software caused connection abort: recv failed

Let me know if i need to change any properties in any configuration files. The JDBC code for connection is same as given in server mode .

Message was edited by: ramya r

[rareddy]

Is the server machine same when you used 7.0 and 6.2?

It seems like for some reason the Server is un-reachable in the 6.2 version. This may be because server used a different address on which it is listening for connections. Check the "deploy.properties" for the "server.bindAddress" and see what that property value is, this is server bind address your client is trying to connect. Use the same name on the URL for the connection.

Otherwise, try editng the "deploy.properties" and modify "server.bindAddress" property to "172.20.178.44" (which is your ip address from your message) and re-start. This may solve it.

Make sure there is only one version JDBC client jar in the classpath of your client.

Ramesh..

[beginnerram]

Yes I used the same machine to test both 6.2 and 7.0. I had even tried changing the "server.bindAddress" to my machine IP but in vain. Though i am not able to run the JDBC client from remote machines for 6.2 , I am able to telnet 31000 of my machine from remote machines. Just attaching the Java class and deploy.properties for your reference.

[rareddy]

They both look fine. In the client, you are trying make connections through "driver" and as well as "data source". Typically in a given client application you only need one.

The only one thing I can think why this may be causing is, you may have firewall on your machine which is not letting any out side connections come in. So, either disable the firewall or open the port 31000 on your machine for external connections to come in. Note on Windows machines some antivirus systems come with firewalls. Also Windows itself comes with firewall activated. Check it out.

Ramesh..

[beginnerram]

Hi Ramesh,

They both look fine. In the client, you are trying make connections through "driver" and as well as "data source". Typically in a given client application you only need one.

Ya . Was just checking with both type of connection. hoping anyone will work.

The only one thing I can think why this may be causing is, you may have firewall on your machine which is not letting any out side connections come in. So, either disable the firewall or open the port 31000 on your machine for external connections to come in. Note on Windows machines some antivirus systems come with firewalls. Also Windows itself comes with firewall activated. Check it out.

As i previously told, I tried to run both 6.2 and 7.0 from same machine and remote access works for Teiid 7.0 for the same port 31000 and the other point is I am able to Telnet  my machine 31000 from remote machines .But i had checked the firewall setting and it is disabled. I am attaching the complete error logs . Attaching 3 error logs as follows:

     1. Log while accessing teiid using JDBC client  from remote machine.

     2. Log while accesssing teiid using JDBC client from host machine for the first time after restart of teiid 6.2

     3. Log for second hit from host machine.

[rareddy]

From the logs I can see that you are mixing up the JDBC client libraries for Teiid between both versions.

May be in your application classpath you have "teiid-7.1.0-client.jar", that is reason it works with 7.0 but not with 6.2.

For working with 6.2 you need "teiid-6.2.0-client.jar" in your classpath. Please double check your classpath.

Ramesh..