SQLiteStatement return unexpected values.

[GoogleCodeExporter]

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/

Original issue reported on code.google.com by escallie...@gmail.com on 21 May 2011 at 4:34

[GoogleCodeExporter]

Forgot to explain my code.
So I noticed that even if a first call to columnObject(int) returned a null 
value, when the value is not a true null value, a second call do return the 
expected value.
So I wrapped columnObject method into a loop that try 10 times to call 
columnObject in case of null value.
The throw Error case is not supposed to be possible. However, it do throw :(

Original comment by escallie...@gmail.com on 21 May 2011 at 4:40

[GoogleCodeExporter]

My first suggestion is to check the contracts of the SQLite methods. 
columnXXX() methods are directly mapped into SQLite methods - see 
http://sqlite.org/c3ref/column_blob.html

For example, it says "If the SQL statement does not currently point to a valid 
row, or if the column index is out of range, the result is undefined."

Secondly, if you know the type of values that are stored in the column, I'd use 
columnString or other column... method to look if that result is consistent. 

If you find that this does not help - could you please post code that 
reproduces this problem?

Thanks!
Igor

Original comment by ser...@almworks.com on 21 May 2011 at 10:16

[GoogleCodeExporter]

The statement is supposed to point to a valid row, and there is no problem with 
the column index. As a second call to the same method (with no other operation 
on the statement between both calls, as you can see the loop) return a value, 
it can be seen as a proof there is no problem with the request nor column.
I noticed the same bug when I tried to replace columnObject by columnString on 
a text row.
I'll try to make a reduced JUnit depending on smaller amount of code, but I'm 
not sure if I'll be able to reproduce the bug.

By the way, I use to return the SQLiteStatement out of the SQLiteQueue (so, out 
of its thread, and then just read the values), can It be a problem ? Can the 
SQLiteQueue corrupt my SQLiteStatement object by any mean ? (Assuming my code 
does not post new jobs).

Original comment by escallie...@gmail.com on 25 May 2011 at 11:59

[GoogleCodeExporter]

> By the way, I use to return the SQLiteStatement out 
> of the SQLiteQueue (so, out of its thread, and then 
> just read the values), can It be a problem ?

Yes it can - you need to work with SQLiteStatement in a single thread. You need 
to read out data in the same thread, in the same SQLiteJob, if you use it, and 
immediately after you do step(). If you manage to read data in a different 
thread - this is strange, because sqlite4java should throw an exception.

Original comment by ser...@gmail.com on 25 May 2011 at 12:17

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

Well, I do a lot of things like this :

[code]
// Somewhere in my code..
final String sql = "SELECT * FROM table WHERE id='%s' AND criterium='%s'";
final Object params[] = new Object[] {"abc", "def"};

SQLiteStatement stmnt = sqliteQueue.execute(new SQLiteJob<SQLiteStatement>()
{
    @Override
    protected SQLiteStatement job(SQLiteConnection connection) throws Throwable
    {
        String prep = String.format(Locale.UK, sql, params);
        try
        {
            return connection.prepare(prep);
        }
        catch(Throwable t)
        {
            log.log(Level.SEVERE, "SQL error:\n"+prep, t);
            throw t;
        }
    }
}).complete();

// work with my statement (no more request needed, just step through and 
read/copy the values)
[/code]

Is sqlite4java supposed to throw exception with this code ?

Original comment by escallie...@gmail.com on 27 May 2011 at 10:29

[GoogleCodeExporter]

Yes - I'm surprised it works at all. We need to improve thread checking.

For a correct usage of the queue, please see the example at 
http://almworks.com/sqlite4java/javadoc/com/almworks/sqlite4java/SQLiteQueue.htm
l

Note that SQLiteConnection and any SQLiteStatements must not and do not escape 
the boundaries of SQLiteJob. You need to work with your statement within the 
job, then dispose it.

Original comment by ser...@gmail.com on 27 May 2011 at 11:02

[GoogleCodeExporter]

Original comment by ser...@gmail.com on 2 Mar 2012 at 4:34

• Changed state: Invalid