SqlDataReader's Read() function returns empty row, same query returns value from database browser

Question

I have a table in SQLite with the following structure:

+-------+------+------+
| id    |name  |value |
|int    |text  |int   |
+-------+------+------+

I am attempting to follow this guide on using sqlite databases in Unity. I am able to query the sqlite_master table to retrieve the only database table name using the following code:

    _dbConnection = (IDbConnection)new SqliteConnection(_dbURI);
    _dbConnection.Open();
    string sqlStatement = "SELECT name FROM sqlite_master WHERE type='table' AND name='" + tableName + "';";
    _dbCommand = _dbConnection.CreateCommand();
    _dbCommand.CommandText = sqlStatement;
    _dbReader = _dbCommand.ExecuteReader();
    while (_dbReader.Read())
    {
        Debug.Log("Table: " + _dbReader.GetString(0));
    }

However, when I attempt to query from the table itself to get the maximum ID using similar code my DataReader returns a null row:

    _dbConnection = (IDbConnection)new SqliteConnection(_dbURI);
    _dbConnection.Open();
    string sqlStatement = "SELECT max(id) FROM " + tableName + ";";
    _dbCommand = _dbConnection.CreateCommand();
    _dbCommand.CommandText = sqlStatement;
    _dbReader = _dbCommand.ExecuteReader();
    while (_dbReader.Read())
    {
        Debug.Log("MaxId = " + _dbReader.GetInt32(0));
    }

The null causes a conversion error with the call to _dbReader.GetInt32(0). When I write the sql statement to the log and paste it into my database browser it yields a number, so the query should be returning a value.

I'm not clear on why similar code works when querying sqlite_master but not my other table.

I've also tried ExecuteScalar with no success.

Thanks!

EDIT:

• tableName is equal to "unit_def"
• error message is

InvalidCastException: Cannot cast from source type to destination type. Mono.Data.Sqlite.SqliteDataReader.VerifyType (Int32 i, DbType typ)

• using count(*) yields a 0, so it seems that unity can't see the data in the table?

Also, oddly, changing the code messed up the GUI elements in my editor for some reason.

Answer 1

I solved the issue, hopefully this answer is helpful to others:

The issue was tricky to troubleshoot since

• the correct database was being referenced
• all code was correct
• the sql query did return a result when run in my database browser

The problem is that I am using the DB Browser for Sqlite, which I am new to. I had written rows of data to the database so that my query would return results within the browser, but apparently these changes are not written to the database file for other applications to see until the "Write Changes" button is pushed.

Hopefully this helps anyone else who might be new to the db browser and stumbles into this error!