Alex Jolig Alex Jolig - 3 months ago 29
Android Question

Using populated SQLite database in android via Unity

I'm going to use a pre-populated SQLite database in an Android game which I'm creating using

Unity
.

Since the simple way doesn't work on Android (It works perfect on Windows thou), I've followed this tutorial to use my database in an Android app.

public void OpenDB(string p) //p is the database name

{
// check if file exists in Application.persistentDataPath

string filepath = Application.persistentDataPath + "/" + p;

if(!File.Exists(filepath))

{

// if it doesn't ->

// open StreamingAssets directory and load the db ->

WWW loadDB = new WWW("jar:file://" + Application.dataPath + "!/assets/" + p); // this is the path to your StreamingAssets in android

while(!loadDB.isDone) {} // CAREFUL here, for safety reasons you shouldn't let this while loop unattended, place a timer and error check

// then save to Application.persistentDataPath

File.WriteAllBytes(filepath, loadDB.bytes);

}

//open db connection

connection = "URI=file:" + filepath;

dbcon = new SqliteConnection(connection);

dbcon.Open();

}


When I run this code, I get following error:


SqliteSyntaxException: file is encrypted or is not a database


Here's the full error:


SqliteSyntaxException: file is encrypted or is not a database
Mono.Data.SqliteClient.SqliteCommand.GetNextStatement (IntPtr pzStart,
System.IntPtr& pzTail, System.IntPtr& pStmt)
Mono.Data.SqliteClient.SqliteCommand.ExecuteReader (CommandBehavior
behavior, Boolean want_results, System.Int32& rows_affected)
Mono.Data.SqliteClient.SqliteCommand.ExecuteReader (CommandBehavior
behavior) Mono.Data.SqliteClient.SqliteCommand.ExecuteDbDataReader
(CommandBehavior behavior) System.Data.Common.DbCommand.ExecuteReader
() System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader
() dbAccess.SingleSelectWhere (System.String tableName, System.String
itemToSelect, System.String wCol, System.String wPar, System.String
wValue) (at Assets/dbAccess.cs:152)


I've downloaded the example from that post, and got the same error.

I'm suspicious about this line:

File.WriteAllBytes(filepath, loadDB.bytes);


and my thought is for some reason, it can't write the database data into the file.

Does anyone know how to solve this?

Answer

I solved the problem thanks to this blog.

This code works perfect on every platform due to the if statements which will do different behaviors depending on which platform the app is running on.

Here's the DataService.cs which do the important parts (or maybe I better say the whole part)

using SQLite4Unity3d;
using UnityEngine;
#if !UNITY_EDITOR
using System.Collections;
using System.IO;
#endif
using System.Collections.Generic;

public class DataService  {

    private SQLiteConnection _connection;

    public DataService(string DatabaseName){

#if UNITY_EDITOR
            var dbPath = string.Format(@"Assets/StreamingAssets/{0}", DatabaseName);
#else
        // check if file exists in Application.persistentDataPath
        var filepath = string.Format("{0}/{1}", Application.persistentDataPath, DatabaseName);

        if (!File.Exists(filepath))
        {
            Debug.Log("Database not in Persistent path");
            // if it doesn't ->
            // open StreamingAssets directory and load the db ->

#if UNITY_ANDROID 
            var loadDb = new WWW("jar:file://" + Application.dataPath + "!/assets/" + DatabaseName);  // this is the path to your StreamingAssets in android
            while (!loadDb.isDone) { }  // CAREFUL here, for safety reasons you shouldn't let this while loop unattended, place a timer and error check
            // then save to Application.persistentDataPath
            File.WriteAllBytes(filepath, loadDb.bytes);
#elif UNITY_IOS
                 var loadDb = Application.dataPath + "/Raw/" + DatabaseName;  // this is the path to your StreamingAssets in iOS
                // then save to Application.persistentDataPath
                File.Copy(loadDb, filepath);
#elif UNITY_WP8
                var loadDb = Application.dataPath + "/StreamingAssets/" + DatabaseName;  // this is the path to your StreamingAssets in iOS
                // then save to Application.persistentDataPath
                File.Copy(loadDb, filepath);

#elif UNITY_WINRT
        var loadDb = Application.dataPath + "/StreamingAssets/" + DatabaseName;  // this is the path to your StreamingAssets in iOS
        // then save to Application.persistentDataPath
        File.Copy(loadDb, filepath);
#else
    var loadDb = Application.dataPath + "/StreamingAssets/" + DatabaseName;  // this is the path to your StreamingAssets in iOS
    // then save to Application.persistentDataPath
    File.Copy(loadDb, filepath);

#endif

            Debug.Log("Database written");
        }

        var dbPath = filepath;
#endif
            _connection = new SQLiteConnection(dbPath, SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.Create);
        Debug.Log("Final PATH: " + dbPath);     

    }

    public void CreateDB(){
        _connection.DropTable<Person> ();
        _connection.CreateTable<Person> ();

        _connection.InsertAll (new[]{
            new Person{
                Id = 1,
                Name = "Tom",
                Surname = "Perez",
                Age = 56
            },
            new Person{
                Id = 2,
                Name = "Fred",
                Surname = "Arthurson",
                Age = 16
            },
            new Person{
                Id = 3,
                Name = "John",
                Surname = "Doe",
                Age = 25
            },
            new Person{
                Id = 4,
                Name = "Roberto",
                Surname = "Huertas",
                Age = 37
            }
        });
    }

    public IEnumerable<Person> GetPersons(){
        return _connection.Table<Person>();
    }

    public IEnumerable<Person> GetPersonsNamedRoberto(){
        return _connection.Table<Person>().Where(x => x.Name == "Roberto");
    }

    public Person GetJohnny(){
        return _connection.Table<Person>().Where(x => x.Name == "Johnny").FirstOrDefault();
    }

    public Person CreatePerson(){
        var p = new Person{
                Name = "Johnny",
                Surname = "Mnemonic",
                Age = 21
        };
        _connection.Insert (p);
        return p;
    }
}

It follows by two other script to create or use the existing database.

ExistingDBScript.cs

using UnityEngine;
using System.Collections.Generic;
using UnityEngine.UI;

public class ExistingDBScript : MonoBehaviour {

    public Text DebugText;

    // Use this for initialization
    void Start () {
        var ds = new DataService ("existing.db");
        //ds.CreateDB ();
        var people = ds.GetPersons ();
        ToConsole (people);

        people = ds.GetPersonsNamedRoberto ();
        ToConsole("Searching for Roberto ...");
        ToConsole (people);

        ds.CreatePerson ();
        ToConsole("New person has been created");
        var p = ds.GetJohnny ();
        ToConsole(p.ToString());

    }

    private void ToConsole(IEnumerable<Person> people){
        foreach (var person in people) {
            ToConsole(person.ToString());
        }
    }

    private void ToConsole(string msg){
        DebugText.text += System.Environment.NewLine + msg;
        Debug.Log (msg);
    }

}

CreateDBScript.cs

using UnityEngine;
using System.Collections.Generic;
using UnityEngine.UI;

public class CreateDBScript : MonoBehaviour {

    public Text DebugText;

    // Use this for initialization
    void Start () {
        StartSync();
    }

    private void StartSync()
    {
        var ds = new DataService("tempDatabase.db");
        ds.CreateDB();

        var people = ds.GetPersons ();
        ToConsole (people);
        people = ds.GetPersonsNamedRoberto ();
        ToConsole("Searching for Roberto ...");
        ToConsole (people); 
    }

    private void ToConsole(IEnumerable<Person> people){
        foreach (var person in people) {
            ToConsole(person.ToString());
        }
    }

    private void ToConsole(string msg){
        DebugText.text += System.Environment.NewLine + msg;
        Debug.Log (msg);
    }
}

And the person script, demonstrates the person table in the database

using SQLite4Unity3d;

public class Person  {

    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }
    public string Name { get; set; }
    public string Surname { get; set; }
    public int Age { get; set; }

    public override string ToString ()
    {
        return string.Format ("[Person: Id={0}, Name={1},  Surname={2}, Age={3}]", Id, Name, Surname, Age);
    }
}

Also you need to add plugins and Sqlite.cs to your project which you can find in the git repository

It helped me to overcome the issue, Hope it help the others as well.