How to qualify table and field names

Qualifying table and field names

Geodatabases that reside in a relational database management system (RDBMS)—such as Oracle, SQL Server, or IBM DB2—use standard database naming conventions for identifying tables for specific users. An RDBMS can contain thousands of tables from many different users; therefore, users of an enterprise geodatabase must understand how to correctly qualify the name of an object so the correct feature class, relationship class, feature dataset, or table is used during an operation.

If an unqualified name is specified as input to a tool, the geoprocessor will qualify it automatically using the currently connected user name, which is specified as a property of the connected workspace. If a program needs to access data from a number of users, it should qualify the name of the table using the geoprocessor's QualifyTableName method so the syntax of the qualified name is correct. It requires the name of a table, the user name, and the path to a geodatabase.

Output parameter values do not need to be qualified, because a tool's output is always created by the connected user of the workspace. The following code example shows how a program qualifies table names so it can use tables from a number of users:

[C#]


        // Qualify the feature class name for the user map connected to MySDE.sde.
string fcName = GP.QualifyTableName("Counties", @"Database Connections\MySDE.sde");

[VB.NET]


        ' Qualify the feature class name for the user map connected to MySDE.sde.
Dim fcName As String = GP.QualifyTableName("Counties", "Database Connections\MySDE.sde")

Structured Query Language (SQL) queries can contain the names of many fields from two or more tables. It is common for the same field name to exist in separate tables, especially when working with foreign and primary keys. Qualified field names must also be used when a table contains certain field names that are also used by ArcSDE.

To resolve the ambiguity between duplicate names, the field name must be qualified with the table or view name. The QualifyFieldName method allows a database-independent mechanism for creating fully qualified field names, using the table and field name as input. The following code example ensures that a program will always create a correct SQL statement, regardless of the underlying database type:

You practically do not need to use the QualifyFieldName method as the default value of geoprocessing environment named qualifiedFieldNames is set to true. If you don't want fields to be qualified just set the environment to 'UNQUALIFIED'. See Using environment settings to know how to set or get environments.

[C#]


        public void ExampleGPTableAndFieldNames()
{

    // Initialize the geoprocessor.
    Geoprocessor GP = new Geoprocessor();

    // Set the workspace. 
    GP.SetEnvironmentValue("workspace", @
        "Database Connections\Calgary.sde\LegalFabric");

    // Make a feature layer for the add join and selection.
    MakeFeatureLayer makefeaturelayer = new MakeFeatureLayer("TaxParcels", 
        "parcelslayer");
    GP.Execute(makefeaturelayer, null);

    // Join the parcel updates table using the parcel_id field.
    string jointable = GP.QualifyTableName("ParcelUpdates", (string)
        GP.GetEnvironmentValue("workspace"));
    AddJoin addjoin = new AddJoin("parcelslayer", "PARCEL_ID", jointable, 
        "PARCEL_ID");
    GP.Execute(addjoin, null);

    //Qualify the identical named fields; returns user.table.fieldname.
    string id = GP.QualifyFieldName("TaxParcels.PARCEL_ID", (string)
        GP.GetEnvironmentValue("workspace"));
    string update_id = GP.QualifyFieldName("ParcelUpdates.PARCEL_ID", (string)
        GP.GetEnvironmentValue("workspace"));

    // Select the parcels using the qualified names.
    SelectLayerByAttribute selByAttribute = new SelectLayerByAttribute();
    selByAttribute.in_layer_or_view = "parcelslayer";
    selByAttribute.selection_type = "New_Selection";
    selByAttribute.where_clause = id + " = " + update_id;
    GP.Execute(selByAttribute, null);

    // Calculate the update flag to be true for the selected parcels.
    CalculateField calcField = new CalculateField("parcelslayer", "updateflag", 
        "True");

}

[VB.NET]


        Public Sub ExampleGPTableAndFieldNames()
    
    ' Initialize the geoprocessor.
    Dim GP As Geoprocessor = New Geoprocessor()
    
    ' Set the workspace.
    GP.SetEnvironmentValue("workspace", "Database Connections\Calgary.sde\LegalFabric")
    
    ' Make a feature layer for the add join and selection.
    Dim makefeaturelayer As MakeFeatureLayer = New MakeFeatureLayer("TaxParcels", "parcelslayer")
    GP.Execute(makefeaturelayer, Nothing)
    
    ' Join the parcel updates table using the parcel_id field.
    Dim jointable As String = GP.QualifyTableName("ParcelUpdates", CStr(GP.GetEnvironmentValue("workspace")))
    Dim addjoin As AddJoin = New AddJoin("parcelslayer", "PARCEL_ID", jointable, "PARCEL_ID")
    GP.Execute(addjoin, Nothing)
    
    'Qualify the identical named fields; returns user.table.fieldname.
    Dim id As String = GP.QualifyFieldName("TaxParcels.PARCEL_ID", CStr(GP.GetEnvironmentValue("workspace")))
    Dim update_id As String = GP.QualifyFieldName("ParcelUpdates.PARCEL_ID", CStr(GP.GetEnvironmentValue("workspace")))
    
    ' Select the parcels using the qualified names.
    Dim selByAttribute As SelectLayerByAttribute = New SelectLayerByAttribute()
    selByAttribute.in_layer_or_view = "parcelslayer"
    selByAttribute.selection_type = "New_Selection"
    selByAttribute.where_clause = id & " = " & update_id
    GP.Execute(selByAttribute, Nothing)
    
    ' Calculate the update flag to be true for the selected parcels.
    Dim calcField As CalculateField = New CalculateField("parcelslayer", "updateflag", "True")
    
End Sub

ArcGIS Desktop applications, such as ArcMap and ArcCatalog, always present fully qualified feature class, table, and field names. If you are unsure of the owner of a table or of the tables that are accessible, use ArcCatalog to view the contents of the geodatabase.
Field names, such as Area, Entity, and Len, are used by ArcSDE system tables and require programs to fully qualify fields with these names. Refer to the ArcSDE Administration Guide for more information on ArcSDE field names.

How to qualify table and field names

Qualifying table and field names

See Also: