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/