crawshaw / sqlitejdbc Goto Github PK

I have the following setup:
1 JAVA Program using your SQLite driver, this communicates with two databases.
One is the settings database(settings.sqlite) the other is the work database.
In the work database I can add rows/update/delete without problems.
When using the settings database I can do the same, but whenever I restart my application all settings are lost.

When I try to use 'autocommit' (before the execute it is set to false and afterwards it is set to true I get the following error:
java.sql.SQLException: SQL logic error or missing database

SQLite datatypes says: (http://www.sqlite.org/datatype3.html#datetime)

INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC

But ResultSet.getDate() treats the database stored value as the number of milliseconds since the Unix epoch.

From http://www.sqlite.org/datatype3.html#datetime:

SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

• TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
• REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
• INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.

When ResultSet.getDate() is called, sqlitejdbc appears to assume the format is the INTEGER UnixTime.

It should also handle the standard ISO8601 format if the date is stored as TEXT as an ISO8601 string. (And probably the REAL option for storing dates, though I've never heard of anyone using that.)

Currently the driver doesn't seem to have any way to handle BLOB's, as both getBlob() and getBinaryStream() are unsupported

I set auto commit disable using

connection.setAutoCommit(false);

then, I commit manually

connection.setAutoCommit(true);

but not get it.

java.sql.SQLException: SQL logic error or missing database
    at org.sqlite.DB.throwex(DB.java:288)
    at org.sqlite.DB.exec(DB.java:68)
    at org.sqlite.Conn.commit(Conn.java:172)
    at database.Database.insert(Database.java:93)
    at junit.DatabaseTest.main(DatabaseTest.java:32)

This isn't a "bug", it's more of an enhancement request.

I'm working [1] on making sqlite one of the supported databases in ActiveObjects [2]. Currently I'm forced to write my own wrapper classes that delegate to your implementations of RS, PrepStmt because they aren't public.

If the classes were public, extensions would be much cleaner.

[1] https://bitbucket.org/lvijay/activeobjects-sqlite
[2] http://java.net/projects/activeobjects/pages/Home

I've noticed that DatabaseMetaData getTables method returns its TABLE_NAME as upper cased:

+ " upper(name) as TABLE_NAME,"

This isn't correct from my understanding and no other driver I know do it. Is there a reason for this behavior?

Need to add additional hook for calling sqlite3_limit, this would be very helpful to help set run time limits

It would be nice to see support added for easily executing .sql files. For example, the Web Exploitation Framework implements the 'jdbc/sqlite3' library. We have a module that is intended to load and execute .sql files. From what I noticed, there isn't support for such. My workaround for this situation bypasses the library directly and uses shell execution to get the job done.

res = `cat #{ModDatumDb}fingerprint_schemas/#{filename} | sqlite3 #{@db}`

I'm not happy with this solution. If anyone has any suggestions about how to improve this please let me know!