Prepared Statement Consistency

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Prepared Statement Consistency

Mitchell Keith Bloch
I have observed that preparing statements that depend on a table will fail
if the table does not currently exist. It appears that those prepared
statements will, however, continue to function if the table is dropped and
subsequently recreated. Will I find this behavior to be consistent if I
later switch to another SQL implementation? What should I read to know
what's standard SQL behavior, well defined SQLite behavior, or possibly a
quirk of rusqlite?

Thanks,
Mitchell
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Prepared Statement Consistency

Richard Hipp-3
On 8/19/19, Mitchell Keith Bloch <[hidden email]> wrote:
> I have observed that preparing statements that depend on a table will fail
> if the table does not currently exist. It appears that those prepared
> statements will, however, continue to function if the table is dropped and
> subsequently recreated. Will I find this behavior to be consistent if I
> later switch to another SQL implementation? What should I read to know
> what's standard SQL behavior, well defined SQLite behavior, or possibly a
> quirk of rusqlite?
>

All prepared statements stop working - in a sense - after every schema
change.  However, assuming you have used sqlite3_prepare_v2() or
_v3(), they will automatically rerun the SQL parser and code generator
and reprepare themselves when you attempt to run them.  Will the same
SQL statement continue to work after you DROP and then CREATE a table
it uses?  That depends, I suppose, one how the new CREATE differs from
the prior table that you DROP-ed.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Prepared Statement Consistency

Simon Slavin-3
In reply to this post by Mitchell Keith Bloch
On 19 Aug 2019, at 11:22pm, Mitchell Keith Bloch <[hidden email]> wrote:

> I have observed that preparing statements that depend on a table will fail if the table does not currently exist. It appears that those prepared statements will, however, continue to function if the table is dropped and subsequently recreated.

To expand a little on the previous answer, preparing a statement uses internal details of the tables it refers to.  The 'prepared' version doesn't just refer to entity names, it exploits information about how the table is organised.  So deleting a table invalidates all preparations which refer to it.

So deleting the table causes the prepared statement to become invalid (you might call this 'fail') too.  However, as DRH wrote, if you used sqlite3_prepare_v2() or _v3(), the next time the statement is used SQLite realises that the statement is no longer prepared and prepares it again.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users