-
Language:
English
-
Language:
English
Red Hat Training
A Red Hat training course is available for Red Hat JBoss Data Virtualization
Development Guide Volume 1: Client Development
This guide is for developers wanting to interface to Red Hat JBoss Data Virtualization from within client applications.
Red Hat Customer Content Services
Abstract
Chapter 1. Read Me
1.1. Back Up Your Data
Warning
1.2. Variable Name: EAP_HOME
EAP_HOME
refers to the root directory of the Red Hat JBoss Enterprise Application Platform installation on which JBoss Data Virtualization has been deployed.
1.3. Variable Name: MODE
MODE
will either be standalone
or domain
depending on whether JBoss Data Virtualization is running in standalone or domain mode. Substitute one of these whenever you see MODE
in a file path in this documentation. (You need to set this variable yourself, based on where the product has been installed in your directory structure.)
1.4. Red Hat Documentation Site
Chapter 2. Connecting to a Virtual Database
2.1. Java Database Connectivity (JDBC)
2.2. JBoss Data Virtualization and JDBC
Note
Note
2.3. Generated Keys
org.teiid.CommandContext
methods dealing with generated keys for more.
2.4. Connection Methods
- Using the
org.teiid.jdbc.TeiidDriver
driver class - Using the
org.teiid.jdbc.TeiidDataSource
data source class - Configuring a VDB as a JBoss data source
2.5. Connecting to a VDB Using the Driver Class
org.teiid.jdbc.TeiidDriver
class, found in EAP_HOME/dataVirtualization/jdbc/teiid-VERSION-redhat-N-jdbc.jar
, should be used to create a connection using the Java java.sql.DriverManager
class.
2.6. Create a Connection to a VDB Using the Driver Class
Prerequisites
- The client application must have the JBoss Data Virtualization
EAP_HOME/dataVirtualization/jdbc/teiid-VERSION-redhat-N-jdbc.jar
JAR file in its classpath. (If the application is running on the same application server as the JBoss Data Virtualization instance to which the connection is desired, then this will already be the case.) - JBoss Data Virtualization must be installed and running, with the relevant virtual databases (VDBs) deployed.
Procedure 2.1. Create a Connection to a VDB Using the Driver Class
Create a Connection to the VDB
Within your client application code, create aConnection
to the VDB using theDriverManager.getConnection()
method: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); } }
2.7. Driver Connection URL Format
jdbc:teiid:VDB-NAME@mm[s]://HOSTNAME:PORT;[prop-name=prop-value;]*
Table 2.1. URL Entities
Variable Name | Description |
---|---|
VDB-NAME | The name of the virtual database (VDB) to which the application is connected.
Important
VDB names can contain version information; for example, myvdb.2 . If such a name is used in the URL, this has the same effect as supplying a version=2 connection property. Note that if the VDB name contains version information, you cannot also use the version property in the same request.
|
mm[s] | The JBoss Data Virtualization JDBC protocol. mm is the default for normal connections. mms uses SSL for encryption and is the default for the AdminAPI tools. |
HOSTNAME | The server where JBoss Data Virtualization is installed. |
PORT | The port on which JBoss Data Virtualization is listening for incoming JDBC connections. |
[prop-name=prop-value] | Any number of additional name-value pairs can be supplied in the URL, separated by semi-colons. Property values must be URL encoded if they contain reserved characters, for example, ? , = , and ; . |
2.8. Connection Properties for the Driver and Data Source Classes
org.teiid.net.TeiidURL
and org.teiid.jdbc.ExecutionProperties
(waitForLoad
is defined in org.teiid.jdbc.EmbeddedProfile
), and the corresponding set methods are defined for the data source class in org.teiid.jdbc.TeiidDataSource
and its superclass, org.teiid.jdbc.BaseDataSource
.
Table 2.2. Connection Properties
Property Name | Method Name | Type | Description |
---|---|---|---|
ansiQuotedIdentifiers | setAnsiQuoted Identifiers | boolean | Sets the parsing behavior for double quoted entries in SQL. If true, then parses doubled quoted entries as identifiers. If false, then double quoted values that are valid string literals are parsed as string literals. Default is true. |
ApplicationName | setApplicationName | String | Name of the client application; allows the administrator to distinguish between connections. |
autoCommitTxn | setAutoCommitTxn | String | Only applies only when autoCommit is set to "true". This determines how an executed command needs to be wrapped as a transaction inside the JBoss Data Virtualization engine to maintain the data integrity.
|
autoFailover | setAutoFailover | boolean | If true, automatically selects a new server instance after a communication exception. This is typically not needed when connections are managed, as the connection can be purged from the pool. Default is false. |
disableLocalTxn | setDisableLocalTxn | boolean | If true, the autoCommit setting, commit and rollback is ignored for local transactions. Default is false. |
fetchSize | setFetchSize | int | Size of the resultset. Default is 2048. <=0 indicates that the default should be used. |
NOEXEC | setNoExec | String | (Typically not set as a connection property.) Can be ON or OFF. ON prevents query execution, but parsing and planning still occurs. Default is OFF. |
partialResultsMode | setPartialResultsMode | boolean | Enable/disable support partial results mode. Default false. See Section 4.12, “Partial Results Mode” for more details. |
Passthrough Authentication | setPassthrough Authentication | boolean | Only applies to local connections. When this option is set to true, JBoss Data Virtualization looks for an authenticated security context on the calling thread. If one is found, it uses that user's credentials to create a session. JBoss Data Virtualization 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 and the new user is eligible to login to JBoss Data Virtualization, it switches the identity on the connection. |
useCallingThread | Not applicable. Must be set using setAdditionalProperties method if connecting via the data source class. | boolean | Only applies to local connections. When this option is set to true, the calling thread is used to process the query. If false, then an engine thread is used. Default is true. |
QUERYTIMEOUT | setQueryTimeout | int | Default query timeout in seconds. Must be >= 0. 0 indicates no timeout. Can be overridden by Statement.setQueryTimeout . Default is 0. |
useJDBC4ColumnName AndLabelSemantics | setUseJDBC4ColumnName AndLabelSemantics | boolean |
A change was made in JDBC4 to return unaliased column names as the ResultSetMetadata column name. Prior to this, if a column alias was used it was returned as the column name. Setting this property to false enables backwards compatibility when JDBC3 and older support is still required. Defaults to true.
|
password | setPassword | String | Credential for user |
resultSetCacheMode | setResultSetCacheMode | boolean | ResultSet caching is turned on/off. Default is false. |
SHOWPLAN | setShowPlan | String | (Typically not set as a connection property.) Can be ON, OFF or 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 is OFF. |
user | setUser | String | User name. |
version | setDatabaseVersion | String | Version number of the VDB. |
jaasName | setJaasName | String |
JAAS configuration name. Only applies when configuring GSS authentication. See the JBoss Data Virtualization Administration Guide for more information about GSS configuration.
|
kerberosService PrincipleName | setKerberosService PrincipleName | String |
Kerberos authenticated principle name. Only applies when configuring GSS authentication. See the JBoss Data Virtualization Administration Guide for more information about GSS configuration.
|
encryptRequests | setEncryptRequests | boolean |
Only applies to non-SSL socket connections. When set to true, the request message and any associated payload is encrypted using the connection cryptor. Default is false.
|
waitForLoad | Not applicable. Needs to be specified using setAdditionalProperties if set using the data source class. | String | Only applies to local connections. When this option is set to a non-negative value, the connection will wait that number of milliseconds for the VDB to become active. Setting to a negative number uses the system default setting. |
2.9. Connecting to a VDB Using the Data Source Class
org.teiid.jdbc.TeiidDataSource
class is based on the javax.sql.DataSource
connection factory. It can be used to create ManagedConnection
s and XAConnection
s to both DataSource
s and XADataSource
s. XA transactions are extended to JBoss Data Virtualization sources that support XA.
DataSource
class is serializable and can be used with JNDI naming services.
EAP_HOME/quickstarts/simpleclient/
example for more information.
2.10. Create a Connection to a VDB Using the Data Source Class
Prerequisites
- The client application must have the JBoss Data Virtualization
EAP_HOME/dataVirtualization/jdbc/teiid-VERSION-redhat-N-jdbc.jar
JAR file in its classpath. (If the application is running on the same application server as the JBoss Data Virtualization instance to which the connection is desired, then this will already be the case.) - JBoss Data Virtualization must be installed and running, with the relevant virtual databases (VDBs) deployed.
Procedure 2.2. Create a Connection to a VDB Using the Data Source Class
Create a Connection Object
Create aorg.teiid.jdbc.TeiidDataSource
object, set the required properties, and use theTeiidDataSource.getConnection()
method to obtain a Connection object. For example: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(); } }
2.11. Additional Data Source Connection Properties
Table 2.3. Data Source Connection Properties
Method Name | Type | Description |
---|---|---|
setAlternateServers | String | Optional delimited list of host:port entries. Refer to Section 3.1, “Using Multiple Hosts” for more information. |
setAdditionalProperties | String | Optional setting of properties that has the same format as the property string in a driver connection URL. Refer to Section 2.7, “Driver Connection URL Format” |
setDatabaseName | String | The name of a virtual database (VDB) deployed to JBoss Data Virtualization.
Important
VDB names can contain version information; for example, myvdb.2 . If such a name is used in the URL, this has the same effect as supplying a version=2 connection property. Note that if the VDB name contains version information, you cannot also use the version property in the same request.
|
setDatabaseVersion | String | The VDB version. |
setDataSourceName | String | The name given to this data source |
setPortNumber | int | The port number on which the server process is listening. |
setServerName | String | The server hostname where the JBoss Data Virtualization runtime is installed. |
setSecure | boolean | Secure connection. Flag to indicate to use SSL (mms) based connection between client and server. |
Note
useCallingThread
property as
<xa-datasource-property name="AdditionalProperties">useCallingThread=false</xa-datasource-property>
2.12. Connecting to a VDB as a Data Source
Note
standalone.xml
configuration file. See the Red Hat JBoss Enterprise Application Platform Administration and Configuration Guide for more information on how to configure data sources in JBoss EAP.
Chapter 3. Multiple Hosts
3.1. Using Multiple Hosts
host:port
combinations:
jdbc:teiid:<vdb-name>@mm://host1:31000,host1:31001,host2:31000;version=2
setAlternateServers
method can be used to specify the failover servers. The format is also a comma separated list of host:port
combinations.
3.2. Failover
autoFailover
connection property 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.
Warning
3.3. Load Balancing
TeiidDataSource
class and the PooledConnection
returned by getPooledConnection
is terminated using the close()
method, then a new server instance will be selected automatically. (When using driver based connections or when using the TeiidDataSource
class in a connection pool, the automatic load balancing will not happen.)
SET NEWINSTANCE TRUE
. Typically you will not issue this statement manually, but you can use it as the connection test query on your data source configuration:
<datasources> <datasource jndi-name="java:/teiidDS" pool-name="teiidDS"> <connection-url>jdbc:teiid:{vdb}@mm://{host}:31000</connection-url> <driver>teiid</driver> <pool> <prefill>false</prefill> <use-strict-min>false</use-strict-min> <flush-strategy>FailingConnectionOnly</flush-strategy> <check-valid-connection-sql>SET NEWINSTANCE TRUE</check-valid-connection-sql> </pool> <security> <user-name>{user}</user-name> <password>{password}</password> </security> </datasource> <drivers> <driver name="teiid" module="org.jboss.teiid.client"> <driver-class>org.teiid.jdbc.TeiidDriver</driver-class> <xa-datasource-class>org.teiid.jdbc.TeiidDataSource</xa-datasource-class> </driver> </drivers> </datasources>
Important
3.4. Increase the Maximum Number of Cached Instances
Procedure 3.1. Increase the Maximum Number of Cached Instances
- Increase the
org.teiid.sockets.maxCachedInstances
property in theteiid-client-settings.properties
file located inEAP_HOME/dataVirtualization/jdbc/teiid-VERSION-redhat-N-jdbc.jar
.Note
An example file (teiid-client-settings.orig.properties
) is packaged in the client JAR file.
3.5. Advanced Configuration
org.teiid.net.socket.ServerDiscovery
interface and default implementation org.teiid.net.socket.UrlServerDiscovery
for more information on customization.
UrlServerDiscovery
implementation provides the following features:
- discovery of servers from URL hosts (including the data source server and alternative servers)
- random selection for load balancing and failover
- one connection attempt per host
- no biasing
- black listing
UrlServerDiscovery
so that it can be used as the fall-back strategy and to only implement the necessary changed methods. It is important to consider that one 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.
discoveryStrategy
connection/DataSource property using its full class name.
3.6. Reauthentication
org.teiid.jdbc.TeiidConnection
interface) support the changeUser
method to reauthenticate a given connection. If reauthentication is successful, the current connection may be used with the given identity. Existing statements and resultsets are still available for use under the old identity.
Chapter 4. Extensions to JDBC
4.1. Prepared Statements
org.teiid.jdbc.TeiidPreparedStatement
, a custom interface for the standard java.sql.PreparedStatement
, and implementations org.teiid.jdbc.CallableStatementImpl
and org.teiid.jdbc.PreparedStatementImpl
. Prepared statements can be important in speeding up common statement execution, since they allow the server to skip parsing, resolving, and planning of the statement.
- It is not necessary to pool client-side JBoss Data Virtualization prepared statements, because JBoss Data Virtualization performs plan caching on the server side.
- The number of cached plans is configurable. The plans are purged in order of least recently used (LRU).
- Cached plans are not distributed through a cluster. A new plan must be created for each cluster member.
- Plans are cached for the entire VDB or for just a particular session. The scope of a plan is detected automatically based upon the functions evaluated during its planning process.
- Runtime updates of costing information do not yet cause re-planning. At this time only session-scoped temporary table or internally materialized tables update their costing information.
- Stored procedures executed through a callable statement have their plans cached in the same way as a prepared statement.
- Bind variable types in function signatures, for example
where t.col = abs(?)
, can be determined if the function has only one signature or if the function is used in a predicate where the return type can be determined. In more complex situations it may be necessary to add a type hint with a cast or convert, for exampleupper(convert(?, string))
.
4.2. JDBC Statement Extensions
org.teiid.jdbc.TeiidStatement
, provides functionality beyond the JDBC standard. To use this interface, cast or unwrap the statement returned by the connection. The interface provides the following methods for setting and retrieving statement properties:
Table 4.1. Statement Properties
Method Name | Description |
---|---|
getAnnotations | This method has been deprecated. Use the SHOW statement. |
getDebugLog | This method has been deprecated. Use the SHOW statement. |
getExecutionProperty | This method has been deprecated. Use the SHOW statement. |
getPlanDescription |
Get the query plan description if the statement was last executed with SHOWPLAN ON|DEBUG. The plan is a tree made up of
org.teiid.client.plan.PlanNode objects. Typically PlanNode.toString() or PlanNode.toXml() will be used to convert the plan into a textual form.
|
getRequestIdentifier |
Get an identifier for the last command executed on this statement. If no command has been executed yet, null is returned.
|
setExecutionProperty | Note
This method has been deprecated. Use the SET statement.
|
setPayload |
Set a per-command payload to pass to translators. Currently the only built-in use is for sending hints for an Oracle data source.
|
4.3. Non-blocking Statement Execution
org.teiid.jdbc.TeiidStatement
and org.teiid.jdbc.TeiidPreparedStatement
interfaces to execute queries with a callback org.teiid.jdbc.StatementCallback
that will be notified of statement events, such as an available row, an exception, or completion. Your calling thread will be free to perform other work. The callback will be executed by an engine processing thread as needed. If your results processing is blocking and you want query processing to run concurrently with results processing, then your callback should implement onRow handling in a multi-threaded manner to allow the engine thread to continue.
PreparedStatement stmt = connection.prepareStatement(sql); TeiidPreparedStatement tStmt = stmt.unwrap(TeiidPreparedStatement.class); tStmt.submitExecute(new StatementCallback() { @Override public void onRow(Statement s, ResultSet rs) { //any logic that accesses the current row ... System.out.println(rs.getString(1)); } @Override public void onException(Statement s, Exception e) throws Exception { s.close(); } @Override public void onComplete(Statement s) throws Exception { s.close(); }, new RequestOptions() });
Note
org.teiid.jdbc.AsynchPositioningException
will be thrown. That exception is recoverable if caught or can be avoided by calling TeiidResultSet.available()
to determine if your desired positioning will be valid.
4.4. Continuous Execution
RequestOptions
object may be used to specify a special type of continuous async execution via the continuous
or setContinuous
methods. In continuous mode the statement will be continuously re-executed. This is intended for consuming real-time or other data streams processed through a SQL plan. A continuous query will only terminate on an error or when the statement is explicitly closed. The SQL for a continuous query is no different than any other statement. Care should be taken to ensure that retrievals from non-continuous sources is appropriately cached for reuse, such as by using materialized views or session scoped temp tables. A continuous query must return a result set, must be executed with a forward-only resultset, and cannot be used in the scope of a transaction. Since resource consumptions is expected to be different in a continuous plan, it does not count against the server max active plan limit. Typically custom sources will be used to provide data streams.
Statement.close()
or Statement.cancel()
method should be called. Typically your callback will close whenever it no long needs to process results.
ContinuousStatementCallback
for use as the StatementCallback
for additional methods related to continuous processing.
4.5. Execution Properties
org.teiid.jdbc.ExecutionProperties
. These can be modified using the SET statement.
Table 4.2. Execution Properties
Constant Identifier | String Value | Description |
---|---|---|
ANSI_QUOTED_IDENTIFIERS | ansiQuotedIdentifiers | See Section 2.8, “Connection Properties for the Driver and Data Source Classes”. |
DISABLE_LOCAL_TRANSACTIONS | disableLocalTxn | See Section 2.8, “Connection Properties for the Driver and Data Source Classes”. |
JDBC4COLUMNNAME ANDLABELSEMANTICS | useJDBC4ColumnName AndLabelSemantics | See Section 2.8, “Connection Properties for the Driver and Data Source Classes”. |
NOEXEC | See Section 2.8, “Connection Properties for the Driver and Data Source Classes”. | |
PROP_FETCH_SIZE | fetchSize | See Section 2.8, “Connection Properties for the Driver and Data Source Classes”. |
PROP_PARTIAL_RESULTS_MODE | partialResultsMode | See Section 2.8, “Connection Properties for the Driver and Data Source Classes” and Section 4.12, “Partial Results Mode”. |
PROP_TXN_AUTO_WRAP | autoCommitTxn | See Section 2.8, “Connection Properties for the Driver and Data Source Classes”. |
PROP_XML_FORMAT | XMLFormat |
Determines the formatting of XML documents returned by XML document models. Can be one of
XML_COMPACT_FORMAT or XML_TREE_FORMAT . See Section 4.7, “XML Document Formatting” for more information.
|
PROP_XML_VALIDATION | XMLValidation |
Determines whether XML documents returned by XML document models will be validated against their schema after processing. See Section 4.8, “XML Schema Validation” and topics on "XML SELECT" in the JBoss Data Virtualization Development Guide: Reference Material for more information.
|
QUERYTIMEOUT | QUERYTIMEOUT | See Section 2.8, “Connection Properties for the Driver and Data Source Classes”. |
RESULT_SET_CACHE_MODE | resultSetCacheMode | See Section 2.8, “Connection Properties for the Driver and Data Source Classes”. |
SQL_OPTION_SHOWPLAN | SHOWPLAN | See Section 2.8, “Connection Properties for the Driver and Data Source Classes”. |
See Also:
4.6. XML Extensions
org.teiid.jdbc.ExecutionProperties
:
PROP_XML_FORMAT
(defined as XMLFormat)PROP_XML_VALIDATION
(defined as XMLValidation)
Note
4.7. XML Document Formatting
PROP_XML_FORMAT
execution property, defined in org.teiid.jdbc.ExecutionProperties
, can be set using the SET statement to modify the way that XML documents are formatted from XML document models. The following valid values are also defined:
XML_TREE_FORMAT
- XML_TREE_FORMAT (defined as Tree) returns a version of the XML formatted for display. The XML will use line breaks and tabs as appropriate to format the XML as a tree. This format is slower due to the formatting time and the larger document size.
XML_COMPACT_FORMAT
- XML_COMPACT_FORMAT (defined as Compact) returns a version of the XML formatted for optimal performance. The XML is a single long string without any unnecessary white space.
Important
XML_COMPACT_FORMAT
execution property is not set, the formatting flag of the XML document in the original model is honored. This may produce either the tree or compact form of the document depending on the document setting.
4.8. XML Schema Validation
PROP_XML_VALIDATION
execution property, defined in org.teiid.jdbc.ExecutionProperties
, can be set using the SET statement to indicate that the server should validate XML document model documents against their schema before returning them to the client. If schema validation is on, then the server sends an SQLWarning if the document does not conform to the schema it is associated with.
Note
4.9. The SET Statement
- SET [PAYLOAD] (parameter|SESSION AUTHORIZATION) value
- SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL (READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE)
- The parameter must be an identifier - it cannot contain spaces or other special characters only if quoted.
- The value may be either a non-quoted identifier or a quoted string literal value.
- If payload is specified, for example,
SET PAYLOAD x y
, then a session scoped payload properties object will have the corresponding name value pair set. The payload object is not fully session scoped. It will be removed from the session when the XAConnection handle is closed/returned to the pool (assumes the use of TeiidDataSource). The session scoped payload is superseded by usage of theTeiidStatement.setPayload
.
- SET SHOWPLAN (ON|DEBUG|OFF)
- SET NOEXEC (ON|OFF)
Statement s = connection.createStatement(); s.execute("SET SHOWPLAN DEBUG"); Statement s1 = connection.createStatement(); ResultSet rs = s1.executeQuery("select col from table"); ResultSet planRs = s1.executeQuery("SHOW PLAN"); planRs.next(); String debugLog = planRs.getString("DEBUG_LOG"); Query Plan without executing the query s.execute("SET NOEXEC ON"); s.execute("SET SHOWPLAN DEBUG"); ... e.execute("SET NOEXEC OFF");
Statement s = connection.createStatement(); s.execute("SET PASSWORD 'someval'"); s.execute("SET SESSION AUTHORIZATION 'newuser'");
4.10. The SHOW Statement
- SHOW PLAN
SHOW PLAN
returns a resultset with a CLOB column PLAN_TEXT, an xml column PLAN_XML, and a CLOB column DEBUG_LOG with a row containing the values from the previously executed query. If SHOWPLAN is OFF or no plan is available, no rows are returned. If SHOWPLAN is not set to DEBUG, then DEBUG_LOG will return a null value.- SHOW ANNOTATIONS
SHOW ANNOTATIONS
returns a resultset with string columns CATEGORY, PRIORITY, ANNOTATION, RESOLUTION and a row for each annotation on the previously executed query. If SHOWPLAN is OFF or no plan is available, no rows are returned.- SHOW <property>
SHOW <property>
is the inverse of SET and shows the property value for the property supplied. It returns a resultset with a single string column with a name matching the property key.- SHOW ALL
SHOW ALL
returns a resultset with a NAME string column and a VALUE string column with a row entry for every property value.
4.11. Transaction Statements
- START TRANSACTION
- START TRANSACTION is a synonym for
connection.setAutoCommit(false)
- COMMIT
- COMMIT is a synonym for
connection.setAutoCommit(true)
- ROLLBACK
- ROLLBACK is a synonym for
connection.rollback()
and returning to auto commit mode.
4.12. Partial Results Mode
UNION
or OUTER JOIN
queries as these operations handle missing information in a useful way. Most other kinds of queries will simply return 0 rows to the user when used in partial results mode and the source is unavailable.
Note
4.13. Setting Partial Results Mode
Statement statement = ...obtain statement from Connection... statement.execute("set partialResultsMode true");
4.14. Partial Results Warnings
Statement.getWarnings()
method. This method returns a SQLWarning
object, but in the case of partial results warnings this object will be an instance of the org.teiid.jdbc.PartialResultsWarning
class. This class can be used to obtain a list of all the failed sources by name and to obtain the specific exception thrown by each resource adaptor.
Note
statement.execute("set partialResultsMode true"); ResultSet results = statement.executeQuery("SELECT Name FROM Accounts"); while (results.next()) { //process the result set } SQLWarning warning = statement.getWarnings(); if(warning instanceof PartialResultsWarning) { PartialResultsWarning partialWarning = (PartialResultsWarning)warning; Collection failedConnectors = partialWarning.getFailedConnectors(); Iterator iter = failedConnectors.iterator(); while(iter.hasNext()) { String connectorName = (String) iter.next(); SQLException connectorException = partialWarning.getConnectorException(connectorName); System.out.println(connectorName + ": " + connectorException.getMessage()); } }
Chapter 5. JDBC Transactions
5.1. JDBC Transaction Types
- global transactions,
- local transactions, and
- request level transactions.
Warning
5.2. Local Transactions
autoCommit
flag to explicitly control local transactions. By default, autoCommit is set to true
, which indicates request level or implicit transaction control:
// Set auto commit to false and start a transaction connection.setAutoCommit(false); try { // Execute multiple updates Statement statement = connection.createStatement(); statement.executeUpdate("INSERT INTO Accounts (ID, Name) VALUES (10, 'Mike')"); statement.executeUpdate("INSERT INTO Accounts (ID, Name) VALUES (15, 'John')"); statement.close(); // Commit the transaction connection.commit(); } catch(SQLException e) { // If an error occurs, rollback the transaction connection.rollback(); }
- Setting
autoCommit
flag to false. This will start a transaction bound to the connection. - Executing multiple updates within the context of the transaction.
- When the statements are complete, the transaction is committed by calling
commit()
. - If an error occurs, the transaction is rolled back using the
rollback()
method.
5.3. Ending Local Transactions
Connection.setAutoCommit(true)
if previously set to falseConnection.commit()
Connection.rollback()
- A transaction will be rolled back automatically if it times out.
5.4. Turning Off Local Transactions
setAutoCommit(false)
, commit()
and rollback()
even when all access is read-only and no transactions are necessary. In the scope of a local transaction, JBoss Data Virtualization will start and attempt to commit an XA transaction, possibly complicating configuration or causing performance degradation.
disableLocalTxn=true
Warning
5.5. Request Level Transactions
autoCommit
is true
. In a request level transaction, your application does not need to explicitly call commit or rollback, rather every command is assumed to be its own transaction that will automatically be committed or rolled back by the server.
5.6. Transaction Wrapping Modes
ON
This mode always wraps every command in a transaction without checking whether it is required. This is the safest mode.OFF
This mode never automatically wraps a command in a transaction or checks whether it needs to wrap a command. This mode can be dangerous as it will allow multiple source updates outside of a transaction without an error. This mode has best performance for applications that do not use updates or transactions.DETECT
This mode assumes that the user does not know how to execute multiple source updates in a transaction. JBoss Data Virtualization checks every command to see whether it is a multiple source update and wraps it in a transaction. If it is single source then it uses the source level command transaction.
5.7. Set the Transaction Wrapping Mode
- the
autoCommitTxn
property in the connection URL (see Section 2.8, “Connection Properties for the Driver and Data Source Classes”), - the
setAutoCommitTxn
method (see Section 2.8, “Connection Properties for the Driver and Data Source Classes”), - or on a per-query basis, using the SET statement with the
PROP_TXN_AUTO_WRAP
property (see Section 4.5, “Execution Properties”).
5.8. Multiple Insert Batches
5.9. Global Transactions
org.teiid.jdbc.TeiidDataSource
class for establishing connections.
UserTransaction ut = context.getUserTransaction(); try { ut.begin(); Datasource oracle = lookup(...) Datasource teiid = lookup(...) Connection c1 = oracle.getConnection(); Connection c2 = teiid.getConnection(); // do something with Oracle connection // do something with Teiid connection c1.close(); c2.close(); ut.commit(); } catch (Exception ex) { ut.rollback(); }
XAConnection xaConn = null; XAResource xaRes = null; Connection conn = null; Statement stmt = null; try { xaConn = <XADataSource instance>.getXAConnection(); xaRes = xaConn.getXAResource(); Xid xid = <new Xid instance>; conn = xaConn.getConnection(); stmt = conn.createStatement(); xaRes.start(xid, XAResource.TMNOFLAGS); stmt.executeUpdate("insert into â¦"); // other statements on this connection or other resources enlisted in this transaction // ... xaRes.end(xid, XAResource.TMSUCCESS); if (xaRes.prepare(xid) == XAResource.XA_OK) { xaRes.commit(xid, false); } } catch (XAException e) { xaRes.rollback(xid); } finally { // clean up code // ... }
isSameRM()
method only returns true
if connections are made to the same server instance in a cluster. If the JBoss Data Virtualization connections are to different server instances then transactional behavior may not be the same as if they were to the same cluster member. For example, if the client transaction manager uses the same XID
for each connection, duplicate XID exceptions may arise from the same physical source accessed through different cluster members. If the client transaction manager uses a different branch identifier for each connection, issues may arise with sources that lock or isolate changes based upon branch identifiers.
5.10. Enterprise Information System Support
Chapter 6. Client SSL Connections
6.1. SSL Client Connections
Important
jdbc:teiid:<myVdb>@mms://<host>:<port>
-Djavax.net.ssl.trustStore=<dir>/server.truststore (required) -Djavax.net.ssl.trustStorePassword=<password> (optional) -Djavax.net.ssl.keyStoreType (optional)
-Djavax.net.ssl.keyStore=<dir>/client.keystore (required) -Djavax.net.ssl.keyStrorePassword=<password> (optional) -Djavax.net.ssl.trustStore=<dir>/server.truststore (required) -Djavax.net.ssl.trustStorePassword=<password> (optioanl) -Djavax.net.ssl.keyStroreType=<keystore type> (optional)
teiid-client-settings.properties
file:
######################################## # SSL Settings ######################################## # # The key store type. Defaults to JKS # org.teiid.ssl.keyStoreType=JKS # # The key store algorithm, defaults to # the system property "ssl.TrustManagerFactory.algorithm" # #org.teiid.ssl.algorithm= # # The classpath or filesystem location of the # key store. # # This property is required only if performing 2-way # authentication that requires a specific private # key. # #org.teiid.ssl.keyStore= # # The key store password (not required) # #org.teiid.ssl.keyStorePassword= # # The key alias(not required, if given named certificate is used) # #org.teiid.ssl.keyAlias= # # The key password(not required, used if the key password is different than the keystore password) # #org.teiid.ssl.keyPassword= # # The classpath or filesystem location of the # trust store. # # This property is required if performing 1-way # authentication that requires trust not provided # by the system defaults. # #org.teiid.ssl.trustStore= # # The trust store password (not required) # #org.teiid.ssl.trustStorePassword= # # The cipher protocol, defaults to TLSv3 # org.teiid.ssl.protocol=TLSv1 # # Whether to allow anonymous SSL # (the TLS_DH_anon_WITH_AES_128_CBC_SHA cipher suite) # defaults to true # org.teiid.ssl.allowAnon=true 1-way SSL org.teiid.ssl.trustStore=<dir>/server.truststore (required) 2-way SSL org.teiid.ssl.keyStore=<dir>/client.keystore (required) org.teiid.ssl.trustStore=<dir>/server.truststore (required)
Chapter 7. Using Hibernate with JBoss Data Virtualization
7.1. Configure Hibernate for Use with JBoss Data Virtualization
Prerequisites
- You must have the JBoss Data Virtualization JDBC API client JAR file (
teiid-client.jar
) and the JBoss Data Virtualization hibernate dialect JAR file (teiid-hibernate-dialect-VERSION.jar
) in Hibernate's classpath. These files are found inEAP_HOME/modules/system/layers/dv/org/jboss/teiid/client/main/
.These are required for theorg.teiid.dialect.TeiidDialect
,org.teiid.jdbc.TeiidDriver
andorg.teiid.jdbc.TeiidDataSource
classes.
Procedure 7.1. Configure Hibernate for Use with JBoss Data Virtualization
Open the Hibernate configuration file
Open thehibernate.cfg.xml
file.Specify the JBoss Data Virtualization driver class
Specify the JBoss Data Virtualization driver class in theconnection.driver_class
property:<property name="connection.driver_class"> org.teiid.jdbc.TeiidDriver </property>
Set the Connection URL
Specify the URL for the VDB in theconnection.url
property:<property name="connection.url"> jdbc:teiid:VDB-NAME@mm://HOST:POST;user=USERNAME;password=PASSWORD </property>
Note
Be sure to use a local connection if Hibernate is in the same VM as the application server. .Specify the dialect class
Specify the JBoss Data Virtualization dialect class in thedialect
property:<property name="dialect"> org.teiid.dialect.TeiidDialect </property>
Note
hibernate.properties
file instead of hibernate.cfg.xml
:
hibernate.connection.driver_class=org.teiid.jdbc.TeiidDriver hibernate.connection.url=jdbc:teiid:VDB-NAME@mm://HOST:PORT hibernate.connection.username=USERNAME hibernate.connection.password=PASSWORD hibernate.dialect=org.teiid.dialect.TeiidDialect
Note
<class name="CLASSNAME" table="SOURCE/VIEW_MODEL_NAME.[SCHEMA_NAME.]TABLENAME"> ... </class>
<class name="org.teiid.example.Publisher" table="BOOKS.BOOKS.PUBLISHERS"> ... </class>
7.2. Limitations of Using Hibernate with JBoss Data Virtualization
Chapter 8. OData Support
8.1. What is OData?
8.2. Support for OData
EAP_HOME/dataVirtualization/vdb/teiid-odata-VERSION.war
.
8.3. How to Access the Data?
http://localhost:8080/odata/northwind.1/NW.customers
SELECT * FROM NW.customers
Note
Note
<error xmlns="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"> <code>NotFoundException</code> <message lang="en-US">EdmEntitySet NW.customer is not found</message> </error>
http://localhost:8080/odata/northwind.1/NW.customers?$skiptoken=256
http://localhost:8080/odata/northwind.1/NW.customers?$filter=name eq 'bob'
SELECT * FROM NW.customers where name = 'bob'
http://localhost:8080/odata/northwind.1/NW.customers(1234)/NW.orders?$filter=orderdate > '12-31-2012'
SELECT * FROM NW.orders o join NW.customers c join o.customer_id = c.id where c.id=1234 and o.orderdate > '12-31-2012'
8.4. Security
web.xml
WAR file in EAP_HOME/dataVirtualization/vdb/teiid-odata-VERSION.war
directory.
8.5. Configuration
web.xml
file.
Property Name
|
Description
|
Default Value
|
---|---|---|
batch-size
|
Number of rows to send back each time, -1 returns all rows
|
256
|
skiptoken-cache-time
|
Time interval between the results being recycled/expired between $skiptoken requests
|
300000
|
local-transport-name
|
JBoss Data Virtualization's Local transport name for connection
|
odata
|
8.6. Limitations
- JBoss Data Virtualization implements the OData V2 specification along with many V3 features, however OData V3 is not completely supported.
- Blob support for media types are not supported.
- $value construct to retrieve individual column value is not supported.
- create/update/delete $links is not supported.
- $expand is not supported.
8.7. Client Tools for Access
- Your Browser: The OData Explorer is an online tool for browsing an OData data service.
- Microsoft .NET Framework 3.51: the WCF Data Services framework is available as a separate download for .NET 3.x.
- Microsoft .NET Framework 4.0: the WCF Data Services framework built into .NET 4.0.
- Silverlight 3: the Data Services client library for Silverlight is available for download.
- Java: the Restlet 2.0 library for Java (including Java on your Android phone) supports the OData protocol.
- Java: Use a library like OData4J for Java based access, or any Rest based framework
- JavaScript: the XMLHttpRequest object is standard in modern browsers or you can use jQuery, which comes out of the box with .NET 4.0 or is available for download.
- PHP: the Toolkit for PHPprovides OData support for PHP clients.
- AJAX: if you're using AJAX for ASP.NET, Microsoft provides the ASP.NET Ajax Library for getting to OData.
- Excel 2010 PowerPivot: PowerPivot comes with OData support built right in.
- Windows Desktop: LINQPad is a good tool for building OData queries interactively but it has some limitations: firstly, it is not able to handle FunctionImports (procedures). If the model contains only procedures, no tables then also it acts error. Secondly, it does not work with multiple schema, as it does not show all the schemas, only the default one. Since no default schema is set in the VDB's $metadata, it finds the first one and uses it. OData V2 does allow multiple Schemas and multiple EntityContainers in a single $metadata so it appears that LINQPad is not yet fully compliant with this specification.
- Shell Scripts: use CURL tool
8.8. How JBoss Data Virtualization exposes schema for OData?
http://localhost:8080/odata/northwind/$metadata
Relational Entity
|
Mapped OData Entity
|
---|---|
Model Name
|
Schema Namespace, EntityContainer Name
|
Table/View
|
EntityType, EntitySet
|
Table Columns
|
EntityType's Properties
|
Primary Key
|
EntityType's Key Properties
|
Foreign Key
|
Navigation Property on EntityType, Association, AssosiationSet
|
Procedure
|
FunctionImport
|
Procedure's Table Return
|
ComplexType
|
Warning
Appendix A. Unsupported JDBC Methods
A.1. Unsupported JDBC Methods
SQLException
stating that it is not supported.
A.2. ResultSet Limitations
TYPE_SCROLL_SENSITIVE
is not supported.UPDATABLE
ResultSet
s are not supported.- Returning multiple
ResultSet
s from Procedure execution is not supported.
A.3. Unsupported Classes and Methods in java.sql
Table A.1. Connection Properties
Class name
|
Methods
|
---|---|
Array
| Not Supported
|
Blob
|
getBinaryStream(long, long) - throws SQLFeatureNotSupportedException setBinaryStream(long) - - throws SQLFeatureNotSupportedException setBytes - - throws SQLFeatureNotSupportedException truncate(long) - throws SQLFeatureNotSupportedException |
CallableStatement
|
getArray - throws SQLFeatureNotSupportedException getObject(int parameterIndex, Map&lt;String, Class&lt;?&gt;&gt; map) - throws SQLFeatureNotSupportedException getRef - throws SQLFeatureNotSupportedException getRowId - throws SQLFeatureNotSupportedException getURL(String parameterName) - throws SQLFeatureNotSupportedException registerOutParameter - ignores registerOutParameter(String parameterName, *) - throws SQLFeatureNotSupportedException setRowId(String parameterName, RowId x) - throws SQLFeatureNotSupportedException setURL(String parameterName, URL val) - throws SQLFeatureNotSupportedException |
Clob
|
getCharacterStream(long arg0, long arg1) - throws SQLFeatureNotSupportedException setAsciiStream(long arg0) - throws SQLFeatureNotSupportedException setCharacterStream(long arg0) - throws SQLFeatureNotSupportedException setString - throws SQLFeatureNotSupportedException truncate - throws SQLFeatureNotSupportedException |
Connection
|
createArrayOf - throws SQLFeatureNotSupportedException createBlob - throws SQLFeatureNotSupportedException createClob - throws SQLFeatureNotSupportedException createNClob - throws SQLFeatureNotSupportedException createSQLXML - throws SQLFeatureNotSupportedException createStruct(String typeName, Object[] attributes) - throws SQLFeatureNotSupportedException getClientInfo - throws SQLFeatureNotSupportedException releaseSavepoint - throws SQLFeatureNotSupportedException rollback(Savepoint savepoint) - throws SQLFeatureNotSupportedException setHoldability - throws SQLFeatureNotSupportedException setSavepoint - throws SQLFeatureNotSupportedException setTypeMap - throws SQLFeatureNotSupportedException |
DatabaseMetaData
|
getAttributes - throws SQLFeatureNotSupportedException getClientInfoProperties - throws SQLFeatureNotSupportedException getFunctionColumns - throws SQLFeatureNotSupportedException getFunctions - throws SQLFeatureNotSupportedException getRowIdLifetime - throws SQLFeatureNotSupportedException |
NClob
| Not Supported
|
PreparedStatement
|
setArray - throws SQLFeatureNotSupportedException setRef - throws SQLFeatureNotSupportedException setRowId - throws SQLFeatureNotSupportedException setUnicodeStream - throws SQLFeatureNotSupportedException |
Ref
| Not Implemented
|
ResultSet
|
deleteRow - throws SQLFeatureNotSupportedException getArray - throws SQLFeatureNotSupportedException getHoldability - throws SQLFeatureNotSupportedException getObject(*, Map&lt;String, Class&lt;?&gt;&gt; map) - throws SQLFeatureNotSupportedException getRef - throws SQLFeatureNotSupportedException getRowId - throws SQLFeatureNotSupportedException getUnicodeStream - throws SQLFeatureNotSupportedException getURL - throws SQLFeatureNotSupportedException insertRow - throws SQLFeatureNotSupportedException moveToInsertRow - throws SQLFeatureNotSupportedException refreshRow - throws SQLFeatureNotSupportedException rowDeleted - throws SQLFeatureNotSupportedException rowInserted - throws SQLFeatureNotSupportedException rowUpdated - throws SQLFeatureNotSupportedException setFetchDirection - throws SQLFeatureNotSupportedException update* - throws SQLFeatureNotSupportedException |
RowId
| Not Supported
|
Savepoint
| not Supported
|
SQLData
| Not Supported
|
SQLInput
| not Supported
|
SQLOutput
| Not Supported
|
Statement
|
setCursorName(String) |
Struct
| Not Supported
|
A.4. Unsupported Classes and Methods in javax.sql
Table A.2. Connection Properties
Class name
|
Methods
|
---|---|
RowSet*
| Not Supported
|
Appendix B. Keytool
B.1. Keytool
B.2. Using Keytool with JBoss Data Virtualization
- Set the alias to
teiid
using the-alias teiid
option. - Set the algorithm to
RSA
using the-keyslg RSA
option. - Set the validity period to
365
days using the-validity 365
option. - Set the store type to
JKS
using the-storetype JKS
option.
B.3. Create a Private/Public Key Pair with Keytool
Procedure B.1. Create a Private/Public Key Pair with Keytool
- Run the
keytool -genkey -alias ALIAS -keyalg ALGORITHM -validity DAYS -keystore server.keystore -storetype TYPE
command:keytool -genkey -alias teiid -keyalg RSA -validity 365 -keystore server.keystore -storetype JKS
- If the specified keystore already exists, enter the existing password for that keystore, otherwise enter a new password:
Enter keystore password: <password>
- Answer the following questions when prompted:
What is your first and last name? [Unknown]: <userâÂÂs name> What is the name of your organizational unit? [Unknown]: <department name> What is the name of your organization? [Unknown]: <company name> What is the name of your City or Locality? [Unknown]: <city name> What is the name of your State or Province? [Unknown]: <state name> What is the two-letter country code for this unit? [Unknown]: <country name>
- Enter
yes
to confirm the provided information is correct:Is CN=<userâÂÂs name>, OU=<department name>, O="<company name>", L=<city name>, ST=<state name>, C=<country name> correct? [no]: yes
- Enter your desired keystore password:
Enter key password for <server> (Return if same as keystore password)
The server.keystore
file contains the newly generated public and private key pair.
B.4. Extract a Self-signed Certificate from the Keystore
Procedure B.2. Extract a Self-signed Certificate from the Keystore
- Run the
keytool -export -alias ALIAS -keystore server.keystore -rfc -file public.cert
command:keytool -export -alias teiid -keystore server.keystore -rfc -file public.cert
- Enter the keystore password when prompted:
Enter keystore password: <password>
This creates the public.cert
file that contains a certificate signed with the private key in the server.keystore
.
B.5. Add a Certificate to a Truststore Using Keytool
Procedure B.3. Add a Certificate to a Truststore Using Keytool
- Run the
keytool -import -alias ALIAS -file public.cert -storetype TYPE -keystore server.truststore
command:keytool -import -alias teiid -file public.cert -storetype JKS -keystore server.truststore
- If the specified truststore already exists, enter the existing password for that truststore, otherwise enter a new password:
Enter keystore password: <password>
- Enter
yes
when prompted to trust the certificate:Owner: CN=<user's name>, OU=<dept name>, O=<company name>, L=<city>, ST=<state>, C=<country> Issuer: CN=<user's name>, OU=<dept name>, O=<company name>, L=<city>, ST=<state>, C=<country> Serial number: 416d8636 Valid from: Fri Jul 31 14:47:02 CDT 2009 until: Sat Jul 31 14:47:02 CDT 2010 Certificate fingerprints: MD5: 22:4C:A4:9D:2E:C8:CA:E8:81:5D:81:35:A1:84:78:2F SHA1: 05:FE:43:CC:EA:39:DC:1C:1E:40:26:45:B7:12:1C:B9:22:1E:64:63 Trust this certificate? [no]: yes
The certificate in public.cert
has been added to the new truststore named server.truststore
.
Appendix C. Revision History
Revision History | |||
---|---|---|---|
Revision 6.2.0-57 | Thu Dec 10 2015 | David Le Sage | |
|