OIC, it's not actually running and generating that string, it is merely printing what you assigned it to.
Did you try any of the changes I posted above, they are working for me just fine.
I did notice that you have quite a few errors in your script, and, without the error reporting, it just errors out and doesn't tell you why. The code in my other post is working correctly, it is the "other" errors in your script that is throwing the exceptions.
Notice the print statements, best to get away from using "+" in print statements as you were trying to concatenate (that's what the plus sign means) a number and a string. Gives an error, but without it reporting the error, just says Problem executing SQL. Use commas in print statements and you wont run into this problem.
No need to iterate through a list of SQL statements as you are "splitting" your string by ";" as there are no semicolons in your string, so you end up with an empty list. Think you got this code from the example which is showing how to deal with the getParametersAsText() when passing from a script tool. In this case, if allow multiple inputs, they would come across as a semicolon separated string that would be parsed to the list. In this case, you are defining your "single" sql statement.
Try this code, it is working. If it doesn't work for you, I'd suggest grabbing the snippet from the bottom of my earlier post and have it print the fields for you, and make sure fields in the SQL statement are correct (check CasE also, as that may sometimes matter).
Anyway, cleaned out some of the error issues and the loop. Try this, get it to work, then add all the "other" stuff in there after it is working correctly.
R_
import arcpy
from arcpy import *
import sys
arcpy.env.workspace = (r"c:\connectionFiles\conntop10sig.sde")
try:
SQLStatement = ("select * from TOP10_SIG.DBO.ABCBNIVL where TOP10_SIG.DBO.ABCBNIVL.TYPECBN = 3")
sdeConn = arcpy.ArcSDESQLExecute(r"c:\connectionFiles\conntop10sig.sde") # vital part you are missing....
print "Execute SQL Statement: ", SQLStatement
try:
# Pass the SQL statement to the database.
#
print "connecting to dba"
sdeReturn = sdeConn.execute(SQLStatement)
print "Number of rows returned by query: ", len(sdeReturn)
except Exception, ErrorDesc:
print "ErrorDesc",ErrorDesc ## added this to actually print what the error is
sdeReturn = False
# If the return value is a list (a list of lists), display each list as a row from the
# table being queried.
if isinstance(sdeReturn, list):
print "Number of rows returned by query: ", len(sdeReturn), "rows"
for row in sdeReturn:
print "row"
print "\n"
else:
# If the return value was not a list, the statement was most likely a DDL statment.
# Check its status.
if sdeReturn == True:
print "SQL statement: ", SQLStatement , " ran sucessfully."
print "\n"
else:
print "SQL statement: ",SQLStatement ," FAILED."
print "\n"
except:
print "Problem executing SQL."
This should work as long as the workspace.sde connection includes a table named "TOP10_SIG.DBO.ABCBNIVL", and that table has a numeric field named "TYPECBN"
R_
hi rzufelt ,this is the result of the script running in arcgis python window:
>>> import arcpy
... from arcpy import *
... import sys
... arcpy.env.workspace = (r"c:\connectionFiles\conntop10sig.sde")
... try:
... SQLStatement = ("select * from TOP10_SIG.DBO.ABCBNIVL where TOP10_SIG.DBO.ABCBNIVL.TYPECBN = 3")
... sdeConn = arcpy.ArcSDESQLExecute(r"c:\connectionFiles\conntop10sig.sde") # vital part you are missing....
... print "Execute SQL Statement: ", SQLStatement
... try:
... # Pass the SQL statement to the database.
... #
... print "connecting to dba"
... sdeReturn = sdeConn.execute(SQLStatement)
... print "Number of rows returned by query: ", len(sdeReturn)
... except Exception, ErrorDesc:
... print "ErrorDesc",ErrorDesc ## added this to actually print what the error is
... sdeReturn = False
... # If the return value is a list (a list of lists), display each list as a row from the
... # table being queried.
... if isinstance(sdeReturn, list):
... print "Number of rows returned by query: ", len(sdeReturn), "rows"
... for row in sdeReturn:
... print "row"
... print "\n"
... else:
... # If the return value was not a list, the statement was most likely a DDL statment.
... # Check its status.
... if sdeReturn == True:
... print "SQL statement: ", SQLStatement , " ran sucessfully."
... print "\n"
... else:
... print "SQL statement: ",SQLStatement ," FAILED."
... print "\n"
... except:
... print "Problem executing SQL."
...
Execute SQL Statement: select * from TOP10_SIG.DBO.ABCBNIVL where TOP10_SIG.DBO.ABCBNIVL.TYPECBN = 3
connecting to dba
ErrorDesc Problem executing SQL.
