pioneer_database.R

# Package to connect to MySQL from 
# install.packages("RMySQL")
library("RMySQL")

# MySQL authentication credentials
# Get these from Jacob Moody or CSR
dbuser <- ""
dbpassword <- ""
dbhost <- "statmm.byu.edu"
dbport <- 3306
dbname <- "pioneer_recordlink"

# Create Connection to database
con <- dbConnect(MySQL(),
    user = dbuser,
    password = dbpassword,
    host = dbhost,
    port = dbport,
    dbname=dbname)
rm(list=c("dbpassword"))

# Wrapper to run query
gq <- function(query, connection=con){
	dbGetQuery(con, query)
}

# Get list of tables
gq("SHOW Tables;")

# List of columns from a single table
gq("SHOW columns FROM Samp_Overland;")

# Get all variables from both tables
Samp_Overland <- gq("SELECT * FROM Samp_Overland")
Samp_Pioneer <- gq("SELECT * FROM Samp_Pioneer")

# Save data locally to be loaded later
# save.image("Data.R")
# load("Data.R")

dbDisconnect(con)