Tim Anderson's ITWriting

Notes on Sqlite

 

Want to reproduce this article?

If you would like to reproduce this article on your own publication or web site, please contact Tim Anderson .

Symbol representing Sqlite database

Using Sqlite in .Net and Java

Notes on using the open source Sqlite database on Windows, Mac, and with Java. By Tim Anderson.

 

Using Sqlite in .Net and Java

Sqlite is a small C database library. I like it because it is lightweight, fast, cross-platform, and supports a very decent subset of SQL. It reminds me in some ways of Codebase, another excellent database library that runs everywhere. The problem with Codebase is that it is at heart Xbase and not SQL. You can use SQL via its ODBC driver, but frankly ODBC should not be necessary for an embedded database. So if you use the Codebase API and then later want to upscale to SQL Server or Oracle or DB2, everything will need to be redone. A SQL API is more portable, despite the dreaded implementation differences. Sqlite strikes me as nearly ideal for applications that need fast and robust access to local data.

I decided to investigate the feasibility of using Sqlite as an alternative to Microsoft's JET (the Access database engine) in a .Net application. I was also interested in the possibility of porting the application to Java in order to run on Mac OS X and Linux, the two main "other" desktop platforms. What follows is a few notes on how I've got on so far. I've also included some downloads for two things I found tricky - using the Mono Sqlite data provider with Microsoft.Net, and using the Java Sqlite wrapper on the Mac.

Note that while there's no doubting the qualiity of the Sqlite database library itself (kudos to D. Richard Hipp. the main author), the same does not apply to all the wrappers out there. These range from finished code to various states of pre-alpha, alpha and beta. Some will no doubt never be finished. Fortunately many of the wrappers are themselves very small, so there is not too much to go wrong and you should be able to fix problems that arise.

Sqlite in Java

When it comes to Java, there is only one that I know of, and it is here: http://www.ch-werner.de/javaSqlite/. It's a good project, and works exactly as expected on Linux:

./configure
./make
./make install

Now, one of the problems with Sqlite wrappers is that many of them either include or modify the Sqlite code itself. This makes them forks, albeit usually of a very minor kind, and you have the inherent fork problem of what to do when the main branch is updated. Christian Werner's configure script allows for this nicely. You simply put the latest (or your preferred) Sqlite source in a subdirectory of his source, or specify the location of the source with an argument, and his JNI project will use that source. Obviously it may fail if the Sqlite API changes, but so far all is good.

The first problem I had came about when building the JNI library on Windows. Windows doesn't have GNU autoconf or automake, so Christian Werner supplies a makefile for Visual C++. This failed at first, because a few source files have been added to Sqlite since the wrapper was last updated, so I added them in. This worked but I was puzzled to fnd that various functions were disabled although I knew they worked in the Sqlite source i was using (2.8.9). Because of this, even the test.java supplied with the project failed. The reason is that the supplied Windows makefile is ultra-cautious about what is enabled. I revised it to enable the following defines:

HAVE_Sqlite_TRACE
HAVE_Sqlite_COMPILE
HAVE_Sqlite_SET_AUTHORIZER
HAVE_Sqlite_FUNCTION_TYPE

After that it worked fine and all was well, on Windows at least.

Sqlite for Java on Mac OS X

Mac OS X was a different story again. The Mac does use gcc and has autoconf, but clearly the author had not anticipated Mac usage. The configure script actually fell over with an error, but I found I could get it to run by specifying the location of the Java SDK and the Sqlite source. The resultant makefile still wouldn't build. I found out that some had reported problems with dynamic libraries on Mac OS X, so I rebuilt both Sqlite and the JNI wrapper with --disable-shared. I now had a library but it didn't work. I looked up Apple's notes on JNI, and found the library had to have an extension of .jnilib. It still did not work. I looked again at Apple's notes and found that I had to create a bundle. I modified the makefile to do this as an additional step. Joy - a working JNI wrapper on Mac OS X. I've put my build up for download; it works on OS X Panther and hopefully on other versions as well. (Note: the Panther build uses Sqlite version 2.8.11. I've also added a build for Sqlite 2.8.16 and 3.2.7 combined, for OS X 10.4).

.Net and the Mono Sqlite provider

My next adventure was with the .Net provider that comes with Mono. There are a number of .Net providers out there, but if you download them you'll find that all the Windows versions are written with managed C++, rather than C# or VB.Net. The Mono client on the other hand is pure C#. I prefer this, and I also like the fact that it will load a standard Sqlite dynamic library at runtime rather than compiling in a particular set of slightly modified Sqlite source files, as is the case with several of the other .Net providers. However, although the Mono Sqlite client works fine with Mono on Linux (I've not tried Mono on Windows), it does not work with Microsoft .Net.

The main reason for this is that C# does not let you declare a delegate that uses the Cdecl as opposed to Stdcall calling convention. Thus you either have to modify or extend the Sqlite library (which is bad), or use Managed C++. However, it turns out that you can overcome this C# limitation. What you have to do is to disassemble the compiled DLL to IL with ILDASM, modify it to add the correct attribute, and re-assemble it with ILASM. Not much fun if you are doing frequent builds, but fortunately it can be automated. Even better, there is a Perl script you can use. So you can automate this with Make or else simply run the script after building. I got this interesting information from a newsgroup posting by Microsoft's Jan Kotas. You can find it on Google; search for "Callback function using C calling convention". I wasn't quite home and dry. I also found it necessary to change the declaration of the callback function:

internal unsafe int SqliteCallback (IntPtr o, int argc, sbyte **argv, sbyte **colnames)

I also made the callback delegate a class variable rather than giving it local scope, to discourage the garbage collector from disposing of it before Sqlite has finished with it. It works fine now (so far), and I've provided a binary build so that anyone can try it.

Avoiding the callback

I found it interesting that you can overcome this limation of C# and use a Cdecl delegate, but it turns out that you don't have to. Sqlite provides a non-callback interface that you can use instead. It requires a small change to a few lines of code in the Mono .Net provider - and I thank Marcellino Tanumihardja for sending these to me. It makes more sense to do this that to mess around hacking the compiled IL, fun though it is.

Using the Mono data provider

Please note that this is a very basic provider. You can excecute SQL statements and return results through the ExecuteReader method. However there's no support for the DataAdapter yet, so the provider won't work with the VS.Net wizards. I'm hoping to add a few features soon, and will also make the revised source available for download.

Migrating from Access

Another little task concerns migrating data from Access to Sqlite. It turns out that there is a freeware tool out there that has a wizard for this, DBTools DB Manager. This will both generate the database structure and copy the data. Very useful, but I found it extraordinarily slow so I looked for another solution.

Access doesn't have any direct way to generate an SQL script defining a database, but there are some indirect ways.I used the SQL Server upsizing wizard, generated an SQL script from SQL Server, and modified this to work with Sqlite. Not too difficult. That gets you the structure, and the next task is to populate it. I now have a utility written in Delphi that does this; it's not ready to share yet but I may be able to do so at some point. It uses the limited but simple and fast Delphi wrapper by Pablo Pissanetzky.

Unlike my Access MDB, I can use the resultant Sqlite database on all sorts of platforms. Mono on Linux; Java on Windows, Linux and the Mac, and Delphi or C++ on Windows without worrying about MDAC, Microsoft's complex set of data access components.

When I was fighting with JNI on the Mac, I wondered if I should use something like Hsqldb, an excellent 100% Java database library. That's an OK approach, but you are then cutting yourself off from native code applications. It's a trade-off, because JNI is inconvenient, but I like being able to use Java, but not having to use it.

The downloads

Here's my build of the Mono client for Microsoft .Net on Windows. Note does NOT support IDataAdapater yet.

Mono provider for Microsoft.Net

Here's the source code for the Mono provider with the callback, modified to use the Cdecl hack.

Mono provider source

Here's the source code for the Mono provider adapted not to use the callback:

Mono provider source without the callback

This build is the Java wrapper for Mac OS X 10.4:

Mac OS X 10.4 JNI wrapper

Here's my older build of the Java wrapper for Mac OS X Panther:

Mac OS X JNI wrapper

Other links

For all things Sqlite, please see the official site here.

I like these alternative .Net wrappers:

ag-software - also has a VB wrapper and a handy query utility.

ADO.Net provider for Sqlite - another Managed C++ wrapper

.Net wrapper by Richard Heyes - does not implement an ADO.Net provider, but nicely done and well documented.

Copyright Tim Anderson 16th November 2003. Updated October 2005. All rights reserved.

Copyright ©2004 Tim Anderson


 
 
44 comments
Comment posted on 2009-08-11 00:50:09 by: Alex.
There is a new project SQLJet (http://sqljet.com/) that is a pure Java implementation of SQLite. It doesn't support all of the SQLite features yet, but may be a very good option for some of the Java projects that work with SQLite databases.
Comment posted on 2007-01-22 04:32:19 by: vipul.
hi,
i found that executing query on sqlite database using odbc driver is very slow compared to execution of same query in access database using(Microsoft's JET ).

can any one know how to make sqlite with odbc driver work faster.

Thanks

Comment posted on 2006-10-26 03:55:03 by: Jon.
dp you have any instructions for use of your Mono client for Microsoft .Net on Windows ?

I added sqlite3.dll into the bin folder with your dll, but get errors about mising manifest (?)

any instructions would be helpful as I am trying to use this with an ASP.net website .


Comment posted on 2006-09-23 15:54:59 by: David Canar.
I noticed you didn't use your Mono application on Mac OS. I'm planning to create a SQLite application in .NET using MONO do you know if the SQLite connector works on Macs also? Thanks
Comment posted on 2006-08-11 23:42:32 by: JuTs.
It's OK on Windows but not on Linux.
I put the dll/so file in the same directory than my jar file (my application)

Comment posted on 2006-08-05 22:37:22 by: David Crawshaw.
If you put the native library in the directory where you run the java from it will generally work, as the currentdir is added the library path.


Otherwise specify the library path:

java -cp myclassesdir/ -Djava.library.path=mylibdir/ myclass

Comment posted on 2006-08-03 12:21:15 by: JuTs.
Thank you. I'll try this on too.

Just a question : if I put the dll file (or .so file) in my application's directory, can it work correctly ?

Comment posted on 2006-08-02 22:21:13 by: David Crawshaw.
Another option for SQLite in Java is my new JDBC driver:

http://java.zentus.com/sqlitejdbc.html

Binaries are provided for Mac OS X (universal), Linux and Windows. Only supports SQLite 3.3.

Comment posted on 2006-07-30 21:24:36 by: JuTs.
I tried with sqlite 2 and 3 but that doesn't work with sqlite 3
Comment posted on 2006-07-30 07:27:23 by: Tim Anderson.
> Is this dll for sqlite 2 or for sqlite 3 ?

Both are included.

Tim

Comment posted on 2006-07-29 21:18:11 by: JuTs.
> I've updated the sqlite_jni_win.zip with a build with source > and target set for JVM 1.3. Can you let me know if that
> solves the problem?
>
> Tim

Is this dll for sqlite 2 or for sqlite 3 ?

Comment posted on 2006-07-20 18:52:37 by: Anonymous.
Oops, I found that Greg's Xcode project solved the problem of jdk version, too. I used it to build a jdk 1.5.0 sqlite.jar successfully. (Although I still met some other problems)
Sorry for bothering.

Comment posted on 2006-07-20 11:28:14 by: Anonymous.
FYI: http://www.mail-archive.com/sqlite-users@sqlite.org/msg15197.html

So, could you please provide a Java 1.5.0 sqlite.jar ?
Thank you very much.

Comment posted on 2006-04-25 01:15:53 by: Michael Dupuis.
Does anyone know if there are problems using a SQLite 3 database? Things with the latest version work great with a V2 SQLite database, but I can't seem to use it with a database created with version 3. If I do a getTables() call on DatabaseMetadata it now fails with this exception:

java.sql.SQLException: SQLite.Exception: error in prepare/compile
at SQLite.JDBC2x.JDBCStatement.executeQuery(JDBCStatement.java:121)
at SQLite.JDBC2x.JDBCStatement.executeQuery(JDBCStatement.java:135)
at SQLite.JDBC2x.JDBCDatabaseMetaData.getTables(JDBCDatabaseMetaData.java:543)

I am calling it like this: getTables(null, "%", "%", null) while worked before.

Comment posted on 2006-03-25 01:43:24 by: Greg Bolsinga.
From the comments here, I figured out how to build this. I created a Xcode wrapper project as well. This way PPC, Intel, or Universal jni libs can be built.

It's all here:

http://www.io.com/~bolsinga/sqlite_jni_xcode.html

Have fun!

Comment posted on 2006-03-22 05:16:05 by: Greg Bolsinga.
If you can provide the mods to the original sources you used to build this for Mac OS X, it should apply to Mac OS X on Intel as well. Then perhaps we can get the changes back into the original download.
Comment posted on 2006-03-16 20:01:37 by: Rod.
Tim
Would it be possible to get the source/build scripts you have created and I could try to build a universal library on an Intel box?
Some people have had issues building universals that actually work on Intel Macs from their PPCs.
Thanks.
Rod.

Comment posted on 2006-03-15 16:59:47 by: Tim Anderson.
Rod,

Unfortunately the jnilib is native code compiled for PowerPC. I do not yet have an Intel Mac.

Tim

Comment posted on 2006-03-15 16:37:03 by: Rod.
Tim,
Is there any reason that the latest OS X 10.4 libsqlite_jni.jnilib should not work on the Intel Macs?
They seem to have problem with loading the library.
Thanks.
Rod.

Comment posted on 2006-02-19 10:32:52 by: Tim Anderson.
I've updated the sqlite_jni_win.zip with a build with source and target set for JVM 1.3. Can you let me know if that solves the problem?

Tim

Comment posted on 2006-02-17 16:45:43 by: Michael.
i have download
http://www.itwriting.com/sqlite_jni_win.zip
and become the following error (executing getConnection ()).
I use java 1.4.2_10-b03 (the newest Version of 1.4):

Exception in thread "main"
java.lang.Unsupported ClassVersionError:
SQLite/JDBCDriver (Unsupported major.minor version 49.0)

at java.lang.ClassLoader.defineClass0(Native Method)
at java.lang.ClassLoader.defineClass(ClassLoader.java:539)
at java.security.SecureClassLoader.defineClass(SecureClassLoader.java:123)
at java.net.URLClassLoader.defineClass(URLClassLoader.java:251)
at java.net.URLClassLoader.access$100(URLClassLoader.java:55)
at java.net.URLClassLoader$1.run(URLClassLoader.java:194)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(URLClassLoader.java:187)
at java.lang.ClassLoader.loadClass(ClassLoader.java:289)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:274)
at java.lang.ClassLoader.loadClass(ClassLoader.java:235)
at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:302)
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Class.java:141)


Comment posted on 2006-01-25 03:06:16 by: Rod.
>I'm doing a new build... but SQLite.Database.version() may
>still return 2.x. If compiled for both, the wrapper will
>create 3.x databases by default (as far as I can tell).

Thanks for this.

Yes, the version reporting seems to be as you say. And the non-UTF warning has now disappeared from the start up.

Comment posted on 2006-01-17 04:47:56 by: Rachit Khanna.
i have a problem while i'm tryin to run my application on mono then i'm gettin errors like

Unhandled Exception: System.DllNotFoundExceptionSystem.Data.OracleClient.Oci.OciEnvironmentMode,intptr,intptr,intptr,intptr,int,intptr

if someone could help me resolve it tehn it would b very nice of him

Comment posted on 2006-01-05 08:51:27 by: Tim Anderson.
> java.library.path

Put the jni DLL on the system path; or in the Java ext directory; or add a switch to the java command, something like:

-Djava.library.path=where/you/put/the/jni


Comment posted on 2006-01-04 10:15:16 by: Rakesh.
I am trying to connect to a sqlite 2.8.16 database from my javapgm.I have used SQLite.JDBCDriver,compiling is fine but when i was trying to connect i am getting the error :Unable to load sqlite,unsatisfied link error sqlite_jni is not included in java.library.path.
will you please tell me how to do that

Comment posted on 2006-01-03 02:20:30 by: Carlos.
This is funny. You happened to be working on just what I wanted to do at the same time! In fact, from my time zone (still Jan 1 in California), this download is coming from the future!

Thanks for the Mac OS X jar/jnilib. I appreciate it!

Carlos

Comment posted on 2006-01-02 13:06:16 by: Tim Anderson.
I've now updated the OSX build of the JNI wrapper, using the latest source (2.8.17 and 3.2.8).

Tim

Comment posted on 2006-01-01 18:27:48 by: Tim Anderson.
> Does anyone have any idea what I am doing wrong on the
> Mac to get the 2.x engine instead of the 3.x ?

I'm doing a new build; however, this is partly a result of the way the library works. The Java wrapper can be configured to support Sqlite 2.x, 3.x, or both. I've compiled it to support both. In this scenario, the wrapper will open both types of database, but SQLite.Database.version() may still return 2.x. If compiled for both, the wrapper will create 3.x databases by default (as far as I can tell).

Comment posted on 2005-12-26 21:50:32 by: Rod Dunne.
Thanks Tim,
A new build would be very much appreciated.
Rod.

Comment posted on 2005-12-14 07:48:33 by: Tim Anderson.
Rod,

I'll do a new build with the latest source. Perhaps that will fix the problem.

Tim

Comment posted on 2005-12-13 19:59:41 by: Rod Dunne.
Thanks for these Tim. Not being a Mac guy, you have saved me no end of time.

If I use the OS X 10.4 JNI wrapper with the libsqlite_jni.jnilib file dated 17-10-2005, I get a warning in my output window "WARNING: Using non-UTF SQLite2 engine". In addition, calling SQLite.Database.version() will return "2.8.16", while SQLite.Database.dbversion() will return "3.2.7".

If I run the same code against Christian Werner's recent sqlite_jni.dll on a Windows machine, both functions report "3.2.1". Does anyone have any idea what I am doing wrong on the Mac to get the 2.x engine instead of the 3.x ?

Cheers.

Comment posted on 2005-10-20 20:42:52 by: Tim Anderson.
I've now posted an updated build for Mac OSX 10.4 - supports both sqlite 2.x and sqlite 3.x. It is rather large but works OK for me.

Tim

Comment posted on 2005-10-07 07:24:57 by: Tim Anderson.
I do intend to update the builds for OSX. As soon as I have a spare moment.

Tim

Comment posted on 2005-10-07 02:57:02 by: David.
I'm just starting with contemplating the use of SQLite on Mac OS X in a java project and am not comfortable with compiling C. Does a download exist for the binaries for the latest SQLite release on the Mac OS X platform? From what I gather, the java wrapper available is compatible with 3.2.7? I'd much appreciate any help.
Comment posted on 2005-09-27 15:52:55 by: Shalom.
And still... All of the packages here works on Java 1.4.x.
But non is configured to work with Java 5!
You can see that the code points to SQLite.JDBC2x and not to SQLite.JDBC2y as directed in ch-werner code for versions that are above 1.4.

Regards,
Shalom

Comment posted on 2005-08-05 19:08:58 by: Daniel Guerrero.
The new version of the wrapper works with mac os you just have to add the option:
--with-jdk=/System/Library/Frameworks/JavaVM.framework/Home
to the ./configure
and rename the libsqlite_jni.dylib to libsqlite_jni.jnilib :
cp .libs/libsqlite_jni.dylib libsqlite_jni.jnilib
(after you do the make, and without install)

Comment posted on 2005-03-12 19:57:53 by: Steve.
Excellent article !
I am trying to compile the latest SQLite (3.1.5) on Mac OS X, and then try to rebuild the Java wrapper... It will be great if you can post the configuration changes you made (the exact one and what files/lines you changed). Thanks !

Comment posted on 2004-12-21 06:35:54 by: Lapo Luchini.
Migrating from Access is actually both quite simple and fast if you install the SQLite ODBC driver.
It has problems to "link" SQLite tables (says something like "cannot create index on this field") but if you actually do "Export" it will gladly export all the schema and data to the specified ODBC SQLite source.

Comment posted on 2004-12-03 07:56:36 by: http://groups.msn.com/St-.
see http://groups.msn.com/St-Petersburg-Russia
Comment posted on 2004-10-28 20:28:33 by: Michael.
Could you provide your ./configure with parameters to build the JDBC driver for Mac OX X?
Comment posted on 2004-08-31 05:27:51 by: Glenn.
I didn't even try to build from source for Mac OS X.

In researching the topic, I came across Mr. Werner's page, and then yours. Download and "installation" (moving two files to Java's extension dir) went smoothly, but running the test app didn't get very far. It was readily apparent that the issue was that the TEST table was never created. Once I added that, it was smooth sailing.

Many thanks for helping to pioneer this -- it definitely saved me some time! :-)

--Glenn

Comment posted on 2004-07-07 16:23:31 by: Lance E Sloan.
Mr. Anderson,

Thank you very much for providing the Mac OS X build of Christian Werner's SQLite package for Java (javasqlite). I was trying to build it from source myself and ran into troubles.

I'm not a Java programmer, so I didn't know how to correct the build errors I was seeing. I just wanted to get the SQLite JDBC driver built so I could use it with DbVisualizer. When I did another Google search for SQLite and JDBC on Mac OS X, I found your page about it. I just downloaded your zipfile of the build, installed it in a safe place, and I was able to use it with DbVisualizer right away.

Thanks for saving me a lot of time. I will eventually go back and try to figure out how to build it myself, using your description as a guide, just so I will have a better understanding of it.

Comment posted on 2004-02-24 16:01:38 by: Tim Anderson.
James,

I've added it here:

http://www.itwriting.com/sqlite_jni_win.zip

Tim

Comment posted on 2004-02-24 15:33:38 by: James Mathrick.
I would appreciate a link to the dll/jni files for windows, or more intricate instructions to this build, as my own efforts have not been successful, and the distribution by Christian does not include the more recent files/options.

Excellent section on OSX - the download there worked instantly! Thank you! Just need it working on windows to crack on...

Cheers,
James

Comments are closed.