R help - Jun 2006 - Numerical print format and loading with RMySQL

Hi

I'm new to R and have been resisting posting to the list thus far,  
but I think I've exhausted my work around options.

I'm having a few problems with RMySQL and/or it's underlying packages.

I'm doing a very simple vsn transformation on large datasets, using  
RMySQL to retrieve the data for a DB.

My first problem was that I could not get the RMySQL package to write  
back to my DB using the dbWriteTable method.

I constructed a data frame with columns corresponding to the table  
fields, and tried with and without a null column for the auto- 
increment internal id column.  I also tried renaming all the column  
names to match those of the table fields.  All of my attempts  
resulted in a long wait and "TRUE" being printed to the terminal,  
which I though was quite promising, however, on inspection of the DB,  
I found nothing had been written.  So my first question are:

Is it necessary to provide field names of the table, or will correct  
column order suffice?
For tables with an internal auto-increment DBID field, is it  
necessary to provide a null column of the correct length?

To get around this I decided to use the write.table function and then  
import via my controlling perl script.  This works fine apart from  
one problem, numbers >-100000 get printed as exponents, which then  
causes subsequent imports to miss these records. So my second and  
rather simple question is:

Is there a setting(options?) which will force full numerical printing?

Thanks in advance

Nath

On Tue, 13 Jun 2006, Nathan Johnson wrote:
> I'm new to R and have been resisting posting to the list thus far,
> but I think I've exhausted my work around options.
Have you asked the package maintainer: the posting guide suggests you do 
so first?  He is by far the most likely source of help.
> I'm having a few problems with RMySQL and/or it's underlying
packages.
Which is DBI by the same author.
> I'm doing a very simple vsn transformation on large datasets, using
> RMySQL to retrieve the data for a DB.
>
> My first problem was that I could not get the RMySQL package to write
> back to my DB using the dbWriteTable method.
>
> I constructed a data frame with columns corresponding to the table
> fields, and tried with and without a null column for the auto-
> increment internal id column.  I also tried renaming all the column
> names to match those of the table fields.  All of my attempts
> resulted in a long wait and "TRUE" being printed to the terminal,
> which I though was quite promising, however, on inspection of the DB,
> I found nothing had been written.  So my first question are:
>
> Is it necessary to provide field names of the table, or will correct
> column order suffice?
> For tables with an internal auto-increment DBID field, is it
> necessary to provide a null column of the correct length?
>
> To get around this I decided to use the write.table function and then
> import via my controlling perl script.  This works fine apart from
> one problem, numbers >-100000 get printed as exponents, which then
> causes subsequent imports to miss these records. So my second and
> rather simple question is:
>
> Is there a setting(options?) which will force full numerical printing?
Look for options scipen.

-- 
Brian D. Ripley,                  ripley at stats.ox.ac.uk
Professor of Applied Statistics,  http://www.stats.ox.ac.uk/~ripley/
University of Oxford,             Tel:  +44 1865 272861 (self)
1 South Parks Road,                     +44 1865 272866 (PA)
Oxford OX1 3TG, UK                Fax:  +44 1865 272595

Nathan Johnson <njohnson at ebi.ac.uk> wrote:
> I constructed a data frame with columns corresponding to the table  
> fields, and tried with and without a null column for the auto- 
> increment internal id column.  I also tried renaming all the column  
> names to match those of the table fields.  All of my attempts  
> resulted in a long wait and "TRUE" being printed to the terminal,
> which I though was quite promising, however, on inspection of the DB,  
> I found nothing had been written.
Is it possible you needed a call to dbCommit()?

-- Dave