0

Using ArcGIS Pro 2.9.5 I found that ArcSDESQLExecute is not able to handle unique identifiers (UNIQUEIDENTIFIER) a.k.a GUIDS, in a table.

I tried to run the following Script:

database = 'C:\\Installation\\updm.sde'

egdb_conn = arcpy.ArcSDESQLExecute(database)
# Getting the ID's froma table
sql_clause = "SELECT PROJECT_ID from DataOwner.My_PROJECTS;"

query_return = egdb_conn.execute(sql_clause)

# Whatever operation you want to do with the results, in this case
# is just the verification of how many results, but they will be 
# iterated later

len(query_return)

the table My_PROJECTS has a column PROJECT_ID defined as a uniqueidentifier., the result of the execution is:

---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
In  [39]:
Line 12:    query_return = egdb_conn.execute(sql_clause)

File C:\ArcGISPro\Resources\ArcPy\arcpy\arcobjects\arcobjects.py, in execute:
Line 44:    return convertArcObjectToPythonObject(self._arc_object.Execute(*gp_fixargs(args)))

AttributeError: ArcSDESQLExecute: StreamBindOutputColumn ArcSDE Error -65 Invalid pointer argument to function

Is there any way to solve this?

EDIT: Removed the line from the code that was just noise

EDIT 2: Added clarification on the code, and clarification of the type of column

5
  • 1
    Only use supported data types? Cast unsupported types to supported ones?
    – Vince
    Dec 2, 2022 at 13:17
  • The current documentation does not list the supported types
    – XtianGIS
    Dec 4, 2022 at 9:43
  • Of course it does. Located by a google on "ArcGIS SQL Server supported datatypes" -- pro.arcgis.com/en/pro-app/latest/help/data/geodatabases/…
    – Vince
    Dec 4, 2022 at 12:52
  • @Vince I mean the documentation of the tool [link]pro.arcgis.com/en/pro-app/latest/arcpy/classes/… . Taking into account the documentation you show, the uniqueidentifier is supported.
    – XtianGIS
    Dec 5, 2022 at 8:28
  • The -65 bind error usually means the column is unsupported, though the extra semicolon seems to have just thrown it off. BLOB/CLOB/NCLOB columns have a history of errors, plus geometry columns are not supported in native SQL cursors.
    – Vince
    Dec 5, 2022 at 12:50

2 Answers 2

2

Not sure what you're trying to accomplish with the select (a count or rows, all the ids). This is probably just a code snippet but sql_query is not declared. Also if you are trying to return an int I don't believe len will work.

I did some of my own testing and used a where clause, and dropped the semi-colon with your code and it worked.

sql_clause = "SELECT PROJECT_ID from DataOwner.My_PROJECTS WHERE PROJECT_ID > 0"

1
  • In your own testing, could you use a column set as UNIQUEIDENFIER instead if an INT
    – XtianGIS
    Dec 4, 2022 at 9:51
1

After following the suggestion of @Vince, and despite the fact that unique identifiers are supported, I went for a casting approach

database = 'C:\\Installation\\updm.sde'

egdb_conn = arcpy.ArcSDESQLExecute(database)
# Getting the ID's froma table
sql_clause = "SELECT CAST(PROJECT_ID AS VARCHAR(100)) from DataOwner.My_PROJECTS;"

query_return = egdb_conn.execute(sql_clause)

# Whatever operation you want to do with the results, in this case
# is just the verification of how many results, but they will be 
# iterated later

len(query_return)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.