Exceptions/SQLite error
SQLite errors are fairly common exceptions that can appear in the log files of Resco mobile apps.
About this exception
Resco mobile apps use the SQLite database engine for storing CRM data. When you encounter this exception, it means that the app has some problem interacting with its database saved on the device.
Handling
- SQLite errors often include an SQL SELECT statement that caused the error. Knowledge of Structured Query Language (SQL) can help you analyze the problem. Unfortunately, the SQL statements in the log can be abbreviated.
- Knowledge of the user actions can help you identify where the problem occurs. For example, if users encounter a problem while displaying a list of accounts, check the definition of account views in Woodford.
- Usually, a more detailed error message is included in the log. If you don't understand this message, try to find similar problems and solutions online.
Example: ambiguous column name
Application 11.3.1.0 ERROR 2019-01-08 17:33:21.143-05:00
SqliteException: SQLite error
ambiguous column name: statuscode
Failed cmd: SELECT DISTINCT [task].[activityid] ,[task].[ownerid] ,[task].[owneridTarget] ,[task].[regardingobjectid] ,[task].[regardingobjectidTarget] ,[task].[scheduledend] ,[task].[scheduledstart] ,[task].[statuscode] ,[task].[statuscode] ,[task].[subject] FROM [...
at SQLite3.Prepare(Data.Sqlite3.SqliteConnection cnn, strSql, UInt32 timeoutMS, String& strRemain)
at SqliteCommand.BuildNextCommand()
at SqliteCommand.GetStatement(Int32 index)
at SqliteDataReader.NextResult()
at SqliteDataReader..ctor(Data.Sqlite3.SqliteCommand cmd, Data.behave)
at SqliteCommand.ExecuteReader(Data.behavior)
at Database.ExecuteReader(query, Data.behavior, Collections.Generic.IEnumerable<System.Object> parameters)
at MobileCrm.Data.Offline.DatabaseService.ExecuteDynamicEntitiesFetch(Data.FetchXml.Fetch f, Data.WebService.FetchRequestParams p)
at MobileCrm.Data.Online.LoadRequest.Execute(Data.WebService.ICrmService service)
at MobileCrm.Data.Online.OnlineRepository.RequestThread()
Context: A database error while reading some list of records.
Error analysis: If you google for "sqlite ambiguous column name", you may find explanations such as "you are referencing an attribute that belongs to more than one of the tables you are using in the query". We see only abbreviated SQL commands, hence, it is not possible to verify the above statement. (The FROM clause was stripped off.) Despite that, we can say:
- The problem concerns the task list.
- Notice that the column [task].[statuscode] is repeated twice in the query. Even if you don't understand SQL, this must sound strange.
The error is probably in the definition of the Task view. Check the fetches used there. The fetches are translated to SQL when reading local data. And one of them is incorrect. Check task view definitions in Woodford.
Note: Did you notice that the call stack starts in the OnlineRepository? That must sound confusing as it reminds of reading from the server in the online mode. Well, this is just a coincidence. Both reading from the server and reading from the local database used in the past identical source code. Newer MCRM releases (v13+) started to differentiate between OnlineRepository (server communication) and OfflineRepository (working with the database).
Example: no such column
SqliteException: SQLite error
no such column: cnm_certificate.crmp_result
Failed cmd: SELECT [cnm_certificate].[cnm_certificateid] ,[cnm_certificate].[cnm_name] ,[cnm_certificate].[cnm_validfrom] ,[cnm_certificate].[cnm_validuntil] ,[cnm_certificate].[crmp_result] ,[cnm_certificate].[crmp_scopeid] ,[cnm_certificate].[crmp_scopeidTarget] ,[c...
at SQLite3.Prepare(Data.Sqlite3.SqliteConnection cnn, String strSql, UInt32 timeoutMS, String& strRemain)
at SqliteCommand.BuildNextCommand()
at SqliteCommand.GetStatement(Int32 index)
at SqliteCommand.ExecuteReader(Data.CommandBehavior behavior)
at MobileCrm.Data.Offline.DatabaseService.ExecuteDynamicEntitiesFetch(Data.FetchXml.Fetch f, Data.WebService.FetchRequestParams p)
at MobileCrm.Data.Online.LoadRequest.Execute(Data.WebService.ICrmService service)
at MobileCrm.Data.Online.OfflineRepository.OfflineRequestThread()
Context: A database error while reading some list.
Error analysis:
- The error happened when reading the list of cnm_certificate records. (cnm_certificate is a custom entity.)
- The error is "no such column: cnm_certificate.crmp_result", i.e., the fetch uses non-existing entity attribute crmp_result.
Check cnm_certificate view definitions in Woodford, or check if the field crmp_result is enabled in your app project.