Re: [sqlite] JDBC driver experience

[Doug White]

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
dglsn...@gmail.com

On Apr 22, 2018 12:42 PM, "Seiji Amalai"  wrote:

no


On Wed, Apr 18, 2018 at 8: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?
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] JDBC driver experience

[Gerry Snyder]

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  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
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] JDBC driver experience

[Seiji Amalai]

no

On Wed, Apr 18, 2018 at 8: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?
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] JDBC driver experience

[Sylvain Pointeau]

Hi,

Le mer. 18 avr. 2018 à 21:34, Richard Hipp  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
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] JDBC driver experience

[Eduardo Morras]

On Thu, 19 Apr 2018 09:37:20 -0700
"dmp"  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 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] JDBC driver experience

[dmp]

> 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] JDBC driver experience

[Shevek]

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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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  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
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users