rbock
How to clean up & and recreate prepared statements?
How do you tidy up after & fix a dead prepared statement?
You have a statement you reuse a bunch:
thread_local auto prepared = conn->prepare(
select(player_tbl.name).where(player_tbl.id == parameter(player_tbl.id)));
Then your database is rebooted.
That statement will now throw No result set.
I'd like some way of robustly handling prepared statements where if they're used after the original connection has closed it'll be cleaned up (if needed) & a new one prepared.
Is there any nice way to do this?
I can't see any ways to check it the handle is still valid or even access the handle.
It looks like the only way to know if it's valid still is to try & run it? Which makes handling it very tricky.
Good question! I looked briefly at the MySQL documentation and did not find a good way of checking.
Thus, a validity check would be connector-specific (at best). If a specific database allows checks, then it should be possible to add a wrapper to the respective connector.
Seems like it. I couldn't figure out a great way of handling this (for postgres) but I've made up a quick hack that might work:
template<typename Query>
class RobustPrepared {
Query const & preparable_query;
public:
RobustPrepared(Query const & query) : preparable_query{query} {};
template<typename Connection>
auto& prepare(Connection & conn) {
thread_local auto prepared = conn->prepare(preparable_query);
return prepared;
}
auto operator() (auto & conn) {
auto& prepared = prepare(conn);
try {
return conn(prepared);
} catch (sqlpp::postgresql::failure const & e) {
auto old_params = prepared.params;
prepared = conn->prepare(preparable_query);
prepared.params = old_params;
return conn(prepared);
}
};
};
Used like:
static RobustPrepared rp(
select(player_tbl.name).where(player_tbl.id == parameter(player_tbl.id)));
auto& prepared = rp.prepare(conn);
auto results = rp(conn);
Not really getting any luck with it sadly. Still throws the same error & I'm not sure why.
Well it sort of works but if I run an old prepared statement after reconnecting to the database my app seg faults (sometimes) as the old prepared statement references the now deleted connection (I think). Not sure how to handle that.
Looks like I'll need to fork the connector to make this work.
Though I'm not sure how to fix it really.
What does conn->prepare really return? I need to add a method to check if it's connection is current (as the easiest way to start handle things). @rbock
It depends on the connector. In the case of Postgresql, prepared statements are identified by a name. The prepared statement therefore is mostly a reference to the connection and a name.
You would need to change prepared_statement_t in the postgresql connector library.
Thanks, I've got something that seems to work now:
template<typename Query>
class RobustPrepared {
Query const & preparable_query;
public:
RobustPrepared(Query const & query) : preparable_query{query} {};
template<typename Connection>
auto& prepare(Connection & conn) {
thread_local auto prepared = conn->prepare(preparable_query);
return prepared;
}
void reprepare(auto & conn) {
auto& prepared = prepare(conn);
auto old_params = prepared.params;
prepared = conn->prepare(preparable_query);
prepared.params = old_params;
}
auto operator() (auto & conn) {
auto& prepared = prepare(conn);
if (!prepared._prepared_statement.is_valid_for_connection(*conn)) {
prepared._prepared_statement.invalidate_connection();
reprepare(conn);
}
try {
return conn(prepared);
} catch (sqlpp::exception const & e) {
reprepare(conn);
return conn(prepared);
}
};
};
I just needed to add in is_valid_for_connection & invalidate_connection that allows me to check & safely dispose of old statements. Feels a little icky but works.