如何在Raspberry上的SQLite中创建表 [英] How to Create Table in SQLite on Raspberry

问题描述

我收到错误{SQLite.Net.SQLiteException:磁盘I/O错误，尝试在树莓派3上的sqllite数据库中创建表时，操作系统为WIN IOT.我正在使用SQLite的SQLite.Net-PCL 3.1.1版实现.这是一个UWP应用程序.这是完整的错误；

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()}

第二个针对此特定SQLite实现的数据读取器吗?之前我在Android上使用过SQLite都没有问题，但是这里使用的某些命令在这里不可用.

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();
    }

我的表实现是；

    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()
        { }


    }

我看到已经创建了文件Storage.db和Storage.db-journal.它们都具有磁盘上的读写权限.我读到我需要设置标签SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.Create | SQLiteOpenFlags.FullMutex，但不知道该怎么做.我已经尝试过了，

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);

这无济于事.我仍然收到磁盘I/O错误.

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)) ;

我不确定SQLite.Net.SQLiteConnection和SQLiteConnection之间有什么区别，但是SQLite.Net.SQLiteConnection会关闭连接，而SQLiteConnection会使它保持打开状态.那是最后一个问题.

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.

还有一件事情我自己将它放在一个类中，这样它就可以保持打开状态，并在其余的代码中随时可用.这需要将它从using语句中删除，以便连接保持打开状态.

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.

感谢您的帮助.

推荐答案

我可以在"C:\ Data \ Users \ DefaultAccount \ Documents \"路径下重现您的问题.似乎与 UWP文件访问权限有关局限性.

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

将您的代码更改为UWP应用程序路径.

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();
        }

我的应用程序的路径为

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

更多参考资料"在UWP应用"

更新:添加调试快照

这篇关于如何在Raspberry上的SQLite中创建表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！