[R-sig-DB] [R] RMySQL CREATE TABLE error

[Jeffrey Horner]

Not sure why you need to use temporary tables, but if you do you may 
want to add a "COMMIT" statement immediately after your "CREATE 
TEMPORARY TABLE" statement. Using your code, something like this may work:

dbSendQuery(con,"COMMIT")

Scope out this page in the MySQL 5.0 manual:

http://dev.mysql.com/doc/refman/5.0/en/implicit-commit.html

Three notes:

1) It's best to provide small reproducible examples WITHOUT personal 
information such as usernames and passwords (You may want to inform the 
administrators of db.ci.uchicago.edu that you just posted your 
credentials to a publicly available mailing list).

2) It's best to provide software versions, and in particular which 
storage engine you are using with MySQL. For instance innodb tables are 
transaction-safe whereas MyISAM tables are not.

3) Database related questions are best posted to the R-sig-db mailing 
list which you can find here:

http://www.r-project.org/mail.html

Best,

Jeff

Anthony Dick wrote:
> 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)
> 


-- 
http://biostat.mc.vanderbilt.edu/JeffreyHorner