2

I have a case to get the maximum of year value from the table schema in GeoDatabase.

More detail: I have a table named SUPPLYMASTER it contains the columns like Supply2000, Supply2001, Supply2002, Supply2003, ... up to Supply2015 and it can be extended in future.

I can write the logic in simple SQL with the system tables as:

SELECT MAX(REPLACE([name], 'Supply', '')) AS [MaxYear] 
FROM sys.columns 
WHERE object_id = object_id('SUPPLYMASTER') AND [name] LIKE 'Supply%'

It filters the column names starting with Supply from the SUPPLYMASTER table, the replace will remove the Supply text from the filtered column names and Max will return the maximum number from the filtered values. So I will get the result as 2015.

I tried the same in ArcObjects, but when trying to access the system table, it throws error.

I tried to access the system table like the below:

Config settings as:

<appSettings>
    <add key="Database" value="DatabaseName"/>
    <add key="Server" value="ServerName"/>
    <add key="UserName" value="UserName"/>
    <add key="Password" value="Password"/>
    <add key="Version" value="DBO.DEFAULT"/>
</appSettings>

OpenTable code as:

IPropertySet connectionProperties = new PropertySetClass();
propertySet.SetProperty("Database", System.Configuration.ConfigurationManager.AppSettings["Database"]);
propertySet.SetProperty("INSTANCE", "sde:sqlserver:" + System.Configuration.ConfigurationManager.AppSettings["Server"]);
propertySet.SetProperty("USER", System.Configuration.ConfigurationManager.AppSettings["UserName"]);
propertySet.SetProperty("PASSWORD", System.Configuration.ConfigurationManager.AppSettings["Password"]);
propertySet.SetProperty("VERSION", System.Configuration.ConfigurationManager.AppSettings["Version"]);

Type factoryType = Type.GetTypeFromProgID("esriDataSourcesGDB.SdeWorkspaceFactory");
IWorkspaceFactory workspaceFactory = (IWorkspaceFactory)Activator.CreateInstance(factoryType);
IWorkspace workspace = workspaceFactory.Open(connectionProperties, 0);

IFeatureWorkspace featureWorkspace = (IFeatureWorkspace)workspace;

ITable sysTable = featureWorkspace.OpenTable("sys.columns");

IQueryFilter sysTableFilter = new QueryFilterClass();
sysTableFilter.SubFields = "name";
sysTableFilter.WhereClause = "object_id = object_id('SUPPLYMASTER') AND [name] LIKE 'Supply%'";

ICursor cursor = sysTable.Search(sysTableFilter, true);
IRow row = null;

while ((row = cursor.NextRow()) != null)
{
  // .... data processing
}

It throws the error in the line ITable sysTable = featureWorkspace.OpenTable("sys.columns");

DBMS table not found

How can I access the system tables using ArcObjects?

Note: I tried with INFORMATION_SCHEMA.COLUMNS also, it throws the same error.

  • What was the workspacefactory? Are you accessing a database or a Geodatabase? Edit your code to show how you created featureworkspace. – Hornbydd Jan 18 '16 at 8:53
  • @Hornbydd: Details added. – Arulkumar Jan 18 '16 at 10:00
  • I'm not particularly familiar with SQL environment but you say you have a table called SUPPLYMASTER but your Arcobjects code is trying to open a table called "sys.objects"? – Hornbydd Jan 18 '16 at 10:24
  • @Hornbydd: Requirement is I want to get the all column names of the SupplyMaster table and using those column names, to do some string operations. So trying to get the column names by the sys.objects table, since sys.objects contains the details of all table in the database. – Arulkumar Jan 18 '16 at 10:26
  • Have you tried accessing the table with a database name prefix? I suspect you'll need to use a prepareSQL on the raw table because the table contains an unsupported type (OpenTable is only valid on feature classes). – Vince Jan 18 '16 at 11:42
1

You'll need to ask Esri why, but it is not possible to access any sys tables directly using SE_table_describe, which means that any interface which accesses the system view piecemeal will fail. The underlying ArcSDE functions can create a stream accessing the sys.columns view, and it's possible to generate a Query Layer on sys.columns, but IFeatureWorkspace.OpenTable cannot work on sys views.

You therefore have four options:

  1. Fold the system view query into a valid SELECT statement as a subquery, e.g.

    SELECT name = (SELECT name FROM sys.columns WHERE ... ) FROM ...

  2. Create a view which manifests a query on the system view, then use OpenTable on that

  3. Create a Query Layer to implement your query

  4. Use an ODBC connection to access the system view(s) in a SQL prepare.

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.