Search code examples
sqliteraspberry-piiotwindows-10-iot-core

How to Create Table in SQLite on Raspberry


I'm getting error {SQLite.Net.SQLiteException: disk I/O error, when trying to create a table in sqllite database on a raspberry pi 3, with WIN IOT is the OS. I'm using the SQLite.Net-PCL ver 3.1.1 implementation of SQLite. This is a UWP application. Here is the full error;

{SQLite.Net.SQLiteException: disk I/O error
   at SQLite.Net.Platform.WinRT.SQLiteApiWinRT.Prepare2(IDbHandle db, String query)
   at SQLite.Net.SQLiteCommand.Prepare()
   at SQLite.Net.SQLiteCommand.ExecuteNonQuery()
   at SQLite.Net.SQLiteConnection.Execute(String query, Object[] args)
   at SQLite.Net.SQLiteConnection.CreateTable(Type ty, CreateFlags createFlags)
   at SQLite.Net.SQLiteConnection.CreateTable[T](CreateFlags createFlags)
   at InternetRadio.SQLite_Raspberry.GetConnection()}

Second is there a datareader for this particular implementation of SQLite? I've used SQLite on Android before with no problem, but some commands I used there are not available here.

var documentsPath = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);    
var fileName2 = "Storage.db";
var path2 = Path.Combine(documentsPath, fileName2);     

try
    {
        using (var connection = new SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), path2))
        {
               md.CreateTable<RaspTables.Category>();
        }
    }
    catch (Exception ex)
    {
        string m_er = ex.ToString();
    }

my table implementation is;

    public class Category
    {
            [PrimaryKey, AutoIncrement]
            public int _id { get; set; }
            public int Cat_order { get; set; }
            public string Name { get; set; }
            public string Keywords { get; set; }
            public string Sub_Cat { get; set; }

        public Category()
        { }


    }

I see files, Storage.db and Storage.db-journal have been created. They both have read write rights on the disk. I read that I need to set tags SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.Create | SQLiteOpenFlags.FullMutex but don't know how to do that. I've tried,

md = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), path, SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.Create | SQLiteOpenFlags.FullMutex);

which gives an error. I also tried;

md = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), path, true);

which doesn't help. I still receive the disk I/O error.

Through the course of trying things I had change the using line to;

using (SQLite.Net.SQLiteConnection md = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), path)) ;

I'm not sure what the difference between SQLite.Net.SQLiteConnection and SQLiteConnection, but the SQLite.Net.SQLiteConnection closes the connection and SQLiteConnection leaves it open. So that was the last problem.

One more thing I put this in a class by itself so it would stay open and be usable at anytime in the rest of the code. This required taking it out of the using statement so the connection would stay open.

Thanks for the help.


Solution

  • I can reproduce your issue under "C:\Data\Users\DefaultAccount\Documents\" path. It seems related to UWP file access permission limitation.

    Change to UWP application path your code works.

            StorageFolder localFolder = ApplicationData.Current.LocalFolder;
            var fileName2 = "Storage.db";
            var path2 = Path.Combine(localFolder.Path, fileName2);
    
            try
            {
                using (var connection = new SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), path2))
                {
                    connection.CreateTable<RaspTables.Category>();
                }
            }
            catch (Exception ex)
            {
                string m_er = ex.ToString();
            }
    

    The path for my app is

    C:\Data\Users\DefaultAccount\AppData\Local\Packages\911b8f0e-3351-44ed-a3b3-4e15b0d12cb6_a48w6404kk2ea\LocalState\Storage.db

    More reference "Use a SQLite database in a UWP app"

    Update: add debug snapshot

    enter image description here