R sink() to database

Question

sink() is useful for logging errors to file without having to wrap everything in tryCatch's. However, instead of logging to a file, I would like to log to a (SQLite) database table. Is this possible?

More generally, with sink(), how can I specify my own function to handle the actual write process?

Answer 1

sink diverts to a connection, not a file. To sink to a DB table, you simply need to use a connection that writes to a database table instead.

dbc = dbconnection(host="mysql.example.com", table="logs",field="logtext")

This then opens a database connection to the host. Then you do:

sink(dbc)
print("stuff")
sink()

Then the database connection code does INSERT INTO logs (time,logtext) VALUES ("12-Jan-2001" "R output comes here") - if you want to do datestamped log entries, for example.

So all you need to do is write that function that creates a connection to the database. Which I think has to be done at the C level - I don't know if you can create new connection types in pure R. Good luck with that.

Answer 2

You probably want capture.output(). It allows you to save the output of a given command(s) to variable and do whatever you want with it:

out <- capture.output({
    i <- 1
    cat(i, "\n")
    cat(i+1, "\n")
})

You can then use out variable for storing into database, etc.

Answer 3

You can use write.csv(df,file = "~/df.csv",append=TRUE), to put the data frame df out as a .csv which I think matches your needs (since most software reads .csv). Append places your new info at the end of an existing .csv, otherwise it will replace the file.

messups<-warnings()
write.csv(messups,file = "~/messups.csv",append=TRUE)