Re: Is the zentus JDBC driver for SQLite really the best choice

919 views
Skip to first unread message

Ross Evans

unread,
Feb 2, 2010, 3:57:58 PM2/2/10
to Andy Wilbourn, ScubaSteve, calv...@gmail.com, ryan.pa...@gmail.com, ward-to...@googlegroups.com
Andy,
 
I was going to copy Ryan on this, but I didn't have his email handy.  (All the more reason to have this discussion in the group.  So I am proceeding to copy the group to start a thread there.)
--Ross
On Tue, Feb 2, 2010 at 2:50 PM, Andy Wilbourn <awil...@gmail.com> wrote:

Here is my quick reply, the one I choose was a complete package for the different platforms. When I looked at this and saw I had to compile the code, presuming I needed a different version for each platform, I chose to not go that route. I am not a Java guru to know how to have the code use different versions of the native library. Not saying it can’t be done, but on the initial look seemed to be much more work. Now if Steve has more experience and can get me a JAR library to use and it does the internals of picking the right native wrapper then I would use that.

 

I can tell in java the difference of the platform, but not sure if it is relevant here.

 

I have not looked at this closely, as I am at work, but just wanted to share my reason for going the way I did. I think Ryan had said for his day job they are going to use what I chose, so there is some reason for that. I have added him on this thread.

 

Now mind you I did prove that if I wrote a small test app I could get data back Ross, so it may just be how things bind in the IDE to the control that is the issue. The data may be coming back.

 

From: Ross Evans [mailto:boome...@gmail.com]
Sent: Tuesday, February 02, 2010 3:09 PM
To: Andy Wilbourn; ScubaSteve; calv...@gmail.com
Subject: Is the zentus JDBC driver for SQLite really the best choice

 

Andy, Steve:

 

I don't think this issue should remain a private email thread for very long.  As a heads-up I thought I would post it here first, but it really belongs in the Ward Tools Development group.

 

The problems Andy has discovered with the zentus JDBC driver have led me on a research odyssey, which in turn causes me to question whether the zentus JDBC SQLite driver is the right horse to ride.  I had assumed by osmosis that it is the most popular, but that does not even seem to be the case.  Its support group has very little activity.  Andy has shown that it has problems with a couple of mainstream IDEs, and I have not been able to make it work with OpenOffice.  (I get connectivity, but can't retrieve any data.)

 

Has anyone explored Christian Werner's version here?

 

 

Werner's ODBC driver is virtually a defacto standard in the SQLite world.  I wonder if the same is not true of his JDBC driver.  On the surface, it seems more respectable.

 

--Ross

 


Ryan

unread,
Feb 2, 2010, 4:30:23 PM2/2/10
to Ward Tools Development
I feel like I'm partly to blame for the zentus direction. When Andy
asked about it I mentioned that we had a project here at work
requiring SQLite access from Java, and after a cursory survey we
landed on zentus. That project has recently morphed into abstracting
the SQLite access into a shared library that the Java code will access
through JNI. So I probably won't be doing any SQLite JDBC driver
evaluation after all (at least not for my day job).

On Feb 2, 1:57 pm, Ross Evans <boomerbu...@gmail.com> wrote:
> Andy,
>
> I was going to copy Ryan on this, but I didn't have his email handy.  (All
> the more reason to have this discussion in the group.  So I am proceeding to
> copy the group to start a thread there.)
> --Ross
>

> On Tue, Feb 2, 2010 at 2:50 PM, Andy Wilbourn <awilbo...@gmail.com> wrote:
> >  Here is my quick reply, the one I choose was a complete package for the
> > different platforms. When I looked at this and saw I had to compile the
> > code, presuming I needed a different version for each platform, I chose to
> > not go that route. I am not a Java guru to know how to have the code use
> > different versions of the native library. Not saying it can’t be done, but
> > on the initial look seemed to be much more work. Now if Steve has more
> > experience and can get me a JAR library to use and it does the internals of
> > picking the right native wrapper then I would use that.
>
> > I can tell in java the difference of the platform, but not sure if it is
> > relevant here.
>
> > I have not looked at this closely, as I am at work, but just wanted to
> > share my reason for going the way I did. I think Ryan had said for his day
> > job they are going to use what I chose, so there is some reason for that. I
> > have added him on this thread.
>
> > Now mind you I did prove that if I wrote a small test app I could get data
> > back Ross, so it may just be how things bind in the IDE to the control that
> > is the issue. The data may be coming back.
>

> > *From:* Ross Evans [mailto:boomerbu...@gmail.com]
> > *Sent:* Tuesday, February 02, 2010 3:09 PM
> > *To:* Andy Wilbourn; ScubaSteve; calvi...@gmail.com
> > *Subject:* Is the zentus JDBC driver for SQLite really the best choice

Andy Wilbourn

unread,
Feb 2, 2010, 5:42:30 PM2/2/10
to ward-to...@googlegroups.com
Ok I guess I will need to see what the JNI is about, is that like calling
the native API of SQLite directly in .Net terms a PInvoke?

Now mind you, the issues with the JDBC seems to be around pulling the data
back, I believe the putting the data in was ok (minus my last publish which
I am trying to be prepared to push another, but adding more checks
internally).

So the only thing to mind that would be affected is if we were to try and
have a desktop version, as Rich had mentioned he would be glad to do. I will
test some other things in my own code and not depend on the IDE to show me
the data and give more feedback to what happens.

Andy Wilbourn

unread,
Feb 2, 2010, 10:44:49 PM2/2/10
to ward-to...@googlegroups.com
Ok I think I have some good news, I have a test app for trying to see if I
see same behavior for an application I write vs. the IDE. I find that if I
query the LQSql_Ward views that the NetBeans IDE returns no data for, that I
do get the data using the same JDBC driver. I have also tested with a .net
application and I also get the data back and can bind to my own datagrid, so
there is something up with the designer of the .Net data provider (I will
send an email to the developer as they pop up a message saying they have not
fully tested the designer and want to know issues).

So hurray, seems like we may be ok. I had based my comments before on
trusting the IDE would work properly in looking at the data.

-----Original Message-----
From: ward-to...@googlegroups.com
[mailto:ward-to...@googlegroups.com] On Behalf Of Ryan
Sent: Tuesday, February 02, 2010 4:30 PM
To: Ward Tools Development

boomerbubba

unread,
Feb 3, 2010, 12:00:00 AM2/3/10
to Ward Tools Development
Thanks, Andy. That is good news.

I am curious about something: When you experience the problems using
the IDEs, do all your test queries retrieve nothing at all, or just a
subset of the data you are expecting. Perhaps some interfaces may
have problems with certain SQLite datatypes, which are a little
ephemeral -- especially when they are dynamically created by quieries
or views. That is the case with ODBC, and I think JDBC may have
similar problems.

--Ross

> > > --Ross- Hide quoted text -
>
> - Show quoted text -

boomerbubba

unread,
Feb 3, 2010, 6:51:24 PM2/3/10
to Ward Tools Development
In the process of testing JDBC drivers with OpenOffice, I found one
thing that may present a barrier to query app designers. The zentus
SQLite driver supports only TYPE_FORWARD_ONLY cursors so OOo can't get
any results. The zentus author, David Cranshaw, explains his position
on that issue here:

http://groups.google.com/group/sqlitejdbc/browse_thread/thread/37b7fcf0788fd535

He may have point as a matter of purity, but the fact is that
Christian Werner's JDBC driver returns scrollable result sets just
fine. (There is a separate problem with Werner's driver as seen
through OpenOffice -- a failure to display date data types. But I
_think_ that is an artifact of the OpenOffice user interface, and
might be cured by setting a property in the Java code when defining
the connection. Dates display just fine with a command-line query
interface.)

I doubt that this will affect much on the handheld platforms, because
they each have their own interface to SQLite. But these two drivers
seem to be the choices for desktop use. None of this has stopped
Andy's development of the loading utility, because he is mostly
putting data in, not querying it.

--Ross

> > - Show quoted text -- Hide quoted text -

Reply all
Reply to author
Forward
0 new messages