Select to view content in your preferred language

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

302
9
a month ago
JamesMorrison1
Occasional Contributor

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.

 

 

 

 

Tags (2)
9 Replies
BlakeTerhune
MVP Regular Contributor

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
Occasional Contributor

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

0 Kudos
BlakeTerhune
MVP Regular Contributor

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

0 Kudos
JamesMorrison1
Occasional Contributor

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. 

0 Kudos
anonymous_geographer
Occasional Contributor

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

BlakeTerhune
MVP Regular Contributor

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.

0 Kudos
JamesMorrison1
Occasional Contributor

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

 

 

 

 

 

0 Kudos
anonymous_geographer
Occasional Contributor

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.

0 Kudos
JamesMorrison1
Occasional Contributor

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?

0 Kudos