Broken objectid... Can't InsertCursor

[xavierlhomme]

Hello

I m writting a geopocessing tool with arcpy in order to insert some record in the enterprise geodatabase.

I'm using InsertRow from arcpy.da...

But I have an exception and the returned objectid is already in my feature table.

In order to insert the row , I need to get the max ObjectID and use an SQL statement ( Insert Into)...

It's like the sequence of objectid is broken or something like that. 

Is there any operation that can repair the ObjectID ? Or something to modify on these tables ?

Best regards 

[DanPatterson]

Have you confirmed that this is indeed the case?  Find Identical to see if it true (use the ObjectId field)

Find Identical (Data Management)—ArcGIS Pro | Documentation

and if the whole record is a duplicate, the Delete Identical to remove the record

Alternately, add a new incrementing id field and use it for the cursor

[HaydenWelch]

Are you using an Edit Session? If you're working on an enterprise database there might be some funky behavior involving multiuser modes. If you're directly inserting without first securing a lock on the table, there could be some funky results.

Without seeing code and such it's difficult to debug.

[RhettZufelt]

Are you trying to insert an OBJECTID?  That field is handled by the database itself and ESRI should populate the OBJECTID for you.

Have not tested, but possible that getting the max OBJECTID and incrementing it then inserting might be trying to insert your OBJECTID as well as automatically generating one, so they would/could be the same (already in table).

Also, not sure what/how you are inserting a row with arcpay.da.InsertCursor AND SQL insert into.....  Could you clarify this a bit?

R_

[xavierlhomme]

Hello

I will try to clarify my issue.

I need to insert in a Table, a new record.

I'm using a Geodatabase on SQL Server. I wrote a GeoProcessing in Python to do that.

In the first version I used arcpy.da.InsertCursor

def insertCalcul(calculObject:dict):
    id_calcul = 0
    calculFields = [
                   "Titre_Calcul",
                    "Description",
                    "installation",
                    "Origine_X",
                    "Origine_Y",
                    "Id_Evenement",
                    "Affichable"
                    ]
    try:
        with arcpy.da.InsertCursor(calculTableName, calculFields) as cursor:
            x = calculObject.get("Origine_X", None)
            y = calculObject.get("Origine_Y", None)
            title = calculObject.get("Titre_Calcul", None)
            description = calculObject.get("Description", None)
            installation = calculObject.get("installation", None)
            evenementId = calculObject.get("Id_Evenement", None)
            arcpy.AddMessage(" insertion du calcul '{0}' ".format(title))
            try:
             id_calcul = cursor.insertRow((
                title,
                description,
                installation,
                float(x),
                float(y),
                int(evenementId),
                0
             ))
             del cursor
             arcpy.AddMessage("{0} insertions.".format(calculTableName))
             return id_calcul
            except arcpy.ExecuteError:
               arcpy.AddMessage(" erreur insertion de ligne: '{0}' ".format(arcpy.GetMessages()))
    except arcpy.ExecuteError:
        print("Erreur insertion calcul :"+arcpy.GetMessages())

On my develop environnement , this code run well. But I had an exception on th eproduction environnement.

The returned Id_Calcul is an ObjectId already in the database. And this is in my opinion the cause of the exception. But with no certainty... The bad objectod could have been set before the exception...

The only solution I found in order to overcome this issue in the production environment is to replace this code with an SQL expression "insert into" and set the objectid myself after incrementing the maximum value  of  the objectID

def insertCalcul(calculObject:dict):
    id_calcul = 0
   
    title = calculObject.get("Titre_Calcul", None)
    description = calculObject.get("Description", None)
    installation = calculObject.get("installation", None)
    x = float(calculObject.get("Origine_X", 0))
    y = float(calculObject.get("Origine_Y", 0))          
    evenementId = int(calculObject.get("Id_Evenement",0))
   
    arcpy.AddMessage(" insertion du calcul '{0}' ".format(title))
    arcpy.AddMessage(" description '{0}'".format(description))
    arcpy.AddMessage(" installation '{0}'".format(installation))
    arcpy.AddMessage(" x '{0}'".format(str(x)))
    arcpy.AddMessage(" y '{0}'".format(str(y)))
    arcpy.AddMessage(" evenementId '{0}'".format(evenementId))
              
    calculFields = [
                   "Titre_Calcul",
                    "Description",
                    "installation",
                    "Origine_X",
                    "Origine_Y",
                    "Id_Evenement",
                    "Affichable",
                    "OBJECTID"
                    ]
                   
    id_max = int(get_max_objectid(egdb_conn,calculTableName))
    id_calcul = id_max + 1
    try:
      fields = ','.join(calculFields)
      sql=f"INSERT INTO {calculTableName} ({fields}) VALUES ('{title}','{description}','{installation}',{x},{y},{evenementId},{0},{id_calcul})"
      arcpy.AddMessage(" sql '{0}'".format(sql))
      egdb_conn.execute(sql)
      arcpy.AddMessage("fin insertion")
      return id_calcul
    except arcpy.ExecuteError as e:
        arcpy.AddMessage(" exception insertCalcul ")
        arcpy.AddMessage(" Args: '{0}' ".format(e.args))
        arcpy.AddError("Erreur insertion calcul : '{0} {1}' ".format(str(id_calcul),arcpy.GetMessages()))

I think that something is broken in my geodatabase: an indexe , a sequence, or something like that.

Is there an operation that would allow me to check the sequence of objectIDs?