[GIS] Connecting to Oracle User Schema Geodatabase using ArcObjects

arcgis-10.3arcobjectsoracle-spatial

I've set up an Oracle user Schema geodatabase and loaded some data into it. I now want to connect to it programmatically using ArcObjects, the problem is I don't see how. Where can I set the newly created schema name in my connection property set, or how do I change the schema value after I have connected? In ArcCatalog it is done in the Geodatabase connection properties window :

The code below works fine connecting to the default SDE schema. What do I have to change to connect to a user geodatabase? I have tried examining IDatabaseConnectionInfo2 and IWorkspaceProperties but I don't see where I can set or change the schema that is connected to.

 public static IWorkspace ConnectToTransactionalVersion(String dbclient, String dbConnProp, String user, String password, String database, String version, String authentication)
    {
        IPropertySet propertySet = new PropertySet();
        propertySet.SetProperty("DBCLIENT", dbclient);
        propertySet.SetProperty("DB_CONNECTION_PROPERTIES", dbConnProp);
        propertySet.SetProperty("DATABASE", database);
        propertySet.SetProperty("USER", user);
        propertySet.SetProperty("PASSWORD", password);
        propertySet.SetProperty("VERSION", version);
        propertySet.SetProperty("AUTHENTICATION_MODE", authentication);
        Type factoryType = Type.GetTypeFromProgID(
            "esriDataSourcesGDB.SdeWorkspaceFactory");
        IWorkspaceFactory workspaceFactory = (IWorkspaceFactory)Activator.CreateInstance
            (factoryType);
        return workspaceFactory.Open(propertySet, 0);
    }

I tried adding "SCHEMA" as a property in propertySet and it gets ignored, I also tried setting the version property to the new user schema default version but it errors with "version not found"

Best Answer

Create a connection file (sde file) with ArcCatalog.

Solution 1: Use this connection file like in my example (first 7 lines). I think this is not that good, because you have to distribute the sde file, too.

Solution 2: Maybe you can make some kind of ‘reverse engeneering’. In the example below (all lines) you could establish the connection to your workspace by the sde file and read the properties of this file. I hope you will find the missing parameter.

IWorkspaceFactory wsFactory = null;
IFeatureWorkspace featWs = null;
object names = null;
object values = null;
try
{
    wsFactory = new SdeWorkspaceFactoryClass();
    featWs = (IFeatureWorkspace)wsFactory.OpenFromFile(@"D:\Test.sde", 0);

    IWorkspace ws = featWs as IWorkspace;
    if (ws != null)
    {
        IPropertySet props = ws.ConnectionProperties;
        props.GetAllProperties(out names, out values);
    }
}
catch (Exception ex)
{
}

Related Solutions

[GIS] How to connect to ArcSDE for using the ArcObjects Erase tool in C#

You need to create the SDE connection file in order to reference it when calling your 'erase' geoprocessing tool. For example, if you wanted to pass a SDE feature class to a geoprocessing tool, the path will something along the lines of

<some location>\mySDEConnectionFile.sde\myFeatureClass

But in order to create this SDE connection file (.SDE) you need to use the geoprocessing tool mentioned in the link you provided. This will be the tricky part as there are a lot of parameters to pass in order to create it. But just follow the code example. Here is the link to the page describing the order of parameters passed to the tool:

http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//0017000000pt000000

Once you have your SDE connection file created, you can pass it as a parameter to your erase geoprocessing tool, you'll do something like this (sorry it's in VB)

Dim gp As IGeoprocessor = New Geoprocessor
Dim var As IVariantArray = New VarArray()
With var
    .Add(<input features>)
    .Add(mySDEConnectionFile.sde\myFeatureclass)
    .Add(<erase features>)
End With
gp.Execute("EraseTool", var, nothing)

Something similar to that anyway. Do you have a link to the erase tool you're trying to run? I can't find a reference to it.

[GIS] Slow connecting to Oracle Spatial database from QGIS client

To connect to the schema tables, the connection can last 5-8 minutes.

Do you mean that this is the elapsed time between the moment you open a table and the moment you see the result on your screen as a map ? And does that include a view of all the 3600 polygons in that table ? Or does your view contain multiple tables ?

Obviously fetching many geometries from a database will take time, but it should not be that long. For example, fetching some 3200 fairly complex polygons is less than a second (say 800ms) on my database. This is not using QGIS - just a simple fetch. But the cost for reading from the database, passing to the client over the network and decoding the objects is the same irrespective of the client.

So there is definitely something wrong in your case.

Can you try accessing the tables using sqlplus ? Just do the following:

SQL> set autotrace traceonly statistics
SQL> set timing on
SQL> select * from <my_table>;

The first command tells sqlplus to not print out the results (that would be very long). Your DBA will need to grant you some additional privileges in order to to this.

The second command tells sqlplus to show you how long it took to execute the statement.

The third one reads the full content from that table. For example:

SQL> set autotrace traceonly statistics
SQL> set timing on
SQL> select * from us_counties;

3230 rows selected.

Elapsed: 00:00:00.75

Statistics
----------------------------------------------------------
     13  recursive calls
      0  db block gets
   1298  consistent gets
    560  physical reads
      0  redo size
4219623  bytes sent via SQL*Net to client
   3930  bytes received via SQL*Net from client
    224  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
   3230  rows processed

If this is also very slow, then the issue is probably around your connection to the server and you need to ask your DBA to solve this.

One thing that makes reading geometries expensive is their complexity, i.e. the number of vertices: the flattening of those vertices into messages, the transfer, the decoding of the shapes will all take time, and so will the time it takes QGIS to render them. But I would not expect timings of the order of magnitude you mention.

Best Answer

Related Solutions

[GIS] How to connect to ArcSDE for using the ArcObjects Erase tool in C#

[GIS] Slow connecting to Oracle Spatial database from QGIS client

Related Question