Unable to reuse SQLiteStatement after error

[GoogleCodeExporter]

The SQLiteStatement can't be reused efter an error during step().

Normally it works fine:

SQLiteStatement stmt = connection.prepare("INSERT INTO mytable VALUES (?,?,?)", 
false);
...
stmt.bind(1, arg1);
stmt.bind(2, arg2);
stmt.bind(3, arg3);

stmt.step();
stmt.reset(true);

But when I catch this error: "DB[1] step() [INSERT INTO mytable VALUES 
(?,?,?)]DB[1][U] [column Logtime is not unique]"
(Logtime = arg1) and try to bind a new argument to the statement also causes an 
error

    SQLiteStatement stmt = connection.prepare("INSERT INTO mytable VALUES (?,?,?)", false);
...
try
{
    stmt.bind(1, arg1);
    stmt.bind(2, arg2);
    stmt.bind(3, arg3);

    stmt.step();
    stmt.reset(true);
}
catch(SQLiteException sqle)
{
    stmt.cancel();
    stmt.clearBindings();
    stmt.reset(false);    // This throws nearly the same exception : "DB[1] reset() [INSERT INTO jt_error_log VALUES (?,?,?)]DB[1][U] [column Logtime is not unique]"
    //If I don't do a reset and try to do stmt.bind, that throws an exception as well.
}

After reading the Javadoc I thought this would be possible:

    SQLiteStatement stmt = connection.prepare("INSERT INTO mytable VALUES (?,?,?)", false);
...
try
{
    stmt.bind(1, arg1);
    stmt.bind(2, arg2);
    stmt.bind(3, arg3);

    stmt.step();
    stmt.reset(true);
}
catch(SQLiteException sqle)
{
    try
    {
        stmt.cancel();
        stmt.clearBindings();
//-     stmt.reset(false);  // This should not be necessary.
        stmt.bind(1, new_arg1)   (new_arg1 != arg1)
        stmt.bind(2, arg2);
        stmt.bind(3, arg3);

        stmt.step();
        stmt.reset(true);
    }
    catch(SQLiteException sqle2)
    {
        sqle2.printStackTrace();
    }
} // end try - catch

But this is not possible (with or without the reset()), should it not work??
Am I forced to dispose the statement and prepare a new one after a step-call 
that generate 'column not unique'-error?

Original issue reported on code.google.com by John.PV....@gmail.com on 28 Aug 2014 at 8:10

[GoogleCodeExporter]

Hi John, thanks!

Indeed, this seems to be a bug. In SQLiteStatement.reset(boolean), the return 
value from sqlite3_reset is treated as an error code and causes an exception to 
be thrown and the rest of the state not cleared; while in fact sqlite3_reset 
returns the last error from sqlite3_step, according to 
http://www.sqlite.org/c3ref/reset.html

We'll fix it in the next version, which is planned for delivery soon. Meanwhile 
I'm afraid the only workaround is to dispose and create the SQLiteStatement 
object anew. Or, if you feel like hacking, calling reset(), catching the error, 
and then using reflection to clear SQLiteStatement's internal state manually 
like it's done in reset() method.

Sorry about this.

Kind regards,
Igor

Original comment by ser...@almworks.com on 29 Aug 2014 at 4:53

• Changed state: Accepted
• Added labels: Priority-High
• Removed labels: Priority-Medium

[GoogleCodeExporter]

This issue was closed by revision r333.

Original comment by evj...@almworks.com on 20 Sep 2014 at 12:50

• Changed state: Fixed