SQLite (PCL) in Windows XAML not allowing more than 21 columns?
I used SQLite-PCL in my Windows 8.1 Universal RunTime app.
There's a large table with 23 columns in my project. The problem is that although the creation and data entry code tables are executed without any errors, the full-query query (SELECT * FROM table)
does not specify more than 21 columns.
Here's my create-table method (it works fine):
private void CreateTable()
{
string CREATE_TABLE_SQL = @"CREATE TABLE IF NOT EXISTS "
+ TABLE_NAME
+ "( "
+ KEY_ID + " INTEGER PRIMARY KEY, " // 0
+ KEY_UPDATED_AT + " TEXT, " // 1
+ KEY_CREATED_AT + " TEXT, " // 2
// ... all other column names are stated here in the similar way
+ KEY_LAST_EDITED_BY + " INTEGER, " // 20
+ KEY_LAST_EDIT_TIME + " TEXT, " // 21
+ KEY_IS_LD + " INTEGER " // 22
+ ");";
using (var connection = new SQLiteConnection(DB_NAME))
{
using (var statement = connection.Prepare(CREATE_TABLE_SQL))
{
statement.Step();
}
}
}
Here is my SQL query for string input (it works OK too):
string insQuery =
@"INSERT INTO " + TABLE_NAME
+ " ( " + KEY_ID + ", " //1
+ KEY_UPDATED_AT + ", "//2
+ KEY_CREATED_AT + " , "//3
// all other col. names are stated here
+ KEY_LAST_EDITED_BY + ", "//21
+ KEY_LAST_EDIT_TIME + ", "//22
+ KEY_IS_LD + " "//23
+ " ) "
+ " VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";
But if I query all rows, or a specific row with all columns, the prepared statement cannot go beyond index 20, so I assume it does not contain more than 21 columns inside it. Here's the code that doesn't work fine and gives a NullReferenceException when accessing the 21st element:
public List<MyModel> GetAll()
{
List<MyModel> objList = new List<MyModel>();
string query = @"SELECT * FROM " + TABLE_NAME
+ " ORDER BY " + KEY_ID + " DESC;";
using (var connection = new SQLiteConnection(DB_NAME))
{
using (var statement = connection.Prepare(query))
{
// The next line prints this: "Statement data-count=0, ColumnCount=23"
ALog.d("Statement data-count=" + statement.DataCount +
", ColumnCount=" + statement.ColumnCount);
while (statement.Step() == SQLiteResult.ROW)
{
try
{
int id = Int32.Parse(statement[0].ToString());
string updatedAt = statement[1].ToString();
string createdAt = statement[2].ToString();
// ... all other values are extracted here, then I'm building
// my user object & the next line prints my expected values
ALog.d("User: " + user.ToString());
// ... the remaining columns are got here nicely
string imgUrl = statement[19].ToString();
int lastEdt = Int32.Parse(statement[20].ToString());
// This line is the culprit giving out NullReferenceException >_<
string lastEdtTm = statement[21].ToString();
bool isLd = Int32.Parse(statement[22].ToString()) > 0;
objList.Add(new MyModel(
// Params. of the constructor goes here ...
));
}
catch (Exception e)
{
ALog.d("Db - GetAll() : Exception:: " + e.ToString());
}
}
// This line prints as: "Statement data-count=23, ColumnCount=23"
// That means - all my data & columns have been queried,
// but I couldn't read values from statement[21] & statement[22]
ALog.d("Statement data-count=" + statement.DataCount +
", ColumnCount=" + statement.ColumnCount);
statement.Reset();
statement.ClearBindings();
}
}
return objList;
}
Please note that I have more than 10 tables in a project with less than 17 columns and they all work fine.
source to share
The problem is with lines like this:
string lastEdtTm = statement[21].ToString();
Here, if it statement[21]
returns a value null
, then it is equivalent null.ToString()
, which will throw an exception.
Simple fix:
string lastEdtTm = statement[21] == null ? "" : statement[21].ToString();
This will return an empty string if statement[21]
allowed null
, otherwise it`s a string value.
Note that you must do this for all columns that could potentially return null - just because you are not getting this exception from elsewhere right now does not mean that you cannot get it later when new rows are added. that other values may be missing.
source to share