SimpleSQL - SQLite integration with Unity3D

Hi Echo

ahh ok great ill try to implement it now, i think ill need to count the primary key which is an int.

many thanks

 

Sounds good. Keep in mind that the "COUNT" command will work on any field (strings, ints, bools, etc). That's why you can do a COUNT(*) since the field doesn't really matter.

If you were using a "SUM" or "AVG" or some other math command, then you would want the field to be a numeric one.

 

Hi Echo17

I am a developer in south korea.

I use Korean Language in Sqlite DB. But I can not get right result with "Select" query.

How can I Unicode(Multibyte language) : Korean, Chiness, Special Character(pronunciation symbol) ?


Error Messag is this :
//---
QLiteException: no such column: N
SimpleSQL.SQLite3.Prepare2 (IntPtr db, System.String query)
SimpleSQL.SQLiteCommand.Prepare ()
SimpleSQL.SQLiteCommand.ExecuteQuery[T_Hanja] (SimpleSQL.TableMapping map)
SimpleSQL.SQLiteCommand.ExecuteQuery[T_Hanja] ()
SimpleSQL.SQLiteConnection.Query[T_Hanja] (System.String query, System.Object[] args)
SimpleSQL.SimpleSQLManager.Query[T_Hanja] (System.String query, System.Object[] args)
search.Query_FindHanja (System.String searchText) (at Assets/Script/search.cs:118)
search.OnSearch () (at Assets/Script/search.cs:64)
UnityEngine.GameObject:SendMessage(String, Object, SendMessageOptions)
UIButtonMessage:Send() (at Assets/NGUI/Scripts/Interaction/UIButtonMessage.cs:77)
UIButtonMessage:OnClick() (at Assets/NGUI/Scripts/Interaction/UIButtonMessage.cs:56)
UnityEngine.GameObject:SendMessage(String, Object, SendMessageOptions)
UICamera:Notify(GameObject, String, Object) (at Assets/NGUI/Scripts/UI/UICamera.cs:762)
UICamerarocessTouch(Boolean, Boolean) (at Assets/NGUI/Scripts/UI/UICamera.cs:1290)
UICamerarocessMouse() (at Assets/NGUI/Scripts/UI/UICamera.cs:1042)
UICamera:Update() (at Assets/NGUI/Scripts/UI/UICamera.cs:887)
//---


Unity C# Code is this :

inputText.text = "shòu";
inputText.text = "車";
inputText.text = "말";
inputText.text = "door";

Query_FindHanja( inputText.text );

void Query_FindHanja( string searchText )
{
string query = "SELECT " +
"No, " +
"Hanja, " +
"Code, " +
"HanjaDiv, " +
"Turn, " +
"Level, " +
"Step, " +
"PartSix, " +
"Stroke, " +
"Rating, " +
"Pinyin, " +
"HanjaMean, " +
"ChiMean2, " +
"ChiMean1, " +
"EngMean, " +
"sound, " +
"ExWord, " +
"ExPinyin, " +
"ExMean, " +
"Correspond, " +
"CorrespondCode, " +
"Standard, " +
"StandardMeanPinyin, " +
"Center, " +
"CenterCode, " +
"Position, " +
"Property, " +
"ImageName " +
"FROM hanja " +
"WHERE " +
"Pinyin = " + "'" + searchText + "'" +
" OR " +
"HanjaMean = " + "'" + searchText + "'" +
" OR " +
"EngMean = " + "'" + searchText + "'" +
" ORDER BY No ASC ";

hanjas = dbManager.Query<T_Hanja>(
query
);

string outputText = "";
foreach (T_Hanja hanja in hanjas)
{
outputText = "No: '" + hanja.No + "' " +
"Hanja: '" + hanja.Hanja + "' " +
"HanjaMean: " + hanja.HanjaMean +
"ImageName: " + hanja.ImageName +
"Sound : " + hanja.sound + "\n";

print (outputText);
}
}

 

To use non-western characters you will need to utilize parameters. I would recommend parameters anyway since they make cleaner SQL strings. Please see this FAQ for more information:

http://www.echo17.com/forum/index.php?topic=257.msg399#msg399

 

Demo project 7 works fine on desktop but crashes with iPad (tried the demo after it didn't work on my project), iOS 7.04 and latest asset store version. Xcode log:

Code (csharp):

1. OverflowException: Outside range [MinValue,MaxValue]
2.   at System.TimeSpan.From (Double value, Int64 tickMultiplicator) [0x00000] in <filename unknown>:0
3.   at System.TimeSpan.FromSeconds (Double value) [0x00000] in <filename unknown>:0
4.   at SimpleSQL.SQLiteConnection..ctor (System.String databasePath) [0x00000] in <filename unknown>:0
5.   at SimpleSQL.SimpleSQLManager.CreateConnection (System.String documentsPath) [0x00000] in <filename unknown>:0
6.   at SimpleSQL.SimpleSQLManager.Initialize (Boolean forceReinitialization) [0x00000] in <filename unknown>:0
7.   at SimpleSQL.SimpleSQLManager.Awake () [0x00000] in <filename unknown>:0
8.  
9. (Filename:  Line: -1)
10.  
11. OverflowException: Outside range [MinValue,MaxValue]
12.   at System.TimeSpan.From (Double value, Int64 tickMultiplicator) [0x00000] in <filename unknown>:0
13.   at System.TimeSpan.FromSeconds (Double value) [0x00000] in <filename unknown>:0
14.   at SimpleSQL.SQLiteConnection..ctor (System.String databasePath) [0x00000] in <filename unknown>:0
15.   at SimpleSQL.SimpleSQLManager.CreateConnection (System.String documentsPath) [0x00000] in <filename unknown>:0
16.   at SimpleSQL.SimpleSQLManager.Initialize (Boolean forceReinitialization) [0x00000] in <filename unknown>:0
17.   at SimpleSQL.SimpleSQLManager.CreateCommand (System.String cmdText, System.Object[] ps) [0x00000] in <filename unknown>:0
18.   at SimpleSQL.SimpleSQLManager.QueryGeneric (System.String query, System.Object[] args) [0x00000] in <filename unknown>:0
19.   at SimpleTable.Start () [0x00000] in <filename unknown>:0
20.  
21. (Filename:  Line: -1)

 

Sorry you're having troubles. I'm not able to duplicate this issue.

I'm running the Demo 7 on an iPad 3 running iOS 7.0.4 with Unity 4.3.4 and XCode 5.0.2. Perhaps you have a different version of XCode that might be causing the conflict?

Here's a screenshot of it running on my iPad:



Note that I added "Demo 7" to the top to distinguish it from other demos like it. No errors in Unity Editor.

 

Yeah I tried multiple devices too all with same results and then tried to use only timespan or datetime stuff in simple test project and it seemed to be an Unity bug. Finally found this so no need to investigate more:

http://issuetracker.unity3d.com/issues/datetime-difference-on-ios-device-and-in-the-editor

 

It might also be something to do with the build settings. I have mine set to .NET subset, not sure if making it full .NET makes any difference.

 

Has anyone used SimpleSQL in unity/facebook projects?

 

Hi Echo17,

I'm having a problem with this statement:

Code (csharp):

1.  
2. mPlan = dbManager.Query<MovementPlan>("SELECT last_insert_rowid()");
3. Debug.Log("MovementID: " + mPlan[0].id);
4.  

I'm doing an Insert before this statement, the insert is working and the id is incremented but this statement always returns 0

My table looks like this:
id (PrimaryKey AutoIncrement)
targetMinutes
movement
to_plan

EDIT:
It seems that his also works:

Code (csharp):

1.  
2. Plan p = new Plan()
3. dbManager.Insert(p);
4. Debug.Log(p.id);
5.  

also works so that's nice!

 

Any chance of getting support for WP8 and Win RT apps? both platforms have their dll available at sqlite official page and the export support for those platforms do not require additional license purchases to Unity. Also AFAIK you don't need real devices as you can use emulators to test.

Cant add them myself as the runtime is .dll only and no source available (unless its by request only)?

 

Yep, that's what I was going to suggest, glad you found it!

 

I'll have to look into the emulators, I wasn't aware of that.

If you would like the source, you can send an email to the link in my signature with the invoice number you received at purchase time. I'll get it to you right away.

 

Hi,

Just a quick post about my tests.

I got Windows Store to work fine (at least seems so) in locally/emulator after adding all metro RuntimePlatforms in the awake check to prevent it trying to copy the dll and adding the official sqlite package to the visual studio project that Unity creates. Queries and direct updates Executes worked without errors.

For WP8 the only problem preventing to build seems to be System.IO.File.WriteAllBytes which WP8 does not support. Unity has made their own class for this use case: http://docs.unity3d.com/Documentation/ScriptReference/Windows.File.html but I didn't yet try to set it up a sit seems to be platform specific class and seems it would need its own runtime_wp8 project to get it linked and compiled. If you are trying to test this some point you can see the error when building inside Unity.

 

Thanks for the info, that is definitely helpful!

 

Hi Echo17,

I'm getting an error on iOS when executing this code:

Code (csharp):

1.  
2. public bool updateUser()
3.     try
4.     {
5.         dbManager.UpdateTable(currentUser);
6.         return true;
7.     }
8.     catch(UnityException e)
9.     {
10.         Debug.Log ("Exception updating user table! " + e.ToString());
11.         return false;
12.     }
13. }
14.  

Before i'm inserting currentUser i've checked that all values are set so that's not the problem. The error i'm getting when executing the
code

Code (csharp):

1. dbManager.UpdateTable(currentUser)

is:

Any idea what could fix this problem?

EDIT:
Just to be clear, it's not executing the Debug.Log statement in the Catch part of the code. And it happens only on iOS.

 

Not sure, I haven't come across this before. It appears from the error that iOS can't compile with the reflection. Using the UpdateTable function uses the reflection, so I'm guessing that triggers it.

Have you tried using SQL statements to do your update?

 

Hi Echo 17.

Haven't tried using SQL statements, i'll try that. Weird thing is, when using Insert it does work but maybe that doesn't use reflection. Do you know of any problems with the UpdateTable function? Or any settings I should try?

I've wrote a SQL statement now i'm going to test it tomorrow morning.

 

I haven't tried the UpdateTable on iOS before, only on desktop. I'm a DB programmer by trade, so I'm more comfortable using SQL statements myself. The specialized ORM functions are more of a convenience that just wrap SQL statements for you. They're good for rapid coding, but I find myself going back to SQL statements for the pure flexibility.

I don't have access to a Mac at the moment, but I'll try to play around with that function later and see if there is something obvious that can be changed.

 

Well it seems that UpdateTable was indeed the problem, i've replaced them all with SQLite queries and now it's working fine again

 

Hi, sometimes I have a problem doing UpdateTable(myObject), it returns a Contraint exception. My table's PrimaryKey is formed by two columns. And in my class:

[PrimaryKey]
public int phase_id { get; set; }
[PrimaryKey]
public int phase_configuration_id { get; set; }

Code:

Code (csharp):

1. try{
2.    dbManager.BeginTransaction ();
3.    dbManager.UpdateTable(myObject);
4.    dbManager.Commit ();
5. }
6. catch (SQLiteException sqle) {
7.             Debug.Log(sqle.Message);
8.             dbManager.Rollback ();
9.         }

Note that this happens sometimes...


Thanks in advance.

 

I'm not sure the reflection will work with a double primary key. For this special case, you'll probably need to use SQL statements to do your updates.

 

Hi Echo17,

I'm getting JIT compile errors now eveytime i'm using ORM. Doesn't matter if i'm using the insert or update function on iOS it always throws a JIT Compile exception. So now I have to use SQL queries. But i'm for instance if i'm creating an event i'm not getting an Event_ID (primary key) back. How do I get the ID of the inserted query?

 

There's a number of ways to do this, but the simplest is probably a query something like:

Code (csharp):

1.  
2.  
3.         bool recordExists;
4.         PlayerStats record = dbManager.QueryFirstRecord<PlayerStats>(out recordExists, "SELECT MAX(PlayerID) PlayerID FROM PlayerStats");
5.         if (recordExists)
6.         {
7.             Debug.Log("Last PlayerID = " + record.PlayerID);
8.         }
9.         else
10.         {
11.             Debug.Log("No record found");
12.         }
13.  
14.  

This is using the classes and database from the example script "InsertCommand.cs" included with the plugin. Note that when using the MAX sql syntax, you have to supply an alias for the field. In the case of the example, the alias is the name of the id field so that the table structure can be mapped to the "PlayerStats.cs" class.

 

SimpleSQL 1.5.0 Approved and in the Asset Store

Download here: https://www.assetstore.unity3d.com/#/content/3845

Be sure to back up your work and download the update into a new project. You can copy the code over into your current projects using your OS's file system to ensure no import errors occur.

Check out this link for a complete list of changes: Changelog

FYI, you can follow these updates on my Facebook, Google+, and Twitter feeds as well (links in my signature)

 

Thx i'm going to try it out. Do you have any idea about the JIT compile exceptions? It's really a big problem because I have to rewrite a lot of code getting my whole database working again

 

Thanks.

Another question, I'm getting this error (in logcat many times) in Android device:

Missing Dll resource: SimpleSQL.Resources.sqlite3.dll.resource
UnityEngine.Debug:Internal_Log(Int32, String, Object)
UnityEngine.Debug:LogError(Object)
SimpleSQL.RuntimeHelper:CreateFileFromEmbeddedResource(String, String)
SimpleSQL.SimpleSQLManager:Awake()

SimpleSQL Options are: Android and No System.Data.


Any idea? The database seems to work fine...

Thanks in advance.

 

I'll try to take a look at this, I haven't come it across it myself. But then again, I rarely use the ORM helper functions, I usually prefer to just code directly in SQL for the flexibility.

 

This error was removed in the latest update released yesterday. Should be available in the asset store now.

 

Hi,

Sorry for the noobish question, but how would I execute a command like "SELECT SUM(Points) AS TotalPoints FROM PlayerGameStats" using SimpleSQL?

I want to get the sum of the Points column for every row in PlayerGameStats.

Thanks in advance!

 

I would do something like:

Code (csharp):

1.  
2.  
3.         bool recordExists;
4.         PlayerStats record = dbManager.QueryFirstRecord<PlayerStats>(out recordExists, "SELECT SUM(Points) Points FROM PlayerStats");
5.         if (recordExists)
6.         {
7.             Debug.Log("Total Points : " + record.Points);
8.         }
9.  
10.  

Assuming you have your database manager declared as dbManager.

The key here is that you provide an alias to your aggregate, in this case "Points" is the alias of "SUM(Points)". That allows the ORM functions to map the summation aggregate to the Points field of the PlayerStats class.

 

Excellent, I'll give that a try

I was playing around with it and found that this call also worked:
dbManager.Query<PlayerGameStats> ("SELECT SUM(Points) AS Points FROM PlayerGameStats")[0].Points;

Thanks again!

 

Yes, sql statements works fine.

Perfect, updated.

Thanks again.

 

Hi Echo17,

Did you have time to take a look at the ORM functions and IOS causing the JIT Compile errors for System.Reflection?

 

I think it might be caused by the Linq library, actually. Apple's JIT compiler does not like some of the things that Linq can do since it works on-the-fly. It would not be a quick task to undo all the Linq code, but you may be able to modify the source for just the things you need. In your stack trace it should show which functions are causing the JIT to fail. Try replacing the Linq code from the source and see if that clears it up.

Be sure to read the readme.txt file included with the source to understand how to make changes to the plugin. There are a few steps you need to take after modifying the source to ensure the changes get into your project.


[EDIT]

I went back to your original post and saw that is indeed the reflection, not the linq that is giving you errors. I'll try to devote a few cycles to this when I am able to get access to a mac to test on.

[/EDIT]

[EDIT 2]

Here are some links to other folks having the same issues in different plugins. There is some useful information there that I will take a look at and see if I can integrate, but in the meantime you might have a go at it:

http://forum.unity3d.com/threads/43038-ExecutionEngineException-Attempting-to-JIT-compile-method
http://hutonggames.com/playmakerforum/index.php?topic=6026.0
http://www.daikonforge.com/dfgui/forums/topic/property-binding-via-getset-not-working-on-ios/

[/EDIT 2]

 

One thing that stood out in these links is that if you are using the full .NET 2.0 library, then you may get some errors. Try setting to the subset and see if that helps.

 

SimpleSQL is on sale for 50% off during Unity's 24 Hour Deal Sale!

​

 

I have confirmed that this isn't a bug in SimpleSQL. You just need to set your project's API compatibility level to .NET 2.0 subset. The full .NET implementation gives you the reflection errors.



I've created a FAQ for this here: http://www.echo17.com/forum/index.php?topic=257.msg1961#msg1961

 

Hi, very nice and extremely useful plugin. I had a quick question. I am new to SQL and databases in general and inserting and retrieving single or a few values works amazingly well. However when I tried to save position data along a single axis at every frame into a table it took a huge performance hit. I have included the sample code, am I doing something wrong or is the plugin not intended for such use cases? In case this is wrong, can you please suggest an alternative way in which I would be able to implement this using databases.

Code (csharp):

1.  
2.  
3. private float _plyPositionX;
4. public GameObject Ply;
5. public SimpleSQL.SimpleSQLManager dbManager;
6.  
7. void Update ()
8. {
9. _plyPositionX = Ply.transform.position.x;
10. dbManager.Execute("INSERT INTO PosValues (PosX) VALUES (?)", _plyPositionX);
11. }
12. }
13.  
14.  

Thanks.

 

Thanks for the kind words! Yeah, you definitely do not want to access a database every frame. Database interaction is done through the file system which is the largest bottleneck of any platform. Instead, you should try to save your updates only at critical junctions.

I'm not familiar with your game, but here are some examples:

1) Turn based game: save your data when the turn is over for a player
2) Real-time game: save your data when the object stops moving
3) Real-time game: save your data when the app closes or finishes a level

Obviously these are very generic. You can probably come up with better save points based on the specific nature of your game.

 

Hey, Thanks a lot for the quick reply and for the info. I had guessed that it would not be possible but still wanted to give it a shot in case there had been some recent developments. I will go back to using StreamWriter for those cases. Thanks again for a nice plugin.

 

I am receiving the following error when navigating from a scene which has DB Manager to another scene (this doesn't have a DB Manager) - I have no problems with CRUD operations on the database within the scene I'm navigating away from.

UnassignedReferenceException: The variable databaseFile of 'SimpleSQLManager' has not been assigned.
You probably need to assign the databaseFile variable of the SimpleSQLManager script in the inspector.
SimpleSQL.SimpleSQLManager.Initialize (Boolean forceReinitialization)
SimpleSQL.SimpleSQLManager.Awake ()​

 

In order for the SimpleSQLManager to work, it must be present in the scene you are trying to access it from. Be sure you create a manager gameobject in each scene to avoid this error.

 

Hi, echo17!
I'm trying to port SimpleSQL to WP8, using information from this link http://developer.nokia.com/community/wiki/How_to_use_SQLite_in_Windows_Phone

What did I done:
1. Created new project in simpleSQL solution, target- assembly for WP
2. Add cs files from simplesql_runtime project
3. Remove sqlite.cs
4. Install all sdk and packages from article above
5. Some magic with source code to make simpleSQLManager compiles with new sqlite.cs
6. Profit! New project can be compiled.

Troubles become when i put this new libraries to unity project (new SimpleSQL_Runtime and SQLite).
1. If i put SQLite to Plugins/WP8 unity tells me that it could not find SQLite.dll (during building wp8 player)
2. If I put SQLite to WP8 sdk folder unity starts to find it but i get next error during compilation WP8 player: blablabla BadImageFormat. It looks like my new SimpleSQL_Runtime dll or SQLite.dll has wrong target platform, but i do not understand what platform should it be.

Echo17, can you provide any help on this issue?

 

I've had no success with WP8. There is a warning on the asset store page (https://www.assetstore.unity3d.com/en/#!/content/3845) that says that WP8 is not supported.

The issues I've had are related to the fact that WP8 does not support linking to static libraries. Since SimpleSQL uses the sqlite library, I'm not really sure how to get around that.

You might be able to eliminate this error if you make a small modification to the source code. In the SimpleSQL_Runtime project, SimpleSQLManager.cs script, in the Awake function you'll find a section of code that looks at the platform and extracts the sqlite3.dll if need be. To avoid WP8 trying to extract this library, put the WP8 platform enum after the list of other platforms that don't need sqlite3.dll. I would do this myself, but SimpleSQL is backward compatible to versions of Unity that did not support WP8 (so the platform enum is not available in these versions and will give an error).

Add the WP8 platform enum just after these lines:

caseRuntimePlatform.WindowsEditor:
caseRuntimePlatform.OSXEditor:
caseRuntimePlatform.OSXPlayer:
caseRuntimePlatform.IPhonePlayer:
caseRuntimePlatform.Android:

Once you have this change in place, you'll need to recompile the solution. The solution will embed the runtime into the editor so that it can be extracted by the options window, but you'll want to avoid this workflow if you are using your own code. Instead, manually copy the SimpleSQL_Runtime.dll from the solution to your project.

The only thing the platform selection does in the Options window is to choose the correct runtime to extract from the editor dll into the Unity project. If you are making your own code changes, you can skip this window entirely.

 

The way to get around this discribed in the link.
You can include SQLite.dll in references of SimpleSQL_Runtime.dll, so latest could be able run methods from SQLite.dll without static linking.
I did it, and now my project compiles and deploys on WP8, but it crashes on start, with this error:

An exception of type 'System.TypeLoadException' occurred in Unknown Module. and wasn't handled before a managed/native boundary
'TaskHost.exe' (CLR C:\windows\system32\coreclr.dll: Silverlight AppDomain): Loaded 'C:\windows\system32\en-US\mscorlib.debug.resources.dll'. Module was built without symbols.
An exception of type 'System.Reflection.TargetInvocationException' occurred in mscorlib.ni.dll and wasn't handled before a managed/native boundary
Failed to load assemblies.

 

Ah, i see.

SimpleSQL is compatible with Non-pro versions of Unity, so I cannot include the sqlite library in the project during build time since the free version of Unity does not allow non-managed code. To get around this limitation, I embed the sqlite3 dll into the runtime and extract upon playing the game. This way Unity does not complain about a non-managed assembly, but I can still use it with the free version of Unity.

I have no idea what this error means. It's coming from the either the unity core assemblies or the WP8 specific assemblies, neither of which I have access to. You may find the solution on a WP8 support forum.

 

Hello, when error exiting play mode, sometimes it seems simpleSQL doesn't close connection to database and starts giving me an error " failed to open database" every time I try to open the db. Changing working name would fix the problem but is there proper way to check if the connection to the database was not closed in the last run and close the connection if it wasn't closed?

 

It sounds like the operating system is placing a lock on the file that is not expiring due to the app closing prematurely. I don't have a working project set up to simulate the app failure, so if you could send me an example project that demonstrates the issue, I'll have a look and see if there is a graceful way to handle the locks.

Have a look at this FAQ for more information: http://www.echo17.com/forum/index.php?topic=257.msg950#msg950

 

It seems that happens when the unity editor compiles scripts in the middle of play mode and when that makes error-exit play mode. I'm not sure if it happens on built exe but in case users get the same problem, I am thinking of just randomly change working database name on startup. (Since I only use 1 database).