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.
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.
Thanks for testing Blake. Appreciated.
Could it be related to how Python converts the value 1040058 to a string?
@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.
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.
@JamesMorrison1is not alone with this issue. I'm also seeing this behavior (link to my original post showing my example).
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.
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
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.
Thank you for the reply A_G.
Just to be clear:
ArcGIS Pro 3.1 with python 3.9.16 returns the correct value: 1041602
ArcGIS Server 11.2 with 3.9.18 returns the incorrect value: -6974
So it is the small python difference x.x.16 v x.x.18 which is affecting the result. Plus sadly we can not just upgrade to Pro 3.3
To confirm the incorrect value appears:
sdeReturn = sde_conn_SDE.execute(sql_val)
print("sdeReturn: " + str(sdeReturn))
sdeReturn: [[-6974, 14992, 'Y', '07-Jun-2024 04:22:52', 1, '07-Jun-2024 04:22:52', 1, 'SUCCESS']]
So I assume there is an issue between ArcGIS Pro and ArcGIS Server when calling this function?