Unable to reuse SQLiteStatement after error

[Former user]

Originally reported on Google Code with ID 67

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?

Reported by John.PV.Fors on 2014-08-28 20:10:26

[Former user]

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

Reported by [e-mail] on 2014-08-29 04:53:47 - Status changed: Accepted - Labels added: Priority-High - Labels removed: Priority-Medium

[Former user]

This issue was closed by revision r333.

Reported by [e-mail] on 2014-09-20 12:50:17 - Status changed: Fixed