Are there any methods that assist with resolving common SQLite issues?

Question

It is often that relatively simple errors cause issues often compounded by a misunderstanding of SQLite.

For example:-

• tables and columns not found because it is often assumed that the onCreate method of a DBHelper (subclass of the SQLiteOpenHelper class) runs every time an instance of the DBHelper is created or every time the App is run. (Note! onCreate is only automatically invoked when a Database is first created and then only when an attempt is made to use one of the SQLiteDatabase getReadableDatabase or getWriteableDatabse methods, if changing the database structure/schema then there are 3 simple ways of forcing onCreate to run, a) Clear the App's Data, b) uninstall the App or b) if the onUpgrade method invokes the onCreate method (after dropping the table(s)) then increase the Database Version Number).
• Inserts/Updates not working but not failing.

For the novice to SQLite it can also be infuriating/daunting not being able to readily see what the database contains.

So are there any common utilities that can assist?

Note! this is intended to be a question for sharing knowledge Q & A style.

Answer 1

The following are some common utilities that the novice might find helpful, they are designed to be non-specific and work on any database/table.

Currently there are the following potentially useful methods:-

• getAllRowsFromTable to retreive a Cursor with all rows.
• logCursorColumns to write the Columns in a Cursor to the log.
• logCursorData to write the Cursor Data and Columns to the log.

Note! Requests/suggestions for additions/amendments will definitely be considered.

Additionally there are some methods used by the above or were used for testing/creating the above :-

• getEmptyColumnLessCursor (100 points for a use of this method :) ).
• getBytedata to return a hex string of the given byte array (for BLOB handling).
• convertBytesToHex to convert a byte array into a hex string (for BLOB handling).

---

getAllRowsFromTable

The intended use is for creating a cursor to subsequently be examined by the logCursorColumns and logCursorData methods. This should not be used in a production App (not that it can't be).

This has the signature :-

public static Cursor getAllRowsFromTable(
                                  SQLiteDatabase db,
                                  String tablename,
                                  boolean use_error_checking,
                                  String forceRowidAs)

Where:-

• The first parameter is the SQLite Database (required due to the generic nature).
• The second parameter is the table name from which to get the data.
• The third parameter, if true will check if the table exists before creating the cursor writing to the log if the table is not in the database.
• The fourth parameter, if not null or if a string of length > 0, will create an additional column, named according to the parameter with the contents of the ROWID (intended for assisting when no alias for ROWID has been provided and hence ROWID is not included). What's ROWID??

Example usage:-

    mEventsDBHelper = new EventsDBHelper(this);


    // Get all rows from table (this exist or should do)
    Cursor events = CommonSQLiteUtilities.getAllRowsFromTable(
            mEventsDBHelper.getEventsDB(),
            EventsDBHelper.TBNAME,
            CommonSQLiteUtilities.ERROR_CHECKING_ON,
            null
    );

    // Deisgned to be problematic i.e. no such table        
    Cursor ooops = CommonSQLiteUtilities.getAllRowsFromTable(
            mEventsDBHelper.getEventsDB(),
            "NOTATABLE",
            CommonSQLiteUtilities.ERROR_CHECKING_ON,
            "rumplestiltskin"
    );

• The second invocation results in the following line in the log:-

  D/SQLITE_CSU: Table NOTATABLE was not located in the SQLite Database Master Table.
  

• A Cursor will always be returned although that Cursor may have no rows and no columns (e.g. in the case where the table was not in the database).

• Exceptions are trapped and reported on when the database table is queried (if it is). e.g specifying IS as the 4th parameter (effectively trying to use ROWID AS IS, which SQLIte doesn't like, will result in something similar to :-

---

10-09 18:57:52.591 3835-3835/? E/SQLiteLog: (1) near "IS": syntax error
10-09 18:57:52.592 3835-3835/? D/SQLITE_CSU: Exception encountered but trapped when querying table events Message was: 
                                             near "IS": syntax error (code 1): , while compiling: SELECT rowid AS  IS , * FROM events
10-09 18:57:52.592 3835-3835/? D/SQLITE_CSU: Stacktrace was:
10-09 18:57:52.592 3835-3835/? W/System.err: android.database.sqlite.SQLiteException: near "IS": syntax error (code 1): , while compiling: SELECT rowid AS  IS , * FROM events
10-09 18:57:52.592 3835-3835/? W/System.err:     at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
10-09 18:57:52.592 3835-3835/? W/System.err:     at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
10-09 18:57:52.592 3835-3835/? W/System.err:     at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
10-09 18:57:52.592 3835-3835/? W/System.err:     at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
10-09 18:57:52.592 3835-3835/? W/System.err:     at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
10-09 18:57:52.592 3835-3835/? W/System.err:     at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
10-09 18:57:52.592 3835-3835/? W/System.err:     at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
10-09 18:57:52.592 3835-3835/? W/System.err:     at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1316)
10-09 18:57:52.592 3835-3835/? W/System.err:     at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1163)
10-09 18:57:52.592 3835-3835/? W/System.err:     at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1034)
10-09 18:57:52.592 3835-3835/? W/System.err:     at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1202)
10-09 18:57:52.592 3835-3835/? W/System.err:     at mjt.sqlitedbexamples.CommonSQLiteUtilities.getAllRowsFromTable(CommonSQLiteUtilities.java:97)
10-09 18:57:52.592 3835-3835/? W/System.err:     at mjt.sqlitedbexamples.MainActivity.onCreate(MainActivity.java:61)
10-09 18:57:52.593 3835-3835/? W/System.err:     at android.app.Activity.performCreate(Activity.java:5990)
10-09 18:57:52.593 3835-3835/? W/System.err:     at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1106)
10-09 18:57:52.593 3835-3835/? W/System.err:     at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2278)
10-09 18:57:52.593 3835-3835/? W/System.err:     at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2387)
10-09 18:57:52.593 3835-3835/? W/System.err:     at android.app.ActivityThread.access$800(ActivityThread.java:151)
10-09 18:57:52.593 3835-3835/? W/System.err:     at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1303)
10-09 18:57:52.593 3835-3835/? W/System.err:     at android.os.Handler.dispatchMessage(Handler.java:102)
10-09 18:57:52.593 3835-3835/? W/System.err:     at android.os.Looper.loop(Looper.java:135)
10-09 18:57:52.593 3835-3835/? W/System.err:     at android.app.ActivityThread.main(ActivityThread.java:5254)
10-09 18:57:52.593 3835-3835/? W/System.err:     at java.lang.reflect.Method.invoke(Native Method)
10-09 18:57:52.593 3835-3835/? W/System.err:     at java.lang.reflect.Method.invoke(Method.java:372)
10-09 18:57:52.593 3835-3835/? W/System.err:     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:903)
10-09 18:57:52.593 3835-3835/? W/System.err:     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:698)

• The Cursor returned will be empty with no rows or columns.

---

logCursorColumns

Is intended to be used to write column information about a cursor (which would normally reflect the database, especially if using getAllRowsFromTable).

This has the signature :-

    public static void logCursorColumns(Cursor csr)

Where :-

• The first parameter is a Cursor (any Cursor).

Example usage :-

Note! Following on from the previous example.

    CommonSQLiteUtilities.logCursorColumns(events);
    CommonSQLiteUtilities.logCursorColumns(ooops);

This will result in output along the lines of :-

10-09 18:57:52.593 3835-3835/? D/SQLITE_CSU: logCursorColumns invoked. Cursor has the following 8 columns.
10-09 18:57:52.593 3835-3835/? D/SQLITE_CSU: Column Name 1 is _id
10-09 18:57:52.593 3835-3835/? D/SQLITE_CSU: Column Name 2 is starts
10-09 18:57:52.593 3835-3835/? D/SQLITE_CSU: Column Name 3 is starts_timestamp
10-09 18:57:52.593 3835-3835/? D/SQLITE_CSU: Column Name 4 is ends
10-09 18:57:52.593 3835-3835/? D/SQLITE_CSU: Column Name 5 is ends_timestamp
10-09 18:57:52.593 3835-3835/? D/SQLITE_CSU: Column Name 6 is description
10-09 18:57:52.593 3835-3835/? D/SQLITE_CSU: Column Name 7 is counter
10-09 18:57:52.593 3835-3835/? D/SQLITE_CSU: Column Name 8 is bytedata
10-09 18:57:52.593 3835-3835/? D/SQLITE_CSU: logCursorColumns invoked. Cursor has the following 0 columns.

---

logCursorData

This is very similar to logCursorColumns other than it provides more extensive information regrading columns and that it also provides information regrading the actual data stored in the database.

This has the signature :-

    public static void logCursorData(Cursor csr)

Where :-

• The first parameter is a Cursor (any Cursor).

Example usage :-

Note! Following on from the previous example.

    CommonSQLiteUtilities.logCursorData(events);
    CommonSQLiteUtilities.logCursorData(ooops);

This will result in output along the lines of :-

10-09 19:30:31.801 1455-1455/? D/SQLITE_CSU: logCursorData Cursor has 6 rows with 8 columns.
10-09 19:30:31.801 1455-1455/? D/SQLITE_CSU: Information for row 1 offset=0
                                                For Column _id Type is INTEGER value as String is 1 value as long is 1 value as double is 1.0
                                                For Column starts Type is INTEGER value as String is 1507537831 value as long is 1507537831 value as double is 1.507537831E9
                                                For Column starts_timestamp Type is INTEGER value as String is 1507537831783 value as long is 1507537831783 value as double is 1.507537831783E12
                                                For Column ends Type is INTEGER value as String is 0 value as long is 0 value as double is 0.0
                                                For Column ends_timestamp Type is INTEGER value as String is 0 value as long is 0 value as double is 0.0
                                                For Column description Type is STRING value as String is TESTEVENT 001 just description value as long is 0 value as double is 0.0
                                                For Column counter Type is INTEGER value as String is 0 value as long is 0 value as double is 0.0
                                                For Column bytedata Type is BLOB value as String is unobtainable! value as long is unobtainable! value as double is unobtainable! value as blob is 00000000
10-09 19:30:31.802 1455-1455/? D/SQLITE_CSU: Information for row 2 offset=1
                                                For Column _id Type is INTEGER value as String is 2 value as long is 2 value as double is 2.0
                                                For Column starts Type is INTEGER value as String is 1507537831 value as long is 1507537831 value as double is 1.507537831E9
                                                For Column starts_timesta