JDBC driver experience

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

JDBC driver experience

Richard Hipp-3
Are you using SQLite with JDBC?  If so, can you please answer a few
questions below?

You can post on this mailing list or send your reply directly to me.

1. Which JDBC are you using?
2. For how long have you been using it?
3. What issues you had with this driver?
4. What advise do you have for avoiding problems in this driver?

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: JDBC driver experience

Don V Nielsen
1. Xerial
2. Couple years?
3. None. But I am just a dumb user. I use JetBrains IDE's (RubyMine, Rider,
Datagrip). The IDE's provide direct access to the datasource for looking at
the db, manipulating data, manipulating tables. That sort of stuff. Never
once have I had an issue with the driver crashing, or munging data, or
being a general kludge. It has been effective.
4.
5. I use the driver's through other products. I don't program with them
directly. I'm not at that level of smart.

dvn - wish I could be greater help

On Wed, Apr 18, 2018 at 2:34 PM, Richard Hipp <[hidden email]> wrote:

> Are you using SQLite with JDBC?  If so, can you please answer a few
> questions below?
>
> You can post on this mailing list or send your reply directly to me.
>
> 1. Which JDBC are you using?
> 2. For how long have you been using it?
> 3. What issues you had with this driver?
> 4. What advise do you have for avoiding problems in this driver?
>
> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: JDBC driver experience

Shevek
In reply to this post by Richard Hipp-3
1. Xerial
2. A year or so, relatively heavily.

3.
  * MAX_MMAP_SIZE is too small by a few hundred megabytes. It's safe to
set to around a terabyte.
  * No way to effectively use multiple threads, even on a read-only
mmap'd database.
  * Planner sometimes misses plans - does it need better STAT features
enabled?

4. What immediately comes to mind:

For writing:
  * JournalMode MEMORY
  * SynchronousMode OFF
  * A couple of hundred meg of page cache
  * Use quite big transactions, but it doesn't really matter where the
boundaries are. You can do 50K+ writes per transaction.
  * Use Java-side locks.
  * Any data which doesn't need to be accessible to the relational
engine should be serialized into a BLOB with Kryo and Snappy. Even
simple text fields.


For reading:
  * mmap EVERYTHING
  * JournalMode OFF
  * It's MUCH faster to join in Java than in sqlite. If you're loading
data into memory, do an application-side hash-join.
  * Wrap all your queries in a performance rig and log slow queries.
  * Force the planner using the CROSS keyword for any query which is
ever slow.
  * Sometimes the planner changes the query plan based on e.g. the size
of an IN-list.
  * Use covering indexes, and use sqlite_analyzer to figure out which
ones are winning a space-time trade-off.
  * The partial covering index optimizer doesn't always account for
constants, and we lose a bunch of space because we have to include the
constant values in the index.


Overall:
  * Does anyone respond to issues?


We actually use nondeterministic transaction boundaries for writing
because it really doesn't matter where they are, it only matters that
you have one.

S.

On 04/18/2018 08:34 PM, Richard Hipp wrote:

> Are you using SQLite with JDBC?  If so, can you please answer a few
> questions below?
>
> You can post on this mailing list or send your reply directly to me.
>
> 1. Which JDBC are you using?
> 2. For how long have you been using it?
> 3. What issues you had with this driver?
> 4. What advise do you have for avoiding problems in this driver?
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
dmp
Reply | Threaded
Open this post in threaded view
|

Re: JDBC driver experience

dmp
In reply to this post by Richard Hipp-3
> Are you using SQLite with JDBC?  If so, can you please answer a few
> questions below?

> You can post on this mailing list or send your reply directly to me.

1. Which JDBC are you using?

Current Xerial SQLite JDBC
https://github.com/xerial/sqlite-jdbc

Others that I have tried are a Werner-JDBC and Zentus-JDBC. The
Xerial if I remember was derived from one of those. The most
current functional up to date is I believe Xerial.

2. For how long have you been using it?

My Java project, Ajqvue, began support for SQLite in 2014 and
that is when most of the research was done on finding a decent
functional JDBC.

3. What issues you had with this driver?

Ajqvue is a generic GUI for connecting to several databases to
include SQLite. That means I need to connect to a database and
display any table data and provide functionality for import/export
of data. The application also comes with plotting, analysis,
and profiling capabilities.

SQLite was one of the easiest database to support because it
only has four data types. Most of all my problems with JDBCs
have arrive with data types, mainly temporal ones, Date, Time,
and the dreaded Timestamp. Currently that is one of the main
issues I have with the current Xerial JDBC.

https://github.com/danap/ajqvue/issues/2

See:

https://github.com/xerial/sqlite-jdbc/issues
https://github.com/xerial/sqlite-jdbc/issues/88

Overall:
  * Does anyone respond to issues? :) Thats about it!

4. What advise do you have for avoiding problems in this driver?

Since my main experience with a driver is through a user GUI
and I'm not using in production I'm sure others could answer
this better. Currently I trying to complete a plugin for Ajqvue
that transfers data from others databases to SQLite. So I'm
sure I may come across some other discrepancies.

https://github.com/danap/db_to_filememorydb

From my perspective your database table schemas should be
based on the SQLite four data types, NO others. Of course
since I was instructed in structured programmer, I really
don't think a specified data type, can be any desired object
and the database really doesn't care. It really confuses me!

danap.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: JDBC driver experience

Eduardo Morras-2
On Thu, 19 Apr 2018 09:37:20 -0700
"dmp" <[hidden email]> wrote:

> Currently I trying to complete a plugin for Ajqvue
> that transfers data from others databases to SQLite.

Hi, I made an odbc virtual table to achive this. It's closed source but
the ¿difficulty? to develop one from scratch is easy-medium.

Pass the connection string, user and password, and all queries to vtab
are redirected to the other dbms. I began with the csv file virtual
table and went from there.

Check SQL-MED standard and postgresql docs for syntax.


---   ---
Eduardo Morras <[hidden email]>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: JDBC driver experience

Sylvain Pointeau
In reply to this post by Richard Hipp-3
Hi,

Le mer. 18 avr. 2018 à 21:34, Richard Hipp <[hidden email]> a écrit :

> Are you using SQLite with JDBC?  If so, can you please answer a few
> questions below?
>
> You can post on this mailing list or send your reply directly to me.
>
> 1. Which JDBC are you using?


I am using xerial and one from Christian Werner

>
> 2. For how long have you been using it?


since 3 years ago when I migrated to sqlite from H2

>
> 3. What issues you had with this driver?


the xerial is almost up to date with frequent release so ready to use.
(intellij uses xerial when querying a sqlite db)
however it does not give the possibility to use the SSE encryption module
and it found it impossible to compile myself.

when I though I will need to encrypt the database, I looked at the lib from
Christian Werner.
With his help I succeeded to compile it for mingw32 for windows (I then
proposed a CMake file to compile it cross plateform so easy to use the
latest sqlite release). At the end, I did not buy the SSE but I am happy
about my journey.


> 4. What advise do you have for avoiding problems in this driver?


I did not have issues with both of these driver. I trend to like more the
one from Christian Werner since I can compile it and use the most recent
sqlite version, as well as the option for the SSE (that I may buy one day)

Best regards,
Sylvain


>
> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: JDBC driver experience

Seiji Amalai
In reply to this post by Richard Hipp-3
no

On Wed, Apr 18, 2018 at 8:34 PM, Richard Hipp <[hidden email]> wrote:

> Are you using SQLite with JDBC?  If so, can you please answer a few
> questions below?
>
> You can post on this mailing list or send your reply directly to me.
>
> 1. Which JDBC are you using?
> 2. For how long have you been using it?
> 3. What issues you had with this driver?
> 4. What advise do you have for avoiding problems in this driver?
>
> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: JDBC driver experience

Gerry Snyder-4
In reply to this post by Richard Hipp-3
1.  I use an old version of xerial with Aejaks for an on-line voting system.
2.  I started around 2010, and updated only once, in order to get the
backup command (greatly simplifying my process).
3.  My only issue is paranoia about breaking something, which keeps me from
doing further updates.
4.  None. After learning how to use it I have had no problems.

Gerry Snyder

On Wed, Apr 18, 2018 at 12:34 PM, Richard Hipp <[hidden email]> wrote:

> Are you using SQLite with JDBC?  If so, can you please answer a few
> questions below?
>
> You can post on this mailing list or send your reply directly to me.
>
> 1. Which JDBC are you using?
> 2. For how long have you been using it?
> 3. What issues you had with this driver?
> 4. What advise do you have for avoiding problems in this driver?
>
> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: JDBC driver experience

Doug White
In reply to this post by Seiji Amalai
This writing is 4/22/2018.
I have been playing with the latest connect/j for android java downloaded
from Oracle's web site and have had no luck getting it to connect to the
latest Community version of MySQL on Windows 10 using Oracles example java
code... then I found an example that someone posted that does all of the
java calls from within a java thread and it works perfectly on several
types of SQL calls that I added to the code. As soon as I try to
incorporate the same code outside of a thread it won't connect. Has anyone
else had similar problems, solutions or ideas about what may be causing the
problem.  A working android studio code example would be wonderful to
analyze.
Doug White
[hidden email]

On Apr 22, 2018 12:42 PM, "Seiji Amalai" <[hidden email]> wrote:

no


On Wed, Apr 18, 2018 at 8:34 PM, Richard Hipp <[hidden email]> wrote:

> Are you using SQLite with JDBC?  If so, can you please answer a few
> questions below?
>
> You can post on this mailing list or send your reply directly to me.
>
> 1. Which JDBC are you using?
> 2. For how long have you been using it?
> 3. What issues you had with this driver?
> 4. What advise do you have for avoiding problems in this driver?
>
> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users