[R-sig-DB] DBI column names

[Tim Keitt]

This may be a bit of a hang-over from some of my early work on rpgsql,
et al. I had never used SQL (nor really programmed in R) prior to
writing the packages and wasn't too savy about quoting options. I
remember writing code to swap dots and underscores. The swapping
certainly could be relaxed as properly quoted, these characters should
not cause problems in R or SQL.

THK

On 7/21/07, Sean Davis <sdavis2 using mail.nih.gov> wrote:
> Gabor Grothendieck wrote:
> > It seems that the DBI package changes column names that have
> > dots in them replacing the dots with underscores.  Is this really
> > necessary?  I understand that dot is an SQL operator; nevertheless,
> > SQLite can handle column names with dots and I suspect other
> > databases can too:
> >
> > C:\tmp2>sqlite3
> > SQLite version 3.4.0
> > Enter ".help" for instructions
> > sqlite> create table mytable (`my.field` integer);
> > sqlite> insert into mytable(`my.field`) values (1);
> > sqlite> select `my.field` from mytable;
> > 1
>
> Just fyi....
>
> Postgres (works):
>
> test5=# create temp table mytable ("my.field" integer);
> LOG:  statement: create temp table mytable ("my.field" integer);
> CREATE TABLE
> test5=# insert into mytable ("my.field") values (1);
> INSERT 0 1
> test5=# select "my.field" from mytable;
>  my.field
> ----------
>         1
> (1 row)
>
> And mysql (works, also):
>
> mysql> create temporary table mytable (`my.field` integer);
> Query OK, 0 rows affected (0.04 sec)
>
> mysql> insert into mytable(`my.field`) values(1);
> Query OK, 1 row affected (0.00 sec)
>
> mysql> select `my.field` from mytable;
> +----------+
> | my.field |
> +----------+
>
> Sean
>
> |        1 |
> +----------+
> 1 row in set (0.00 sec)
>
> _______________________________________________
> R-sig-DB mailing list -- R Special Interest Group
> R-sig-DB using stat.math.ethz.ch
> https://stat.ethz.ch/mailman/listinfo/r-sig-db
>


-- 
Timothy H. Keitt, University of Texas at Austin
Contact info and schedule at http://www.keittlab.org/tkeitt/
Reprints at http://www.keittlab.org/tkeitt/papers/
ODF attachment? See http://www.openoffice.org/