topic Re: Execute Sql Statement in SQL Server 2008 with ArcSDE connection in Python Questions

Execute Sql Statement in SQL Server 2008 with ArcSDE connection

ben_abdallahmhd — Mon, 29 Jul 2013 08:52:01 GMT

hi,
i'm trying to execute this SqlStatement to establish an sql query in sql server 2008 with an arcsde connection (arcsde personel use)

but there is no result.

the sql statement is :

Execute SQL Statement: (SELECT * FROM [TOP10_SIG].[dbo].[ABCBNIVL] WHERE [TOP10_SIG].[dbo].[ABCBNIVL].[TYPECBN] = 3)

an idea please !
I use ArcGIS 10.0
Arcsde personal use

Code:

import arcpy from arcpy import env import sys import os arcpy.env.workspace = (r"c:\connectionFiles\conntop10sig.sde") try:     SQLStatement = "SELECT * FROM [TOP10_SIG].[dbo].[ABCBNIVL] WHERE [TOP10_SIG].[dbo].[ABCBNIVL].[TYPECBN] = 3 "     SQLStatementList = SQLStatement.split(";")     print "\n"     for sql in SQLStatementList:         print "Execute SQL Statement: " + sql         try:             sdeReturn = sdeConn.execute(sql)         except Exception, ErrorDesc:             print "ErrorDesc"             sdeReturn = False         if isinstance(sdeReturn, list):             print "Number of rows returned by query: " + len(sdeReturn), "rows"             for row in sdeReturn:                 print "row"                 print "\n"             else:                 if sdeReturn == True:                     print "SQL statement: " + sql + " ran sucessfully."                     print "\n"                 else:                     print "SQL statement: " + sql + " FAILED."                     print "\n" except Exception, ErrorDesc:     print "Exception, ErrorDesc" except:     print "Problem executing SQL.

Re: Execute Sql Statement in SQL Server 2008 with ArcSDE connection

RhettZufelt — Mon, 29 Jul 2013 19:33:33 GMT

You didn't say why you want to select (I.e., what is the purpose) these features.

Is there some reason you can't just use arcpy.SelectLayerByLocation_management to make the selection? This will select all the features as well as the table attributes.

R_

Also, what is the output of this line that you have in there?

print "Execute SQL Statement: " + sql

Re: Execute Sql Statement in SQL Server 2008 with ArcSDE connection

ben_abdallahmhd — Mon, 29 Jul 2013 22:53:22 GMT

You didn't say why you want to select (I.e., what is the purpose) these features.

Is there some reason you can't just use arcpy.SelectLayerByLocation_management to make the selection? This will select all the features as well as the table attributes.

R_

Also, what is the output of this line that you have in there?

print "Execute SQL Statement: " + sql

hi rzufelt:
this query is trying to set an example of sql query in the sql server.
the final query that I want to establish is a query on two tables linked by a relationship and then copy the selection
in another table tables.
the output of : print "Execute SQL Statement: " + sql is
Execute SQL Statement: SELECT * FROM TOP10_SIG.dbo.ABCBNIVL WHERE TOP10_SIG.dbo.ABCBNIVL.TYPECBN = 3

thank you

Re: Execute Sql Statement in SQL Server 2008 with ArcSDE connection

RhettZufelt — Sat, 11 Dec 2021 18:04:55 GMT

hi,
i'm trying to execute this SqlStatement to establish an sql query in sql server 2008 with an arcsde connection (arcsde personel use)

but there is no result.

the sql statement is :

Execute SQL Statement: (SELECT * FROM [TOP10_SIG].[dbo].[ABCBNIVL] WHERE [TOP10_SIG].[dbo].[ABCBNIVL].[TYPECBN] = 3)

an idea please !
I use ArcGIS 10.0
Arcsde personal use

Code:

import arcpy


arcpy.env.workspace = (r"c:\connectionFiles\conntop10sig.sde")

sql= "select * from TOP10_SIG.DBO.ABCBNIVL where TOP10_SIG.DBO.TYPECBN = 3"   # don't use the brackets here
#    SQLStatementList = SQLStatement.split(";")  # this is doing nothing as you end up with empty list.

sdeConn = arcpy.ArcSDESQLExecute(r"c:\connectionFiles\conntop10sig.sde")   # need to define your connection


sdeReturn = sdeConn.execute(sql)
print "number records selected ",len(sdeReturn)
for ret in sdeReturn:
    print ret

You need to establish the sdeConn connection using the ArcSDESQLExecute(). The above code will select all fields in the table where TYPECBN = 3 (assuming this is a numeric field) as long as the table/field names are correct.

Also, need to make sure you have the table names correct. This snippet will give you the list of tables and the proper names for them:

>>> tables = arcpy.ListTables()
>>> for table in tables:
 print(table)

R_

More info here, but it looks like you have already extracted "some" snippets from here. http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//002z00000021000000

Re: Execute Sql Statement in SQL Server 2008 with ArcSDE connection

RhettZufelt — Tue, 30 Jul 2013 00:24:33 GMT

hi rzufelt:
the output of : print "Execute SQL Statement: " + sql is
Execute SQL Statement: SELECT * FROM TOP10_SIG.dbo.ABCBNIVL WHERE TOP10_SIG.dbo.ABCBNIVL.TYPECBN = 3

This looks like what you "want" your sql to look like, but I can't figure any way you got that from the attached code. Are you running a different snippet than you posted?

R_

Re: Execute Sql Statement in SQL Server 2008 with ArcSDE connection

ben_abdallahmhd — Sat, 11 Dec 2021 18:04:57 GMT

This looks like what you "want" your sql to look like, but I can't figure any way you got that from the attached code. Are you running a different snippet than you posted?

R_

hi rzuflet,

this is the full script :

>>> import arcpy
... from arcpy import *
... import sys
... arcpy.env.workspace = (r"c:\connectionFiles\conntop10sig.sde")
... try:
...     
...     # Two ways to create the object, which also creates the connection to ArcSDE.
...     #   Using the first method, pass a set of strings containing the connection properties:
...     #   <serverName>,<portNumber>,<version>,<userName>,<password>
...     #   sdeConn = arcpy.ArcSDESQLExecute("gpserver3","5151","#","toolbox","toolbox")
...     #   Using the second method pass the path to a valid ArcSDE connection file
...     #
...    
...     # Get the SQL statements, separated by ; from a text string.
...     #
...     SQLStatement = ("SELECT * FROM [TOP10_SIG].[dbo].[ABCBNIVL] WHERE [TOP10_SIG].[dbo].[ABCBNIVL].[TYPECBNV] = 3 ")
...     SQLStatementList = SQLStatement.split(";")
...     
...     
...     print "\n"
...     # For each SQL statement passed in, execute it.
...     #
...     for sql in SQLStatementList:
...         print "Execute SQL Statement: " + sql
...         try:
...             # Pass the SQL statement to the database.
...             #
...             sdeReturn = sdeConn.execute(sql)
...         except Exception, ErrorDesc:
...             print "ErrorDesc"
...             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: " + sql + " ran sucessfully."
...                 print "\n"
...             else:
...                 print "SQL statement: " + sql + " FAILED."
...                 print "\n"
...                 
... except Exception, ErrorDesc:
...     print "Exception, ErrorDesc"
... except:
...     print "Problem executing SQL."
...

the result is :

Execute SQL Statement: SELECT * FROM [TOP10_SIG].[dbo].[ABCBNIVL] WHERE [TOP10_SIG].[dbo].[ABCBNIVL].[TYPECBNV] = 3

ErrorDesc

SQL statement: SELECT * FROM [TOP10_SIG].[dbo].[ABCBNIVL] WHERE [TOP10_SIG].[dbo].[ABCBNIVL].[TYPECBNV] = 3 FAILED.

thank you

Re: Execute Sql Statement in SQL Server 2008 with ArcSDE connection

RhettZufelt — Sun, 12 Dec 2021 16:25:39 GMT

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_

Re: Execute Sql Statement in SQL Server 2008 with ArcSDE connection

ben_abdallahmhd — Sat, 11 Dec 2021 18:05:00 GMT

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.

Re: Execute Sql Statement in SQL Server 2008 with ArcSDE connection

JamesCrandall — Wed, 31 Jul 2013 10:50:31 GMT

Just a shot in the dark (I've seen all kinds of database design quirks), but... Is the "TYPECBN" field really type integer? If it is actually a string, then = 3 would fail without the quotes around '3'.

Also, you really should list the fields you wish to include in your result. Using "Select *" is just a poor habit to get into.

Re: Execute Sql Statement in SQL Server 2008 with ArcSDE connection

RhettZufelt — Sat, 11 Dec 2021 18:05:03 GMT

Try this:

import arcpy
from arcpy import *
import sys
arcpy.env.workspace = r"c:\connectionFiles\conntop10sig.sde"




sql = "select * from TOP10_SIG.DBO.ABCBNIVL where TOP10_SIG.DBO.ABCBNIVL.TYPECBN = 3"

sdeConn = arcpy.ArcSDESQLExecute(r"c:\connectionFiles\conntop10sig.sde")

print "Execute SQL Statement: ", sql
print "connecting to dba"
sdeReturn = sdeConn.execute(sql)

if isinstance(sdeReturn, list):
    print "Number of rows returned by query: ", len(sdeReturn), "rows"
    for row in sdeReturn:
        print row
else:
    print "no selection made"

If you are still getting errors, you need to confirm that there is a numeric field named TYPECBN in the table named TOP10_SIG.DBO.ABCBNIVL in the sde connection r"c:\connectionFiles\conntop10sig.sde". Look in ArcCatalog and make sure that TOP10_SIG.DBO.ABCBNIVL is listed as the table name there. If so, and still getting errors, you might try dropping the DBO from it ( TOP10_SIG.ABCBNIVL ) as I have seen in the FlexApp environment where links to DBO tables didn't work with that in the path.

R_

Re: Execute Sql Statement in SQL Server 2008 with ArcSDE connection

ben_abdallahmhd — Sat, 11 Dec 2021 18:05:06 GMT

Try this:
import arcpy
from arcpy import *
import sys
arcpy.env.workspace = r"c:\connectionFiles\conntop10sig.sde"




sql = "select * from TOP10_SIG.DBO.ABCBNIVL where TOP10_SIG.DBO.ABCBNIVL.TYPECBN = 3"

sdeConn = arcpy.ArcSDESQLExecute(r"c:\connectionFiles\conntop10sig.sde")

print "Execute SQL Statement: ", sql
print "connecting to dba"
sdeReturn = sdeConn.execute(sql)

if isinstance(sdeReturn, list):
    print "Number of rows returned by query: ", len(sdeReturn), "rows"
    for row in sdeReturn:
        print row
else:
    print "no selection made"
If you are still getting errors, you need to confirm that there is a numeric field named TYPECBN in the table named TOP10_SIG.DBO.ABCBNIVL in the sde connection r"c:\connectionFiles\conntop10sig.sde". Look in ArcCatalog and make sure that TOP10_SIG.DBO.ABCBNIVL is listed as the table name there. If so, and still getting errors, you might try dropping the DBO from it ( TOP10_SIG.ABCBNIVL ) as I have seen in the FlexApp environment where links to DBO tables didn't work with that in the path.

R_

hi rzufelt,

you have reason the type of the field is SubtypeField, when i change the query to another field (integer) it works very good.

thank you very much

for the subtype field, have you please an idea how can select it.

>>> 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.ALTDCRNV = 50") # ALTDCRNV = the elevation
...     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.ALTDCRNV = 50

connecting to dba

Number of rows returned by query:    38

Number of rows returned by query:    38   rows

row

row
.
.

Re: Execute Sql Statement in SQL Server 2008 with ArcSDE connection

RhettZufelt — Thu, 01 Aug 2013 16:04:35 GMT

Ben,

I don't use sde or subtypes, so can't really test it. I'd look here: http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#/A_quick_tour_of_using_SQL_with_ArcSDE_geodatabases/006z00000008000000/ and the sub folders under that link. Might get you what you need.

R_