2

I manage a library of query layers and have recently had the need to apply the same update to a bunch of them at once. Seems the only way to do that in python is to create new ones and apply symbology from the old ones and that's fine. But in creating new ones, one of the important bits is specifying the field(s) that provide a unique identifier. There is a parameter for this in arcpy.MakeQueryLayer_management but the layers that come out of my script have the first field in the list checked rather than the one that I specified near the bottom. What am I missing?

Below is the business end of the script I wrote that contains commented examples of things I've tried to specify that parameter successfully.

arcpy.MakeQueryLayer_management(
    input_database = self._environment.DatabaseConnections["Database"],
    out_layer_name = self.LayerName,
    query =          self.Query,  # SELECT * FROM Database.Schema.View
    # None of these work--first field in layer gets auto-selected every time
    oid_fields =     "FieldName",
    #oid_fields =     "View.FieldName",
    #oid_fields =     "Schema.View.FieldName",
    #oid_fields =     "Database.Schema.View.FieldName",
    #oid_fields =     "Instance.Database.Schema.View.FieldName",
    #oid_fields =     ["FieldName"],
    #oid_fields =     ["Instance.Database.Schema.View.FieldName"],
    #oid_fields =     "FieldName;SecondFieldNameJustToMakeAList",
    #oid_fields =     ["FieldName","SecondFieldNameJustToMakeAList"],
    #oid_fields =     "YouCanBeSureThisNameAppearsNowhereInTheDatabase",
    shape_type =     "POINT",
    spatial_reference = arcpy.SpatialReference(102605)
    )

I've also tried (to no effect):

  • Running the associated tool in ArcMap produces the desired results
  • Adding that tool to ModelBuilder and exporting python from there. That parameter was specified by sequence simply as "FieldName" in the exported python, but that script contained dozens of lines of python specifying seemingly every Arcpy environment setting possible and then immediately resetting them to their old values afterward. I dumped all that into my script immediately above and below the tool reference.
  • Specifying the parameters by position rather than by keyword
  • Source queries in the query layers in our library have always been SELECT * FROM VIEW... and I tried specifying field names instead

The obvious hack I'd like to avoid:

Arcpy doesn't throw a "Field Not Found" sort of exception but rather just picks the first field that it encounters and keeps trucking (as if that doesn't have great potential to hose things up down the road). Consequently one might hack a solution by re-wiring the views these layers query to put the primary key first, but then there'd have to be other rigmarole to reorder fields in the layer so that users are presented info in the order they expect.

1

I contacted Esri support and they found an existing documented bug for this problem. It calls out Oracle and ArcGIS v10.3.1 but I'm using v10.2.2 and SQL Server 2008 R2 (support gal said the bug's notes also called out PostgreSQL). I've given up looking for a workaround. If I absolutely need to automate this functionality then I'll likely try my hack.

  • Perhaps try not using the * wildcard selection and put the desired object id field first in the selection statement select DESIRED_ID, FIELD1, FIELD2, ... from DB.TABLE – matt wilkie Nov 14 '18 at 20:00
  • @mattwilkie see my question's note about the obvious hack that I'd like to avoid. – id est laborum Nov 14 '18 at 20:05
  • But yes, 'select *...' is suboptimal. – id est laborum Nov 14 '18 at 20:07
  • 1
    Agreed, not nice for the user writing the query. My DB guy tells me there's a performance drop using * select instead of explicit names (but isn't sure if that makes any practical difference for what we do). Given that the bug has existed for 5 years already and Esri has explicitly stated ArcMap is only seeing security fixes and critical bugs now and all new work is going to ArcPro I think we're stuck with it. – matt wilkie Nov 14 '18 at 20:12
  • 1
    Geonet thread on this same issue: community.esri.com/thread/178045 – matt wilkie Nov 14 '18 at 21:10

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.