mercredi 22 juillet 2015

SQLite (PCL) query in Windows XAML not allowing more than 21 columns?

I've used SQLite-PCL in my Universal Windows 8.1 RunTime app.
There's a big table with 23 columns in my project. The problem is - though table-creation & data-insertion codes run without any error, but in the full-column query (SELECT * FROM table) is not giving me more than 21 columns.

Here's my create-table method (it runs OK):

    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's my row-insertion SQL query (it runs 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 the rows or a specific row with all columns, the prepared statement can't go beyond index 20, hence I'm assuming it's not containing more than 21 columns inside it. Here's the code which doesn't run OK & gives NullReferenceException during access of 21th item:

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've more than 10 tables in the project with less than 17 columns & all of those are working fine.

Aucun commentaire:

Enregistrer un commentaire