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.

JamesMorrison1

Hello Blake we have found the issue but have no reason why?

My only thought is when the value 1041602 gets converted to a sting str(parameters[0]) – there is an int overflow but this does not make too much sense to me. Google offered some insights but no direct answer.

The script above gave different resuls between python.exe : ArcGIS Pro 3.1 / 3.9.16 and ArcGIS Server 11.2 / 3.9.18

[1]

DESCRIBE SDE.COMPRESS_LOG;

Name Null? Typ
----------------- -------- ------------
SDE_ID NOT NULL NUMBER(38)
SERVER_ID NOT NULL NUMBER(38)
DIRECT_CONNECT NOT NULL VARCHAR2(1)
COMPRESS_START NOT NULL DATE
START_STATE_COUNT NOT NULL NUMBER(38)
COMPRESS_END DATE
END_STATE_COUNT NUMBER(38)
COMPRESS_STATUS VARCHAR2(20)

SQL Developer – all is correct

SDE_ID SERVER_ID D COMPRESS_START START_STATE_COUNT COMPRESS_END END_STATE_COUNT COMPRESS_STATUS

---------- ---------- - -------------------- ----------------- -------------------- --------------- --------------------

1041602 14992 Y 07-Jun-2024 04:22:52 1 07-Jun-2024 04:22:52 1 SUCCESS

[2]

ArcGIS Pro 3.1 / 3.9.16 ArcGIS\Pro\bin\Python\envs\arcgispro-py3\python.exe test.py – all is correct

1041602, 14992, Y, 07-Jun-2024 04:22:52, 1, 07-Jun-2024 04:22:52, 1, SUCCESS

[3]

ArcGIS Server 11.2 / 3.9.18 ArcGIS\Server\framework\runtime\ArcGIS\bin\Python\envs\arcgispro-py3\python.exe test.py – all is not ok. A minus value is being returned.

-6974, 14992, Y, 07-Jun-2024 04:22:52, 1, 07-Jun-2024 04:22:52, 1, SUCCESS

anonymous_geographer

Update: Try upgrading to Pro 3.3.0 and test again. I'm not seeing anything specific regarding this issue in the release notes, but our negative number problem resolved itself in Python once we upgraded to 3.3.0.