[R] RMySQL CREATE TABLE error

[Anthony Dick]

Hi all-

I am stumped. The code in A. returns errors at lines 14 and 15 and fails 
to load series1 and series2. However, in B., if temp1 and temp2 are 
called again (which returns a "Table exists" error; see lines 14-17 in 
B.) series1 and series2 load correctly. Any ideas? Also-I am open to any 
suggestions to improve the code as I am a horrific programmer. Thanks

A.

1 > library(RMySQL)
2 > library(psych)
3 > drv=dbDriver("MySQL")
4 > con = dbConnect(drv, user="tsgcst", password = "sOy9Nays", 
dbname="tsgcst", host="db.ci.uchicago.edu")
5 > dbListTables(con)
[1] "TS_descript_spec"        "participant_TS_descript" 
"snr_adultsTS1"           "snr_adultsTS2"           
"time_series_small"       "tvalFDR_small"         
6 > temp1 <- paste("CREATE TEMPORARY TABLE MyTemporaryTable1 SELECT * 
FROM time_series_small WHERE participant = '005_avg' AND roi = '1033' 
AND timeseries_run = '1';")
7 > temp2 <- paste("CREATE TEMPORARY TABLE MyTemporaryTable2 SELECT * 
FROM time_series_small WHERE participant = '005_avg' AND roi = '1033' 
AND timeseries_run = '2';")
8 > temp3 <- paste("CREATE TEMPORARY TABLE MyTemporaryTable3 SELECT * 
FROM tvalFDR_small WHERE participant = '005_avg' AND roi = '1033' AND 
nogestureFDR > 1.95;")
9 > dbSendQuery(con, temp1)
<MySQLResult:(436,20,1)>
10 > dbSendQuery(con, temp2)
<MySQLResult:(436,20,2)>
11 > dbSendQuery(con, temp3)
<MySQLResult:(436,20,3)>
12 > query.string1 <- paste("SELECT t.timeseries_id, t.timeseries_signal 
FROM MyTemporaryTable1 t, MyTemporaryTable3 v WHERE t.participant = 
v.participant AND t.roi = v.roi AND t.voxel_id = v.voxel_id;",sep="")
13 > query.string2 <- paste("SELECT t.timeseries_id, t.timeseries_signal 
FROM MyTemporaryTable2 t, MyTemporaryTable3 v WHERE t.participant = 
v.participant AND t.roi = v.roi AND t.voxel_id = v.voxel_id;",sep="")
14 > series1 <- dbGetQuery(con, query.string1)
Error in mysqlExecStatement(conn, statement, ...) :
  RS-DBI driver: (could not run statement: Table 
'tsgcst.MyTemporaryTable1' doesn't exist)
15 > series2 <- dbGetQuery(con, query.string2)
Error in mysqlExecStatement(conn, statement, ...) :
  RS-DBI driver: (could not run statement: Table 
'tsgcst.MyTemporaryTable2' doesn't exist)
 >

B.

1 > library(RMySQL)
2 > library(psych)
3 > drv=dbDriver("MySQL")
4 > con = dbConnect(drv, user="tsgcst", password = "sOy9Nays", 
dbname="tsgcst", host="db.ci.uchicago.edu")
5 > dbListTables(con)
[1] "TS_descript_spec"        "participant_TS_descript" 
"snr_adultsTS1"           "snr_adultsTS2"           
"time_series_small"       "tvalFDR_small"         
6 > temp1 <- paste("CREATE TEMPORARY TABLE MyTemporaryTable1 SELECT * 
FROM time_series_small WHERE participant = '005_avg' AND roi = '1033' 
AND timeseries_run = '1';")
7 > temp2 <- paste("CREATE TEMPORARY TABLE MyTemporaryTable2 SELECT * 
FROM time_series_small WHERE participant = '005_avg' AND roi = '1033' 
AND timeseries_run = '2';")
8 > temp3 <- paste("CREATE TEMPORARY TABLE MyTemporaryTable3 SELECT * 
FROM tvalFDR_small WHERE participant = '005_avg' AND roi = '1033' AND 
nogestureFDR > 1.95;")
9 > dbSendQuery(con, temp1)
<MySQLResult:(436,19,1)>
10 > dbSendQuery(con, temp2)
<MySQLResult:(436,19,2)>
11 > dbSendQuery(con, temp3)
<MySQLResult:(436,19,3)>
12 > query.string1 <- paste("SELECT t.timeseries_id, t.timeseries_signal 
FROM MyTemporaryTable1 t, MyTemporaryTable3 v WHERE t.participant = 
v.participant AND t.roi = v.roi AND t.voxel_id = v.voxel_id;",sep="")
13 > query.string2 <- paste("SELECT t.timeseries_id, t.timeseries_signal 
FROM MyTemporaryTable2 t, MyTemporaryTable3 v WHERE t.participant = 
v.participant AND t.roi = v.roi AND t.voxel_id = v.voxel_id;",sep="")
14 > dbSendQuery(con, temp1)
Error in mysqlExecStatement(conn, statement, ...) :
  RS-DBI driver: (could not run statement: Table 'MyTemporaryTable1' 
already exists)
15 > series1 <- dbGetQuery(con, query.string1)
16 > dbSendQuery(con, temp2)
Error in mysqlExecStatement(conn, statement, ...) :
  RS-DBI driver: (could not run statement: Table 'MyTemporaryTable2' 
already exists)
17 > series2 <- dbGetQuery(con, query.string2)

-- 
Anthony Steven Dick, Ph.D.
Post-Doctoral Fellow
Human Neuroscience Laboratory
Department of Neurology
The University of Chicago
5841 S. Maryland Ave. MC-2030
Chicago, IL 60637
Phone: (773)-834-7770
Email: adick at uchicago.edu
Web: http://home.uchicago.edu/~adick/