MySQL odbc timeout from R

Question

I'm using R to read in some data from a MySQL database using the RODBC package. The data is then processed and some results are sent back to the database. The problem is that the server closes the connection after about a minute due to inactivity, which is the time needed to process the data locally. Its a shared server, so the host won't bump up the timeout time.

I think there are two possibilities to get around this 1) Open a connection before every database transaction and close it immediately after 2) Send some small 'ping' command to the server every 30 seconds or so to let the server know that I'm still there.

I can implement the first fairly easily, but it seems pretty slow to constantly open and close connections. Anyone know an efficient command for the second? Or a better way altogether?

Answer 1

The first solution is the one I prefer. It's really hard to do the latter with a single threaded program like R. If R is busy running analysis there's no way for it to handle the ping. Unless you are doing hundreds of reads/writes the method of opening and closing the connection should not introduce an extreme amount of overhead.