Re: [Cppcms-users] Cppcms-users] cppdb: test connect to database and reconnection

[Artyom <art...@ya...>]

>Hello.
>
>I can't find a method to test database connection. I think there is no such 
>method (Similarly, there is no such method in SOCI).
>
>I think this method would be useful for connection error handling and 
>robustness.

The simplest way to do this would be 

    int value;
    sql << "SELECT 1" >> cppdb::row >> value;

The problem is that usually you do not want to "ping" a server
for each query. The reason is round trip cost. So if you query 
the database every second you probably don't need this, and if
you idle for hours you probably not need to keep the idle connection
opened.

cppdb allows you use connection pooling.

Each connection in pool has its maximal idle timeout so if you
fetch a connection from the pool and it was idle for more
then certain period of time it would just be closed and
a new one opened for you.

So basically what you need it following: every time you need
a connection you just open it.

However if you specify option @pool_size=8 it would keep up to
8 open connections and each time you request it it would give you
an opened connection from pool. If the connection was idle
for too long time (10 min by default) it would be closed
and new connection would be opened for you.

For details see: http://art-blog.no-ip.info/sql/cppdb/connstr.html

For example:

    void some_func()
    {
        session sql("mysql:user=foo;password=bar;@pool_size=5");
        sql << "SELECT foo FROM bar";
        ...
    }

Thats it, all timeouts would be handled for you.
If you want to explicitly close all idle connections 
and not close them of attempt to connect.
you may use void cppdb::connections_manager::gc()

>
>Another issue related: When connection is lost to database and we reconnect 
>later, we need to re build all prepared statements. 
>
>
>The ideal would have a statement object to be rebuilt when its associated 
>session is reconnected.
>


When connection is closed all prepared statements are destroyed.
They would be prepared upon first request automatically.

Of course you should **not** keep them on your own, i.e.:

   class foo {
       foo()
       { 
         sql("mysql:...");
         my_insert = sql.prepare("INSERT ...");
       }
       void insert() {
         my_insert.bind(...);
         my_insert.exec() 
       }        
       session sql;
       statement my_insert;
   }

But rather access them as usually,

   class foo {
       foo()
       { 
          conn_str = "mysql:...;@pool_size=5"
       }
       void insert() {
         session sql(conn_str);
         sql << "INSERT ..." << x << exec;
       }
       std::string conn_str;
   }
  



> For example in mysql by default the server closes the connection after eight 
> hours if nothing has happened. ("MySQL server has gone away" error ). Then a 
>web 
>
> application that is idle for 8 hours or more suffer from such problems. 

I've had with issue with my blog - the cache was too damn good so I hadn't
need my database at all and it was gone after hours.

I had changed MySQL server options and increased idle time. However
with CppDB and connection pooling it would be much simpler.

Artyom