Unity sqlite android database query issue ("Table not found")

Question

Im trying to implement a local database (sqlite) in an Android/PC game

I am able to load the database from the editor and the standalone pc build of the games but the android version has an error that does not find the tables im trying to query from

This is the code that loads the database into three lists from the streaming assets folders

using System;
using System.IO;
using System.Data;
using System.Collections;
using System.Collections.Generic;

using Mono.Data.Sqlite;

using UnityEngine;
using UnityEngine.SceneManagement;


public class DBAdministrator : MonoBehaviour {

    //
    //Android sdk > platform-tools > adb logcat MarsGames.ChippyRemix *:E

    public List<LevelDP> levelsList;

    public List<LevelDP> defaultLevels;
    public List<LevelDP> myLevels;
    public List<LevelDP> otherLevels;

    GameObject playMenu;
    public string myName;
    public string defaultName = "chippy";

    public string debugString;

    void Start () {
        //myName = PlayerPrefs.GetString("MyName");
        playMenu = GameObject.FindWithTag("UI");

        //levelsList = new List<LevelDP>();
        defaultLevels   = new List<LevelDP>();
        myLevels        = new List<LevelDP>();
        otherLevels     = new List<LevelDP>();        
        //Fill the list

        FillLists();
        //PrintAllLevels();
        if(playMenu !=  null){
            playMenu.SendMessage("CreateDisplayLists");
        }
    }


    void FillLists(){
        //Path to database

        string conn = "";

        //Debug.Log(Application.streamingAssetsPath);

        #if UNITY_EDITOR_WIN

            //Debug.Log("Using unity editor conn");
            conn = "URI=file:" + Application.dataPath + "/StreamingAssets/chickdb.db";
        #elif UNITY_ANDROID

            debugString = "Using andriod";
            conn = Application.persistentDataPath + "/chickdb.db";
            if(!File.Exists(conn)){
                debugString = "DB file does not exist";
                //Open Streaming assets and load the db
                WWW loadDb = new WWW("jar:file://" + Application.dataPath + "!/assets/chickdb.db");
                while (!loadDb.isDone) { }
                File.WriteAllBytes(conn,loadDb.bytes);
            }else{
                debugString = "File exists";
                //debugString = conn;
            }
            //Once file is loaded, use the appropiate filepath to access db
            conn = "URI=file:" + Application.persistentDataPath + "/chickdb.db";
        #elif UNITY_STANDALONE
            //debugString = "Using standalone PC";
            //string conn = "URI=file:" + System.IO.Path.Combine(Application.streamingAssetsPath, "Database/TMDB.s3db");
            //conn = "URI=File:" + System.IO.Path.Combine(Application.streamingAssetsPath,"/StreamingAssets/chickdb.db");
            conn = "URI=file:" + Application.streamingAssetsPath + "/chickdb.db";
            debugString = conn;
        /*
        #elif UNITY_IOS
            debugString = "Using ios";
            // this is the path to your StreamingAssets in iOS
            var loadDb = Application.dataPath + "/Raw/chickdb.do";
            // then save to Application.persistentDataPath
            File.Copy(loadDb, filepath);
            //conn = "URI=file " + Application.persistentDataPath + "/chickdb.db"; 
        */
        #endif

        //Debug.Log("ST Connection DB: " + conn);

        #if UNITY_ANDROID
            debugString = "Connection Attempted";
        #endif

        IDbConnection dbconn = (IDbConnection) new SqliteConnection(conn);

        //Open connection to the database.
        dbconn.Open(); 

        #if UNITY_ANDROID
            debugString = "Database Open";
        #endif

        IDbCommand dbcmd = dbconn.CreateCommand();

        string sqlQuery = "SELECT * FROM levels";

        dbcmd.CommandText = sqlQuery;

        IDataReader reader = dbcmd.ExecuteReader();

        #if UNITY_ANDROID
            debugString = "Query executed";
        #endif
        while (reader.Read()){

            LevelDP lp = new LevelDP();

            lp.SetLevelID(reader.GetInt32(0));
            lp.SetLevelName(reader.GetString(1));
            lp.SetCreationDate(reader.GetString(2));
            lp.SetUserID(reader.GetString(3));
            lp.SetBlocks(reader.GetString(4));
            lp.SetCompleted(reader.GetInt32(5));

            //levelsList.Add(lp);

            if(lp.GetUserID().Equals(defaultName)){
                defaultLevels.Add(lp);
            }else if(lp.GetUserID().Equals(myName)){
                myLevels.Add(lp);
            }else{
                otherLevels.Add(lp);
            }
        }

        //Close the db reader
        reader.Close();
        reader = null;
        //Close the comand executer
        dbcmd.Dispose();
        dbcmd = null;
        //Close the db connection
        dbconn.Close();
        dbconn = null;
    }

    void PrintAllLevels(){
        Debug.Log("Print all levels");
        for(int i = 0; i < defaultLevels.Count; i++){

            Debug.Log(defaultLevels[i].ToString());
        }
        for(int i = 0; i < myLevels.Count; i++){

            Debug.Log(myLevels[i].ToString());
        }
        for(int i = 0; i < otherLevels.Count; i++){

            Debug.Log(otherLevels[i].ToString());
        }
    }
 }

From this code i've been able to print ("Database Open") but when the database is supposed to query i get an error (from the adb tool) that states ("Table levels not found") and i dont know where the error is

I do have to add the editor, and pc build work just fine,

Steps i've followed are:

Place the .db file in the Streaming Assets folder Unpack the .apk and check the values present in the .db file via sqlitebrowser

i have a theory that maybe im not loading the db correctly into memory in the #if UNITY_ANDROID conditional statement but almost every other example ive seen works this way

On which android version did you test this? I've heard about older versions of Android producing problems with sqlite databases whereas newer ones (5+) worked just fine. — yuri206
– yuri206, Commented Apr 5, 2018 at 8:06

M_Armendariz · Accepted Answer · 2018-04-17 16:13:10Z

1

I can't comment, that's why Im writing an answer.

Try to use Debug.Log to see the pathfile when you are testing the game/app. And then checking if your sq3db file is in the correct folder.

I Have a similar code for streaming the sqlite DB but if I test it on PC the DB should be in Assets folder not in StreamingAssets folder.

Edit 1:

First: I'll show you a fragment of the code I use when I make games:

if(Application.platform != RuntimePlatform.Android)
    _filepath =  Application.dataPath + "/" + _DBName;    
else
{
    _filepath = Application.persistentDataPath + "/" + _DBName;
    if (!File.Exists(_filepath))
    {
        //Debug.Log("Check in");
        WWW loadDB = new WWW("jar:file://" + Application.dataPath + 
            "!/assets/" + _DBName);

        while (!loadDB.isDone) { }
        File.WriteAllBytes(_filepath, loadDB.bytes);
    }
}
    _StringConnection = "URI=file:" + _filepath;

Second: When I test the game on pc (unity editor) I have my Database (.s3db) here: ProjectName/Assets/Database.s3db

When I run the game on android I have my Database here: ProjectName/Assets/StreamingAssets/Database.s3db

Something you may notice is that my Database file extension is s3db, I use SQlite3, but it should work anyway.

edited Apr 17, 2018 at 16:13

answered Apr 6, 2018 at 18:05

M_Armendariz

1509 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

D. Shinoda Over a year ago

I've been using the "debugString" to test on the device, i've been able to reach the Database open statement but the command is not executed, ive been using adb logcat to debug as well and i get the error "no such table: levels" however the table exists and in any other platform ive tried it works just fine (Pc, editor and standalone builds)

M_Armendariz Over a year ago

I edited my answer but maybe when you use this: conn = "URI=file:" + Application.persistentDataPath + "/chickdb.db"; it generates another GUID, try using: conn = "URI=file:" + conn;

D. Shinoda Over a year ago

I have a new and weird development First off i've looked into other sources as well and it seems the routes are correct now, however im still unable to move past the "table not found" error, I've downloaded the "sqlite examples" package from here [forum.unity.com/threads/unity-3d-android-sqlite-examples.114660/… and run it to success, however implementing the same scripts and plugins does not work on my original project, ive also used my project's database in the examples project to a succesful build and access

D. Shinoda Over a year ago

Since now the culprit seems to be the project im going to see if a configuration or anything else is the root of the problem and post it here as reference

ali afshari · Accepted Answer · 2018-04-05 09:06:15Z

0

In Android sqlite db should save in persistent data path. I put my sqlite db in resources with .txt extension and read it as text. then chagne the extension in code and save it in Application.persistentDataPath.

answered Apr 5, 2018 at 9:06

ali afshari

621 silver badge8 bronze badges

Collectives™ on Stack Overflow

Unity sqlite android database query issue ("Table not found")

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related