koobas.hobune.stream
SQLite: no such table
I am trying to integrate SQLite into unity. I can connect to the database, test code shows that I have an open connection to the right database. However when I try to look up data from a table I get the following error. I do have a 'test' Table and can query it from another program successfully. Any ideas on what I am doing wrong?
Please no comments about why I should use PlayerPrefs, XML, etc.... I choose SQLite because it meets my needs.
SqliteSyntaxException: no such table: test
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 () ItemDB.Awake () (at Assets/Scripts/Item/ItemDB.cs:20)
Here is the code:
using UnityEngine;
using System.Collections;
using System.Data;
using Mono.Data.SqliteClient;
public class ItemDB : MonoBehaviour {
private string _constr="URI=file:Item.db";
private IDbConnection _dbc;
private IDbCommand _dbcm;
private IDataReader _dbr;
void Awake()
{
_dbc = new SqliteConnection(_constr);
_dbc.Open();
_dbcm = _dbc.CreateCommand();
_dbcm.CommandText="SELECT number,name FROM test";
_dbr = _dbcm.ExecuteReader();
}
// Use this for initialization
void Start () {
}
// Update is called once per frame
void Update () {
if(Input.GetKeyUp(KeyCode.F))
{
Debug.Log(_dbcm.CommandText);
}
}
}
Do you have the specify schema owner before tablename, such as 'select 1,2 from myschema.test'?
I am really new to this, I don't have a schema set, but I can set one up and try again. I'll post the results.
Update: Not sure if this is right but I updated the code on line 17 to:
cmd = new SqliteCommand("SELECT * FRO$$anonymous$$ sqlite_master.item", con);
Same error, SqliteSyntaxException: no such table: sqlite_master.item
I think I don't have my database set-up right. I move the file location and it still tells me it makes an open connection. Ergo I am not actually connecting to my db ...
I'm thinking of adding SQLite to my project but have not done so yet, I did collect a few hyperlinks maybe one of these will provide an answer:
http://forum.unity3d.com/threads/28500-SQLite-Class-Easier-Database-Stuff/page2
http://forum.unity3d.com/threads/7866-Unity-and-Sqlite/page3
ok... so... I jsut fixed this same error in my project... the problem was in the filepath i was passing as parameter... use this:
(!C#!)
public void OpenDB ()
{
Debug.Log("Call to open BD:" + DbName);
// check if file exists in Application.DataPath
string filepath = Application.dataPath+ "/StreamingAssets/" + DbName;
if(!File.Exists(filepath))
{
Debug.LogWarning("Arquive \"" + filepath + "\" doenst exist. tring to create from \"" + filepath);
// if it doesn't ->
// open StreamingAssets directory and load the db ->
WWW loadDB = new WWW("jar:file://" + filepath);
while(!loadDB.isDone) {}
// then save to Application.persistentDataPath
File.WriteAllBytes(filepath, loadDB.bytes);
}
//open db connection
connection = "URI=file:" + filepath;
Debug.Log("Stabilishing connection to: " + connection);
dbConnection = new SqliteConnection(connection);
dbConnection.Open();
}
the secret is in the use of application.datapath, which usually returns ""C:/Users/username/projectname/Assets/StreamingAssets/database.db.
I hope this helps... it helped me...
hello how are you ? i have a small problem ... i have written this code and it works really good on windows , but not on android on android the last thing that the debugging text gives me is step 6 so that is where it is stopping my guess is that maybe it is going through a infinite while loop how can i solve this issues?
using UnityEngine; using System; using System.IO; using System.Collections; using System.Data; using System.Text; using $$anonymous$$ono.Data.SqliteClient; using UnityEngine.UI;
public class dbAccess : $$anonymous$$onoBehaviour { private string connection; private IDbConnection dbcon; private IDbConnection dbConnection; private IDbCommand dbcmd; private IDataReader reader; private StringBuilder builder; public Text debuggingthis; // Use this for initialization void Start () {
}
public void OpenDBtwo()
{
debuggingthis.text = "step1";
Debug.Log("Call to open BD: GameDatabase.db" );
debuggingthis.text = "step2";
// check if file exists in Application.DataPath
string filepath = Application.dataPath + "/Strea$$anonymous$$gAssets/GameDatabase.db";
debuggingthis.text = "step3";
if (!File.Exists(filepath))
{
debuggingthis.text = "step14";
Debug.LogWarning("Arquive \"" + filepath + "\" doenst exist. tring to create from \"" + filepath);
// if it doesn't ->
// open Strea$$anonymous$$gAssets directory and load the db ->
debuggingthis.text = "step5";
WWW loadDB = new WWW("jar:file://" + filepath);
debuggingthis.text = "step6";
/*------reached this point on mobile-------*/
while (!loadDB.isDone) { }
// then save to Application.persistentDataPath
File.WriteAllBytes(filepath, loadDB.bytes);
debuggingthis.text = "step7";
}
debuggingthis.text = "step8";
//open db connection
connection = "URI=file:" + filepath;
debuggingthis.text = "step9";
Debug.Log("Stabilishing connection to: " + connection);
debuggingthis.text = "step10";
dbConnection = new SqliteConnection(connection);
debuggingthis.text = "step11";
dbConnection.Open();
debuggingthis.text = "step12";
}
Do not hijack other questions. If you have a question you should post it as question.
To understand your problem you should carefully read the docs on Strea$$anonymous$$gAssets, especially the special section about android. The strea$$anonymous$$g assets are readonly on android and can only be accessed with the WWW class.
The usual solution is to copy the file from the strea$$anonymous$$g assets location to an actual file location in the persistant data path on first launch.
Dear Bunny83 , I apologies ... I really needed an answer asap so I had to post a comment asking for help ... I tried asking questions on my own but I got not any replies so far . However , a big big thanks for the hint that you dropped for me . Could I have more of your help on my questions please? I will follow
http://answers.unity3d.com/questions/1343845/sql-lite-database-for-andoid-in-unity.html#comment-1343946 http://answers.unity3d.com/questions/1343982/sqllite-android-and-unity.html#comment-1343985
