arcpy.ArcSDESQLExecute returning Boolean for EXEC SQL Statement?

4700
6
08-29-2014 07:38 AM
Highlighted
New Contributor

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.

Reply
0 Kudos
6 Replies
Highlighted
New Contributor

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? 

Reply
0 Kudos
Highlighted
MVP Esteemed Contributor

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

Reply
0 Kudos
Highlighted
New Contributor

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)

Reply
0 Kudos
Highlighted
MVP Esteemed Contributor

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?

Reply
0 Kudos
Highlighted
MVP Regular Contributor

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.

Reply
0 Kudos
Highlighted
New Contributor

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‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍
Reply
0 Kudos