- Home /
problem retrieving data from SQLite to Android
Hi everybody!!, this is my first post,
Im working on a University project: Augmented reality app for guidance inside Museum rooms,
Im using Vuforia SDK + Unity3D(3.5.5) indie + SQLite database
I used:[http://wiki.unity3d.com/index.php?title=SQLite][1]
I could make it work on Unity indie Android! I made a edited C# version of both dbAccess.js and ScriptThatUsesTheDatabase.js
I CAN retrieve data using ReadFullTable(string tableName) from dbAccess.cs through ScriptuseDB.cs but I CANT retrieve data using SingleSelectWhere( ) - dont know why(this is the problem).
when i touch the GUI.Button("Titulo") it is supposed to call dbAccess.SingleSelectWhere() BUT nothing shows up and all three buttons disappear and ScrollView...What can I do?, here's the code:
ScriptuseDB.cs:
using UnityEngine;
using System.Collections;
public class ScriptUsaDB : MonoBehaviour {
public string DatabaseName = "TestDB.sqdb";
public string TableName = "TestTable";
DBAccess db = new DBAccess();
void Start () {
db.OpenDB (DatabaseName);
}
// Update is called once per frame
void Update () {
}
//var databaseData : ArrayList = new ArrayList();
ArrayList databaseData = new ArrayList();
Vector2 scrollPosition;
int DatabaseEntryStringWidth = 100;
void OnGUI(){
GUI.Box(new Rect (25,25,Screen.width - 50, Screen.height - 50),"");
GUILayout.BeginArea(new Rect(50, 50, Screen.width - 100, Screen.height - 100));
GUILayout.BeginHorizontal();
if (GUILayout.Button ("Leer BD"))//here all OK, it shows all data from table
databaseData = ReadFullTable();
if (GUILayout.Button("Limpiar"))
databaseData.Clear();
if (GUILayout.Button("Titulo"))//when I touched this ALL GUI.Layout disappear!(the GUI.Box remains though)
databaseData = SingleSelect();
GUILayout.EndHorizontal();
GUILayout.Label("Contenidos de la tabla de prueba");
scrollPosition = GUILayout.BeginScrollView(scrollPosition, GUILayout.Height(400));
//foreach (string day in week)
foreach (ArrayList line in databaseData){
GUILayout.BeginHorizontal();
foreach(var s in line){
GUILayout.Label(s.ToString(), GUILayout.Width(DatabaseEntryStringWidth));
}
GUILayout.EndHorizontal();
}
GUILayout.EndScrollView();
GUILayout.EndArea();
}
ArrayList ReadFullTable(){
return db.ReadFullTable(TableName);
}
ArrayList SingleSelect(){
return db.SingleSelectWhere(TableName,"Titulo","Cultura","=","'Chavin'");//CAMBIO1: agregamos ' ' para el string luego del =
//ORDER BY ProductName DESC;
}
}
Here is dbAccess function:
public ArrayList SingleSelectWhere(string tableName, string itemToSelect, string wCol, string wPar, string wValue){ // Selects a single Item
string query;
query = "SELECT " + itemToSelect + " FROM " + tableName;// + " WHERE " + wCol + wPar + wValue;CAMBIO: limitamos el query para pruebas
dbcmd = dbcon.CreateCommand();
dbcmd.CommandText = query;
reader = dbcmd.ExecuteReader();
var readArray = new ArrayList();//CAMBIO2: Cambiamos Arraylist por var de acuerdo a ReadFullTable
while(reader.Read()){
readArray.Add(reader.GetString(0)); // Llenamos el arreglo con los datos //CAMBIO1:probamos cambiar reader.GetString(0) por .GetValue(0).
}
return readArray; // devuelve los datos
}
I tried to solve this but I cant, PLEASE any help? Thank You, [1]: http://wiki.unity3d.com/index.php?title=SQLite
Do you get any errors? The SQL statment seems fine other than you are not using half of it. Why is part of it commented out?
Hi, when I touch the "Titulo" Button, all GUILayout goes away(all three buttons, scroll), only the GUI.box stays there. I Cut the query to make things simplier so I could catch the error.... I tried changing reader.GetString(0) for reader.GetValue(), but no success , do you know where can I find a workable example on select query on c#"?
As you're not using coroutine, suggest you put codes in try-catch and debug.log its exception, this may help you find error easier. Especially for SingleSelectWhere(), try to make sure if it returns correctly, if so, then it's not reader's problem.
BTW, our DB helper usually return reader directly, so it's decoupled and can be debug/reuse easily.
What's more, if you're trying to build a better UI, suggest leave native GUI system alone and try NGUI ins$$anonymous$$d.
Thank for your comments zhuchun, Yeah, I was about to use try-catch but got the answers sooner. I cant use NGUI by now because its a college project, so its better if I do all I can on my own.
Answer by JamieSinn · Sep 16, 2013 at 09:32 PM
My github has pretty close to it, https://github.com/JamieSinn/MySQLDatabaseInserterConsole have fun there. Note. there are some issues with it, but again, it is just a temporary thing.
JamieSinn, thank you for sharing this, I'll take into account :)
Answer by alonsoGarrote · Sep 17, 2013 at 02:18 PM
FOUND THE ANSWER!! 1. I change the path to StandAlone MAC, so I could see the Console messages 2. I got the error "Cannot cast from source type to destination type." 3. Check that line - foreach (ArrayList line in databaseData){- 4. IF I use SingleSelectWhere function it doesnt return Arraylist(THATS THE ERROR) 5. Change ArrayList line for string line. 6. Now it works.
Your answer
Follow this Question
Related Questions
SqliteException: Unable to open the database file - Ready-made DB Provided To Access 0 Answers
Unity 4 Android Game cannot access SQLite DB (C# Script) 1 Answer
SQLite Transaction Update Problem on Android 0 Answers
Sqlite3.dll not found on specific Android devices 2 Answers
SqlLite , android and unity 1 Answer