arcpy.ArcSDESQLExecute() returns a negative value from SDE.CONPRESS_LOG Table on update Python 2.7 to Python 3.0

[JamesMorrison1]

Hello Everyone

I updated a python script from 2.7 (ArcGISx6410.9\python.exe) to 3.0 (arcgispro-py3\python.exe) and now it returns a strange negative value for the SDE_ID column.

2.7 correct.

04:24:15 INFO 1040058, 10432, Y, 02-Jun-2024 04:22:54, 1, 02-Jun-2024 04:22:54, 1, SUCCESS

3.0 not correct.

15:51:31 INFO     -6974, 14992, Y, 07-Jun-2024 04:22:52, 1, 07-Jun-2024 04:22:52, 1, SUCCESS

If I run the same select statement in SQL Developer, we use Oracle, the query returns the correct values

script:

# connection to SDE Tables
sde_conn_SDE = arcpy.ArcSDESQLExecute(conn)

sql_val = "SELECT SDE_ID, SERVER_ID, DIRECT_CONNECT, TO_CHAR(COMPRESS_START,'DD-Mon-YYYY HH24:MI:SS') as COMPRESS_START, START_STATE_COUNT, TO_CHAR(COMPRESS_END,'DD-Mon-YYYY HH24:MI:SS') as COMPRESS_END, END_STATE_COUNT, COMPRESS_STATUS FROM SDE.COMPRESS_LOG WHERE SDE_ID IN (SELECT MAX(SDE_ID) FROM SDE.COMPRESS_LOG)"
# read value
AddPrintMessage(sql_val,0)
logging.info(sql_val)

sdeReturn = sde_conn_SDE.execute(sql_val)
headings = "SDE_ID, SERVER_ID, DIRECT_CONNECT, COMPRESS_START, START_STATE_COUNT, COMPRESS_END, END_STATE_COUNT, COMPRESS_STATUS"
AddPrintMessage(headings,0)
logging.info(headings)

for i in sdeReturn: #data list
parameters = []
para = ""
#print i
for j in i:
parameters.append(j)


para = str(parameters[0]) + ", " + str(parameters[1]) + ", " + str(parameters[2]) + ", " + str(parameters[3]) + ", " + str(parameters[4]) + ", " + str(parameters[5]) + ", " + str(parameters[6]) + ", " + str(parameters[7])
AddPrintMessage(str(para),0)
logging.info(str(para))

Can anyone see the issue? Feedback appreciated.

[BlakeTerhune]

I tested your code in SQL Developer (Oracle 19c) and in Python (ArcGIS Pro 3.1.3) and the SDE_ID value is the same both times: the correct (non-negative) id.

[JamesMorrison1]

Thanks for testing Blake. Appreciated. 
Could it be related to how Python converts the value 1040058 to a string? 

[BlakeTerhune]

---

@JamesMorrison1 wrote:

Could it be related to how Python converts the value 1040058 to a string? 

---

That doesn't seem reasonable to me, but you can test that with print(sdeReturn) before you do all that parsing and formatting.

[JamesMorrison1]

Hi Blake. Nor to me. But nothing else makes any logical sense yet. basically the same script with small updates to handle error handling in Python 3  strange all other attributes in the row are returned correctly except this one  

Thanks for the suggestion. I try some more debugging over the weekend and report back. 

[anonymous_geographer]

@JamesMorrison1is not alone with this issue. I'm also seeing this behavior (link to my original post showing my example).

[BlakeTerhune]

Thanks for chiming in, @anonymous_geographer! Seeing as I'm on Pro 3.1.3, it makes sense that I am not able to replicate the issue. Looking forward to your results with Pro 3.3.