# RMYSQL
# In the virtual desktop, the package RMySQL is installed
library(RMySQL)

# I do not recommend fetching all query results (dbGetQuery) from itcont and taxi, since these are very large files (over 10 million records), unless if you include LIMIT.
# Instead, retrieve results a chunk at a time: dbSendQuery, dbFetch, then dbClearResults

rmysql.settingsfile<-paste(R.home(component="home"),'/etc/.rmysql.cnf', sep = "")
rmysql.settingsfile

# ITS set up a database for us called mathr_db
# We only have reading privileges.
mydb <-dbConnect(MySQL(),default.file=rmysql.settingsfile,group="mathr_db") 
mydb
dbListResults(mydb)
dbListTables(mydb)

# I do not recommend fetching all query results (dbGetQuery) from itcont and taxi, since these are very large files, unless if you include LIMIT.
# Instead, retrieve results a chunk at a time: dbSendQuery, dbFetch, then dbClearResults

result <- dbSendQuery(mydb, "select * from ny_taxi_yellow_tripdata_2016_01 limit 10000")
data <- dbFetch(result, n = 10)
data
dbClearResult(result)

result <- dbSendQuery(mydb, "select count(*) from ny_taxi_yellow_tripdata_2016_01")
dbFetch(result)
dbClearResults(result)

# Is there a way to search via a pattern, say, just last name?
result=dbSendQuery(mydb, "select * from itcont where NAME="LAST, FIRST")
dbFetch(result, 10)

dbHasCompleted(result)
#dbDisconnect(mydb)
on.exit(dbDisconnect(mydb))