How to connect to a database on the NAS server

Basics: Connect

Load the RODBC package, and connect with the DSN (which needs to be configured from Windows Control Panel). See which tables are in the DB. Then close the connection.

library(RODBC)
con <- odbcConnect("hausaufgaben")
sqlTables(con)

##      TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE REMARKS
## 1 hausaufgaben                     df      TABLE        

close(con)

Write to DB

Next, create a data.frame and store it in the DB.
It is safest to use this after having removed any existing table of the same name, which can be done by sqlDrop(ch, “tablename”, errors=FALSE)

df <- as.data.frame(rnorm(100))
con <- odbcConnect("hausaufgaben")
sqlDrop(con, "df", errors = FALSE)
sqlSave(con, df)
sqlTables(con)

##      TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE REMARKS
## 1 hausaufgaben                     df      TABLE        

close(con)

Read table from DB

Now lets read the new table from the db and do some stuff with it.

con <- odbcConnect("hausaufgaben")
sqlTables(con)

##      TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE REMARKS
## 1 hausaufgaben                     df      TABLE        

mydf <- sqlFetch(con, "df")
close(con)
head(mydf)

##   rnorm100
## 1   0.2556
## 2   0.1335
## 3  -0.2082
## 4  -0.3804
## 5   0.5000
## 6  -0.3527

summary(mydf)

##     rnorm100      
##  Min.   :-1.9058  
##  1st Qu.:-0.4570  
##  Median : 0.0522  
##  Mean   : 0.0009  
##  3rd Qu.: 0.5087  
##  Max.   : 2.4622  

plot(density(mydf$rnorm100))

That's it for now.