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?

Why go out of your way to avoid using the right tools? There's a specific package designed to talk to SQLite db's. Use that to write your own logging function. Why insist on using sink (which isn't even a generic function)? — joran, Dec 16 '13 at 16:50
@joran I want to use `sink` because it automatically logs errors/warnings, no matter if they are handled or not. Sure, I could write my own logging function, but then I would have to wrap all my calls in `tryCatch`, i.e. `tryCatch(dodgyFunc(), error = myLoggingFunc)` — mchen, Dec 16 '13 at 16:57
Making `sink` talk to a SQLite db is going to be **way** more work than simply writing your own code. — joran, Dec 16 '13 at 17:02

score 4 · Accepted Answer · answered Dec 16 '13 at 17:34

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.

score 1 · Answer 2 · answered Dec 16 '13 at 16:49

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.

score 0 · Answer 3 · answered Dec 16 '13 at 16:52

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)

R sink() to database

3 Answers3