topic arcpy.ArcSDESQLExecute returning Boolean for EXEC SQL Statement? in Data Management Questions

arcpy.ArcSDESQLExecute returning Boolean for EXEC SQL Statement?

TomasWoods — Fri, 29 Aug 2014 14:38:21 GMT

I am trying to call a stored procedure using arcpy.ArcSDESQLExecute to retrieve a single record. In MSSQL I can call the stored procedure and I get the expected value. When I use arcpy.ArcSDESQLExecute to execute the stored procedure I get a Boolean True value.

sdeConn.execute('EXEC dbo.usp_GetValue;') //Always returns true

Has anyone ever encountered this problem before.

Re: arcpy.ArcSDESQLExecute returning Boolean for EXEC SQL Statement?

TomGiles — Thu, 03 Nov 2016 18:16:16 GMT

I also have this problem. I am executing a simply "SELECT column FROM table WHERE column='something'" and I am being returned True, even though the correct response is 2 rows of data. Anyone?

Re: arcpy.ArcSDESQLExecute returning Boolean for EXEC SQL Statement?

JoshuaBixby — Thu, 03 Nov 2016 18:58:15 GMT

Are you querying against versioned or unversioned tables? It appears the WHERE statement is against a text field, correct?

Re: arcpy.ArcSDESQLExecute returning Boolean for EXEC SQL Statement?

TomGiles — Thu, 03 Nov 2016 19:23:15 GMT

It is an unversioned feature class. I am using direct connect. The where statement does execute against a text field of Hash IDs.

Note: I have gotten around this by dropping Arcpy and using pymssql - it works and the overhead is pretty small. I would still be interested in hearing why I am being returned True (which is supposed to be returned in cases where no records are returned)

Re: arcpy.ArcSDESQLExecute returning Boolean for EXEC SQL Statement?

JoshuaBixby — Thu, 03 Nov 2016 19:55:21 GMT

Can you get any query to work using arcpy.ArcSDESQLExecute or is it just this specific query that is having issues? What data type, specifically, is the hash ID field? Are any special characters used in the hashes, specifically backslashes?

Re: arcpy.ArcSDESQLExecute returning Boolean for EXEC SQL Statement?

BlakeTerhune — Thu, 03 Nov 2016 20:01:13 GMT

arcpy.ArcSDESQLExecute() returns a boolean TRUE if a select statement was valid but no rows were returned. In your case, you're executing a procedure and I think it's just telling you the procedure ran successfully.

...for statements that do not return rows, it will return an indication of the success or failure of the statement (True for success; None for failure).

Maybe try reformatting your SQL into a select statement to trick it into expecting rows back.

Re: arcpy.ArcSDESQLExecute returning Boolean for EXEC SQL Statement?

TomGiles — Sun, 12 Dec 2021 08:08:20 GMT

Thanks for your help walking me through this. A little embarrassing, I rewrote the stub and it is working fine. This script isn't on git so unfortunately I cannot back track. It's curious I found the original post here, I now wonder what I/we were doing; it must have been a typo. Note, it wasn't a procedure but good call on that behaviour.

Very simple, as the sample goes..

import arcpy

print "Connecting to SQL Server.."
sqlConn = arcpy.ArcSDESQLExecute(r"\\gismsa\...\sde@iSphereBuoys.sde")

table_name = 'iSphereBuoys'
field_name = 'hashId'

sql = '''
SELECT {0} FROM {1} WHERE {0}='943906c71d6c93c7a2515b33d7a5be65'
'''.format(field_name, table_name)

sqlReturn = sqlConn.execute(sql)
print sqlReturn
#or
for i in sqlReturn:
    print i‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍