Wt

Hi!

By the moment, the reconnection to DB server after connection is lost implemented for Postgres and MySql backends.

1) Is there a special reason that for MS SQL backend this functional is not implemented?

2) Is it acceptable to implement checkConnection function like below and call it in functions: executeSql, startTransaction, commitTransaction, rollbackTransaction, MSSQLServerStatement constructor, MSSQLServerStatement::execute (similar to MySql backend approach)?

void Wt::Dbo::backend::MSSQLServer::checkConnection()
{
    if (!impl_ || !impl_->env || !impl_->dbc)
        return;

    try
    {
        // ping MS SQL Server by execution 'SELECT 1' query
        SQLHSTMT s;
        SQLRETURN rc = SQLAllocHandle(SQL_HANDLE_STMT, impl_->dbc, &s);
        handleErr(SQL_HANDLE_DBC, impl_->dbc, rc);
        SQLWCHAR wstr[] = L"SELECT 1";
        rc = SQLPrepareW(s, wstr, 8);
        handleErr(SQL_HANDLE_STMT, s, rc);

        rc = SQLExecute(s);
        SQLFreeStmt(s, SQL_CLOSE);
        SQLFreeHandle(SQL_HANDLE_STMT, s);
        handleErr(SQL_HANDLE_STMT, s, rc);
    }
    catch (MSSQLServerException& )
    {
        try
        {
            // do reconnect
            clearStatementCache();
            impl_->reset(); // free SQLHENV env, SQLHDBC dbc, SQLHSTMT stmt
            impl_->connect();
        }
        catch(MSSQLServerException& e)
        {
            throw MSSQLServerException("checkConnection: Error when reconnecting: " + std::string(e.what()));
        }
    }
}

Remark: 'SELECT 1' query chosen because of https://stackoverflow.com/questions/2440060/whats-the-best-way-to-test-sql-server-connection-programmatically

3) Checking the connection is alive for Postgres backend seems to be a bit incorrect:

  if (PQstatus(conn_) != CONNECTION_OK)  {
    LOG_WARN("connection lost to server, trying to reconnect...");
    if (!reconnect()) {
      throw PostgresException("Could not reconnect to server...");
    }
  }

because of this may require to call pg_ping (see https://stackoverflow.com/questions/14294696/postgresql-pqstatus-always-return-connection-ok)