Description
Description
In GH-6805, I implemented liveness checks for PDO_ODBC, like procedural ODBC and other PDO drivers. I had used the same technique that procedural ODBC does of SQLGetInfo(SQL_DATA_SOURCE_READ_ONLY), but I suspect this is not the best idea in retrospect for either ODBC driver.
The reason why I mention this is because it seemed the proper ODBC facility for this, SQL_ATTR_CONNECTION_DEAD (introduced in a recent-ish version of ODBC), seemed to have spotty support. The driver I support for my users (Db2 for i) didn't seem to support it at that time. This meant we had to use this method, which isn't semantically correct (so there are no guarantees it works like we're using it), but seemed to work.
The problem now is that a new version of the Db2 for i driver both now supports this and caches the SQL_DATA_SOURCE_READ_ONLY value, making it useless for determining if a connection is alive. However, I don't want to be selfish here by only caring about the driver I have to deal with. There are other drivers we should be considerate about, though it's hard since their behaviour and ODBC version levels aren't really well documented, especially for closed-source ones.
I wonder if the best course of action would be to try SQL_ATTR_CONNECTION_DEAD, then fall back to the previous strategy, which seems to work OK for most drivers.
However, I don't want to create a mess like ibm_db2 has, where there are multiple strategies the user picks from, some of which won't work anymore. It would be a massive cognitive burden on the user. Using different strategies transparently may not work either - what if one hangs because the connection is dead? That, and the performance impact may cancel out the benefits of things like persistent connections.
I'm raising this as a discussion here, since I think @cmb69 has a lot more experience with other drivers, as well as what could be an appropriate solution towards liveness checks that work consistently.