topic Re: SQL statements in ArcObjects SDK Questions

SQL statements

Corbinde_Bruin — Fri, 16 Nov 2012 12:05:54 GMT

Hi all,

I'm new to SQL statements and especially within ArcGIS. I'm programming an addin against a file geodatabase in in Desktop 10.1 and I'm getting this error:

An invalid SQL statement was used.
[SELECT FeatureClassAlias FROM tblFeatureClasses WHERE IsSearchable = 'Y' ORDER BY SortSequence]

It works if I take out the ORDER BY SortSequence part, but it obviously doesn't sort at all. I've looked at all the ESRI references on SQL with File geodatabases and can't see my error. It says field names may be delimited by quotes, but that hasn't helped. It also says that ORDER BY won't work if I'm searching in all fields with SELECT *, but that shouldn't be my problem.

An invalid SQL statement was used.
[SELECT "FeatureClassAlias" FROM tblFeatureClasses WHERE "IsSearchable" = 'Y' ORDER BY "SortSequence"]

I'd greatly appreciate help with this issue from someone with SQL experience. I'll also post the code if you think it will help.

Thank you,
cdebruin

  Public Sub LoadTblToCbx(inputTable As ITable, inputFieldName As String, cbxInput As System.Windows.Forms.ComboBox)                  ' Create the query filter.         Dim queryFilter As IQueryFilter2 = New QueryFilter          ' Select the fields to be returned         queryFilter.SubFields = inputFieldName          ' Set the filter to return only searchable         queryFilter.WhereClause = """IsSearchable"" = 'Y'"          ' Use the PostfixClause to sort ascending         Dim queryFilterDef As IQueryFilterDefinition2 = CType(queryFilter, IQueryFilterDefinition2)         queryFilterDef.PostfixClause = "ORDER BY ""SortSequence"""          ' Output the returned feature class name.         Dim fieldIndex As Integer = inputTable.FindField(inputFieldName)         Dim cursor As ICursor = inputTable.Search(queryFilter, True)         Dim row As IRow = cursor.NextRow()         While Not row Is Nothing             Dim field As String = Convert.ToString(row.Value(fieldIndex))             cbxInput.Items.Add(field)             row = cursor.NextRow()         End While          Marshal.FinalReleaseComObject(cursor)      End Sub

Re: SQL statements

MichaelVolz — Sat, 11 Dec 2021 20:04:12 GMT

Try this for additional information:

http://resources.esri.com/help/9.3/ArcGISEngine/arcobjects/esriGeoDatabase/IQueryFilterDefinition.PostfixClause_Example.htm

[Visual Basic 6.0]'this is an example of using the PostfixClause property to append a Order By clause to the query
Dim pQueryFilter As IQueryFilterSet pQueryFilter = New QueryFilterpQueryFilter.SubFields = "FULLNAME"pQueryFilter.WhereClause = "OBJECTID > 10"
Dim pQueryFilterDefinition As IQueryFilterDefinitionSet pQueryFilterDefinition = pQueryFilterpQueryFilterDefinition.PostFixClause = "ORDER BY FULLNAME"
Dim pFeatureCursor As IFeatureCursorSet pFeatureCursor = pFeatureClass.Search(pQueryFilter, True)
Dim codeix As Longcodeix = pFeatureCursor.FindField("FULLNAME")
Dim pFeature As IFeatureSet pFeature = pFeatureCursor.NextFeature
While Not pFeature Is Nothing    MsgBox pFeature.Value(codeix)    Set pFeature = pFeatureCursor.NextFeatureWend

Another forum example:

Dim queryFilter As IQueryFilter
Set queryFilter = new QueryFilter
queryFilter.WhereClause = "theField = 'abc'"

Dim queryFilterDef As IQueryFilterDefinition
Set queryFilterDef = queryFilter
queryFilterDef.PostfixClause = "ORDER BY sortField"

Re: SQL statements

Corbinde_Bruin — Fri, 16 Nov 2012 14:19:10 GMT

Hmm,

No luck. I tried that syntax and I'd already referenced that VB6 example.

Thanks you Mike

Re: SQL statements

AndrewMay — Fri, 16 Nov 2012 14:44:07 GMT

Do filegeodatabase support the ORDER BY function? They didn't used to in ArcGIS 9 - not sure whether they do or not in 10.

From http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//003n00000032000000:

File geodatabases do not support all the features and functions available for personal geodatabases. At ArcGIS 9.2, the most commonly used functions not supported by file geodatabases include DISTINCT, GROUP BY, and ORDER BY, and the set functions AVG, COUNT, MIN, MAX, and SUM are not supported outside subqueries. Support for some of these is likely to be added in future releases.

Re: SQL statements

Corbinde_Bruin — Fri, 16 Nov 2012 14:49:04 GMT

Do filegeodatabase support the ORDER BY function? They didn't used to in ArcGIS 9 - not sure whether they do or not in 10.

That's a question I was hoping someone else could answer, haha. I see it's supported in subqueries. Can I somehow use subqueries as a workaround. I don't really understand the syntax for subqueries.

Thank you for you reply Andrew

Re: SQL statements

MichaelVolz — Fri, 16 Nov 2012 15:06:04 GMT

I think this link answers your question that this is not currently possible:

http://support.esri.com/en/bugs/nimbus/TklNMDY0NDcy

Is there any chance you could call this layer from an SDE geodatabase where ORDER BY is supported?

You might try a personal geodatabase as it might also be supported there, but you would need to test that out.

Re: SQL statements

Corbinde_Bruin — Fri, 16 Nov 2012 15:24:58 GMT

We will actually be moving to an SDE environment for our current project, so I'm glad to know that it works there.

I wanted to know if it worked in File geodatabases for other/future purposes.

Thanks Mike

Re: SQL statements

LanceShipman — Fri, 16 Nov 2012 15:25:04 GMT

ORDER BY is supported by File Geodatabase at ArcGIS 10.0. You must include the sort field(s) in the field list.

Something like:

' Select the fields to be returned queryFilter.SubFields = inputFieldName & ", SortSequence"  ' Set the filter to return only searchable queryFilter.WhereClause = """IsSearchable"" = 'Y'"  ' Use the PostfixClause to sort ascending  Dim queryFilterDef As IQueryFilterDefinition2 = CType(queryFilter, IQueryFilterDefinition2)  queryFilterDef.PostfixClause = "ORDER BY SortSequence"

Re: SQL statements

Corbinde_Bruin — Fri, 16 Nov 2012 15:35:14 GMT

ORDER BY is supported by File Geodatabase at ArcGIS 10.0. You must include the sort field(s) in the field list.

Thank you for responding Lance,

I'm not sure what you mean by field list. Perhaps you could take a quick look at my code up top and see where I went wrong? Or point me towards an example where this field list is used/documented/explained?

-cdebruin

Re: SQL statements

LanceShipman — Fri, 16 Nov 2012 15:38:29 GMT

See the code fragment that I posted. SubField is the field list.

Lance

Re: SQL statements

MichaelVolz — Fri, 16 Nov 2012 15:49:02 GMT

Corbin:

Can you please post your code when you have it working with the file geodatabase?

Lance:

How come the NIM I referenced says that this functionality is on-hold for file geodatabases, but you say it actually does work?

Re: SQL statements

Corbinde_Bruin — Sat, 11 Dec 2021 20:04:15 GMT

It works!

Thanks Lance, you helped a lot.

This Sub takes in 5 parameters, but it should work for anyone looking to populate a combobox from a table with SQL

Public Sub LoadTblToCbx(inputTable As ITable, searchField As String, whereClause As String, sortField As String, cbxInput As System.Windows.Forms.ComboBox)
        'CJD 11/15/2012 - uses SQL statements to query a geodatabase table and write selective values to a combobox

        cbxInput.Items.Clear() 'clear old comboboxitmes

        Dim queryFilter As IQueryFilter2 = New QueryFilter 'Create the query filter.

        'Select the fields to be returned
        queryFilter.SubFields = searchField & "," & sortField
        
        'Set the field to query
        queryFilter.WhereClause = whereClause

        ' Use the PostfixClause to sort ascending
        Dim queryFilterDef As IQueryFilterDefinition2 = CType(queryFilter, IQueryFilterDefinition2)
        queryFilterDef.PostfixClause = "ORDER BY " & sortField

        Dim fieldIndex As Integer = inputTable.FindField(searchField)
        Dim cursor As ICursor = inputTable.Search(queryFilter, True)
        Dim row As IRow = cursor.NextRow()
        While Not row Is Nothing
            Dim field As String = Convert.ToString(row.Value(fieldIndex))
            cbxInput.Items.Add(field)
            row = cursor.NextRow()
        End While

        Marshal.FinalReleaseComObject(cursor)

    End Sub

Re: SQL statements

LanceShipman — Fri, 16 Nov 2012 16:19:45 GMT

Corbin:

Can you please post your code when you have it working with the file geodatabase?

Lance:

How come the NIM I referenced says that this functionality is on-hold for file geodatabases, but you say it actually does work?

The NIMBUS bug is for the File Geodatabase API, where we don't support ORDER BY. We added significant SQL functionality to the File Geodatabase at 10.0 and 10.1. This includes ORDER BY and GROUP BY (10.1). None of this was ported to the File Geodatabase API.