5

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?

  • What is in the tnsnames.ora C:\app\username\product\11.2.0\client_1\network\admin ? are both machines the same? – Mapperz Jul 22 '14 at 13:29
  • Sometimes there's nothing in the tnsnames.ora. But we set the server instance connection directly: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=lorem)(PORT=1521)))(CONNECT_DATA=(SID=ipsum))). I prefer this option since the actual address name differs from machine to machine. – Jay Cummins Jul 22 '14 at 13:38
  • I'm still waiting to hear about all of the various configuration options. But I can tell you that 1 machine has 2 11.2.0 32-bit clients installed. tnspings work fine, so I wasn't initially thinking it was tns related. – Jay Cummins Jul 22 '14 at 13:42
  • Which ArcGIS version are you running? The property set for connections to a sql workspace changed somewhere between 10.0 and 10.2. – Rich Wawrzonek Jul 23 '14 at 17:01
  • 10.1 in this case. But I potentially have another AddIn that runs in multiple versions where it would be nice to be able to detect. – Jay Cummins Jul 23 '14 at 17:53
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.

| improve this answer | |
1

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);
    }
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.