Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

Oracle OLE DB and MTS : Page 2

Trying to figure out how to use the Oracle OLE DB Provider under MTS or COM+ and with Oracles OS Authentication? This article explains how these parts fit together and supplies information that will help you get started. The focus is deliberately kept narrow: using the Oracle OLE DB Provider with MTS/COM+ components on Windows NT 4.0 or Windows 2000 Server. Most of the information also applies to using Oracle ODBC Driver and Oracles OCI with MTS.


advertisement

MTS, Oracle Services for MTS and Oracle Server

There can be at most, one instance of Oracle Services for MTS for each Oracle instance (database). Oracle Services for MTS can be installed on the same computer as the Oracle instance, or a different computer. Oracle Services for MTS can be used in conjunction with an Oracle instance on any platform supported by Oracle, not just for Oracle on NT or Windows 2000 (e.g. most UNIX flavours).



Each instance of the Oracle Services for MTS works with exactly one Oracle instance, just as there is at most, one instance of MTS and DTC on any given computer.

Oracle Services for MTS implements the functions that MTS and DTC expect of a Resource Dispenser and a Resource Manager using OLE Transactions. These functions map MTS/OLE Transactions to Oracle transactions and work with MTS/DTC to commit or abort a transaction. The Oracle Services for MTS documentation explains this fairly well (see Web Site References - Documentation at the end of this article for where to find this documentation).

The Oracle Services for MTS NT service and Oracle security

Oracle Services for MTS runs as an NT/2000 service. Every NT/2000 service runs in a security context associated with an NT/2000 user account. The user account used for the Oracle Services for MTS service must be known to the Oracle database and have these Oracle System Privileges:

  • Create Public Synonym
  • Drop Public Synonym
  • Force any Transaction

and these Oracle Roles:

  • Connect
  • Resource
  • Select_catalog_role

Connections in the MTS database connection pool are associated with a particular security context. To make good use of MTS connection pooling, the number of different security contexts must be limited. A common recommendation in this regard is to use the MTS component’s Identity when accessing a database. You can do this quite easily by using Oracle’s OS Authentication feature (see the Sidebar Oracle Security:) and appropriate assignment of NT accounts to MTS components’ Identity properties. If these NT accounts are then defined to be Oracle External OS Users and granted appropriate External Roles using Oracle Administrative Assistant for NT, connection pooling should work as intended.

Then, there is no need to specify a username and associated password for ADO connection’s User ID property. Oracle’s OLE DB Provider will automatically get the NT account from the current security context and pass this to Oracle. Oracle, via its OS Authentication feature for NT then knows what System Privileges and Roles to use for the database accesses. This can also make managing usernames and passwords simpler and reduces (or eliminates) the exposure of passwords, either in code or elsewhere.

Connecting ADO to Oracle via the Oracle OLE DB Provider

Before you can use ADO to connect to an Oracle database, you have to know the Net Service Name associated with the Oracle instance of interest. See the Sidebar Identifying and locating an Oracle instance: for additional information. Only the people that set up the Oracle database can provide you with this name; it’s not the same for every Oracle installation.

Armed with the name to specify as the Data Source, the ADO Connection properties required to use the Oracle OLE DB provider are:

  • Provider=OraOLEDB.Oracle;
  • Data Source=NetServiceName;
  • User ID=someOracleusername;
  • Password=password for someOracleusername;

Some sample ADO "connect strings" are:

  • To use a specific username and password (e.g. when not using OS Authentication):

"Provider=OraOLEDB.Oracle;User ID=XYZ;Password=xyzpass;Data Source=NetServiceName"

  • To use OS Authentication and the MTS Component’s Identity property for controlling permissions and rights in the Oracle database:

"Provider=OraOLEDB.Oracle; User ID=/;Data Source=NetServiceName"

  • to use the ADO Data Shaping service to create hierarchical recordsets:

"Provider=MSDATASHAPE;Data Provider=OraOLEDB.Oracle; User ID=/;Data Source=NetServiceName"

Data Types

When you use VB, ADO and Oracle OLE DB Provider, there are four environments, each with their own idea about data types.

  • Native VB data types
  • ADO data types
  • OLE DB data types
  • Native Oracle data types

ADO data types map almost directly into OLE DB data types, so that isn’t too much of a problem.

The Oracle OLE DB Provider documentation includes a table showing the mapping of Oracle data types to OLE DB data types (see Chapter 2 OraOLEDB Features; OraOLEDB Provider Specific Features section).

Although in a somewhat obscure place, the mapping of ADO to OLE DB data types is shown in the Enumerated Constants part of ADO programming reference documentation (MSDN; Platform SDK; Data Access Services; Microsoft Data Access Components (MDAC) SDK; Microsoft ActiveX Data Objects (ADO); ADO API Reference; ADO Enumerated Constants; DataTypeEnum).

When it comes to finding out what VB data type best maps ADO data types (and indirectly, Oracle data types), your kind of on your own but see http://www.able-consulting.com/ADODataTypeEnum.htm for some hints. Most are pretty obvious (e.g. varchar2 maps to string). VB has little or no concept of numeric precision and scale, so dealing correctly with some numeric Oracle data types may be a challenge.

Gotcha’s

Oracle Services for MTS instructions not quite correct

The Oracle document Using Microsoft Transaction Server with Oracle8 explains fairly well what Oracle Services for MTS is and how to configure. However, there are two parts that are not quite correct.

  • Step 2 says to logoff and logon with the username that is to be used by the Oracle Services for MTS (e.g. MTSSYS). This is not necessary; you can complete the remaining steps while logged on with your normal NT administrative account. "Service accounts" are not associated with people and often don’t have the "logon interactively" right, so logging on with the Oracle Services for MTS account doesn’t make much sense.
  • If you are using OS Authentication with Oracle Services for MTS, there are special instructions in Appendix A of Using Microsoft Transaction Server with Oracle8 for configuring this. One of the instructions is to remove the username and password from the Oracle Manager for MTS Services panel for configuring a service. This is incorrect. If you remove the username and password, the dialog will not complete successfully and Oracle Services for MTS won’t work. Leave the username and password fields filled in. Although the values in these fields are not relevant when OS Authentication is used, their absence causes an obscure problem in the dialog that caused me much grief, as explained in the Introduction.

Oracle Services for MTS is not set to start automatically

Configuring Oracle Services for MTS using Oracle Manager for MTS Services, creates a new NT service. Unfortunately, at least in some cases, this service is not configured to start automatically. This means that Oracle Services for MTS will not be available after the operating system is restarted. Use the NT Services dialog (e.g. Control Panel, Services) to set the Oracle Services for MTS service to start automatically. The name of the (first instance of) Oracle Services for MTS service is OracleMTSService0.

Oracle Services for MTS may fail during start-up

When the Oracle Services for MTS is set to start automatically, it may fail during an operating system restart because the Oracle instance is not yet running. If you suspect this problem, look in the Oracle Services for MTS trace file (in the Oracle Home\oramts\Trace folder) for records like:

106953: [150] OracleMgr::Initialize - Error: GetOCIConn failed with error 81.
121955: [150] OracleMgr::Initialize - Start.
121975: [150] OracleMgr::GetOCIConn - Error: failed to connect. ORA-01033: ORACLE initialization or shutdown in progress

This can be a problem on slower servers. If you get this problem, use regedt32 to add the following registry value:

HKEY_LOCAL_MACHINE
System
CurrentControlSet
Services
OracleMTSService0
DependOnService REG_MULTI_SZ OracleServiceName

"OracleServiceName" is the name of the NT service for the Oracle instance. Normally this will be something like "OracleServiceabcd", where abcd is the Oracle service name for the instance.

Hierarchical Recordsets not updateable

If you use the Data Shaping service to build a hierarchical ADO recordset, you will likely find that you can not make any changes to field values in the recordset. This applies to fields in the parent recordset as well as the child recordsets. If you attempt to change a field’s value using VB code such as:

Rs.fields(0).value = "abc"

you get a run time error to the affect that a multi-step operation has failed (not very enlightening). Oracle says this problem is fixed in Oracle 8.1.7.

If you use the recordset.clone method to create a copy of the recordset, that copy is not updateable either. So, if you need to bypass this problem, see MS KB article Q241202. HOWTO: Produce a Manufactured Hierarchical Recordset Base on an Existing Recordset. This is a good article and includes sample code you can copy that does the job.



Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date