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

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

JamesMorrison1 — Fri, 14 Jun 2024 11:27:46 GMT

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.

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

BlakeTerhune — Fri, 14 Jun 2024 14:30:31 GMT

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.

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

JamesMorrison1 — Fri, 14 Jun 2024 15:00:15 GMT

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

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

BlakeTerhune — Fri, 14 Jun 2024 16:31:51 GMT

@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.

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

JamesMorrison1 — Fri, 14 Jun 2024 16:35:27 GMT

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.

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

David_Lindsey — Fri, 14 Jun 2024 17:12:21 GMT

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

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

BlakeTerhune — Fri, 14 Jun 2024 18:20:27 GMT

Thanks for chiming in, @David_Lindsey! 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.

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

JamesMorrison1 — Wed, 19 Jun 2024 11:04:34 GMT

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

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

David_Lindsey — Wed, 19 Jun 2024 16:04:42 GMT

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.

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

JamesMorrison1 — Thu, 20 Jun 2024 11:26:33 GMT

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?