JBoss.orgCommunity Documentation
The Teiid JDBC API provides Java Database Connectivity (JDBC) access to any Virtual Database (VDB) deployed on a Teiid Server. The Teiid JDBC API is compatible with the JDBC 4.0 specification; however, it does not fully support all methods. Advanced features, such as updatable result sets or SQL3 data types, are not supported.
Java client applications connecting to a Teiid Server will need to use Java 1.6 JDK. Previous versions of Java are not supported.
Before you can connect to the Teiid Server using the Teiid JDBC API, please do following tasks first.
Install the Teiid Server. See the "Admin Guide" for instructions.
Build a Virtual Database (VDB). You can either build a "Dynamic VDB" (Designer not required), or you can use the Eclipse based GUI tool Designer. Check the "Reference Guide" for instructions on how to build a VDB. If you do not know what VDB is, then start with this document.
Deploy the VDB into Teiid Server. Check "Admin Guide" for instructions.
Start the Teiid Server (JBoss AS), if it is not already running.
Now that you have the VDB deployed in the Teiid Server, client applications
can connect to the Teiid Server and issue SQL queries against deployed VDB using Teiid's JDBC API. If you are new to JDBC, see Java's documentation about
JDBC. Teiid ships with
teiid-7.7-client.jar in the "jboss-install/server/<profile>/lib" directory.
Main classes in the client JAR:
org.teiid.jdbc.TeiidDriver - allows JDBC connections using the
DriverManager class.
org.teiid.jdbc.TeiidDatasource - allows JDBC connections using the
DataSource or XADataSource class. You should use this class to create managed or XA connections.
Once you have established a connection with the Teiid Server, you can use standard JDBC API classes to interrogate metadata and execute queries.
Use org.teiid.jdbc.TeiidDriver as the driver class.
Use the following URL format for JDBC connections:
jdbc:teiid:<vdb-name>@mm[s]://<host>:<port>;[prop-name=prop-value;]*
URL Components
<vdb-name> - Name of the VDB you are connecting to. Optionally VDB name can also contain version information inside it. For example: "myvdb.2", this is equivalent to supplying the "version=2" connection property defined below. However, use of vdb name in this format and the "version" property at the same time is not allowed.
mm - defines Teiid JDBC protocol, mms defines a secure channel (see the SSL chapter for more)
<host> - defines the server where the Teiid Server is installed. If you are using IPv6 binding address as the host name, place it in square brackets. ex:[::1]
<port> - defines the port on which the Teiid Server is listening for incoming JDBC connections.
[prop-name=prop-value] - additionally you can supply any number of name value pairs separated by semi-colon [;]. All supported URL properties are defined in the connection properties section. Property values should be URL encoded if they contain reserved characters, e.g. ('?', '=', ';', etc.)
The following table shows all the supported connection properties that can used with Teiid JDBC Driver URL connection string, or on the Teiid JDBC Data Source class.
Table 1.1. Connection Properties
| Property Name | Type | Description |
|---|---|---|
ApplicationName
|
String
| Name of the client application; allows the administrator to identify the connections |
FetchSize
|
int
| Size of the resultset; The default size if 500. <=0 indicates that the default should be used. |
partialResultsMode
|
boolean
| Enable/disable support partial results mode. Default false. See the partial results section. |
autoCommitTxn
|
String
| Only applies only when "autoCommit" is set to "true".
This determines how a executed command needs to be transactionally wrapped inside the
Teiid engine to maintain the data integrity.
|
disableLocalTxn
|
boolean
| If "true", the autoCommit setting, commit and rollback will be ignored for local transactions. Default false. |
user
|
String
| User name |
Password
|
String
| Credential for user |
ansiQuotedIdentifiers
|
boolean
| Sets the parsing behavior for double quoted entries in SQL. The default, true, parses dobuled quoted entries as identifiers. If set to false, then double quoted values that are valid string literals will be parsed as string literals. |
version
|
integer
| Version number of the VDB |
resultSetCacheMode
|
boolean
| ResultSet caching is turned on/off. Default false. |
autoFailover
|
boolean
| If true, will automatically select a new server instance after a communication exception. Default false. This is typically not needed when connections are managed, as the connection can be purged from the pool. |
SHOWPLAN
|
String
| (typically not set as a connection property) Can be ON|OFF|DEBUG; ON returns the query plan along with the results and DEBUG additionally prints the query planner debug information in the log and returns it with the results. Both the plan and the log are available through JDBC API extensions. Default OFF. |
NoExec
|
String
| (typically not set as a connection property) Can be ON|OFF; ON prevents query execution, but parsing and planning will still occur. Default OFF. |
PassthroughAuthentication
|
boolean
| Only applies to "local" connections. When this option is set to "true", then Teiid looks for already authenticated security context on the calling thread. If one found it uses that users credentials to create session. Teiid also verifies that the same user is using this connection during the life of the connection. if it finds a different security context on the calling thread, it switches the identity on the connection, if the new user is also eligible to log in to Teiid otherwise connection fails to execute. |
useCallingThread
|
boolean
| Only applies to "local" connections. When this option is set to "true" (the default), then the calling thread will be used to process the query. If false, then an engine thread will be used. |
QueryTimeout
|
integer
| Default query timeout in seconds. Must be >= 0. 0 indicates no timeout. Can be overriden by Statement.setQueryTimeout. Default 0. |
useJDBC4ColumnNameAndLabelSemantics
|
boolean
|
A change was made in JDBC4 to return unaliased column names as the ResultSetMetadata column name. Prior to this, if a column alias were used it was returned as the column name. Setting this property to false will enable backwards compatibility when JDBC3 and older support is still required. Defaults to true. |
jaasName
|
String
|
JAAS configuration name. Only applies when configuring a GSS authentication. See the Admin Guide for configuration required for GSS. |
kerberosServicePrincipleName
|
String
|
Kerberos authenticated principle name. Only applies when configuring a GSS authentication. See the Admin Guide for configuration required for GSS |
To use a data source based connection, use org.teiid.jdbc.TeiidDataSource as the data source class.
The TeiidDataSource is also an XADatasource. Teiid DataSource class is also Serializable, so it possible for it to be used with
JNDI naming services.
Teiid supports the XA protocol, XA transactions will be extended to Teiid sources that also support XA.
All the properties (except for version, which is known on TeiidDataSource as DatabaseVersion) defined in the connection propertieshave corresponding "set" methods on the org.teiid.jdbc.TeiidDataSource.
Properties that are assumed from the URL string have addtional "set" methods, which are described in the following table.
Table 1.2. Datasource Properties
| Property Name | Type | Description |
|---|---|---|
DatabaseName
|
String
| The name of a virtual database (VDB) deployed to Teiid. Optionally Database name can also contain "DatabaseVersion" information inside it. For example: "myvdb.2", this is equivalent to supplying the "DatabaseVersion" property set to value of 2. However, use of Database name in this format and use of DatabaseVersion property at the same time is not allowed. |
ServerName
|
String
| Server hostname where the Teiid runtime installed. If you are using IPv6 binding address as the host name, place it in square brackets. ex:[::1] |
AlternateServers
|
String
| Optional delimited list of host:port entries. See the multiple hosts section for more information. If you are using IPv6 binding address as the host name, place them in square brackets. ex:[::1] |
AdditionalProperties
|
String
| Optional setting of properties that has the same format as the property string in a connection URL. |
PortNumber
|
integer
| Port number on which the Server process is listening on. |
secure
|
boolean
| Secure connection. Flag to indicate to use SSL (mms) based connection between client and server |
DatabaseVersion
|
integer
| VDB version |
DataSourceName
|
String
| Name given to this data source |
To use either Driver or DataSource based connections, add the client JAR to your Java client application's classpath. See the simple client example in the kit for a full Java sample of the following.
Sample Code:
public class TeiidClient {
public Connection getConnection(String user, String password) throws Exception {
String url = "jdbc:teiid:myVDB@mm://localhost:31000;ApplicationName=myApp";
return DriverManager.getConnection(url, user, password);
}
}Sample Code:
public class TeiidClient {
public Connection getConnection(String user, String password) throws Exception {
TeiidDataSource ds = new TeiidDataSource();
ds.setUser(user);
ds.setPassword(password);
ds.setServerName("localhost");
ds.setPortNumber(31000);
ds.setDatabaseName("myVDB");
return ds.getConnection();
}
}Teiid can be configured as a JDBC data source in the JBoss Application Server to be accessed from JNDI or injected into your JEE applications. Deploying Teiid as data source in JBoss AS is exactly same as deploying any other RDBMS resources like Oracle or DB2.
Defining as data source is not limited to JBoss AS, you can also deploy as data source in Glassfish, Tomcat, Websphere, Weblogic etc servers, however their configuration files are different than JBoss AS. Consult the respective documentation of the environment in which you are deploying.
A special case is if the Teiid instance you are connecting to is in the same VM as the JBoss AS instance. If that matches you deployment, then follow the Section 1.4.3, “Local JDBC Connection” instructions
Installation Steps
If Teiid is not installed in the AS instance, copy the teiid-7.7-client.jar into
<jboss-install>/server/<profile>/lib directory.
Create a "<datasource name>-ds.xml" file in <jboss-install>/server/<profile>/deploy
directory. Based on the type of deployment (XA, driver, or local), the contents of the file will be different. See the following sections for more.
The data source will then be accessable through the JNDI name specified in the -ds.xml file.
Make sure you know the correct DatabaseName, ServerName, Port number and credentials that are specific to your deployment environment.
Example 1.1. Sample XADataSource in the JBoss AS using the
Teiid DataSource class org.teiid.jdbc.TeiidDataSource
<datasources>
<xa-datasource>
<jndi-name>TEIID-DS</jndi-name>
<xa-datasource-class>org.teiid.jdbc.TeiidDataSource</xa-datasource-class>
<xa-datasource-property name="DatabaseName">myVDB</xa-datasource-property>
<xa-datasource-property name="serverName">localhost</xa-datasource-property>
<xa-datasource-property name="portNumber">31000</xa-datasource-property>
<xa-datasource-property name="user">admin</xa-datasource-property>
<xa-datasource-property name="password">password</xa-datasource-property>
<!-- pool and other JBoss datasource properties -->
<check-valid-connection-sql>SELECT 1</check-valid-connection-sql>
<min-pool-size>5</min-pool-size>
<max-pool-size>10</max-pool-size>
</xa-datasource>
</datasources>You can also use Teiid's JDBC driver class org.teiid.jdbc.TeiidDriver to create a data source
<datasources>
<local-tx-datasource>
<jndi-name>TEIID-DS</jndi-name>
<connection-url>jdbc:teiid:myVDB@mm://localhost:31000</connection-url>
<driver-class>org.teiid.jdbc.TeiidDriver</driver-class>
<user-name>admin</user-name>
<password>teiid</password>
<!-- pool and other JBoss datasource properties -->
<check-valid-connection-sql>SELECT 1</check-valid-connection-sql>
<min-pool-size>5</min-pool-size>
<max-pool-size>10</max-pool-size>
</local-tx-datasource>
</datasources>If you are deploying your client application on the same JBoss AS instance as the Teiid runtime is installed, then there is a way to make connections that by-pass making a socket based JDBC connection. You can use slightly modified data source configuration to make a "local" connection, where the JDBC API will lookup a local Teiid runtime in the same VM.
Since DataSources start before before Teiid VDBs are deployed, leave the min pool size of local connections as the default of 0. Otherwise errors will occur on the startup of the Teiid DataSource.
By default local connections use their calling thread to perform processing operations rather than using an engine thread while the calling thread is blocked. To disable this behavior set the connection property useCallingThreads=false.
Example 1.2. Local data source
<datasources>
<xa-datasource>
<jndi-name>TEIID-DS</jndi-name>
<xa-datasource-class>org.teiid.jdbc.TeiidDataSource</xa-datasource-class>
<xa-datasource-property name="DatabaseName">myVDB</xa-datasource-property>
<xa-datasource-property name="user">admin</xa-datasource-property>
<xa-datasource-property name="password">password</xa-datasource-property>
<!-- pool and other JBoss datasource properties -->
<max-pool-size>10</max-pool-size>
</xa-datasource>
</datasources>This is essentially the same as the XA configuration, but "ServerName" and "PortNumber" are not specified. Local connections have additional features such as using ???
A group of Teiid Servers in the same JBoss AS cluster may be connected using failover and load-balancing features. To enable theses features in their simplest form, the client needs to specify multiple host name and port number combinations on the URL connection string.
Example 1.3. Example URL connection string
jdbc:teiid:<vdb-name>@mm://host1:31000,host1:31001,host2:31000;version=2
If you are using a DataSource to connect to Teiid Server, use the "AlternateServers" property/method to define the failover servers. The format is also a comma separated list of host:port combinations.
The client will randomly pick one the Teiid server from the list and establish a session with that server. If that server cannot be contacted, then a connection will be attempted to each of the remaining servers in random order. This allows for both connection time fail-over and random server selection load balancing.
Post connection fail over will be used, if you're using an admin connection (such as what is used by AdminShell) or if the autoFailover connection property on JDBC URL is set to true. Post connection failover works by sending a ping, at most every second, to test the connection prior to use. If the ping fails, a new instance will be selected prior to the operation being attempted. This is not true "transparent application failover" as the client will not restart the transaction/query/recreate session scoped temp tables, etc. So this feature should be used with caution by non-admin connections.
Post connection load balancing can be utilized in one of two ways. First if you are using TeiidDataSource and the Connections returned by
Teiid PooledConnections have their close method called, then a new server instance will be selected automatically.
However when using driver based connections or even when using TeiidDataSource in a connection pool (such as JBoss AS), the automatic load balancing will not happen.
Second you can explicitly trigger load balancing through the use of the set statement:
SET NEWINSTANCE TRUE
Typically you will not need want to issue this statement manually, but instead use it as the connection test query on your DataSource configuration.
Example 1.4. JBoss AS DataSource With Post Connection Load Balancing
<datasources>
<local-tx-datasource>
<jndi-name>TEIID-DS</jndi-name>
<connection-url>jdbc:teiid:myVDB@mm://localhost:31000,mm://localhost:32000</connection-url>
<driver-class>org.teiid.jdbc.TeiidDriver</driver-class>
<user-name>admin</user-name>
<password>teiid</password>
<!-- pool and other JBoss datasource properties -->
<check-valid-connection-sql>SET NEWINSTANCE TRUE</check-valid-connection-sql>
<min-pool-size>5</min-pool-size>
<max-pool-size>10</max-pool-size>
</local-tx-datasource>
</datasources>
Teiid by default maintians a pool of extra socket connections that are reused. For load balancing, this reduces the potential cost of switching a connection to another server instance.
The default setting is to maintain 16 connections (see org.teiid.sockets.maxCachedInstances in the client jar's teiid-client-settings.properties file.
If you're client is connecting to large numbers of Teiid instances and you're using post connection time load balancing, then consider increasing the number of cached instances.
You may either set an analogous system property or create another version of teiid-client-settings.properties file and place it into the classpath ahead of the client jar.
Session level temporary tables, currently running transactions, session level cache entries, and PreparedPlans for a given session will not be available on other cluster members. Therefore, it is recommended that post connection time load balancing is only used when the logical connection could have been closed, but the actual connection is reused (the typical connection pool pattern).
Server discovery, load balancing, fail over, retry, retry delay, etc. may be customize if the default policy is not sufficient.
See the org.teiid.net.socket.ServerDiscovery interface and default implementaion org.teiid.net.socket.UrlServerDiscovery for how to start with your customization.
The UrlServerDiscovery implemenation provides the following: discovery of servers from the URL hosts (DataSource server/alternativeServers), random selection for load balancing and failover, 1 connection attempt per host, no biasing, black listing, or other advanced features.
Typically you'll want to extend the UrlServerDiscovery so that it can be used as the fallback strategy and to only implement the necessary changed methods.
It's important to consider that 1 ServerDiscovery instance will be created for each connection. Any sharing of information between instances should be done through static state or some other shared lookup.
Your customized server discovery class will then need to be referenced by the discoveryStategy connection/DataSource property by its full class name.
You may also choose to use an external tcp load balancer, such as haproxy. The Teiid driver/DataSource should then typically be configured to just use the single host/port of your load balancer.
Teiid connections (defined by the org.teiid.jdbc.TeiidConnection interface) support the changeUser method to reauthenticate a given connection.
If the reauthentication is successful the current connection my be used with the given identity.
Existing statements/result sets are still available for use under the old identity. See the JBossAS issue JBAS-1429 for more on using reauthentication support with JCA.