tyut05000 / sqlite4java Goto Github PK

I wish to add type constraints to tables. The following CREATE TABLE SQL query 
with a CHECK is adapted from SQLite The Definitive Guide:

SQLiteConnection db = new SQLiteConnection();
db.open(true);
SQLiteStatement statement = db.prepare("CREATE TABLE MyTypes (s TEXT 
CHECK(typeof(s)='TEXT'));");
statement.step();
statement = db.prepare("INSERT INTO MyTypes VALUES ('Hello');");
statement.step();

It fails with the following SQLiteException:
[19] DB[1] step() [INSERT INTO MyTypes VALUES ('Hello');]DB[1][C] [constraint 
failed]

Is the exception expected?
Is there a better way to add type constraints?

Regards, Mal.

Supporting sqlite3_table_column_metadata can be very interesting.

sqlite4java.library.path doesn't seem to be set i had to set it to the current 
directory (".") before i could get my jar to run.

Why not have a sensible default?

This was most frustrating for me because i was working from an eclipse project 
and where it would work, but when i exported it, it didn't work.


Thanks for this project guys :)

It can be very useful to know the exact type of a column. Please change 
visibility of getColumnType to public.

Workaround: use reflection ;)

Currently, a crash is possible when two intarrays with the same name are 
created consequently - see LongArrayTests, method 
testCrashFollowingFailedCreate(). 

The whole organization of intarray extension should be reviewed and improved.

i was trying to use sqlite4java in android project. i don't know anything about 
gant so i ended up with this solution how to get both jar and needed native 
libraries

i create normaln android project and in its jni subdirectory i have content of 
attached archive

then i just run get.sh, it gets last sqlite4java sources from svn, builds it 
and then calls ndk-build to compile native version of sqlite4java.so library 
and place it where its needed

this works well, but it would be nicer if you would be able to integrate it 
somehow directly to gant scripts. please consider adding it there. (and if you 
don't like the idea then its ok for me too)

When i execute the program written in scala to retrieve all rows but it display 
the latest one inserted.

Can anybody help me how to rectify this.Or i want to do any changes?

while(1)
    {
        // fetch a row's status
        retval = sqlite3_step(stmt);

        if(retval == SQLITE_ROW)
        {
            // SQLITE_ROW means fetched a row

            // sqlite3_column_text returns a const void* , typecast it to const char*
            for(col=0 ; col<cols;col++)
            {
                const char *val = (const char*)sqlite3_column_text(stmt,col);
                printf("%s = %s\t",sqlite3_column_name(stmt,col),val);
            }
            printf("\n");
        }
        else if(retval == SQLITE_DONE)
        {
            // All rows finished
            printf("All rows fetched\n");
            break;
        }
        else
        {
            // Some error encountered
            printf("Some error encountered\n");
            return -1;
        }
    }

We have received a report that someone needs to run sqlite4java-based app on a 
Solaris workstation. 

Hello, 

I would like to know if you have the file .dll for win 64?

Thank you for your answer.

Hello,

I'm using sqlite on my linux system and loving it, but need to move it to an 
internet accessible machine for the moment, but that system runs FreeBSD. 
FreeBSD often can load a linux library ok, but the libsqlite4java.so gives an 
error about _x64.

I downloaded the source to rebuild the library on the FreeBSD machine, but 
haven't been able to recreate the right cc args and includes to get it done.

Has anyone had success or a makefile for building the lib on FreeBSD or some 
pointers?

Here's what I gleaned from the gant build, but it seems like I'm not getting 
the sqlite.h file included despite included.

gcc -O2 -DNDEBUG -fpic -Di586 -DARCH='"i586"' -DLINUX -D_LARGEFILE64_SOURCE 
-D_GNU_SOURCE \
  -D_LITTLE_ENDIAN -fno-omit-frame-pointer -fno-strict-aliasing -static-libgcc -I./sqlite -I/usr/local/diablo-jdk1.5.0/incl
ude \
  -I/usr/local/diablo-jdk1.5.0/include/freebsd/ intarray.c

It produces this output, all of which looks like sqlite.h to me;

/usr/lib/crt1.o(.text+0x72): In function `_start':
: undefined reference to `main'
/var/tmp//ccXHO69G.o(.text+0x46a): In function `create_vtable':
: undefined reference to `sqlite3_mprintf'
/var/tmp//ccXHO69G.o(.text+0x481): In function `create_vtable':
: undefined reference to `sqlite3_exec'

thanks,
andrew

Right now there's SQLiteStatement.loadLongs() which is a fast way to transfer 
one column of integer values from SQLite into Java. A generic method to 
transfer any type of data and in multiple columns is needed.

If the SQL query includes some non-ascii characters, the prepare method fails 
to translate them to the correct charset for sqlite.

The following test fails:

SQLiteStatement st = cnx.prepare("select '\uD840\uDC40';");
st.step();
Assert.assertEquals("\uD840\uDC40", st.columnString(0));

There is no problem with SQLiteStatement.bind(int, String). The problem seems 
to come from the JNI code (_SQLiteManualJNI.sqlite3_prepare_v2).

I attached an unit test.

I need to work with sqlite database from android.
I'm able to get data from the database.I need to use user version in someway 
with sqlite4java

Is there any way to set and get userversion?

Best Regards
Mikael Andersson

It seems that the code that searches for the JNI library always includes the 
architecture in the name string, thus it will always search for 
sqlite4java-osx-x86_64.jnilib or sqlite4java-osx-i386.jnilib, and will never 
match to the fat binary. On my (x86_64) machine this code always ends up using 
sqlite4java-osx-10.4.jnilib, which doesn't seem ideal.

I would suggest adding the following to collectBaseLibraryNames:
if ("osx".equals(os)) {
      r.add(base);
}

http://www.sqlite.org/threadsafe.html

Sqlite, by default, is compiled in "serialized mode".  This means that sqlite 
connections can be used by multiple threads with no restrictions.

I would like to get some discussion going to determine if it makes sense for 
this light SQLite wrapper to have similar multithreading capabilities.

Currently, SqliteConnection will ensure that all SQL that is executed will 
execute on the same thread that the connection was opened with.  This doesn't 
seem necessary.  Are these protections left over from previous versions of 
SQLite that were not threadsafe?  What dangers and pitfalls are there for 
allowing an SqliteConnection to be used by multiple threads, besides proper 
locking and maintenance of the state inside of the java object SqliteConnection?

I realize that there is already an SqliteQueue and SqliteJob class that helps 
users dedicate a thread to sqlite and queue jobs on it, but this is not always 
desired or convenient.

when I use sqlite4java, I gave a warning 
WARNING: [sqlite] SQLiteBridge$2@15d3388: job exception
com.almworks.sqlite4java.SQLiteException: [1] DB[2] exec() cannot commit - no 
transaction is active at 
com.almworks.sqlite4java.SQLiteConnection.throwResult(SQLiteConnection.java:1192
)  at com.almworks.sqlite4java.SQLiteConnection.exec(SQLiteConnection.java:418) 
at lab.bridge.SQLiteBridge$2.job(SQLiteBridge.java:71)
at com.almworks.sqlite4java.SQLiteJob.execute(SQLiteJob.java:372)
at com.almworks.sqlite4java.SQLiteQueue.executeJob(SQLiteQueue.java:534)
at om.almworks.sqlite4java.SQLiteQueue.queueFunction(SQLiteQueue.java:667)
at com.almworks.sqlite4java.SQLiteQueue.runQueue(SQLiteQueue.java:623)
at com.almworks.sqlite4java.SQLiteQueue.access$000(SQLiteQueue.java:77)
at com.almworks.sqlite4java.SQLiteQueue$1.run(SQLiteQueue.java:205)
at java.lang.Thread.run(Unknown Source)

Support sqlite3_wal_checkpoint and sqlite3_wal_checkpoint_v2 calls

http://sqlite.org/c3ref/wal_checkpoint.html
http://sqlite.org/c3ref/wal_checkpoint_v2.html

Would love to see this enhancement, I imagine small to make

Improve the message

SQLiteStatement.bind starts with 1, columnType, columnValue, etc. start with 0. 

The virtual table is not committed, and it is no longer visible in future 
transactions.

The SQLiteConnection class has a private open0 method that takes a flags 
parameter. These flags are passed to sqlite3_open_v2. But there is no public 
method to open a database providing some flags, like the thread-mode ones.

Can you add this method please?

I buildt RPMs of the native Linux libs for x86_64 and i386. They don't compile 
the source (because I couldn't find it), so they won't show up in the official 
Fedora repos. However, maybe a third-party repo will adopt them or other 
packagers will refer to them. It's at least a first step to spare the 
distribution of them with every single application using sqlite4java.

Since I'm not an official maintainer, careful people probably want to import my 
pubkey from sutor-it.com/0xA94EE37B.asc

regards.

Access violation/segmentation fault is possible in the following scenario:

1. T1: open connection
2. T1: start any method that use sqlite3* handle
3. T1: method checks handle/state for validity, releases lock
4. T2: dispose is called, handle is now invalid
5. T1: method uses now-invalid handle - crash

SQLite provides "Online Backup API" for the incremental copying of the database 
into another database. Support this with sqlite4java.

Java: 1.6.0_07 10.0-b23 The FreeBSD Foundation Diablo Java HotSpot(TM) Server VM
OS: FreeBSD 8.1-STABLE unknown, i386 32 little, 2 cpu

I've just begun to investigate using sqlite4java as a database backend for a 
web application.

I noticed in shared cache mode that I was getting a lot SQLITE_LOCKED errors.

I realized I needed sqlite3_unlock_notify() to implement blocking prepare() and 
step() and that sqlite4java hadn't implemented it yet.

So here's a patch for it!

I implemented it based on the documentation/code presented at 
http://www.sqlite.org/unlock_notify.html.

Here's how to enable blocking prepare() and step() (I added Javadoc documention 
too):

SQLiteConnection db = new SQLiteConnection(new File("test.db"));

// must be in shared cache mode to use unlock notify blocking
db.openV2(SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_SHAREDCACHE);

// call setBlocking(true) to turn on blocking of prepare() and step()
db.setBlocking(true);

// prepare() and step() will now attempt to block when locked in accordance 
with sqlite3_unlock_notify() documentation

I have tested on my web application in a test environment and I no longer get 
the SQLITE_LOCKED (or SQLITE_LOCKED_SHARECACHE) errors so it's looking good, 
but granted it hasn't been battle-tested yet in any kind of high-traffic 
production environment.

It's my hope you can merge this into the mainline after reviewing it.

When packaging you jar file, make an OSGI bundle of it

I'm not sure if I'm doing something wrong, but I had an issue using prepared 
statements to do lots of insert operations.
I was using the following code to do the inserts :
void Insert(SQLiteConnection db){
...
String sql = "INSERT INTO `" + tablename
        + "` VALUES (" + this.time + "," + this.nodeID + values + ")";

SQLiteStatement st = db.prepare(sql);
st.step();
st.dispose();
}

Surrounded by a db.exec("BEGIN IMMEDIATE TRANSACTION"); and after a few 
thousand inserts a db.exec("COMMIT TRANSACTION");

After a while (maybe 500,000 inserts), the SQLite.getMemoryUsed() went through 
the roof - multiple gigabytes, and the JVM got killed.
I've tried setting SQLite.softHeapLimit(HEAP_LIMIT) and even 
SQLite.releaseMemory(), but got 0 effect (No matter how much i asked to 
released, never released more then a few KB).

Now the weird part : when I changed my inserts from db.prepare->step->dispose 
into db.exec(sql), memory usage never gets over a few MB!
According to http://old.nabble.com/Memory-Usage-td23154686.html it could be 
because sqlite3_finalize() is not called on every statement. However, I 
couldn't find out how to do that with SQLStatement.

Build binaries for Linux/ppc platform

Build a binary for Android Intel-based architecture.

Quote from #29:
Any chance to support non-ARM Android platforms, such as GoogleTV? Here's the 
error I'm getting when deploying my test app on GoogleTV:

com.almworks.sqlite4java.SQLiteException: [-91] cannot load library: 
java.lang.UnsatisfiedLinkError: Couldn't load sqlite4java-android-i686: 
findLibrary returned null
  at com.almworks.sqlite4java.SQLite.loadLibrary(SQLite.java:97)
  at com.almworks.sqlite4java.SQLiteConnection.open0(SQLiteConnection.java:1314)
  at com.almworks.sqlite4java.SQLiteConnection.open(SQLiteConnection.java:258)
  at com.robert.Test.<init>(Test.java:110)
Caused by: java.lang.UnsatisfiedLinkError: Couldn't load 
sqlite4java-android-i686: findLibrary returned null
  at java.lang.Runtime.loadLibrary(Runtime.java:425)
  at java.lang.System.loadLibrary(System.java:554)
  at com.almworks.sqlite4java.Internal.tryLoadFromSystemPath(Internal.java:349)
  at com.almworks.sqlite4java.Internal.loadLibraryX(Internal.java:124)
  at com.almworks.sqlite4java.SQLite.loadLibrary(SQLite.java:95)
  ... 8 more

Hi!
I am using Netbeans on a x64 platform using the latest sqlite4java-213.jar.

There was no error when compiling but when running, this error prompted.

[-93] cannot load library: java.lang.UnsatisfiedLinkError: no 
sqlite4java-linux-amd64-213 in java.library.path

Please advise.

Thanks!

I have downloaded sqlite4java to my Windows machine to try it out.

The getting Started example uses db.close() method, but close() does not exist 
in class SQLiteConnection.

I wish to close the database and write it out to disk as an sqlite file.
How should I do that?

Regards, Mal.

Hi,
I think support of the Simple Logging Facade for Java (SLF4J)[1] would be 
really cool since it helps you to integrate sqlite4java in existing projects. 
If I saw it right there would only a few changes in Internal.java have to be 
made, correct? 

Is there interest in it or any reasons against it?

Thanks for opinions!




[1] http://slf4j.org/

After some quick promising test I decided to use SQLiteDB via sqlite4java 
(0.213) for my project. I designed my own DB-independant ORM API which wrap 
sqlite4java API, but when I run my big JUnit It fails because of unexpected 
values return by SQLiteStatement.columnXXX(int) methods.

Actually, to make investigations I wrapped SQLiteStatement class under a custom 
class which implements columnObject(int) method like this :

private static final int LOOP_BREAK = 0;
public Object columnValue(int column) throws SQLiteException
{ 
    Object o = null;
    for(int i=0; i < 10; ++i)
    {
        // stmnt is the actual wrapped SQLiteStatement object.
        o = stmnt.columnValue(column);
        if (o != null)
        {
            if (i > LOOP_BREAK)
            {
                throw new Error("After "+i+" loops finally got a value.");
            }
            break;
        }
    }

    return o;
}

My JUnit make a lot of request and columnValue(int) is successfully called a 
lot of time. Yet it always happen to fail at some random point.

My project use severals instances of SQLiteQueue which work on separate DB 
files. I wrap the sqlite4java api so each DB access create a new SQLiteJob sent 
to the queue and block untill completion. It is also checked that no DB calls 
can be made (new SQLiteJob sent to the queue and joined) if already in the DB 
thread (i.e. from a currently running SQLiteJob), cause that will obviously 
make a dead-lock.

I also encapsulate my DB class so it is serializable (copy/restore attached 
file). But It doesn't seem to cause any problem.

PS: I did not try to study the library sources yet.

Any idea ?

http://code.google.com/p/space-empire-pulsar/

See if sqlite4java works normally with Java 1.7 on the supported platforms

I used the procedure described by 
http://code.google.com/p/sqlite4java/issues/detail?id=29 and I am able to run 
this sqlite wrapper on Android. Which is good!

However, I am having a very strange (to me at least) problem.

My test involves identical databases with BLOB content.... there are 2 attached 
DBs with the same structure ... they have a table named "media" with just 2 
fields, name (text) and content (BLOB). The BLOB content is 1MB worth of binary 
data.

The 2 tables have one row each only.

The query is very simple:

SELECT * FROM main.media WHERE main.media.name='1024k_02_0.in' UNION SELECT * 
FROM attached_1.media WHERE attached_1.media.name='1024k_22_1.in'

where "attached_1" is the alias I used when attaching the second database to 
the main one.

I'm doing SQLiteConnection.prepare() then I'm calling step() from the resulted 
SQLiteStatement that's all.

Strangely, step() returns a vague error [14] "unable to open database file".

If I repeat the same thing using the sqlite3 tool via adb shell, it works fine 
(no errors).

The exact same code runs fine on Linux. The exact same code runs fine if the 
BLOB content stored by the database tables is less than 1MB (works with no 
errors if it's 200KB or so).

I am using the 2 attached databases in other queries and they all work okay. 
The problem is when the query uses the tables that have the large BLOB content.

Hello.

I tried to use sqlite4java with maven build, but I can't find maven repository 
that provides it.

Do you have a plan to regist sqlite4java into any known maven repository?

Thank you.

When you use columnValue method to retrieve an Integer, you obtain a Long. Here 
is a small test case:

SQLiteStatement st = cnx.prepare("select 1;");
st.step();
Assert.assertEquals(Integer.class, st.columnValue(0).getClass());

The problem comes from SQLiteStatement.java, line 1044:

return value == ((long) ((int) value)) ? Integer.valueOf((int) value) : 
Long.valueOf(value);

This instruction works fine unless you mix Integer, Byte, Short, Long, Float or 
Double value as the result of the inline condition. For example, the following 
inline condition:

<cond> ? new Integer(1) : new String()

has a type of Object: the common base class of Integer and String. But the 
following condition:

<cond> ? new Integer(1) : new Long(1)

has a type of Long. This is because a promotion occurs on Integer and it 
becomes a Long.

you must use standard condition like:

if (value == (int)value) {
  return Integer.valueOf(value);
}
return Long.valueOf(value);

It works fine for me.

Big Decimal data type is not supported in SQLITEStatement. Because we are not 
able to set and fetch Big Decimal types. Can we add this support to sqlite4Java?

This looks very promising, yet I have no concrete, implementable idea on how to 
"get started." I might just be ignorant, but maybe a simple "hello world" 
database creating lesson might do the trick.

Add Linux/ARMv5 build to the base package, following instructions committed 
into /ant directory

Here is a nonsensical code example I just made up:

int getQuantity(int orderId) {
  SQLiteStatement st = db.prepare("SELECT quantity FROM orders WHERE order_id = " + orderId);
    try {
      while (st.step()) {
        return st.columnLong(0);
      }
    } finally {
      st.dispose();
    }
}

for(int orderId = 0; orderId <= 1000000; orderId++) {
  int quantity = getQuantity(orderId);
  ...
}

Every time getQuantity is called, a new statement is prepared and disposed.  
The problem is that when each statement is disposed, since each statement is 
different, a new entry is added to SqliteConnection.myStatementCache.  This 
will grow absolutely unbounded until it causes a Java Heap Space exception or 
until the connection is closed.

Now, I realize this code is bad:
1.  Using the same prepared statement would be much more efficient.
2.  In general, you shouldn't concatenate your own strings to form SQL, you 
should use bindings.

However, I would not expect it to use 100MB of memory and cause a Java Heap 
Space exception.

So here are my proposed fixes:
1.  Get rid of the statement cache.  Do we really need a statement cache?  Is 
this just to help people out that don't want to reuse their prepared 
statements?  I would have just expected that if I call .prepare(...) multiple 
times, that I would actually have to prepare again.  Would people really feel 
the performance hit if the cache was removed?

2.  Set a limit on the statement cache.  What should the limit be?  How would 
you pick one to remove from the cache when it's full?

I'll write a patch if we get some discussion going on what needs to be done.

Thanks,
Brian Vincent

SQLiteConnection can be used only from the thread that opened it. To make it 
easier to use in a multi-threaded environment, a basic job queue class could be 
added to the library.

dyld: lazy symbol binding failed: Symbol not found:
_pthread_mutexattr_destroy$UNIX2003
  Referenced from: /private/tmp/sqlitejdbc36941.jnilib
   Expected in: /usr/lib/libSystem.B.dylib

 dyld: Symbol not found: _pthread_mutexattr_destroy$UNIX2003
   Referenced from: /private/tmp/sqlitejdbc36941.jnilib
   Expected in: /usr/lib/libSystem.B.dylib


I use version sqlite4java-175 on Mac OS X 10.4.11

The column count is set to 0 when a select statement returns no row. This is 
not the good value. Here is a use case (with empty database):
  - create table t (c text);
    * step returns false
    * columnCount returns 0
  - select * from sqlite_master
    * step returns true (one row for table t)
    * columnCount returns 5
  - select * from sqlite_master where name='not_exists'
    * step returns false
    * columnCount returns 0 but 5 was expected

This bug comes from two places:
  - how SQLiteStatement.step() handle SQLITE_DONE
  - the use a sqlite3_data_count

The step method sets myColumnCount to COLUMN_COUNT_UNKOWN when sqlite returns 
SQLITE_ROW and 0 when it returns SQLITE_DONE. But a select without row return 
SQLITE_DONE. So, line 322 of SQLiteStatement must be changed to:

myColumnCount = COLUMN_COUNT_UNKNOWN

(and maybe some code refractoring can be done).

The SQLiteStatement.ensureCorrectColumnCount(...) method use 
sqlite3_data_count. It returns the number of data in the result. If no result 
(no row) is returned, it returns 0. According to the following comment is 
sqlite3.c, sqlite3_column_count will returns the right column count:

** Set the number of result columns that will be returned by this SQL
** statement. This is now set at compile time, rather than during
** execution of the vdbe program so that sqlite3_column_count() can
** be called on an SQL statement before sqlite3_step().

So, line 1228 must be changed to:

myColumnCount = _SQLiteSwigged.sqlite3_column_count(handle);

I called sqlite3_column_count using reflection and obtained the right value.

A unit test is attached to this issue.

Enable R-tree in the compilation options

I have an android 2.1 app and a separate JRE 1.6 jar library with core methods 
of the app, compatible with other java apps. The core uses sqlite4java.

When I use the core jar with a JRE 1.7 java app, sqlite4java works fine, 
loading sqlite4java-win32-x86.dll on W7 environment.

When I try it with an Android app, it complains of "Couldn't load 
sqlite4java-android-armv6l". If I look at the libraries that sqlite4java comes 
with, there is libsqlite4java-android-armv7.so but not any one reffering to 
armv6l.

Am I doing something wrong?

Currently discussion take place in a forum. Using Google groups, it would be 
more easier (at least for me) because we don't have to create a new account on 
another forum.

See http://sqlite.org/c3ref/load_extension.html