Problem with sqlite android [Table not found]
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
Answer by SilverSc0ut · Jan 08, 2019 at 06:33 PM
For anyone wondering i've solved the issue, Turns it was the correct combination of libraries for it to work