title: RMySQL dbWriteTable adding columns to table (dynamically?)
body: <p>I just started using the R package called RMySQL in order to get around some memory limitations on my computer. I am trying to take a matrix with 100 columns in R (called data.df), then make a new table on an SQL database that has &quot;100 choose 2&quot; (=4950) columns, where each column is a linear combination of two columns from the initial matrix. So far I have something like this:</p>
  <pre><code>countnumber &lt;- 1
  con &lt;- dbConnect(MySQL(), user = &quot;root&quot;, password = &quot;password&quot;, dbname = &quot;myDB&quot;)
  temp &lt;- as.data.frame(data.df[,1] - data.df[,2])
  colnames(temp) &lt;- paste(pairs[[countnumber]][1], pairs[[countnumber]][2], sep = &quot;&quot;)
  dbWriteTable(con, &quot;spreadtable&quot;, temp, row.names=T, overwrite = T)
  
  for(i in 1:(n-1)){
  for(j in (i+1):n){
      if(!((i==1)&amp;&amp;(j==2))){     #this part excludes the first iteration already taken care of
          temp &lt;- as.data.frame(data.df[,i] - data.df[,j])
          colnames(temp) &lt;- &quot;hola&quot;
          dbWriteTable(con, &quot;spreadtable&quot;, value = temp, append = TRUE, overwrite = FALSE, row.names = FALSE)
          countnumber &lt;- countnumber + 1
              }
          }
  }
  </code></pre>
  <p>I've also tried toying around with the &quot;field.types&quot; argument of RMySQL::dbWriteTable(), which was suggested at <a href="https://stackoverflow.com/questions/8864174/rmysql-dbwritetable-with-field-types">RMySQL dbWriteTable with field.types</a>. Sadly it hasn't helped me out too much.</p>
  <p>Questions:</p>
  <ol>
  <li>Is making your own sql database a valid solution to the memory-bound nature of R, even if it has 4950 columns?</li>
  <li>Is the dbWriteTable() the proper function to be using here?</li>
  <li>Assuming the answer is &quot;yes&quot; to both of the previous questions...why isn't this working?</li>
  </ol>
  <p>Thanks for any help.</p>
  <hr />
  <hr />
  <p>[EDIT]: code with error output:</p>
  <pre><code>names &lt;- as.data.frame(index)
  names &lt;- t(names)
  #dim(names) is 1 409
  con &lt;- dbConnect(MySQL(), user = &quot;root&quot;, password = &quot;password&quot;, dbname = &quot;taylordatabase&quot;)
  
  
  dbGetQuery(con, dbBuildTableDefinition(MySQL(), name=&quot;spreadtable&quot;, obj=names, row.names = F))
  #I would prefer these to be double types with 8 decimal spaces instead of text
  #dim(temp) is 1 409
  temp &lt;- as.data.frame(data.df[,1] - (ratios[countnumber]*data.df[,2]))
  temp &lt;- t(temp)
  temp &lt;- as.data.frame(temp)
  dbWriteTable(con, name = &quot;spreadtable&quot;, temp, append = T)
  </code></pre>
  <p>The table is created successfully in the database (I will change variable type later), but the dbWriteTable() line produces the error:</p>
  <pre><code>Error in mysqlExecStatement(conn, statement, ...) : 
  RS-DBI driver: (could not run statement: Unknown column 'row_names' in 'field list')
  [1] FALSE
  Warning message:
  In mysqlWriteTable(conn, name, value, ...) : could not load data into table
  </code></pre>
  <p>If I make a slight change, I get a different error message:</p>
  <pre><code>dbWriteTable(con, name = &quot;spreadtable&quot;, temp, append = T, row.names = F)
  </code></pre>
  <p>and</p>
  <pre><code>Error in mysqlExecStatement(conn, statement, ...) : 
  RS-DBI driver: (could not run statement: Unknown column 'X2011_01_03' in 'field list')
  [1] FALSE
  Warning message:
  In mysqlWriteTable(conn, name, value, ...) : could not load data into table
  </code></pre>
  <p>I just want to use &quot;names&quot; as a bunch of column labels. They were initially dates. The actual data I would like to be &quot;temp.&quot;</p>