Operations Modes
You can expose tables, views, and stored procedures using the Microsoft BizTalk Adapter for Oracle Database. The adapter supports two modes of use:
- Session-based mode
- Connection pool-based mode
You can use a mixture of session-based and connection pool-based references to tables/views and stored procedures as needed. For example, a query from an XML-based client returns thousands of records, each of which may have dozens of fields. The XML document is large and performance might be a concern; however, most operations operate on a small set of records.
The session-based mode is intended for use with applications such as Oracle Projects where a series of stored procedures must be executed in a certain sequence and then the work committed. Microsoft BizTalk Adapter for Oracle Database supports both commit and rollback. It does not support two-phase commit because the prepare step is implicit, and BizTalk Adapter for Oracle Database does not guarantee data integrity in the event of a system failure. A unit of work must be committed on the same connection on which it was started; you must perform these operations in the context of a session.
When you create a session, you get a dedicated connection that stays until the session ends. The session keeps the connection object. BizTalk Adapter for Oracle Database maintains the client-proxy/plus/server-object pairs.
Queries on Oracle Tables
If you call GetTable twice by using the same metadata path to an Oracle table in the same session, you get two different server objects and client proxies that point to the same Oracle table. BizTalk Adapter for Oracle Database creates the client-proxy/plus/server-object pairs. The Session ID is the same; however, the Table IDs are different, which means BizTalk Adapter for Oracle Database created or used a different reference to that table.
Updating a Table
To update an Oracle table, call insert/update/delete on a table, and then commit.
If you have two different references, you can use both references to call insert/update/delete and then query—each reference sees the table in its original state until the changes are committed. If ReferenceA commits and then ReferenceB commits, errors might occur. For example, this might occur if the second set of committed operations updated a record which the first reference deleted.
Each call to a table/view or stored procedure is a single unit of work, and it either succeeds or fails. If the call fails, the database is rolled back. This kind of call uses pools of connections that are automatically acquired and released for each call. This guarantees there is a connection available to service the request when a request is received.
Time-outs
If a connection time-out has been configured, the ODBC driver or Oracle database might still return an error. BizTalk Adapter for Oracle Database performs stateless calls into the Oracle database. Each call obtains a connection from the pool, makes a call that was instantly committed, and then releases the connection. All server objects are transient.
BizTalk Adapter for Oracle Database can also turn off the autocommit on the ODBC connection if it is required by Oracle Database. This means you must explicitly commit or roll back all table/view and stored procedure operations. The adapter cannot control a stored procedure call that causes data to be committed internally.
No comments:
Post a Comment