Sqlite for Android, help please

hi,

I have been trying to get sqlite to work on Android for quite a while now. The problem seems to be that Mono.Data.Sqlite.dll can not find sqlite3.dll . I am thinking, there could be multiple solutions.

1. Locate the missing sqlite3.dll and include it in the apk;
2. Use the native libsqlite.so which is included in Android;
3. Compile sqlite from source and use as a plugin.

However, each one is easier said then done. If you know the answer to any of my question, please don't hesitate to reply. Anything helps. My questions are:

1. a. Can I use the win32 sqlite3.dll from sqlite.org, or do I need a sqlite3.dll assembly specific for Mono;
b. How can I include it in the apk? Where should it go?

2. a. Is this the job of Mono.Data.Sqlite.dll?
b. Is there another way to access the libsqlite.so which is already in Android?

3. a. Can I compile using Android NDK? Isn't that specifically for porting C to Java, since it uses JNI?
b. If I can not use Android NDK, how should I do it?
c. How to wrap around the library using C#? I have indeed located a linux C# wrapper for sqlite, it import "kernel32" to handle pointers among other things. I do not know how to adapt it for Unity.

Once again, any help will be greatly appreciated.

 

Mine works perfectly fine, here is my setup :
- create a "plugin" folder (or whatever name) into your "Assets" folder.
- put the latest sqlite3.dll from the web into it
- copy paste the DLLs "Mono.Data.Sqlite.dll" and "System.Date.dll" from your Unity/Mono directory.

The 3rd step is very important not to mess with, you absolutely have to put the 2 dll from Unity/Mono directory, or they won't recognize each other if one comes from another version (latest Mono for ex). I had horrendous unexplainable bugs before I realized this and replaced my files.

 

Bump, I just realized that like many other Android users, sqlite3.dll is not integrated into the Android build (but it works fine under Editor).
Any news on this problem ? I tried to include the dll in any possible directory, doesn't change anything.

 

Well, a DLL is a Windows binary, so it's obviously not going to run on Android (Linux based) unless it's a Mono DLL (which will automatically be compiled by Unity), which I don't believe it is.

Android itself has Sqlite support built into its APIs. I'm not sure how you'd interface with that using Unity, but it's a much better approach than trying to get a native DLL to run on Android.

 

Ok I understand, thanks

So, as long as sqlite appears to be supported by either iOS and Android natively, and because we can't integrate it pseudo-natively into our Unity build, wouldn't it be logical for Unity to create a native interface to it, just like OpenGL and visual Keyboards ?

 

In the end, I can't find any answer to this question :
How can we possibly use Sqlite for Android, then ?

I tried without putting sqlite3.dll, which still makes it work in Editor (like it wouldn't need sqlite3.dll), but the build crashes again with the same DllNotFoundException for sqlite3.dll. Which means it's finally required.

It's a bit lame, because it means that the Unity/Mono integrated Sqlite version cannot work at all.
Why integrating something that cannot work ?

 

did you check this issue of Unity Creative:

http://www.3dattack.us/3DAttack/May-June2011.html
May-June 2011
Unity Creative May-June 2011 features the following content.
p43
• Accessing A SQLite Database (Android) - Article

 

Sounds interesting, but, set apart the coolness of the offer, I don't want to pay 6$ to learn how to make an integrated Mono feature build properly

 

How long is $6 of your time worth?!

I solved this one last week. Compile sqlite as a .so and put it in the android plugins directory. Works great.

 

Ouch, sorry no offense was intended, and I mean it when I say the magazine content looks very good It was just a matter of principle
Thanks for your advice, I'll search on how to compile into .so

 

Re :
Requires Linux unfortunately :/

I've been searching over the web for a potential Shared Object Library of sqlite3, but can't find any.... even on www.sqlite.org ! (isn't it odd ?)
Would there be any way to find a sqlite3.so anywhere ?

Thanks

 

Can anyone post the sqlite3.so they have compiled to this thread?
Would be helpful.

 

Here is the one I compiled, hope it helps.

Drop it in Plugins/Android/

 

That's awesome ! Thank you very much DTreble

 

Thanks will give this a try tonight!

 

Still can't get it to work, tried the SO in the Android folder under plugins, tried to rename it to sqlite.so, still get the DLL not found exception with the Android project with SQL Lite, not sure if I want to bother any more with this yet or not since I plan on writing something else using a simple binary file structure but this was going to be interesting if it worked.

 

logcat?

Unity Android Pro?

 

FYI, this Shared Obejct has solved the DLLNotFoundException for me. Totally.

Another problem appeared, although not related to Sqlite, which was a NullReferenceException on .NET Cryptography functions. As databases are commonly used with cryptography, I put the solution here :

When you chose micro mscorlib Stripping level under building settings, it does strip what should not be stripped under Reflection classes of C#, as Unity point out here, under iOS hints (but not under Android, as it does pose a problem under Android too).

So I created the link.xml file, and now Cryptography perfectly works with my SQLite Databases.

Thanks again DTreble for the .SO, works like a charm. This should be provided natively in future Unity builds, imo.

 

I could solve the libraries on Android. But now there is like having access to the path of my database.
I try (C#)
String connection = "URI= file:jar:" + Application.dataPath + "!/assets/" + myDatabase.db;
dbcon = new SqliteConnection(connection);

String connection = "URI= file:" + Application.dataPath + "/assets/" + myDatabase.db;
String connection = "URI= file:" + Application.dataPath + "/" + myDatabase.db;

In IOS works

connection = "URI=file:" + Application.dataPath.Replace("/Data","") +"/" + mydatabase.db;

the error is not finding the database. (logcat)
I/Unity ( 5599): SqliteException: Unable to open the database file
I/Unity ( 5599): at Mono.Data.Sqlite.SQLite3.Open (System.String strFilename, SQLiteOpenFlagsEnum flags, Int32 maxPoolSize, Boolean usePool) [0x00000] in <filename unknown>:0
I/Unity ( 5599): at Mono.Data.Sqlite.SqliteConnection.Open () [0x00000] in <filename unknown>:0
I/Unity ( 5599):
I/Unity ( 5599): (Filename: Line: -1)

Any ideas?
any similar problem?

ps: Thanks for the .SO

 

Try "URI=file:"+Application.persistentDataPath+"MyDB.db", I'm not sure it needs any other sub-folder.

 

Thanks!.
That path is working but I have the entire database.
I have a database loaded with many values​​. And I would need to export the entire project, I can not move it in place because that live in the project

 

Hi all,

Thanks for the tips in this thread, I think I'm 99% there.

Android finds the libsqlite3.so library, but when i try to open a connection with this code:

Code (csharp):

1.  
2. using System.Data;
3. using Mono.Data.Sqlite;
4. (...)
5. Debug.Log("Finding path...");
6. string db_path = Application.persistentDataPath+"/"+db_name;
7. string connStr = "URI=file:"+db_path;
8. Debug.Log("db_path = " + db_path);
9. Debug.Log("about to connect to " + connStr);
10. Debug.Log("Exists?");
11. Debug.Log(File.Exists(db_path) ? "YES" : "NO");
12. dbConnection = new SqliteConnection(connStr);
13. dbConnection.Open();
14. Debug.Log("connected!");
15.  

I get this cryptic error:


I/Unity ( 6607): db_path = /data/data/com.not.real/files/puzzles.db
I/Unity ( 6607): UnityEngine.Debug:Internal_Log(Int32, String, Object)
I/Unity ( 6607): UnityEngine.Debug:Log(Object)
I/Unity ( 6607): Logger:log(String)
I/Unity ( 6607): VDBMS_Standalone:_connect(String)
I/Unity ( 6607): VDBMS:connect(String)
I/Unity ( 6607): PuzzleLoader:loadPuzzle(Int32, Int32, Int32)
I/Unity ( 6607): NumberGrid:setup(Int32)
I/Unity ( 6607): GameScreen:Start()
I/Unity ( 6607):
I/Unity ( 6607): (Filename: /Applications/buildAgent/work/842f9557127e852/Runtime/Export/Generated/UnityEngineDebug.cpp Line: 34)
I/Unity ( 6607):
I/Unity ( 6607): about to connect to URI=file:/data/data/com.not.real/files/puzzles.db
I/Unity ( 6607): UnityEngine.Debug:Internal_Log(Int32, String, Object)
I/Unity ( 6607): UnityEngine.Debug:Log(Object)
I/Unity ( 6607): Logger:log(String)
I/Unity ( 6607): VDBMS_Standalone:_connect(String)
I/Unity ( 6607): VDBMS:connect(String)
I/Unity ( 6607): PuzzleLoader:loadPuzzle(Int32, Int32, Int32)
I/Unity ( 6607): NumberGrid:setup(Int32)
I/Unity ( 6607): GameScreen:Start()
I/Unity ( 6607):
I/Unity ( 6607): (Filename: /Applications/buildAgent/work/842f9557127e852/Runtime/Export/Generated/UnityEngineDebug.cpp Line: 34)
I/Unity ( 6607):
I/Unity ( 6607): Exists?
I/Unity ( 6607): UnityEngine.Debug:Internal_Log(Int32, String, Object)
I/Unity ( 6607): UnityEngine.Debug:Log(Object)
I/Unity ( 6607): VDBMS_Standalone:_connect(String)
I/Unity ( 6607): VDBMS:connect(String)
I/Unity ( 6607): PuzzleLoader:loadPuzzle(Int32, Int32, Int32)
I/Unity ( 6607): NumberGrid:setup(Int32)
I/Unity ( 6607): GameScreen:Start()
I/Unity ( 6607):
I/Unity ( 6607): (Filename: /Applications/buildAgent/work/842f9557127e852/Runtime/Export/Generated/UnityEngineDebug.cpp Line: 34)
I/Unity ( 6607):
I/Unity ( 6607): YES
I/Unity ( 6607): UnityEngine.Debug:Internal_Log(Int32, String, Object)
I/Unity ( 6607): UnityEngine.Debug:Log(Object)
I/Unity ( 6607): VDBMS_Standalone:_connect(String)
I/Unity ( 6607): VDBMS:connect(String)
I/Unity ( 6607): PuzzleLoader:loadPuzzle(Int32, Int32, Int32)
I/Unity ( 6607): NumberGrid:setup(Int32)
I/Unity ( 6607): GameScreen:Start()
I/Unity ( 6607):
I/Unity ( 6607): (Filename: /Applications/buildAgent/work/842f9557127e852/Runtime/Export/Generated/UnityEngineDebug.cpp Line: 34)
I/Unity ( 6607):
I/dun_service( 262): The value returned from dun_getusbmodemstate_fromsys is 1
I/dun_service( 262): Post event 1
D/dalvikvm( 6607): Trying to load lib /data/data/com.not.real/lib/libsqlite3.so 0x459bd0f0
D/dalvikvm( 6607): Added shared lib /data/data/com.not.real/lib/libsqlite3.so 0x459bd0f0
D/dalvikvm( 6607): No JNI_OnLoad found in /data/data/com.not.real/lib/libsqlite3.so 0x459bd0f0, skipping init
D/dalvikvm( 6607): Trying to load lib /data/data/com.not.real/lib/libsqlite3.so 0x459bd0f0
D/dalvikvm( 6607): Shared lib '/data/data/com.not.real/lib/libsqlite3.so' already loaded in same CL 0x459bd0f0
I/Unity ( 6607): ExecutionEngineException: SIGILL
I/Unity ( 6607): at Mono.Data.Sqlite.SQLite3.Open (System.String strFilename, SQLiteOpenFlagsEnum flags, Int32 maxPoolSize, Boolean usePool) [0x00000] in <filename unknown>:0
I/Unity ( 6607): at Mono.Data.Sqlite.SqliteConnection.Open () [0x00000] in <filename unknown>:0
I/Unity ( 6607): at (wrapper remoting-invoke-with-check) Mono.Data.Sqlite.SqliteConnection:Open ()
I/Unity ( 6607): at VDBMS_Standalone._connect (System.String db_name) [0x00000] in <filename unknown>:0
I/Unity ( 6607): at VDBMS.connect (System.String db_name) [0x00000] in <filename unknown>:0
I/Unity ( 6607): at PuzzleLoader.loadPuzzle (Int32 puzzleID, System.Int32 numMoves, System.Int32 time) [0x00000] in <filename unknown>:0
I/Unity ( 6607): at NumberGrid.setup (Int32 pid) [0x00000] in <filename unknown>:0
I/Unity ( 6607): at GameScreen.Start () [0x00000] in <filename unknown>:0
I/Unity ( 6607):
I/Unity ( 6607): (Filename: Line: -1)
I/Unity ( 6607):

Anybody know what in the world would case "ExecutionEngineException: SIGILL" ??

Thanks!

 

Make sure your System.Data.DLL and Mono.System.Data.DLL are coming from the very same source (here, Unity's mono folder).
I had a bunch of unexplained problems when I first try to import them without checking the source.

 

What if you want to embed a already populated database in the apk?
I've tried lots of combinations for the path with no luck. Seems like Android only lets you access files in the assets folder with AssetManager... not sure how Unity handles this though. :s

 

That's a bit trickier. I did similar for the video plugin. The apk is just a zip and assets in the StreamingAssets directory get added to that zip. Now if your asset is heavily compressed already it will get added to the zip without being deflated. This means using the java AssetManager you can get the byte offset of your asset in the apk. You can then load directly from that byte offset in your apk.

An alternative is to write code that unpacks the asset to the sd card or the persistantDataPath and load it from there.

Either way a bit of work.

 

I see, but how can you use AssetManager in Unity in the first place? Do I need to create a Plugin to use Android API directly?

 

Your right, it needs a plug in.

 

Thank you.

I finally could access the sqlite db stored inside "StreamingAssets" folder using

I also had to include sqlite3.so inside Plugins/Android

 

In case somebody have troubles with not being able to load libsqlite3.so, I made a custom built one from Android's sources that works. I attach a package with all that is needed to get SQLite running on Android. Cheers!


View attachment $Android-sqlite.unitypackage

 

I downloaded your unitypackage and I had trouble making it work so I attached a working example (C#) to the package that will work on windows and on android. (the code is not entirely mine, almost everything is from an internet example)

If after downloading anyone is having problems making it work, do the following:

Go to your unity folder (normally under Program Files) and do a search for the "Mono.Data.Sqlite.dll" and "System.Data.dll" (as n0mad said) and copy them and replace the files in the folder you unpacked the unitypackage.

View attachment $SQLite.unitypackage

 

I'm in the needs of accessing an already populated database, could you explain how you did it? I searched the net with no answer as how to use the WWW class to load a database without using PHP and remote connections.

 

I finally got everything working, I made some examples and placed them in this post: http://forum.unity3d.com/threads/114660-Unity-3D-Android-SQLite-Examples

 

Hi 0mad,
Can you please tell me how you managed to solve your problem with the so file?

A step by step of this would be awesome!

Thanks in advanced,
Gaetak

 

i have downloaded this .so file and it works perfectly on android
i was just wondering will it work for ios too ? if not can sum1 provide the file who he had compiled for ios ??

 

I put libsqlite3.so in Plugins/Android but the android build is still unable to find sqlite3.dll.

Is Unity Android Pro necessary for this to work?

 

After importing Android-sqlite.unitypackage we are now able to read the database on Nexus 7, but we receive the same error as before on a Kindle Fire HD:

Unable to find sqlite3.dll​

DllNotFoundException: sqlite3.dll​

Has anyone had similar problems where the error appears on some Android devices but not others?

 

Hi,

I need also the newest version of sqlite3 for android. Do you have a tutorial how you compiled this older version? And where did yo took the source from? Thank you upfront.

 

Hi,

I am searching for the new sqlite with version > 3.7.11 for android. Can you please provide this asset or a guide on how to compile it from the current sqlite version with the ndk. When I compiled it with ndk, everything works, but when I add the so-library to the project and run it I get "sqlite3 not found" exception.

You find the android eclipse project I use for compiling the so-library here:
https://dl.dropboxusercontent.com/u/66232579/AndroidSqlite.zip

Would be gratefull if someone can identify the problem with the project or post a compiled version of the sqlite with version > 3.7.11

Kind regards,
Renato Giurea.

 

Hi, I'm using the libsqlite3.so file provided time ago in this thread without troubles in all android devices but 4.2.2. Any known issues that I should be aware of?

 

I've delimited the issue. It seems that the processor of the tablet I'm testing is an Atom x86 so I think that libsqlite3.so must be compiled for ARM and that's causing the failure. Is there someone with experience in this kind of escenario? Does someone have a libsqlite.so version for x86 devices?

Best regards,
Roberto.

 

Supposedly ARM binaries are said to be able to run through the magic of something called houdini, but I sure couldn't get my ARM libsqlite3.so to run on a Nexus Player. (Kept getting an error about unexpected e_machine 40.)

I went and snagged the source for sqlite3 and built it against ndk r9b, and gave that a shot. It worked. (In fact, I hope I have both built and put into the proper folders so I don't have to worry about what I'm targeting at the moment, using subfolders of Plugins/Android/libs/, where I have an armeabi folder and an x86 folder, each containing their respective shared objects, just like the guy speaks about in the stack overflow answer I linked. Haven't tested it targeting the ARM stuff yet, but stands to reason it should probably work.)

Either way, here you go, attached is the libsqlite3.so that I'm using. Hope this helps.

 

Hi Nitz!
Thanks for your help. A few days ago @oRuban pushed his x86 solution to an open source project I'm maintaining at the moment: sqlite4Unity3d

Feel free to take a look at it. It maybe can interest you.

Here you can see the issue: https://github.com/codecoding/SQLite4Unity3d/issues/1

Thanks for the insight in the Houdini thing. I discovered that if I only published the ARM version and limited my distribution to ARM devices it seemed to be working somehow on x86 devices so I assumed some magic was going on there


Best regards,
Roberto.

 

Hi roberto and thanks for your really useful effort
BTW, as for the new Unity 5 I encountered some issues 'cos the sqlite3.dll is obviously targetted for x86_32 machines.
As a quick workaround, I snatched here a precompiled x64 dll, placed in a "x86_64" folder on the same level as x86, and renamed it as sqlite3.dll
Anyhow it is a dirty solution 'cos, while I have no problem to run inside the IDE nor to build either for x86 or for x86_64 for windows and linux, the compiler begun picky building for Android, complaining that:
Found plugins with same names and architectures, Assets/Plugins/x86_64/sqlite3.dll () and Assets/Plugins/x86/sqlite3.dll (). Assign different architectures or delete the duplicate.
forcing me to move away the x86 folder. It would be perfect if someone would find a better solution but for now with little bother one could get the job done anyway.

 

Hi f4bo,

I'm very sorry for this late answer but I didn't receive any email from the Unity forum and I've just seen your post.
Another user has created an issue in the GitHub project:
https://github.com/codecoding/SQLite4Unity3d/issues/7

The x64 library was added to the project a week ago under Plugins/x64.

I will check this as soon as I can and will update the GitHub issue.

Thanks a lot for spotting this!

Best regards,
Roberto.

 

Hello,

I was have the DLL exception problem on few android devices, specially Jelly Bean. All the dll's and .so's were in place and still it was complaining. This was fixed in code as the reference to sqlite3.dll had to be without the extension. For example,

Old Code:
[DllImport("sqlite3.dll", EntryPoint = "sqlite3_open")]

New Code
[DllImport("sqlite3", EntryPoint = "sqlite3_open")]

So just removing the .dll extension fixed the problem.

Cheers,
Swami.

 

Sqlite Database plugin is here for unity3d developers.
http://u3d.as/content/devesh-pandey/sqlite-database

 

Hi,
I would like to reborn this topic asking you help with an issue I've found after updating my device to Android 6.0.
I've a simple SQLite application that works perfectly if I use Android SDK ver 21 during the build and running it on my device (Nexus 5 with Android 6.0).
If I select Android SDK ver 23 during the build, when I run it on the same device, the app crash after trying reading the DB with the attached error.

It seams that the sqlite3 doesn't be placed into the apk, but I've it im my Plugins/Android folder.

Where am I wrong?

As told, the same application works with sdk 21, but not with sdk 23... maybe I need another sqlite3.so?

Thanks in advance

 

Same issue here app working fine on all android versions except 6.0 and above with below exception in logcat.
DllNotFoundException: sqlite3

 

----I encountered same issue as you, I changed the libsqlite3.so file to another one, then it work. I attached the file(I'm not sure where I downloaded it), wish that helpful.

 

The libsqlite3.so file for arm and x86 i compiled can be downloaded from: http://answers.unity3d.com/question...-specific-android-devices.html#answer-1162976