ArcGIS Engine: Can a Query Layer be created from a SQLite database in 10.2?

ScottKutz

Is it possible to use a SQLite database as the basis for creating a Query Layer in an ArcGIS Engine application starting with ArcGIS 10.2?

The ArcGIS 10.2 Help on Query Layers has been updated to specify the use of the Teradata databases (newly-supported in ArcGIS 10.2).

Other portions of ArcGIS 10.2 Help mention SQLite, but the Query Layers documentation is notably silent on SQLite.

The code fragment below shows an example of what would seem to be a potential approach (based on an existing method for creating a Query Layer against other database types).

But I have not been able to locate any documentation on how to populate the PropertySet to establish a connection to a SQLite database.

// Code fragment for creating a feature layer for a Query Layer in ArcGIS Engine.
// Environment: Visual C++, MFC, Visual Studio 2010
IWorkspaceFactory2Ptr       pWorkspaceFactory2 = NULL; 
IWorkspacePtr               pWorkspace = NULL;
ISqlWorkspacePtr            pSqlWorkspace = NULL;
IQueryDescriptionPtr        pQueryDescription = NULL;
ITablePtr                   pTableForSQL = NULL;
IFeatureClassPtr            pFeatureClassForSQL = NULL;
IFeatureLayerPtr            pFeatureLayer = NULL;

IPropertySetPtr             pPropertySet = NULL;
OLE_HANDLE                  hWnd = 0;
HRESULT                     hr = S_OK;

// Note: All error handling removed from the sample code.
pWorkspaceFactory2.CreateInstance(CLSID_SdeWorkspaceFactory);

pPropertySet.CreateInstance(CLSID_PropertySet);

// ... Q: How to populate the PropertySet appropriately for a SQLite database(?)
//
//  Once the Property Set is populated, open the workspace.
hr = pWorkspaceFactory2->Open(pPropertySet, hWnd, &pWorkspace); 

// QI to get the ISqlWorkspace interface pointer
pSqlWorkspace = pWorkspace;

// Pass the incoming SQL query text on to ArcGIS Engine Runtime so it can make its
// attempt to create the Query Description object.
hr = pSqlWorkspace->GetQueryDescription(CComBSTR(_T("SELECT * FROM BASEDB.DISTRIBUTIONMAIN")),
                                        &pQueryDescription);

// Attempt to open the Query Class for the Query Layer by passing in the
// (possibly modified) Query Description object.
hr = pSqlWorkspace->OpenQueryClass(CComBSTR(_T("Distr_Mains")), 
                                   pQueryDescription,
                                   &pTableForSQL);

// QI to determine if the query class also supports the IFeatureClass interface.
// If so, this indicates it is able to form the basis for a Feature Layer.
pFeatureClassForSQL = pTableForSQL;

if (pFeatureClassForSQL != NULL)
{
   // Create the new feature layer object
   pFeatureLayer.CreateInstance(CLSID_FeatureLayer);

   // Assign the just-created feature class to the layer
   hr = pFeatureLayer->putref_FeatureClass(pFeatureClassForSQL);

   // ... additional logic to assign layer properties ...
}

Any insights would be appreciated.

Thanks for your help.

Scott

ScottKutz

We opened an incident with Esri Tech Support to investigate this question. It turns out it is possible to create a Query Layer from a SQLite database in ArcGIS 10.2 . The general approach from ArcObjects uses the SqlWorkspaceFactory::OpenFromFile method to gain access to the ISqlWorkspace interface pointer (where the file name is the name of the *.sqlite file itself).

Essentially, the full file name of the *.sqlite file serves as the "connection data". There is no need to instantiate a class that supports the IPropertySet interface and build up a formal set of connection parameters/properties.

An updated version of the Visual C++ code fragment is included below.

// Code fragment for creating a feature layer for a Query Layer from a SQLite database in ArcGIS Engine 10.2.
// Environment: Visual C++, MFC, Visual Studio 2010
IWorkspaceFactory2Ptr       pWorkspaceFactory2 = NULL; 
IWorkspacePtr               pWorkspace = NULL;
ISqlWorkspacePtr            pSqlWorkspace = NULL;
IQueryDescriptionPtr        pQueryDescription = NULL;
ITablePtr                   pTableForSQL = NULL;
IFeatureClassPtr            pFeatureClassForSQL = NULL;
IFeatureLayerPtr            pFeatureLayer = NULL;

CString                     csSQLiteFileName = _T("C:\\Data\\TestDB.sqlite");

CComBSTR                    bstrNewQueryFeatureClassName;

OLE_HANDLE                  hWnd = 0;
HRESULT                     hr = S_OK;

// Note: All error handling removed from the sample code.
//
// For accessing a SQLite database file, it is necessary to
// use the SQL Workspace Factory.
pWorkspaceFactory2.CreateInstance(CLSID_SqlWorkspaceFactory);

// Use the *.sqlite file name as the basis for instantiating the
// necessary workspace interface pointer.
hr = pWorkspaceFactory2->OpenFromFile(CComBSTR(csSQLiteFileName), 
                                      hWnd, &pWorkspace); 

// QI to get the ISqlWorkspace interface pointer
pSqlWorkspace = pWorkspace;

// Pass the SQL query text on to ArcGIS Engine Runtime so it can make its
// attempt to create the Query Description object.
// Note: This phase of the definition process may be iterative, repeated as often
// as needed to generate valid data held in the IQueryDescription interface pointer.
// For example, the methods similar to IQueryDescription::get_IsSpatialQuery, 
// IQueryDescription::get_OIDFields, and IQueryDescription::get_Srid can be used
// to verify that the necessary information is available to ArcGIS Engine Runtime.
// If data is missing, it is necessary to obtain it and then re-invoke 
// ->GetQueryDescription() (such as prompting the user for the name of the Unique ID 
// attribute field if the underlying database table metadata does not allow ArcGIS 
// Engine Runtime to identify the appropriate attribute by default).
hr = pSqlWorkspace->GetQueryDescription(CComBSTR(_T("SELECT * FROM BASEDB.DISTRIBUTIONMAIN")),
                                        &pQueryDescription);

// Ensure that the specified name for the Query Layer feature class is not
// already being used.
// Please note that ISqlWorkspace::CheckDataSetName needs to be called before 
// using the OpenQueryClass method. The name returned by ->CheckDatasetName()
// is the name to be used as the first parameter in the later
// call to ->OpenQueryClass().
bstrNewQueryFeatureClassName.Empty();
hr = pSqlWorkspace->CheckDatasetName(CComBSTR(_T("Distr_Mains"),
                                     pQueryDescription,
                                     &bstrNewQueryFeatureClassName);

// Attempt to open the Query Class for the Query Layer by passing in the
// (possibly modified) Query Description object.
hr = pSqlWorkspace->OpenQueryClass(bstrNewQueryFeatureClassName), 
                                   pQueryDescription,
                                   &pTableForSQL);

// QI to verify the query class also supports the IFeatureClass interface.
// If so, this indicates it is able to form the basis for a Feature Layer.
pFeatureClassForSQL = pTableForSQL;

if (pFeatureClassForSQL != NULL)
{
   // Create the new feature layer object
   pFeatureLayer.CreateInstance(CLSID_FeatureLayer);

   // Assign the just-created feature class to the layer
   hr = pFeatureLayer->putref_FeatureClass(pFeatureClassForSQL);

   // ... additional logic to assign layer properties ...
} 

When using the "Add Data" button in ArcMap 10.2, browsing out to a *.sqlite file, and then selecting a feature class, ArcMap internally goes through the process of creating a Query Layer that then appears in the Table of Contents. If you open the Layer Properties dialog, go to the Source tab, and then click the [Change Query] button, the [Edit Query Layer] dialog is launched and you will see the text of the query built internally as part of creating the layer in the ArcMap environment.

For those developers using .Net, the code example below is the one received from Esri Tech Support.

After opening the SQLite dataset through ISqlWorkspaceFactory::OpenFromFile, you need to use ISqlWorkspace::GetQueryDescription(Query String). The code snippet below will help you better understand the workflow.

 
public class LoadSQLiteLayer : ESRI.ArcGIS.Desktop.AddIns.Button
    {
        private static readonly String Root = System.IO.Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
        private static readonly String Data = System.IO.Path.Combine(Root, "Data");
        private static readonly String Lite = System.IO.Path.Combine(Data, "test1.sqlite");

        private static readonly String[] Fields = { "OBJECTID", "Shape", "HNR", "ZOT_ID", "AEN_DAT" };
        private const String TableName = "main.locations";

        private static readonly String Sql = string.Format("SELECT {0} FROM {1}", string.Join(", ", Fields), TableName);

        protected override void OnClick()
        {
            try
            {
                Type factoryID = Type.GetTypeFromProgID("esriDataSourcesGDB.SqlWorkspaceFactory");
                IWorkspaceFactory2 pWorkspaceFactory = (IWorkspaceFactory2)Activator.CreateInstance(factoryID);
                ISqlWorkspace pSqlWorkspace = (ISqlWorkspace)pWorkspaceFactory.OpenFromFile(Lite, 0);

                IStringArray pStringArray = pSqlWorkspace.GetTables();
                List tableNames = new List();

                for (int i = 0; i < pStringArray.Count; i++)
                {
                    tableNames.Add(pStringArray.Element);
                }

                if (!tableNames.Contains(TableName)) throw new Exception("Table name not found");

                //using (ComReleaser pComReleaser = new ComReleaser())
                //{
                //    IFeatureCursor pFeatureCursor = (IFeatureCursor)pSqlWorkspace.OpenQueryCursor(Sql);
                //    pComReleaser.ManageLifetime(pComReleaser);

                //    IFeature pFeature = null;
                //    while ((pFeature = pFeatureCursor.NextFeature()) != null)
                //    {
                //        Debug.WriteLine("OID: " + pFeature.Value[0]);
                //    }
                //}
                
                IQueryDescription pQueryDesc = pSqlWorkspace.GetQueryDescription(Sql);
                pQueryDesc.OIDFields = Fields[0];

                string tableNameX;
                pSqlWorkspace.CheckDatasetName(TableName, pQueryDesc, out tableNameX);
                IFeatureClass pFeatureClass = (IFeatureClass)pSqlWorkspace.OpenQueryClass(tableNameX, pQueryDesc);

                IFeatureLayer pFeatureLayer = new FeatureLayerClass { FeatureClass = pFeatureClass, Name = TableName, Visible = true };
                ArcMap.Document.FocusMap.AddLayer(pFeatureLayer);
            }
            catch (Exception ex) { MessageBox.Show(ex.Message, "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error); }
        }

        protected override void OnUpdate() { }
    }
}

Please note that ISqlWorkspace::CheckDataSetName needs to be called before using OpenQueryClass method. If you need to know more details about ISQLWorkspace interface, you could visit the below link:

ISqlWorkspace Interface
http://resources.arcgis.com/en/help/arcobjects-net/componenthelp/index.html#/ISqlWorkspace_Interface/00250000085s000000/

I'm hoping this information will be useful for other developers.

Scott