Invalid SQL Statement in ValidateDatabase

[LindaTedrow]

This error occurred in the latest and a previous version.

line 1112, in
for row in cursor:
RuntimeError: An invalid SQL statement was used. [SELECT MapUnit FROM DescriptionOfMapUnits ORDER BY HierarchyKey]

my change to code stopped the error-- added HierarchyKey to table list of with Search Cursor line:

open search cursor on DMU sorted by HKey

sql = (None, 'ORDER BY HierarchyKey')
###with arcpy.da.SearchCursor('DescriptionOfMapUnits',('MapUnit'),None,None,False,sql) as cursor:
with arcpy.da.SearchCursor('DescriptionOfMapUnits',('MapUnit','HierarchyKey'),None,None,False,sql) as cursor:

[ethoms-usgs]

Hi Linda,
I just looked at this and was able to create a SearchCursor sorted on HierarchyKey without also listing that field in the field names parameter; as the original code shows. It doesn't break anything later on to include your fix, but I am interested in why it fails for you and not for me. Can you tell me more? Format of the database? Are you using aliases? All the required fields are in the table? Any other guess about how your table might be different from what we used to debug the tool?

[LindaTedrow]

Evan, I had similar problem on previous version of validate. Here are my notes from that: # open search cursor on DMU sorted by HKey ### the sql clause is causing an error -- will try adding MapUnit to clause as per ### https://desktop.arcgis.com/en/arcmap/latest/analyze/arcpy-data-access/searchcursor-class.htm -- LAT sql = (None, 'ORDER BY HierarchyKey') ##sql = (None, 'ORDER BY HierarchyKey','MapUnit' ) with arcpy.da.SearchCursor('DescriptionOfMapUnits',('MapUnit', 'HierarchyKey'),None,None,False,sql) as cursor: for row in cursor: mu = row[0] if notEmpty(mu): summary.write('<tr><td>'+mu+'</td><td>X</td>') # 2nd cell: value is, by definition, in DMU for f in fds_MapUnits: if mu in f[1]: summary.write('<td>X</td>') else: summary.write('<td>---</td>') summary.write('</tr>\n') # for mapunits not in DMU The database is one that has had a long history from auto cad to mdb and now “improved” and converted to gdb. In going from mdb to gdb there have been some field updates, and other conversion woes. Re: aliases – I do not think so . We do use domains Yes all the required fields are in the table. During conversion process one torment was changing field name from “TYPE” to “Type”. I don’t know if there is something like this in DOMU table? Question back at you: If you run a test validation does the fix I suggested work? Linda From: Evan Thoms <[email protected]> Sent: Tuesday, October 13, 2020 2:13 PM To: usgs/gems-tools-arcmap <[email protected]> Cc: Tedrow, Linda ([email protected]) <[email protected]>; Author <[email protected]> Subject: Re: [usgs/gems-tools-arcmap] Invalid SQL Statement in ValidateDatabase (#49) Hi Linda, I just looked at this and was able to create a SearchCursor sorted on HierarchyKey without also listing that field in the field names parameter; as the original code shows. It doesn't break anything later on to include your fix, but I am interested in why it fails for you and not for me. Can you tell me more? Format of the database? Are you using aliases? All the required fields are in the table? Any other guess about how your table might be different from what we used to debug the tool? — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub<https://urldefense.com/v3/__https:/github.com/usgs/gems-tools-arcmap/issues/49*issuecomment-708012172__;Iw!!JYXjzlvb!1xU2wVMJrAVZ1rRk3jYdMKwXM4AFL6Omkey95OcTbVqWTbt6ZaPrpCMfsj7ucp2E$>, or unsubscribe<https://urldefense.com/v3/__https:/github.com/notifications/unsubscribe-auth/AKYEVB55W2Q3U3EWH7CPMFLSKS7HBANCNFSM4SNLMBXQ__;!!JYXjzlvb!1xU2wVMJrAVZ1rRk3jYdMKwXM4AFL6Omkey95OcTbVqWTbt6ZaPrpCMfst3ISdJt$>.

[ethoms-usgs]

Thanks, Linda.
That is odd. I have no clue. Could you send me the gdb?

To answer your last question, it's more or less moot because I don't get the error with the code as it is. In adding another field to be retrieved by the SearchCursor, it's just some extra values in the cursor that aren't ever retrieved.

Case doesn't matter as far as retrieving fields in the search cursor goes but in other parts of the code, it could. That does have to be considered.

[ethoms-usgs]

Did you ever send me a gdb?
In the meantime, or instead, your choice, could you try this in ArcMap?

1. Add a DescriptionOfMapUnits table
2. open the python interactive window
3. type in these commands:

>>> sql = (None, 'ORDER BY HierarchyKey')
>>> rows = arcpy.da.SearchCursor("DescriptionOfMapUnits", 'MapUnit', None None, False, sql)
>>> for row in rows: print(row)

with the very simple DMU I have, I get

(None,)
(u'Da',)
(u'Dt',)
(u'Ddr',)
(None,)
(u'Swlc',)

Now, type these commands:

>>> sql = (None, 'ORDER BY Name') 
>>> rows = arcpy.da.SearchCursor("DescriptionOfMapUnits", 'MapUnit', None, None, False, sql)
>>> for row in rows: print(row)

and my result is

(u'Da',)
(u'Ddr',)
(None,)
(u'Swlc',)
(None,)
(u'Dt',)

The sort order is different (and correct according to when I do the same sorts in the table view) but you see that I don't specify the sorting field in the fields parameter when setting the SearchCursor. Do you get similar results?