[GIS] Invalid SQL-Statement error when querying file-geodatabase table date field with arcobjects

arcobjectsdateenterprise-geodatabasefile-geodatabasesql

I want to query a DateTime Field in a table in a file-geodatabase and I keep on getting the error "invalid sql statement" (Error Code: -2147220985) while that sql statement does not give me an error on an empty table with the same configuration on an sde (even though there is no data in that table I think if the sql statement was wrong it should produce the same error there).

I use ArcObjects .Net SDK 10. I construct my whereclause like this, passing in two DateTime Objects as arguments. I've tried various ways of formating them.

string whereClause = string.Format("dat >= '{0}' AND dat <= '{1}'", fromDate.ToShortDateString(), toDate.ToShortDateString());

I then create an IQueryDef on a file-geodatabase workspace and set the previous whereclause. I then call the Evaluate() method of the IQueryDef and that is where it crashes. I was able to successfully launch other where-clauses on that same table in that same workspace but without taking the Date-Field into account. At the moment I don't see where the problem is. Is there a difference between accessing Date columns on a file-geodatabase vs a SDE with SQL out of ArcObjects? Or is something wrong with the way I use SQL here?

Best Answer

There is a differences between ArcSDE and File GDB SQL query syntax for Date fields:

SQL reference for query expressions used in ArcGIS (10.2)(Dates and time section)

http://resources.arcgis.com/en/help/main/10.2/index.html#//00s500000033000000

It appears you need to encapsulate your column names in double quotes and precede your values with date. I believe the File GDB workspace is case-sensitive as well:

This is the File GDB SQL syntax:

"Datefield" = date 'yyyy-mm-dd'

So yours might look something like:

string whereClause = string.Format(""dat" >= date '{0}' AND "dat" <= date '{1}'", fromDate.ToShortDateString(), toDate.ToShortDateString());

You can check if this is correct using the ISQLSyntax interface:

ISQLSyntax Interface

http://help.arcgis.com/en/sdk/10.0/arcobjects_net/componenthelp/index.html#/ISQLSyntax_Interface/002500000850000000/

I was also referring to this page for reference:

IQueryDef.WhereClause Property

http://resources.arcgis.com/en/help/arcobjects-net/componenthelp/index.html#//0025000006m1000000

Related Solutions

[GIS] ArcObjects: Alternative workflow to using a SQL query with over 1000 values in IN statement

Depending on performance requirements, you might consider loading a dictionary of objectid lists keyed by unique attribute at startup. Populate your picklist from the dictionary keys, then when the user picks from the list you would call IGeodatabaseBridge2.GetFeatures(), which has no limit AFAIK on the size of the objectID array passed to it.

Here's the code to load the dictionary:

public Dictionary<string, List<int>> GetUnique(IFeatureClass fc, string fldName)
{
    var outDict = new Dictionary<string, List<int>>();
    int idx = fc.Fields.FindField(fldName);
    if (idx == -1)
        throw new Exception(String.Format("field {0} not found on {1}", fldName, ((IDataset)fc).Name));

    IFeatureCursor fCur = null;
    IFeature feat;
    try
    {
        var qf = new QueryFilterClass();
        qf.SubFields = fc.OIDFieldName + "," + fldName; // updated per comment
        fCur = fc.Search(qf, true);
        while ((feat = fCur.NextFeature()) != null)
        {
            string key = feat.get_Value(idx) is DBNull ? "<Null>" : feat.get_Value(idx).ToString();
            if (!outDict.ContainsKey(key))
                outDict.Add(key, new List<int>());
            outDict[key].Add(feat.OID);
        }
    }
    catch
    {
        throw;
    }
    finally
    {
        if(fCur != null)
            System.Runtime.InteropServices.Marshal.FinalReleaseComObject(fCur);
    }
    return outDict;
}

[GIS] the best approach to using IQueryFilter.WhereClause in ArcObjects

Use the methods available on the ISQLSyntax interface to make your code workspace-independent.

As the help on IQueryFilter.WhereClause explains, use the ISQLSyntax.GetSpecialCharacter method to return the delimited identifier prefix and suffix specific to your workspace and add them to your column identifiers.

Example:

ISQLSyntax sqlSyntax = (ISQLSyntax)((IDataset)table).Workspace;
string fieldPrefixDelimiter = sqlSyntax.GetSpecialCharacter(
    esriSQLSpecialCharacters.esriSQL_DelimitedIdentifierPrefix);
string fieldSuffixDelimiter = sqlSyntax.GetSpecialCharacter(
    esriSQLSpecialCharacters.esriSQL_DelimitedIdentifierSuffix);
whereClause = String.Format("{0}{1}{2} IN({3})",
    fieldPrefixDelimiter,
    fieldName,
    fieldSuffixDelimiter,
    delimitedValueString);

Of course you would still have to check field types to build the part after the operator -- delimitedValueString is produced by some other logic that checks the field type and builds up a list of values to put in the IN statement.

That part is implementation specific as to what field types you want to support. In my case I just wrap strings and GUIDs in single-quotes, pass numeric types through unchanged, and throw an error for other unsupported field types (I wouldn't know how to or want to handle date fields for example).

Best Answer

Related Solutions

[GIS] ArcObjects: Alternative workflow to using a SQL query with over 1000 values in IN statement

[GIS] the best approach to using IQueryFilter.WhereClause in ArcObjects

Related Question