Copyright © TIBCO Software Inc. All Rights Reserved
Copyright © TIBCO Software Inc. All Rights Reserved


Chapter 6 Using TIBCO Object Service Broker Adapter for JDBC-ODBC : Accessing TIBCO Object Service Broker Using 32-bit ODBC

Accessing TIBCO Object Service Broker Using 32-bit ODBC
Overview of ODBC support
What is TIBCO Object Service Broker Adapter for ODBC?
Microsoft’s Open Database Connectivity (ODBC) interface is used by a wide variety of ODBC-aware applications to access data in ODBC-compliant data sources. Such DBMSs expose ODBC functionality using Structured Query Language (SQL).
The ODBC component of TIBCO Object Service Broker Adapter for JDBC-ODBC is the TIBCO Object Service Broker-specific implementation of the ODBC application programming interface (API). An application that is unaware of TIBCO Object Service Broker’s specifics can use it to:
How Does it Work?
ODBC support for TIBCO Object Service Broker Adapter uses the OpenAccess™ product to expose the ODBC functionality to applications. OpenAccess accepts SQL statements and translates them into a standardized subset of requests passed to the TIBCO Object Service Broker-specific software layer. The latter uses the TIBCO Object Service Broker SDK (C/C++) client layer to handle TIBCO Object Service Broker sessions and transactions and to communicate with its rules-based server layer.
When an application requests an ODBC connection to TIBCO Object Service Broker, a regular TIBCO Object Service Broker session starts. The application uses standard ODBC function calls to set required connection and statement options. It submits a SQL statement that the OpenAccess layer parses and breaks into irreducible units that can be translated into the basic TIBCO Object Service Broker data requests: FORALL, INSERT, REPLACE, and DELETE. The required actions are carried out via the SDK (C/C++) and the results are returned to the OpenAccess layer.
Any number of ODBC cursors can be opened against a TIBCO Object Service Broker table simultaneously within a transaction. TIBCO Object Service Broker security settings are fully honored by the ODBC driver for TIBCO Object Service Broker when accessing tables. Because the SQL standard does not support the concept of data-parameterized tables, the ODBC driver presents a parameterized table to the ODBC caller as a flat construct, that is, data parameters are reported as fields (members of a composite key).
How Can it be Used?
Normally, third party applications make use of the ODBC driver for TIBCO Object Service Broker exactly the way they use other ODBC drivers. Users can, however, write their own applications to issue standard ODBC function calls. For details on programming environments and using ODBC, refer to relevant ODBC documentation. Refer to ODBC Conformance Levels for details on the ODBC conformance levels supported by the TIBCO Object Service Broker Adapter for JDBC-ODBC.
Configuring the TIBCO Object Service Broker Adapter for ODBC
The ODBC driver for TIBCO Object Service Broker uses a number of connection attributes to connect to a TIBCO Object Service Broker node. These attributes are provided via an ODBC Data Source Name (DSN) that the caller references at connection time. Alternatively, a method without DSN can be used. Refer to Connecting Without a DSN for details on connecting without using a DSN.
Creating and Configuring a DSN for the TIBCO Object Service Broker Adapter for JDBC-ODBC
1.
2.
Click the Add... button.
3.
In the Create New Data Source dialog, select the TIBCO ODBC driver for the current release. For example, for the 6.0.0 release, select the TIBCO OSB 6.0.0.0 ODBC Adapter.
4.
Click Finish. The TIBCO OSB ODBC Data Source Setup dialog appears. For example:
5.
In the Setup dialog, enter a name of your data source and, optionally, a description for this data source.
6.
7.
Optionally, enter an OSB user name and password. These fields can also be entered in the Custom Properties field or not at all.
8.
Make sure that correct driver is selected for the Service Name. The Service Name should show the TIBCO ODBC driver selected in step 3.
9.
Select Default in the Service Data Source field.
10.
Click OK to save the data source configuration.
Testing a Data Source
You can test a data source by pressing the Test button on the Setup dialog. A Logon to Data Source dialog appears, allowing you to enter a OSB user name and password and modify connection parameters.
Click the OK button to test whether a connection can be established for the data source.
Updating a Data Source
You can edit an existing data source by selecting a data source in the ODBC Data Source Administrator and clicking the Configure button. The dialog displayed is the same dialog used to create a data source, and described above in Creating and Configuring a DSN for the TIBCO Object Service Broker Adapter for JDBC-ODBC.
Connecting to TIBCO Object Service Broker
Applications request a connection to TIBCO Object Service Broker via a SQLConnect, a SQLDriverConnect, or a SQLBrowseConnect ODBC function call. If a DSN is referenced, the ODBC driver for TIBCO Object Service Broker assumes the session startup attributes to be available from the DSN. Therefore, it reads the Connect String from the DSN. If the caller does not reference a DSN, it is expected to provide a string that contains, along with the mandatory tokens, the same elements as the Connect String field in a DSN. Refer to Connecting Without a DSN for details on connecting without using a DSN.
Constructing the Connect String
The value entered in the Connect String field is the actual configuration string of an ODBC client session to be started by the SQLConnect, the SQLDriverConnect, or the SQLBrowseConnect ODBC function call referring to this DSN.
The string is a sequence of keyword/value pairs, separated by semicolons. For example:
OSBHOST=abc;OSBPORT=9000;UID=JOHN;PWD=JOHN
Keyword Description
 
A reference to the machine where the Execution Environment (osMon in case of Windows/Solaris; Native/CICS Execution Environment in case of z/OS) is running (“localhost” if connecting locally). Must be specified with PORT, if NODE is not specified. Cannot be used with NODE.
The port number defined for the Execution Environment denoted by HOST. Must be specified with HOST, if NODE is not specified. Do not use with NODE.
A reference to an entry in the huron.dir file describing the TIBCO Object Service Broker nodes available for connections. Cannot be used with HOST or PORT. Must be specified if HOST and PORT are not specified.
OSBSESS [optional Windows/Solaris only]
OSBEE [optional Windows/Solaris only]
The user ID for connecting to TIBCO Object Service Broker. If not available either from the caller at the connect time or from the connect string, UID defaults to the value, if any, in the respective section of the session.prm file.
The user password corresponding to UID. If not available either from the caller at the connect time or from the connect string, PWD defaults to the value, if any, in the respective section of the session.prm file.
OSBBROWSE [optional]
“Y” or “y” for TRUE; “D” or “d” for DEFAULT; any other value stands for FALSE. TRUE means that TIBCO Object Service Broker is to start up a BROWSE session.
DEFAULT means that the TIBCO Object Service Broker session’s BROWSE attribute is set to the value, if any, in the respective section of the session.prm file.
OSBCOML [optional]
COMMIT or ROLLBACK at COMMITLIMIT time. COMMIT means “commit updates and proceed with transaction”; ROLLBACK means “roll back changes and raise a COMMITLIMIT error condition” (standard behavior).
OSBUNIT0, OSBUNIT1, OSBUNIT2, OSBUNIT3, OSBUNIT4
[all optional]
Values of the TIBCO Object Service Broker UNIT table attribute to restrict the result set returned by the ODBC SQLTables function.
OSBCPAD [optional]
“Y” or “y” for TRUE; any other value stands for FALSE. Determines whether fixed-length character (CHAR) fields should be right- blank-padded by TIBCO Object Service Broker Adapter for JDBC-ODBC.
Connecting Without a DSN
The string supplied by way of SQLDriverConnect or SQLBrowseConnect must contain the mandatory substring:
DRIVER={TIBCO OSB 6.0.0.0 ODBC Adapter};PRT=TIBCO OSB 6 0 0 0
The mandatory substring is followed by a substring constructed according to the rules in Constructing the Connect String and Keyword Description. For example:
DRIVER={TIBCO OSB 6.0.0.0 ODBC Adapter};PRT=TIBCO OSB 6 0 0 0;UID=john;PWD=JOHN;OSBSESS=efg.
Pre-Configured Data Sources
The installation of the TIBCO Object Service Broker Adapter for JDBC-ODBC creates two ODBC data sources with the following characteristics:
TIBCO Object Service Broker Local
 
TIBCO OSB version Local.
where version is the current release of the TIBCO Object Service Broker Adapter for JDBC-ODBC. For example:
TIBCO Object Service Broker Remote
 
TIBCO OSB version Remote.
where version is the current release of the TIBCO Object Service Broker Adapter for JDBC-ODBC. For example:
If the TIBCO Object Service Broker Adapter for JDBC-ODBC is installed on a computer along with TIBCO Object Service Broker, the TIBCO OSB version Local DSN is ready for use by an ODBC-aware application. If the ODBC driver for TIBCO Object Service Broker is to start a session on another computer (for example, the TIBCO Object Service Broker Adapter for JDBC-ODBC is installed as a stand-alone client), the TIBCO Object Service Broker Remote DSN must be updated: namely, <host> should be replaced with the name or IP address of the computer where the Execution Environment runs.
These two DSNs are removed when the TIBCO Object Service Broker Adapter for JDBC-ODBC is uninstalled, and they are restored to their initial state when the TIBCO Object Service Broker Adapter for JDBC-ODBC is reinstalled. Accordingly, a new DSN should be created if it is to persist, as the installation of the TIBCO Object Service Broker Adapter for JDBC-ODBC does not affect DSNs except TIBCO OSB Local and TIBCO OSB Remote.
Configuring TIBCO Object Service Broker Components
The ODBC driver for TIBCO Object Service Broker Adapter interacts with the following TIBCO Object Service Broker components:
The TIBCO Object Service Broker Adapter for JDBC-ODBC attributes OSBHOST/OSBPORT or OSBNODE fully describe the osMon instance to be used to establish connections to TIBCO Object Service Broker.
Execution Environment
On a connection request, osMon either creates or uses an already available Execution Environment, which, in turn, creates a TIBCO Object Service Broker session. The ODBC driver connection parameters determine which Execution Environment will be used. The DOB Execution Environment parameter designates the node in the huron.dir file. This parameter can be specified either in the mon.prm or in the ee.prm file.
Note  The target Data Object Broker can reside on any of the platforms supported by TIBCO Object Service Broker.
The Execution Environment creates a session for each connection requested by the ODBC driver for TIBCO Object Service Broker. If the SESS attribute is included in the connect string, its value refers to the name of a group in the session.prm file. The OSBEE, UID,PWD, and OSBBROWSE attributes take precedence, if they are part of the connect string.
 
Native/CICS Execution Environment
On a connection request, a standby session within an Execution Environment is allocated. A reference to a particular Execution Environment comes as part of the connection request by the ODBC driver for TIBCO Object Service Broker (OSBHOST/OSBPORT or OSBNODE). The Execution Environment's TDS parameter designates the TIBCO Object Service Broker node.
The TIBCO Object Service Broker Execution Environment uses a standby session for each connection requested by the ODBC driver for TIBCO Object Service Broker.
Determining the Rules Search Path (Windows / Solaris)
As part of a session startup procedure, TIBCO Object Service Broker looks up the rules library denoted by the INSTLIB Execution Environment parameter (usually referred to as the SITE library), to pre-bind the rules available. This can constitute a performance setback for the user, especially when working with a Data Object Broker on z/OS, should the site library contain a large number of rules. This activity is mostly useless, as none of the TIBCO Object Service Broker Adapter for JDBC-ODBC’s features depend on it, except for the invocation of trigger rules associated with TIBCO Object Service Broker tables.
If no trigger rules are associated with TIBCO Object Service Broker tables to be accessed by the ODBC driver for TIBCO Object Service Broker, the recommended setting for the SEARCH session parameter is S (system).
If the trigger rules to be potentially invoked can be identified in advance and moved into a separate library, specify that library as the INSTLIB Execution Environment parameter, and set the SEARCH parameter to I (installation).
Setting the COML Attribute
Normally, TIBCO Object Service Broker raises a COMMITLIMIT exception whenever its intent list becomes full. This behavior is the default for the TIBCO Object Service Broker Adapter for JDBC-ODBC as well. An ODBC application can, however, submit a SQL statement that would require a longer sequence of updates than the intent list could hold.
If the COML attribute is set to COMMIT, the TIBCO Object Service Broker Adapter for JDBC-ODBC issues a COMMIT request and proceeds with the SQL statement until completion. This allows an ODBC application to overcome the TIBCO Object Service Broker limitation on the intent list’s size. This feature should be used with care, as transactional integrity is compromised by intermediate commits during what appears to be one unit of work to the caller.
Setting the BROWSE Attribute
The ODBC driver for TIBCO Object Service Broker interprets the BROWSE attribute as follows:
All transactions are created in BROWSE mode. This means that no lock on data is taken, and no update (INSERT, UPDATE, DELETE) is carried out.
The application can effectively change the BROWSE attribute via a SQLSetConnectOption function call to set the ODBC option SQL_ACCESS_MODE to either SQL_MODE_READ_WRITE or SQL_MODE_READ_ONLY.
Using the OSBUNITx Attributes
The connect string can contain the attributes OSBUNIT0, OSBUNIT1, OSBUNIT2, OSBUNIT3, and OSBUNIT4 to request the TIBCO Object Service Broker Adapter for JDBC-ODBC to restrict the result set returned by the SQLTables function. If none of these attributes is specified, no restriction applies. The restriction, if any, is effective throughout the entire session. The restriction looks similar to UNIT0=<value1> OR UNIT3=<value1>. Only those of the UNITx attributes assigned values in the connect string are included in the form of a logical OR relationship.
TIBCO Object Service Broker Adapter for JDBC-ODBC and Distributed Data
The SQL-compliant notation used by the ODBC driver for TIBCO Object Service Broker for tables in SQL statements includes three dot-delimited, case-insensitive character strings:
[Optional] Schema, or owner: the TIBCO Object Service Broker node name known in TIBCO Object Service Broker as location table parameter.
Provided L is the home location, that is, the node name of the Data Object Broker to which the ODBC driver for TIBCO Object Service Broker is connected, an application can refer to the local instance of table T as follows: TIBCO.L.T, L.T, or T, whereas TIBCO.R.T and R.T refer to a remote instance of table T residing on node R. This is analogous to the TIBCO Object Service Broker native notation as in T(R).
To provide access to a table remotely, the ODBC driver for TIBCO Object Service Broker does not require that a definition of that table, whether full or minimal, be available from the local node. Also, a location parameter is not required. The following rules apply for location evaluation:
A minimal definition, the request is directed to the location derived from the definition (standard TIBCO Object Service Broker behavior)
A minimal definition, the request is directed to the location derived from the definition (standard TIBCO Object Service Broker behavior)
Locations derived from minimal definitions are honored, whereas locations derived from full definitions are ignored. This is because the SQL standard regards the notation T as identical to L.T, where L is T’s home location.
Stored Procedures
Available Function Calls
The ODBC specification provides the following function calls to handle stored procedures:
Returns a result set of rows, each describing the name and properties of a parameter of a stored procedure
SQLPrepare (SQLExecDirect)
Submits a SQL statement such as {[<r>=]call <name>[(<v1>,<v2>,…)]}, where <name> is the name of the procedure, <r> is an optional return value, and <v1>, <v2>,… are optional parameters of the procedure
SQLExecute (SQLExecDirect)
Switches to the next result, if any, returned by the procedure. In the discussion below, a sequence of one SQLExecute or SQLExecDirect call, followed by a series of SQLMoreResults calls on the same statement handle, is referred to as stored procedure invocation cycle.
A procedure can return the following results:
Designating TIBCO Object Service Broker Rules as ODBC Stored Procedures
TIBCO Object Service Broker rules to be run as ODBC stored procedure must be pre-registered in TIBCO Object Service Broker's persistent table @IP_PROCS that has three columns:
If ARGTAB is an empty value, PROC is considered neither to return a value nor to have any parameters.
If ARGTAB is a non-empty value, it must be the name of TIBCO Object Service Broker table of type TEM, with the IDgen property set to Y, one data parameter defined as B 4 and one primary key (for example, ARGK) defined as I B 4. All of ARGT's fields except ARGK, in their positional order, represent PROC's optional return value and parameters. If not designated otherwise, PROC is understood to return no value and to accept INPUT parameters corresponding to all fields of ARGT, except ARGK.
The designation of a field (for example, ARGF) of table ARGT as a return value or parameter of a particular type is carried out by setting the field SOURCE of the occurrence corresponding to ARGF in the FIELDS(ARGT) table instance:
Writing TIBCO Object Service Broker Rules as ODBC Stored Procedures
The following requirements apply to the rule (for example, PROC) designated as an ODBC stored procedure:
0 - the rule is being passed control for the first time within the current invocation cycle of PROC (in other words, the client has issued SQLExecute/SQLExecDirect to invoke PROC)
Positive integer (for example, N) - the rule is being passed control for the (N+1)-th time within the current invocation cycle of PROC (in other words, the client has issued SQLMoreResults for the Nth time).
The argument HANDLE is set by the adapter and is guaranteed to have the same value throughout the entire invocation cycle.
At the time PROC relinquishes control, it must have assigned the following locals (defined by the TIBCO Object Service Broker Adapter for JDBC-ODBC):
Y—the current invocation cycle is not over, that is, PROC expects to be invoked later via at least one more SQLMoreResults call
N (preset by the TIBCO Object Service Broker Adapter for JDBC-ODBC)—this is the last time PROC is entered within the current invocation cycle, that is, a subsequent SQLMoreResults will return SQL_DATA_NOT_FOUND
an empty value (preset by the TIBCO Object Service Broker Adapter for JDBC-ODBC)—neither a result set (cursor) nor a result count are being returned (this is identical to returning -1)
a negative number other than -1—an error code is being returned; PROC is not to be called again within the current invocation cycle
an alphanumeric value—the name (for example, CURT) of the TIBCO Object Service Broker table representing the current cursor for the client to fetch from
The client issues an SQLExecute (or SQLExecDirect) function call, followed by a series of SQLMoreResults function calls, until it gets the SQL_DATA_NOT_FOUND return code. On the adapter side, these function calls are implemented as a respective series of invocations of PROC until it returns N in MORE_. In TIBCO Object Service Broker terms, these invocations are performed as separate events, meaning that PROC may maintain any number of any table occurrences between the calls but must reestablish the current occurrence buffers every time it is invoked.
In order to get hold of the parameter values passed by the caller, PROC is expected to issue GET ARGT(HANDLE). In order to assign the procedure's return value or OUTPUT/INPUTOUTPUT parameter values, PROC has to issue REPLACE ARGT(HANDLE). These actions can be carried out any number of times and in any sequence within an invocation cycle.
Creating Cursors in TIBCO Object Service Broker Adapter for JDBC-ODBC Stored Procedures
The TIBCO Object Service Broker Adapter for JDBC-ODBC implementation allows any number of cursors to be returned by stored procedures, meaning that no restrictions exist as to the number of times SQLMoreResults can return SQL_SUCCESS after one SQLExecute/SQLExecDirect call. This is achieved by having the procedure itself determine whether or not it is finished executing, and if a result set (cursor) is available at a particular stage.
PROC notifies the caller that a cursor is available by returning a non-empty CURT value in the RESULT_ local. This value is passed to the caller, so the ODBC fetch sequence can be carried out by the application against the cursor. This operation ends up issuing a request similar to FORALL CURT(HANDLE).
Normally, CURT refers to a table of type TEM with one data parameter defined as B 4. If so, PROC is expected to populate a cursor by issuing INSERT CURT(HANDLE). The TIBCO Object Service Broker Adapter for JDBC-ODBC clears the instance CURT(HANDLE) after it has been discarded (for example, upon a subsequent SQLMoreResults call). If, however, CURT is not of type TEM, no table clearing occurs.
If CURT has no data parameters, the FORALL request works as if FORALL CURT was issued. In other cases, an error message is returned.
Object Service Broker ODBC Stored Procedures Emulator
Prior to registering a stored procedure and driving its execution via an ODBC client, TIBCO recommends to run and debug it in the Workbench using the emulator provided as a standalone rule IP_PROC_DRV. This mode allows you to detect most of the coding errors that are difficult to fix when running the rule as a stored procedure, because rule dumps are not readily available then. Here are the tasks to follow:
1.
2.
3.
If the stored procedure returns a value or accepts or modifies any parameters, define a table, for example ARGT, of type TEM, with IDgen=Y and one data parameter of syntax B 4.
4.
5.
If ARGT is a non-empty name, a table editor screen is displayed. Enter the initial values for parameters (represented by ARGT's fields).
6.
Browse the message log that contains either a rule dump (in case of error) or a collection of lines representing the output produced by PROC in a context similar to one established by the TIBCO Object Service Broker Adapter for JDBC-ODBC.
Sample
A sample rule IP _SMPL_PROC is provided with the TIBCO Object Service Broker Adapter for JDBC-ODBC:

 
IP_SMPL_PROC (HANDLE, COUNT);
_ LOCAL @WHO;
_ ---------------------------------------------------------------------
_ COUNT = 0; | Y N N N
_ COUNT = 1; | Y N N
_ COUNT = 2; | Y N
_ ------------------------------------------------------------+--------
_ GET @IP_SMPL_ARG(HANDLE); | 1 1
_ @WHO = @IP_SMPL_ARG.WHO; | 2 2
_ FORALL @IP_SMPL_DATA WHERE WHO = @WHO : | 3
_ @IP_SMPL_CUR.SENTENCE = @WHO || ' eats ' || |
_ @IP_SMPL_DATA.WHAT; |
_ INSERT @IP_SMPL_CUR(HANDLE); |
_ END; |
_ FORALL @IP_SMPL_DATA WHERE WHO ^= @WHO : | 3
_ @IP_SMPL_CUR.SENTENCE = @WHO || ' does not eat ' || |
_ @IP_SMPL_DATA.WHAT; |
_ INSERT @IP_SMPL_CUR(HANDLE); |
_ END; |
_ MORE_ = 'Y'; | 1 4
_ RESULT_ = '@IP_SMPL_CUR'; | 5 4
_ MORE_ = 'N'; | 5
_ -------------------------------------------------------------------

 
When invoked as stored procedure, this rule:
Execute IP_SMPL_DRV(IP_SMPL PROC_, @IP_SMPL_ARG); enter “man” (or “bug”) for WHO in the table editor screen and save; the message log should look similar to:

 
=== ARGUMENTS ===
1 man
Pass 0; MORE_: Y,
================
Pass 1; MORE_: Y; RESULT_: @IP_SMPL_CUR
================
1 man eats meat
2 man eats bread
3 man eats fish
Pass 2; MORE_: N; RESULT_: @IP_SMPL_CUR
================
1 man does not eat grass
2 man does not eat wood
=== ARGUMENTS ===
1 man

 
This rule is registered in @IP_PROCS as

 
PROCNAME ARGTAB
---------------- ----------------
IP_SMPL_PROC @IP_SMPL_ARG

 
Accordingly, executing the statement {call IP_SMPL_PROC(‘man’)} via the TIBCO Object Service Broker Adapter for JDBC-ODBC must yield same results as above.
Notes on Behavior
Supported TIBCO Object Service Broker Table Types
The following TIBCO Object Service Broker internal table types are supported:
 
The TIBCO Object Service Broker Adapter for JDBC-ODBC is oblivious of the tables of any other internal table type, even if they are present in the TIBCO Object Service Broker database. Some restrictions apply due to the nature of a particular table type, namely:
TIBCO Object Service Broker internal table types are not exposed to an ODBC caller. Instead, the following ODBC table types are assigned to table definitions:
SYSTEM TABLE
All definitions whose TIBCO Object Service Broker internal table type is supported and whose AUTHOR attribute is HURON or HURON2.
See Also
TIBCO Object Service Broker Managing Data for information about table types.
Using Parameterized Tables
As the SQL standard does not provide for the concept of data-parameterized tables, the TIBCO Object Service Broker Adapter for JDBC-ODBC presents a parameterized table to the ODBC caller as a “flat” construct, that is, data parameters are reported as fields. They are considered, in their natural order, as most-significant members of a composite primary key preceding the actual primary keys in a table definition. In their capacity as fields, data parameters can be used in field lists, WHERE clauses, and so on.
To enable the use of data parameters in a WHERE clause against a table, the TIBCO Object Service Broker Adapter for JDBC-ODBC dynamically creates against that table a PRM view that allows the full resolution of the logical criterion. This is possible only for the TDS, EES, SES, and TEM table types, as well as SUB tables against those.
For those table types that do not allow a PRM definition, the following restriction applies: if the parsing of the WHERE clause does not yield a well-defined set of data parameter instances, the request is rejected as invalid. In this context, the expression “well-defined set” means zero or more fully specified data parameter instances, for example:
How Rows are Replaced
The TIBCO Object Service Broker Adapter for JDBC-ODBC replaces rows whenever an UPDATE <table> SET… WHERE… statement is submitted. The WHERE clause is optional but, if it is part of the statement, it can mention any field, including data parameter and/or primary keys. The SET group could require that these fields be assigned new values, for example, the following statement is completely valid: UPDATE T SET p=0, k='a' WHERE p=1 AND k='b'. In this example, p is assumed to be a data parameter and k, a primary key.
To carry out this UPDATE request, the TIBCO Object Service Broker Adapter for JDBC-ODBC first deletes the row denoted by the WHERE clause and then inserts a new row. This technique implies that two entries are added, transparently for the caller, to the current transaction’s intent list.
How Transactions are Handled
The TIBCO Object Service Broker Adapter for JDBC-ODBC creates a TIBCO Object Service Broker transaction upon establishing a connection to TIBCO Object Service Broker. All subsequent activities are carried out within the boundaries of this transaction, until a commit/rollback request is encountered. While ODBC applications have no means to start transactions explicitly, they do control the termination of transactions via the SQL_AUTOCOMMIT connection option setting:
If the SQL_AUTOCOMMIT option is set to SQL_AUTOCOMMIT_ON, every successful update action triggers a transaction switch, that is, the currently running transaction is implicitly terminated and a new transaction is created.
If the SQL_AUTOCOMMIT option is set to SQL_AUTOCOMMIT_OFF, the application can terminate the current transaction at any time via a SQLTransact (SQLEndTrans) function call. A new transaction is created immediately, that is, before control is returned to the caller of this function.
The TIBCO Object Service Broker Adapter for JDBC-ODBC closes all open cursors at transaction termination time, but it preserves the state of all “prepared” statement contexts. Nested transactions are neither supported nor created implicitly.
Transactions inherit their BROWSE mode from the BROWSE attribute passed to the TIBCO Object Service Broker Adapter for JDBC-ODBC at startup time (or changed subsequently by the ODBC application). A BROWSE transaction can exist indefinitely without running short of resources, as it refrains from taking locks that can be released only at transaction end.
Support for Distributed Transactions (Windows, Solaris)
The TIBCO Object Service Broker Adapter for JDBC-ODBC provides limited support for distributed transactions that a caller initializes and manages via the Microsoft Distributed Transaction Coordinator (MS DTC) product. To exercise this functionality with a COM, OLE DB, or ODBC client, follow the specifications described in the Microsoft documentation. There is no support for XA-compliant callers, that is, the SQL_ATTR_ENLIST_IN_XA option is unsupported.
The sequence of events is as follows:
1.
The caller starts a distributed transaction and requests, via MS DTC, that the TIBCO Object Service Broker Adapter for JDBC-ODBC enlist.
2.
MS DTC invokes the TIBCO Object Service Broker Adapter for JDBC-ODBC’s SQLSetConnectOption function with SQL_ATTR_ENLIST_IN_DTC as option notation and the transaction’s handle as option value.
3.
The TIBCO Object Service Broker Adapter for JDBC-ODBC creates a resource manager (RM) if one does not already exist. It marks the current connection as busy and enlists it on the transaction via the RM.
The enlistment procedure effectively passes to MS DTC the pointers to the prepare-to-commit, commit, and rollback callbacks.
4.
To commit the transaction, MS DTC invokes the prepare-to-commit callback, which, in the current implementation of the TIBCO Object Service Broker Adapter for JDBC-ODBC, always reports success. MS DTC invokes the commit callback or the rollback callback in the TIBCO Object Service Broker Adapter for JDBC-ODBC.
5.
The TIBCO Object Service Broker Adapter for JDBC-ODBC schedules the action to be carried out asynchronously and returns control to MS DTC.
6.
When the action is done, the TIBCO Object Service Broker Adapter for JDBC-ODBC notifies MS DTC via MS DTC’s callback.
7.
MS DTC invokes the TIBCO Object Service Broker Adapter for JDBC-ODBC’s SQLSetConnectOption function with SQL_ATTR_ENLIST_IN_DTC as option notation and NULL as option value.
8.
ODBC Conformance Levels
The 32-bit ODBC driver bases its ODBC Conformance Levels on those supported by OpenAccess Software’s OpenAccess™. Currently, the TIBCO Object Service Broker Adapter for JDBC-ODBC uses OpenAccess 7.0. Refer to the OpenAccess manual for more information.
SQL Conformance
Level of support: Minimum+, which includes SELECT, INSERT, UPDATE, DELETE, SELECT FOR UPDATE, Expressions, Nested queries, and Scalar functions.
ODBC API Conformance
All core and Level 1 and most Level 2 Microsoft ODBC 3.51 API function calls are supported. Use the SQL GetFunctions function call to see a full list.
Error Codes and Messages
The TIBCO Object Service Broker Adapter for JDBC-ODBC follows the ODBC specification with respect to handling error situations, and returns the following to the caller of the SQLError function:
Error message: a character string containing a text error message. The string is prepended with one or more component identifiers in square brackets. If the last of those identifiers is [TIBCO Object Service Broker ODBC], the message comes from the TIBCO Object Service Broker Adapter for JDBC-ODBC. Otherwise, another software layer between the caller and the TIBCO Object Service Broker Adapter for JDBC-ODBC is responsible for the message.
Native Error
SDK (C/C++) general error.
Only TIBCO supported as CATALOG (QUALIFIER).
For positive values of the Native Error Code, refer to Chapter 7, Using TIBCO Object Service Broker SDK (C/C++).

Copyright © TIBCO Software Inc. All Rights Reserved
Copyright © TIBCO Software Inc. All Rights Reserved