Sqlite for Android, help please

[rambramdt]

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.

[n0mad]

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.

[n0mad]

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.

[DallonF]

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.

[n0mad]

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 ?

[n0mad]

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 ?

[anon_8038926]

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

[n0mad]

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

[DanTreble]

@n0mad

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.

[n0mad]

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

[n0mad]

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

[zumwalt]

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

[DanTreble]

Here is the one I compiled, hope it helps.

Drop it in Plugins/Android/

685453–24648–$libsqlite3.7z (209 KB)

[n0mad]

That’s awesome ! Thank you very much DTreble

[zumwalt]

Thanks will give this a try tonight!

[zumwalt]

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.

[DanTreble]

logcat?

Unity Android Pro?

[n0mad]

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).

Various crashes experienced on device in combination of System.Security.Cryptography and managed code stripping

.NET Cryptography services heavily rely on reflection thus are not compatible with managed code stripping which relies on static code analysis. Sometimes easiest solution is to exclude whole System.Security.Crypography namespace from stripping process.

Stripping process can be customized by adding custom link.xml file to your Unity iOS project Assets folder, which specifies what types or namespaces should be excluded from stripping. More details can be found in iOS player size optimization guide.

link.xml

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.

[anon_15046521]

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 :0
I/Unity ( 5599): at Mono.Data.Sqlite.SqliteConnection.Open () [0x00000] in :0
I/Unity ( 5599):
I/Unity ( 5599): (Filename: Line: -1)

Any ideas?
any similar problem?

ps: Thanks for the .SO

[n0mad]

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