How do you determine which dbclient connection property to use for an Oracle SQLWorkspace connection?

Question

I have an Oracle11g database that I connect to via a SQL Workspace; however the actual dbclient string value that works differs depending on machine (and I'm guessing oracle client configuration). On some machines, its Oracle11g(like the example on resources.arcgis.com):

// Create the connection properties.
IPropertySet connectionProps = new PropertySetClass();
connectionProps.SetProperty("dbclient", "Oracle11g");
connectionProps.SetProperty("serverinstance", "Riverside");
connectionProps.SetProperty("authentication_mode", "DBMS");
connectionProps.SetProperty("user", "BlackDog");
connectionProps.SetProperty("password", "123456");

Sometimes it's Oracle.

// Create the connection properties.
IPropertySet connectionProps = new PropertySetClass();
connectionProps.SetProperty("dbclient", "Oracle");
connectionProps.SetProperty("serverinstance", "Riverside");
connectionProps.SetProperty("authentication_mode", "DBMS");
connectionProps.SetProperty("user", "BlackDog");
connectionProps.SetProperty("password", "123456");

How is that dbclient value used by ArcGIS? Is there a way to detect which is needed at runtime?

Answer 1

With 10.0 and Oracle 11g I can open an esriDataSourcesGDB.SqlWorkspaceFactory using a property set as you describe above. However from 10.1 forward I've had to change my programs to use esriDataSourcesGDB.SdeWorkspaceFactory and the following property set:

        IPropertySet propertySet = new PropertySetClass();
        propertySet.SetProperty("SERVER", "<servername>");
        propertySet.SetProperty("INSTANCE", "sde:oracle11g:<instancename>");
        propertySet.SetProperty("DATABASE", "");
        propertySet.SetProperty("USER", "<username>");
        propertySet.SetProperty("PASSWORD", "<password>");
        propertySet.SetProperty("VERSION", "sde.DEFAULT");

I wasn't able to find documentation that they've deprecated the SqlWorkspaceFactory but applications that I used it in worked in 10.0 and don't in 10.2. In Esri help is this remark in the SqlWorkspaceFactoryClass page:

The SDEWorkspaceFactory.Open method should be used to make connections to all databases and enterprise geodatabases.

Also there was a Knowledge Base tech article about it: What has changed in Query Layers between ArcGIS 10.0 and 10.1

I don't know how you would "detect" which properties your application needs other than try/catching in your code. You might see if using the SdeWorkspaceFactory works in all 10.x versions that you support and drop usage of SqlWorkspaceFactory if so.

Answer 2

I had a devil of time trying to use a Direct Connection to Oracle. At the end, I used a .sde file to load up in memory, then parsed out the PropertySet for it so I could see how it was working.

This code works for me:

public static IWorkspace Connect(string serverName, int port, string dbName, string user, string password) {

        //"sde:oracle$//servername:1551/database1";
        string instance = string.Format(@"sde:oracle$//{0}:{1}/{2}", serverName, port, dbName);

        IPropertySet propertySet = new PropertySet();
        propertySet.SetProperty("SERVER", "local_oracle");
        propertySet.SetProperty("INSTANCE", instance);
        propertySet.SetProperty("USER", user);
        propertySet.SetProperty("PASSWORD", password);
        propertySet.SetProperty("VERSION", "SDE.DEFAULT");
        propertySet.SetProperty("AUTHENTICATION_MODE", "DBMS");


        Type factoryType = Type.GetTypeFromProgID("esriDataSourcesGDB.SdeWorkspaceFactory");
        IWorkspaceFactory2 workspaceFactory = (IWorkspaceFactory2)Activator.CreateInstance(factoryType);
        return workspaceFactory.Open(propertySet, 0);
    }