JBoss.orgCommunity Documentation

Chapter 1. Connecting to Teiid Server

1.1. Driver Connection
1.1.1. URL Connection Properties
1.2. Datasource Connection
1.3. Standalone Application
1.3.1. Driver Connection
1.3.2. Datasource Connection
1.4. JBoss AS Data Source
1.4.1. DataSource Connection
1.4.2. Driver based connection
1.4.3. Local JDBC Connection
1.5. Using Multiple Hosts
1.5.1. Fail Over
1.5.2. Load Balancing
1.5.3. Advanced Configuration

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.

  1. Install the Teiid Server. See the "Admin Guide" for instructions.

  2. 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.

  3. Deploy the VDB into Teiid Server. Check "Admin Guide" for instructions.

  4. 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.2-client.jar in the "jboss-install/server/<profile>/lib" directory.

Main classes in the client JAR:

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

  1. <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.

  2. mm - defines Teiid JDBC protocol, mms defines a secure channel (see the SSL chapter for more)

  3. <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]

  4. <port> - defines the port on which the Teiid Server is listening for incoming JDBC connections.

  5. [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 NameTypeDescription
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.
  • ON - Always wrap command in distributed transaction

  • OFF - Never wrap command in distributed transaction

  • DETECT (default)- If the executed command is spanning more than one source it automatically uses distributed transaction.

Transactions with JDBC for more information.
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.

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.

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.


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.

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.

The data source will then be accessable through the JNDI name specified in the -ds.xml file.

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.


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 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.


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.