SQLiteStatement return unexpected values.

[Former user]

Originally reported on Google Code with ID 27 ``` 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/

```

Reported by `escallier.pierre` on 2011-05-21 16:34:19

[Former user]

``` 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 :( ```

Reported by `escallier.pierre` on 2011-05-21 16:40:40

[Former user]

``` 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 ```

Reported by `[e-mail]` on 2011-05-21 22:16:12

[Former user]

``` 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). ```

Reported by `escallier.pierre` on 2011-05-25 11:59:45

[Igor Sereda]

```

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.

```

Reported by `sereda` on 2011-05-25 12:17:39

[Former user]

``` 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 ? ```

Reported by `escallier.pierre` on 2011-05-27 10:29:12

[Igor Sereda]

``` 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.html

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.

```

Reported by `sereda` on 2011-05-27 11:02:12

[Igor Sereda]

Reported by `sereda` on 2012-03-02 16:34:33 - Status changed: `Invalid`