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

[Anthony Dick]

Hi Jeff-

Thanks for the tips. Posting my password information is just stupid, and 
I have contacted my administrator.

I am using temporary tables because I am dealing with very large tables 
and using JOINs, and for joining smaller temporary tables is much faster.

Thanks again.

Anthony

Jeffrey Horner wrote:
> 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)
>>
>
>


-- 
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 using uchicago.edu
Web: http://home.uchicago.edu/~adick/