From h|w|ttm@nn @end|ng |rom goog|em@||@com  Tue Oct 13 23:57:00 2009
From: h|w|ttm@nn @end|ng |rom goog|em@||@com (H. Felix Wittmann)
Date: Tue, 13 Oct 2009 23:57:00 +0200
Subject: [R-sig-DB] rmysql and strings containg \n
Message-ID: <a085c89f0910131457y7ccf354bl57fcd5e6aa6cbdf4@mail.gmail.com>

let me start by saying RMySQL is a wonderful package of a superb
language:R.? One particular feature I cant seem to get rid of easily
is the hadling of  \n in strings. Is there an easy solution to this?

H. Felix Wittmann

PS : What follows is some code, demonstrating the problem

require(RMySQL)

m <- dbDriver("MySQL")
myCon <- dbConnect(m,dbname = "myDatabase"))? ## replace myDatabase by
what is appropriate for you


test <- data.frame(id=1,myString='beginning1 \n end1')


> dbWriteTable(myCon,name='test', value=test)
[1] TRUE

> dbReadTable(myCon,name='test')
????? id??? myString
1????? 1 beginning1
?end1 NA??????? <NA>

> testOut<- dbReadTable(drupal5,name='test')
> dim(testOut)
[1] 2 2

> dim(test)
[1] 1 2



From cr@|g @end|ng |rom cr@|g@chm|dt@com  Thu Oct 15 20:33:07 2009
From: cr@|g @end|ng |rom cr@|g@chm|dt@com (Craig Schmidt)
Date: Thu, 15 Oct 2009 14:33:07 -0400
Subject: [R-sig-DB] using RMySQL with both 64-bit and 32-bit R on OSX
Message-ID: <5D7AE475-C444-4365-B13A-ECA1B908AF07@craigschmidt.com>

Hi There,

I'm trying to install RMySQL on OSX 10.5.8 with both the 64-bit and 32- 
bit version of R.

I had the same problem that was mentioned in this unanswered post:

https://stat.ethz.ch/pipermail/r-sig-db/2009q1/000585.html

I've actually managed to get the 64-bit version working.  I did the  
following.

1. installed the 64-bit version of MySQL so I have a setup like this:

[cschmidt:/usr/local]$ ls -l
[...]
lrwxr-xr-x    1 root      wheel    27 Oct 15 11:22 mysql ->  
mysql-5.1.39-osx10.5-x86_64
drwxr-xr-x   17 root      wheel   578 Apr  1  2009 mysql-5.1.34- 
osx10.5-x86
drwxr-xr-x   17 root      wheel   578 Sep  4 15:18 mysql-5.1.39- 
osx10.5-x86_64

That is, the 64-bit version is in /usr/local/mysql, and the older 32  
bit version is in /usr/local/mysql-5.1.34-osx10.5-x86

2. Downloaded the source of RMySQL_0.7-4.tar.gz from CRAN.

3. Typed the following

export PKG_CPPFLAGS="-I/usr/local/mysql/include"
export PKG_LIBS="-L/usr/local/mysql/lib -lmysqlclient"
R64 --arch x86_64 CMD INSTALL RMySQL_0.7-4.tar.gz

This gives my a good version of RMySQL for R64.

My question is if it is possible to also simultaneously use the 32-bit  
version of R.

Following the suggestions from here http://www.matthewckeller.com/html/64_bit_r_on_mac.html
I tried to do this:

export PKG_CPPFLAGS="-I/usr/local/mysql-5.1.34-osx10.5-x86/include"
export PKG_LIBS="-L/usr/local/mysql-5.1.34-osx10.5-x86/lib - 
lmysqlclient"
R --arch i386 CMD INSTALL --libs-only RMySQL_0.7-4.tar.gz

This seems to almost work. However, it is still trying to load the 64- 
bit version of libmysqlclient.16.dylib
from /usr/local/mysql, rather than the 32-bit version in /usr/local/ 
mysql-5.1.34-osx10.5-x86.  I get this error message:

 > library(RMySQL)
Loading required package: DBI
Error in dyn.load(file, DLLpath = DLLpath, ...) :
   unable to load shared library '/Library/Frameworks/R.framework/ 
Resources/library/RMySQL/libs/i386/RMySQL.so':
   dlopen(/Library/Frameworks/R.framework/Resources/library/RMySQL/ 
libs/i386/RMySQL.so, 6): Library not loaded: /usr/local/mysql/lib/ 
libmysqlclient.16.dylib
   Referenced from: /Library/Frameworks/R.framework/Resources/library/ 
RMySQL/libs/i386/RMySQL.so
   Reason: no suitable image found.  Did find:
	/usr/local/mysql/lib/libmysqlclient.16.dylib: mach-o, but wrong  
architecture
Error : .onLoad failed in 'loadNamespace' for 'RMySQL'
Error: package/namespace load failed for 'RMySQL'

Is this a bug in the makefile?  How do I tell it to look in the  
correct directory? Anyone have suggestions on how to fix it?

Thanks,
Craig Schmidt
  
	[[alternative HTML version deleted]]



From @tp @end|ng |rom p|@kor@k|@com  Tue Oct 20 09:16:15 2009
From: @tp @end|ng |rom p|@kor@k|@com (Andrew Piskorski)
Date: Tue, 20 Oct 2009 03:16:15 -0400
Subject: [R-sig-DB] RSQLite dbWriteTable() fails w/ RS-DBI driver: too many
	SQL variables
Message-ID: <20091020071615.GA33614@piskorski.com>

The simple RSQLite dbWriteTable() example below fails with the cryptic
RS-DBI driver error.  Interestingly, if I reduce the size of my matrix
from 1000 to 998 columns, then it works.

Could anyone here please suggest what's going wrong, and how I should
go about trying to fix it?  How do I see the actual SQL that this
RS-DBI thing is generating?  Also, why doesn't this give me a stack
trace like stop() or any other R error?  (Yes, I have "options(error =
recover)" set.)

library("RSQLite")
dbdr <- dbDriver("SQLite")
ff <- "/home/andy/t/tmp.sqlite"
mm <- matrix(1:1000^2 ,nrow=1000 ,ncol=1000 ,dimnames=list(paste("r",1:1000,sep="") ,paste("c",1:1000,sep="")))
# Here up to 998 columns work, any higher fails:
db <- dbConnect(dbdr ,dbname=ff)
dbWriteTable(db ,"my_table" ,as.data.frame(mm ,stringsAsFactors=F) ,row.names=T ,overwrite=T ,append=F)
dbDisconnect(db)

> dbWriteTable(db ,"my_table" ,as.data.frame(mm[,1:999] ,stringsAsFactors=F) ,row.names=T ,overwrite=T ,append=F)
Warning in value[[3L]](cond) :
  RS-DBI driver: (error in statement: too many SQL variables)
[1] FALSE
> dbWriteTable(db ,"my_table" ,as.data.frame(mm[,1:998] ,stringsAsFactors=F) ,row.names=T ,overwrite=T ,append=F)
[1] TRUE

This is with:

- R 2.9.2 (Patched), 2009-09-24, svn.rev 49961, x86_64-unknown-linux-gnu  
- RSQLite_0.7-3.tar.gz (2009-10-04)
  http://cran.r-project.org/web/packages/RSQLite/
- DBI_0.2-4.tar.gz (2007-10-17, still the latest on CRAN)
- Linux, Ubuntu 8.04.3 LTS 

Thanks!

-- 
Andrew Piskorski <atp at piskorski.com>
http://www.piskorski.com/



From ggrothend|eck @end|ng |rom gm@||@com  Tue Oct 20 15:34:10 2009
From: ggrothend|eck @end|ng |rom gm@||@com (Gabor Grothendieck)
Date: Tue, 20 Oct 2009 09:34:10 -0400
Subject: [R-sig-DB] RSQLite dbWriteTable() fails w/ RS-DBI driver: too
	many SQL variables
In-Reply-To: <20091020071615.GA33614@piskorski.com>
References: <20091020071615.GA33614@piskorski.com>
Message-ID: <971536df0910200634j24be235bwaa62ee87da6a05ac@mail.gmail.com>

SQLITE_MAX_VARIABLE_NUMBER in the sqlite source (in RSQLite) is
currently set to 999.

On Tue, Oct 20, 2009 at 3:16 AM, Andrew Piskorski <atp at piskorski.com> wrote:
> The simple RSQLite dbWriteTable() example below fails with the cryptic
> RS-DBI driver error. ?Interestingly, if I reduce the size of my matrix
> from 1000 to 998 columns, then it works.
>
> Could anyone here please suggest what's going wrong, and how I should
> go about trying to fix it? ?How do I see the actual SQL that this
> RS-DBI thing is generating? ?Also, why doesn't this give me a stack
> trace like stop() or any other R error? ?(Yes, I have "options(error =
> recover)" set.)
>
> library("RSQLite")
> dbdr <- dbDriver("SQLite")
> ff <- "/home/andy/t/tmp.sqlite"
> mm <- matrix(1:1000^2 ,nrow=1000 ,ncol=1000 ,dimnames=list(paste("r",1:1000,sep="") ,paste("c",1:1000,sep="")))
> # Here up to 998 columns work, any higher fails:
> db <- dbConnect(dbdr ,dbname=ff)
> dbWriteTable(db ,"my_table" ,as.data.frame(mm ,stringsAsFactors=F) ,row.names=T ,overwrite=T ,append=F)
> dbDisconnect(db)
>
>> dbWriteTable(db ,"my_table" ,as.data.frame(mm[,1:999] ,stringsAsFactors=F) ,row.names=T ,overwrite=T ,append=F)
> Warning in value[[3L]](cond) :
> ?RS-DBI driver: (error in statement: too many SQL variables)
> [1] FALSE
>> dbWriteTable(db ,"my_table" ,as.data.frame(mm[,1:998] ,stringsAsFactors=F) ,row.names=T ,overwrite=T ,append=F)
> [1] TRUE
>
> This is with:
>
> - R 2.9.2 (Patched), 2009-09-24, svn.rev 49961, x86_64-unknown-linux-gnu
> - RSQLite_0.7-3.tar.gz (2009-10-04)
> ?http://cran.r-project.org/web/packages/RSQLite/
> - DBI_0.2-4.tar.gz (2007-10-17, still the latest on CRAN)
> - Linux, Ubuntu 8.04.3 LTS
>
> Thanks!
>
> --
> Andrew Piskorski <atp at piskorski.com>
> http://www.piskorski.com/
>
> _______________________________________________
> R-sig-DB mailing list -- R Special Interest Group
> R-sig-DB at stat.math.ethz.ch
> https://stat.ethz.ch/mailman/listinfo/r-sig-db
>



From h|w|ttm@nn @end|ng |rom goog|em@||@com  Tue Oct 20 23:37:24 2009
From: h|w|ttm@nn @end|ng |rom goog|em@||@com (H. Felix Wittmann)
Date: Tue, 20 Oct 2009 23:37:24 +0200
Subject: [R-sig-DB] Fwd: rmysql and strings containg \n
In-Reply-To: <a085c89f0910131457y7ccf354bl57fcd5e6aa6cbdf4@mail.gmail.com>
References: <a085c89f0910131457y7ccf354bl57fcd5e6aa6cbdf4@mail.gmail.com>
Message-ID: <a085c89f0910201437n79019b24l3faa8d2c85bee3a6@mail.gmail.com>

I' ve noticed a few typos in my previous post therefore I send a
cleaned up version


require(RMySQL)

m <- dbDriver("MySQL")
myCon <- dbConnect(m,group = 'whateverGroup')
# replace this (ie 'whateverGroup') by the appropriate group specified
in your my.cnf file
# on a typical UBUNTU installation you can
# modify this using the command line editor (eg bash) by this command
# sudo nano /etc/mysql/my.cnf

test <- data.frame(id=1,myString='beginning1 \n end1')
# the above line produces (this is what the output should like like) :
# > test
#   id           myString
# 1  1 beginning1 \n end1

dbWriteTable(myCon,name='test', value=test)
dbReadTable(myCon,name='test')
# the above line produces (here we can see that the output is not the
same as above) :
#       id    myString
# 1      1 beginning1
#  end1 NA        <NA>


H. Felix Wittmann



From je||@horner @end|ng |rom v@nderb||t@edu  Mon Oct 26 14:47:27 2009
From: je||@horner @end|ng |rom v@nderb||t@edu (Jeffrey Horner)
Date: Mon, 26 Oct 2009 08:47:27 -0500
Subject: [R-sig-DB] Fwd: rmysql and strings containg \n
In-Reply-To: <a085c89f0910201437n79019b24l3faa8d2c85bee3a6@mail.gmail.com>
References: <a085c89f0910131457y7ccf354bl57fcd5e6aa6cbdf4@mail.gmail.com>
	<a085c89f0910201437n79019b24l3faa8d2c85bee3a6@mail.gmail.com>
Message-ID: <4AE5A86F.10802@vanderbilt.edu>

Hi H. Felix Wittmann,

We require that you tell us R's version, RMySQL's version, and MySQL's 
version upon which you are running your code. Then we can better 
determine if your problem is a bug or a feature.

Best,

Jeff

H. Felix Wittmann wrote on 10/20/2009 04:37 PM:
> I' ve noticed a few typos in my previous post therefore I send a
> cleaned up version
> 
> 
> require(RMySQL)
> 
> m <- dbDriver("MySQL")
> myCon <- dbConnect(m,group = 'whateverGroup')
> # replace this (ie 'whateverGroup') by the appropriate group specified
> in your my.cnf file
> # on a typical UBUNTU installation you can
> # modify this using the command line editor (eg bash) by this command
> # sudo nano /etc/mysql/my.cnf
> 
> test <- data.frame(id=1,myString='beginning1 \n end1')
> # the above line produces (this is what the output should like like) :
> # > test
> #   id           myString
> # 1  1 beginning1 \n end1
> 
> dbWriteTable(myCon,name='test', value=test)
> dbReadTable(myCon,name='test')
> # the above line produces (here we can see that the output is not the
> same as above) :
> #       id    myString
> # 1      1 beginning1
> #  end1 NA        <NA>
> 
> 
> H. Felix Wittmann
> 
> _______________________________________________
> R-sig-DB mailing list -- R Special Interest Group
> R-sig-DB at stat.math.ethz.ch
> https://stat.ethz.ch/mailman/listinfo/r-sig-db


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



From h|w|ttm@nn @end|ng |rom goog|em@||@com  Tue Oct 27 22:50:24 2009
From: h|w|ttm@nn @end|ng |rom goog|em@||@com (H. Felix Wittmann)
Date: Tue, 27 Oct 2009 22:50:24 +0100
Subject: [R-sig-DB] Fwd: rmysql and strings containg \n
In-Reply-To: <4AE5A86F.10802@vanderbilt.edu>
References: <a085c89f0910131457y7ccf354bl57fcd5e6aa6cbdf4@mail.gmail.com>
	<a085c89f0910201437n79019b24l3faa8d2c85bee3a6@mail.gmail.com>
	<4AE5A86F.10802@vanderbilt.edu>
Message-ID: <a085c89f0910271450y54123832h39cbc0451d6feb2d@mail.gmail.com>

Hi Jeff,

thanks for your answer. At the moment I've got the following

R version 2.9.2 (2009-08-24)
?RMySQL? version 0.7-4
mysql  Ver 14.12 Distrib 5.0.75, for debian-linux-gnu (x86_64) using
readline 5.2

That said, similar things have consistently shown up in pretty much
all the environments that I've tested.

Felix


2009/10/26 Jeffrey Horner <jeff.horner at vanderbilt.edu>
>
> Hi H. Felix Wittmann,
>
> We require that you tell us R's version, RMySQL's version, and MySQL's version upon which you are running your code. Then we can better determine if your problem is a bug or a feature.
>
> Best,
>
> Jeff
>
> H. Felix Wittmann wrote on 10/20/2009 04:37 PM:
>>
>> I' ve noticed a few typos in my previous post therefore I send a
>> cleaned up version
>>
>>
>> require(RMySQL)
>>
>> m <- dbDriver("MySQL")
>> myCon <- dbConnect(m,group = 'whateverGroup')
>> # replace this (ie 'whateverGroup') by the appropriate group specified
>> in your my.cnf file
>> # on a typical UBUNTU installation you can
>> # modify this using the command line editor (eg bash) by this command
>> # sudo nano /etc/mysql/my.cnf
>>
>> test <- data.frame(id=1,myString='beginning1 \n end1')
>> # the above line produces (this is what the output should like like) :
>> # > test
>> # ? id ? ? ? ? ? myString
>> # 1 ?1 beginning1 \n end1
>>
>> dbWriteTable(myCon,name='test', value=test)
>> dbReadTable(myCon,name='test')
>> # the above line produces (here we can see that the output is not the
>> same as above) :
>> # ? ? ? id ? ?myString
>> # 1 ? ? ?1 beginning1
>> # ?end1 NA ? ? ? ?<NA>
>>
>>
>> H. Felix Wittmann
>>
>> _______________________________________________
>> R-sig-DB mailing list -- R Special Interest Group
>> R-sig-DB at stat.math.ethz.ch
>> https://stat.ethz.ch/mailman/listinfo/r-sig-db
>
>
> --
> http://biostat.mc.vanderbilt.edu/JeffreyHorner



From pg||bert @end|ng |rom b@nk-b@nque-c@n@d@@c@  Wed Oct 28 17:15:31 2009
From: pg||bert @end|ng |rom b@nk-b@nque-c@n@d@@c@ (Paul Gilbert)
Date: Wed, 28 Oct 2009 12:15:31 -0400
Subject: [R-sig-DB] Fwd: rmysql and strings containg \n
In-Reply-To: <a085c89f0910271450y54123832h39cbc0451d6feb2d@mail.gmail.com>
References: <a085c89f0910131457y7ccf354bl57fcd5e6aa6cbdf4@mail.gmail.com><a085c89f0910201437n79019b24l3faa8d2c85bee3a6@mail.gmail.com><4AE5A86F.10802@vanderbilt.edu>
	<a085c89f0910271450y54123832h39cbc0451d6feb2d@mail.gmail.com>
Message-ID: <D611103AA7EE3B4DAE7F7D49C72B291A01E8C831@EXMAIL2.bocad.bank-banque-canada.ca>

I've not had much luck myself with \n in MySQL strings. I suspect you get the same problem using the mysql client directly, which is usually a good test to see if the problem is R related or not. This could also have something to do with type of the field, and possibly with the character encoding.

Paul

>-----Original Message-----
>From: r-sig-db-bounces at stat.math.ethz.ch [mailto:r-sig-db-
>bounces at stat.math.ethz.ch] On Behalf Of H. Felix Wittmann
>Sent: October 27, 2009 5:50 PM
>To: Jeffrey Horner
>Cc: r-sig-db at stat.math.ethz.ch
>Subject: Re: [R-sig-DB] Fwd: rmysql and strings containg \n
>
>Hi Jeff,
>
>thanks for your answer. At the moment I've got the following
>
>R version 2.9.2 (2009-08-24)
>'RMySQL' version 0.7-4
>mysql  Ver 14.12 Distrib 5.0.75, for debian-linux-gnu (x86_64) using
>readline 5.2
>
>That said, similar things have consistently shown up in pretty much
>all the environments that I've tested.
>
>Felix
>
>
>2009/10/26 Jeffrey Horner <jeff.horner at vanderbilt.edu>
>>
>> Hi H. Felix Wittmann,
>>
>> We require that you tell us R's version, RMySQL's version, and MySQL's
>version upon which you are running your code. Then we can better
>determine if your problem is a bug or a feature.
>>
>> Best,
>>
>> Jeff
>>
>> H. Felix Wittmann wrote on 10/20/2009 04:37 PM:
>>>
>>> I' ve noticed a few typos in my previous post therefore I send a
>>> cleaned up version
>>>
>>>
>>> require(RMySQL)
>>>
>>> m <- dbDriver("MySQL")
>>> myCon <- dbConnect(m,group = 'whateverGroup')
>>> # replace this (ie 'whateverGroup') by the appropriate group
>specified
>>> in your my.cnf file
>>> # on a typical UBUNTU installation you can
>>> # modify this using the command line editor (eg bash) by this command
>>> # sudo nano /etc/mysql/my.cnf
>>>
>>> test <- data.frame(id=1,myString='beginning1 \n end1')
>>> # the above line produces (this is what the output should like like)
>:
>>> # > test
>>> # ? id ? ? ? ? ? myString
>>> # 1 ?1 beginning1 \n end1
>>>
>>> dbWriteTable(myCon,name='test', value=test)
>>> dbReadTable(myCon,name='test')
>>> # the above line produces (here we can see that the output is not the
>>> same as above) :
>>> # ? ? ? id ? ?myString
>>> # 1 ? ? ?1 beginning1
>>> # ?end1 NA ? ? ? ?<NA>
>>>
>>>
>>> H. Felix Wittmann
>>>
>>> _______________________________________________
>>> R-sig-DB mailing list -- R Special Interest Group
>>> R-sig-DB at stat.math.ethz.ch
>>> https://stat.ethz.ch/mailman/listinfo/r-sig-db
>>
>>
>> --
>> http://biostat.mc.vanderbilt.edu/JeffreyHorner
>
>_______________________________________________
>R-sig-DB mailing list -- R Special Interest Group
>R-sig-DB at stat.math.ethz.ch
>https://stat.ethz.ch/mailman/listinfo/r-sig-db
====================================================================================

La version fran?aise suit le texte anglais.

------------------------------------------------------------------------------------

This email may contain privileged and/or confidential in...{{dropped:26}}



From @ydghyyh14 @end|ng |rom y@hoo@com@cn  Wed Oct 28 16:42:42 2009
From: @ydghyyh14 @end|ng |rom y@hoo@com@cn (=?utf-8?B?5bCnIOiLjw==?=)
Date: Wed, 28 Oct 2009 23:42:42 +0800 (CST)
Subject: [R-sig-DB] problem loading RMySQL
Message-ID: <625058.31044.qm@web15002.mail.cnb.yahoo.com>

Hi Jeff:?? I'd like to use the RMySQL package that you administer but am 
having trouble loading it.?? I can download and install the package but 
can't load it.  Below is the output from my attempt.  I will appreciate 
any help you can give me on this.???? Thanks.
The result of following codes are also attached:
sessionInfo()
.libPaths()
Sys.getenv('MYSQL_HOME')
utils::readRegistry("SOFTWARE\\MySQL AB", hive="HLM", maxdepth=2)

> utils:::menuInstallPkgs()
--- Please select a CRAN mirror for use in this session ---
also installing the dependency ???DBI???
trying URL 'http://ftp.ctex.org/mirrors/CRAN/bin/windows/contrib/2.10/DBI_0.2-4.zip'
Content type 'application/zip' length 375508 bytes (366 Kb)
opened URL
downloaded 366 Kb
trying URL 'http://ftp.ctex.org/mirrors/CRAN/bin/windows/contrib/2.10/RMySQL_0.7-4.zip'
Content type 'application/zip' length 284408 bytes (277 Kb)
opened URL
downloaded 277 Kb
package 'DBI' successfully unpacked and MD5 sums checked
package 'RMySQL' successfully unpacked and MD5 sums checked
The downloaded packages are in
?????????????? C:\Documents and Settings\su\Local Settings\Temp\RtmpUPYJh5\downloaded_packages
> library(RMySQL)
Loading required package: DBI
Error in if (utils::file_test("-d", MySQLhome)) break : 
?? argument is of length zero
Error : .onLoad failed in 'loadNamespace' for 'RMySQL'
Error: package/namespace load failed for 'RMySQL'

> sessionInfo()
R version 2.10.0 (2009-10-26) 
i386-pc-mingw32 
locale:
[1] LC_COLLATE=Chinese_People's Republic of China.936 
[2] LC_CTYPE=Chinese_People's Republic of China.936???? 
[3] LC_MONETARY=Chinese_People's Republic of China.936
[4] LC_NUMERIC=C?????????????????????????????????????????????????????????????????????????? 
[5] LC_TIME=Chinese_People's Republic of China.936?????? 
attached base packages:
[1] stats???????? graphics?? grDevices utils???????? datasets?? methods?? 
[7] base???????? 
other attached packages:
[1] DBI_0.2-4???? RODBC_1.3-1
loaded via a namespace (and not attached):
[1] tools_2.10.0
> .libPaths()
[1] "D:/PROGRA~1/R/R-210~1.0/library"
> Sys.getenv('MYSQL_HOME')
MYSQL_HOME 
?????????????? "" 
> utils::readRegistry("SOFTWARE\\MySQL AB", hive="HLM", maxdepth=2)
$`MySQL Connector/ODBC 5.1`
$`MySQL Connector/ODBC 5.1`$Version
[1] "5.1.5"

$`MySQL Server 5.1`
$`MySQL Server 5.1`$DataLocation
[1] "C:\\Documents and Settings\\All Users\\Application Data\\MySQL\\MySQL Server 5.1\\"
$`MySQL Server 5.1`$FoundExistingDataDir
[1] "0"
$`MySQL Server 5.1`$Location
[1] "D:\\MySQL\\"
$`MySQL Server 5.1`$Version
[1] "5.1.37"



      ___________________________________________________________ 
  ??????????????????????????????????????????????????? 
http://card.mail.cn.yahoo.com/
	[[alternative HTML version deleted]]



From je||@horner @end|ng |rom v@nderb||t@edu  Wed Oct 28 17:28:56 2009
From: je||@horner @end|ng |rom v@nderb||t@edu (Jeffrey Horner)
Date: Wed, 28 Oct 2009 11:28:56 -0500
Subject: [R-sig-DB] problem loading RMySQL
In-Reply-To: <625058.31044.qm@web15002.mail.cnb.yahoo.com>
References: <625058.31044.qm@web15002.mail.cnb.yahoo.com>
Message-ID: <4AE87148.30008@vanderbilt.edu>

? ? wrote on 10/28/2009 10:42 AM:
> Hi Jeff:?  I'd like to use the RMySQL package that you administer but am 
> having trouble loading it.?  I can download and install the package but 
> can't load it.  Below is the output from my attempt.  I will appreciate 
> any help you can give me on this.? ?  Thanks.
> The result of following codes are also attached:
> sessionInfo()
> .libPaths()
> Sys.getenv('MYSQL_HOME')
> utils::readRegistry("SOFTWARE\\MySQL AB", hive="HLM", maxdepth=2)
> 
>> utils:::menuInstallPkgs()
> --- Please select a CRAN mirror for use in this session ---
> also installing the dependency ???DBI???
> trying URL 'http://ftp.ctex.org/mirrors/CRAN/bin/windows/contrib/2.10/DBI_0.2-4.zip'
> Content type 'application/zip' length 375508 bytes (366 Kb)
> opened URL
> downloaded 366 Kb
> trying URL 'http://ftp.ctex.org/mirrors/CRAN/bin/windows/contrib/2.10/RMySQL_0.7-4.zip'
> Content type 'application/zip' length 284408 bytes (277 Kb)
> opened URL
> downloaded 277 Kb
> package 'DBI' successfully unpacked and MD5 sums checked
> package 'RMySQL' successfully unpacked and MD5 sums checked
> The downloaded packages are in
> ? ? ? ? ? ? ?  C:\Documents and Settings\su\Local Settings\Temp\RtmpUPYJh5\downloaded_packages
>> library(RMySQL)
> Loading required package: DBI
> Error in if (utils::file_test("-d", MySQLhome)) break : 
> ?  argument is of length zero
> Error : .onLoad failed in 'loadNamespace' for 'RMySQL'
> Error: package/namespace load failed for 'RMySQL'
> 
>> sessionInfo()
> R version 2.10.0 (2009-10-26) 
> i386-pc-mingw32 
> locale:
> [1] LC_COLLATE=Chinese_People's Republic of China.936 
> [2] LC_CTYPE=Chinese_People's Republic of China.936? ?  
> [3] LC_MONETARY=Chinese_People's Republic of China.936
> [4] LC_NUMERIC=C? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?  
> [5] LC_TIME=Chinese_People's Republic of China.936? ? ?  
> attached base packages:
> [1] stats? ? ? ?  graphics?  grDevices utils? ? ? ?  datasets?  methods?  
> [7] base? ? ? ?  
> other attached packages:
> [1] DBI_0.2-4? ?  RODBC_1.3-1
> loaded via a namespace (and not attached):
> [1] tools_2.10.0
>> .libPaths()
> [1] "D:/PROGRA~1/R/R-210~1.0/library"
>> Sys.getenv('MYSQL_HOME')
> MYSQL_HOME 
> ? ? ? ? ? ? ?  "" 

You can see here that your MYSQL_HOME environment variable is set to an 
empty string. Did you intend to do this? If not, unset it and see if 
RMySQL will find the libmysql.dll from your registry settings.

Jeff

>> utils::readRegistry("SOFTWARE\\MySQL AB", hive="HLM", maxdepth=2)
> $`MySQL Connector/ODBC 5.1`
> $`MySQL Connector/ODBC 5.1`$Version
> [1] "5.1.5"
> 
> $`MySQL Server 5.1`
> $`MySQL Server 5.1`$DataLocation
> [1] "C:\\Documents and Settings\\All Users\\Application Data\\MySQL\\MySQL Server 5.1\\"
> $`MySQL Server 5.1`$FoundExistingDataDir
> [1] "0"
> $`MySQL Server 5.1`$Location
> [1] "D:\\MySQL\\"
> $`MySQL Server 5.1`$Version
> [1] "5.1.37"
> 
> 
> 
>       ___________________________________________________________ 
>   ????????????????? ????????????????????????????????? 
> http://card.mail.cn.yahoo.com/
> 	[[alternative HTML version deleted]]
> 
> 
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> R-sig-DB mailing list -- R Special Interest Group
> R-sig-DB at stat.math.ethz.ch
> https://stat.ethz.ch/mailman/listinfo/r-sig-db


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



From h|w|ttm@nn @end|ng |rom goog|em@||@com  Thu Oct 29 20:51:47 2009
From: h|w|ttm@nn @end|ng |rom goog|em@||@com (H. Felix Wittmann)
Date: Thu, 29 Oct 2009 20:51:47 +0100
Subject: [R-sig-DB] Fwd: rmysql and strings containg \n
In-Reply-To: <D611103AA7EE3B4DAE7F7D49C72B291A01E8C831@EXMAIL2.bocad.bank-banque-canada.ca>
References: <a085c89f0910131457y7ccf354bl57fcd5e6aa6cbdf4@mail.gmail.com>
	<a085c89f0910201437n79019b24l3faa8d2c85bee3a6@mail.gmail.com>
	<4AE5A86F.10802@vanderbilt.edu>
	<a085c89f0910271450y54123832h39cbc0451d6feb2d@mail.gmail.com>
	<D611103AA7EE3B4DAE7F7D49C72B291A01E8C831@EXMAIL2.bocad.bank-banque-canada.ca>
Message-ID: <a085c89f0910291251ld4577c3ga40e6b28f3703b5f@mail.gmail.com>

Paul,

thank you for your message. I agree with you that things are
interconnected with character encoding.

I attach a modified version of a function (dbWriteTable.2) I posted
earlier on this mailing list which may represent a hint towards a
solution. With this fucnction I managed (and manage) to write strings
containing \n into mysql databases. In particular it doesn't fail the
test I gave at the start of thread.

I consider the function dbWriteTable.2 not a full solution since it
requires a directory with write access, by.default it is set to /tmp/.

for ease of reference I give this example below (bottom line is
dbWriteTable.2 works, dbWriteTable doesnt)

maybe, if there is no better solution, we could include this in the package?

Felix

Example below

m <- dbDriver("MySQL")
myCon <- dbConnect(m,group = 'test')
# replace this (ie 'whateverGroup') by the appropriate group specified
# in your my.cnf file
# on a typical UBUNTU installation you can
# modify this using the command line editor (eg bash) by this command
# sudo nano /etc/mysql/my.cnf

test <- data.frame(id=1,myString='beginning1 \n end1')
# the above line produces (this is what the output should like like) :
# > test
#   id           myString
# 1  1 beginning1 \n end1

dbWriteTable(myCon,name='test', value=test , overwrite = TRUE)
dbReadTable(myCon,name='test')

# [1] TRUE
#       id    myString
# 1      1 beginning1
#  end1 NA        <NA>

dbWriteTable.2 (myCon,name='test2', value=test, overwrite = TRUE)
dbReadTable(myCon,name='test2')
# [1] TRUE
#   id           myString
# 1  1 beginning1 \n end1







2009/10/28 Paul Gilbert <pgilbert at bank-banque-canada.ca>:
> I've not had much luck myself with \n in MySQL strings. I suspect you get the same problem using the mysql client directly, which is usually a good test to see if the problem is R related or not. This could also have something to do with type of the field, and possibly with the character encoding.
>
> Paul
>
>>-----Original Message-----
>>From: r-sig-db-bounces at stat.math.ethz.ch [mailto:r-sig-db-
>>bounces at stat.math.ethz.ch] On Behalf Of H. Felix Wittmann
>>Sent: October 27, 2009 5:50 PM
>>To: Jeffrey Horner
>>Cc: r-sig-db at stat.math.ethz.ch
>>Subject: Re: [R-sig-DB] Fwd: rmysql and strings containg \n
>>
>>Hi Jeff,
>>
>>thanks for your answer. At the moment I've got the following
>>
>>R version 2.9.2 (2009-08-24)
>>'RMySQL' version 0.7-4
>>mysql ?Ver 14.12 Distrib 5.0.75, for debian-linux-gnu (x86_64) using
>>readline 5.2
>>
>>That said, similar things have consistently shown up in pretty much
>>all the environments that I've tested.
>>
>>Felix
>>
>>
>>2009/10/26 Jeffrey Horner <jeff.horner at vanderbilt.edu>
>>>
>>> Hi H. Felix Wittmann,
>>>
>>> We require that you tell us R's version, RMySQL's version, and MySQL's
>>version upon which you are running your code. Then we can better
>>determine if your problem is a bug or a feature.
>>>
>>> Best,
>>>
>>> Jeff
>>>
>>> H. Felix Wittmann wrote on 10/20/2009 04:37 PM:
>>>>
>>>> I' ve noticed a few typos in my previous post therefore I send a
>>>> cleaned up version
>>>>
>>>>
>>>> require(RMySQL)
>>>>
>>>> m <- dbDriver("MySQL")
>>>> myCon <- dbConnect(m,group = 'whateverGroup')
>>>> # replace this (ie 'whateverGroup') by the appropriate group
>>specified
>>>> in your my.cnf file
>>>> # on a typical UBUNTU installation you can
>>>> # modify this using the command line editor (eg bash) by this command
>>>> # sudo nano /etc/mysql/my.cnf
>>>>
>>>> test <- data.frame(id=1,myString='beginning1 \n end1')
>>>> # the above line produces (this is what the output should like like)
>>:
>>>> # > test
>>>> # ? id ? ? ? ? ? myString
>>>> # 1 ?1 beginning1 \n end1
>>>>
>>>> dbWriteTable(myCon,name='test', value=test)
>>>> dbReadTable(myCon,name='test')
>>>> # the above line produces (here we can see that the output is not the
>>>> same as above) :
>>>> # ? ? ? id ? ?myString
>>>> # 1 ? ? ?1 beginning1
>>>> # ?end1 NA ? ? ? ?<NA>
>>>>
>>>>
>>>> H. Felix Wittmann
>>>>
>>>> _______________________________________________
>>>> R-sig-DB mailing list -- R Special Interest Group
>>>> R-sig-DB at stat.math.ethz.ch
>>>> https://stat.ethz.ch/mailman/listinfo/r-sig-db
>>>
>>>
>>> --
>>> http://biostat.mc.vanderbilt.edu/JeffreyHorner
>>
>>_______________________________________________
>>R-sig-DB mailing list -- R Special Interest Group
>>R-sig-DB at stat.math.ethz.ch
>>https://stat.ethz.ch/mailman/listinfo/r-sig-db
> ====================================================================================
>
> La version fran?aise suit le texte anglais.
>
> ------------------------------------------------------------------------------------
>
> This email may contain privileged and/or confidential information, and the Bank of
> Canada does not waive any related rights. Any distribution, use, or copying of this
> email or the information it contains by other than the intended recipient is
> unauthorized. If you received this email in error please delete it immediately from
> your system and notify the sender promptly by email that you have done so.
>
> ------------------------------------------------------------------------------------
>
> Le pr?sent courriel peut contenir de l'information privil?gi?e ou confidentielle.
> La Banque du Canada ne renonce pas aux droits qui s'y rapportent. Toute diffusion,
> utilisation ou copie de ce courriel ou des renseignements qu'il contient par une
> personne autre que le ou les destinataires d?sign?s est interdite. Si vous recevez
> ce courriel par erreur, veuillez le supprimer imm?diatement et envoyer sans d?lai ?
> l'exp?diteur un message ?lectronique pour l'aviser que vous avez ?limin? de votre
> ordinateur toute copie du courriel re?u.
>
>

-------------- next part --------------
A non-text attachment was scrubbed...
Name: dbWriteTable.2.r
Type: application/octet-stream
Size: 782 bytes
Desc: not available
URL: <https://stat.ethz.ch/pipermail/r-sig-db/attachments/20091029/39d1d6e2/attachment.obj>

From Robert@McGehee @end|ng |rom geodec@p|t@|@com  Tue Nov  3 22:35:09 2009
From: Robert@McGehee @end|ng |rom geodec@p|t@|@com (McGehee, Robert)
Date: Tue, 3 Nov 2009 16:35:09 -0500
Subject: [R-sig-DB] dbWriteTable permission problem on Mac OSX
Message-ID: <EEBC169715EB8C438D3C9283AF0F201C0724A7A2@MSGBOSCLM2WIN.DMN1.FMR.COM>

R-Sig-DB,
I'm getting a 'Permission denied' error when trying to use the
'dbWriteTable' function (RPostgreSQL\DBI package) to access PostgreSQL
on my Mac OSX. I saw that this error appears to have been documented in
the README file as affecting versions of Linux running SELinux. However,
since my OSX Server is not running SELinux, I wanted to see if anyone
else has had success using 'dbWriteTable' on a Mac with RPostgreSQL.

I can update a database using INSERT commands, but am looking for a
faster solution. Also RODBC crashes my R session when executing the
'sqlTables' function--presumably due to an ODBC driver problem--so
switching packages won't easily help.

Thanks, Robert

---------------------
> dbWriteTable(pcon, "test", data)
Error in postgresqlExecStatement(conn, statement, ...) : 
  RS-DBI driver: (could not Retrieve the result : ERROR:  could not open
file "/tmp/RtmprNh2yy/rsdbi60b7acd9" for reading: Permission denied
)
Warning in postgresqlWriteTable(conn, name, value, ...) :
  could not load data into table
[1] FALSE


> R.version
               _                           
platform       x86_64-apple-darwin9.5.0    
arch           x86_64                      
os             darwin9.5.0                 
system         x86_64, darwin9.5.0         
status                                     
major          2                           
minor          9.0                         
year           2009                        
month          04                          
day            17                          
svn rev        48333                       
language       R                           
 



From @rm@trong@wh|t @end|ng |rom gm@||@com  Tue Nov  3 23:13:46 2009
From: @rm@trong@wh|t @end|ng |rom gm@||@com (Whit Armstrong)
Date: Tue, 3 Nov 2009 17:13:46 -0500
Subject: [R-sig-DB] dbWriteTable permission problem on Mac OSX
In-Reply-To: <EEBC169715EB8C438D3C9283AF0F201C0724A7A2@MSGBOSCLM2WIN.DMN1.FMR.COM>
References: <AcpczYM55AIvhg2/RvCIdIVwFvPm8g==>
	<EEBC169715EB8C438D3C9283AF0F201C0724A7A2@MSGBOSCLM2WIN.DMN1.FMR.COM>
Message-ID: <8ec76080911031413x2b157ecbr5c2df4eff1a17ad8@mail.gmail.com>

postgresqlWriteTable serializes your entire table to disk, and then
asks postgres to read it back.

It looks like the user name running postgres isn't able to read your file.

I'm sure someone on this list will help you solve your permission
issues.  In case they can't you can try my postgres driver (which
writes via binary transfer though the connection).

http://github.com/armstrtw/unifieddbi

-Whit


On Tue, Nov 3, 2009 at 4:35 PM, McGehee, Robert
<Robert.McGehee at geodecapital.com> wrote:
> R-Sig-DB,
> I'm getting a 'Permission denied' error when trying to use the
> 'dbWriteTable' function (RPostgreSQL\DBI package) to access PostgreSQL
> on my Mac OSX. I saw that this error appears to have been documented in
> the README file as affecting versions of Linux running SELinux. However,
> since my OSX Server is not running SELinux, I wanted to see if anyone
> else has had success using 'dbWriteTable' on a Mac with RPostgreSQL.
>
> I can update a database using INSERT commands, but am looking for a
> faster solution. Also RODBC crashes my R session when executing the
> 'sqlTables' function--presumably due to an ODBC driver problem--so
> switching packages won't easily help.
>
> Thanks, Robert
>
> ---------------------
>> dbWriteTable(pcon, "test", data)
> Error in postgresqlExecStatement(conn, statement, ...) :
> ?RS-DBI driver: (could not Retrieve the result : ERROR: ?could not open
> file "/tmp/RtmprNh2yy/rsdbi60b7acd9" for reading: Permission denied
> )
> Warning in postgresqlWriteTable(conn, name, value, ...) :
> ?could not load data into table
> [1] FALSE
>
>
>> R.version
> ? ? ? ? ? ? ? _
> platform ? ? ? x86_64-apple-darwin9.5.0
> arch ? ? ? ? ? x86_64
> os ? ? ? ? ? ? darwin9.5.0
> system ? ? ? ? x86_64, darwin9.5.0
> status
> major ? ? ? ? ?2
> minor ? ? ? ? ?9.0
> year ? ? ? ? ? 2009
> month ? ? ? ? ?04
> day ? ? ? ? ? ?17
> svn rev ? ? ? ?48333
> language ? ? ? R
>
>
> _______________________________________________
> R-sig-DB mailing list -- R Special Interest Group
> R-sig-DB at stat.math.ethz.ch
> https://stat.ethz.ch/mailman/listinfo/r-sig-db
>



From edd @end|ng |rom deb|@n@org  Tue Nov  3 23:14:49 2009
From: edd @end|ng |rom deb|@n@org (Dirk Eddelbuettel)
Date: Tue, 3 Nov 2009 16:14:49 -0600
Subject: [R-sig-DB] dbWriteTable permission problem on Mac OSX
In-Reply-To: <EEBC169715EB8C438D3C9283AF0F201C0724A7A2@MSGBOSCLM2WIN.DMN1.FMR.COM>
References: <EEBC169715EB8C438D3C9283AF0F201C0724A7A2@MSGBOSCLM2WIN.DMN1.FMR.COM>
Message-ID: <19184.43865.926410.871364@ron.nulle.part>


On 3 November 2009 at 16:35, McGehee, Robert wrote:
| I'm getting a 'Permission denied' error when trying to use the
| 'dbWriteTable' function (RPostgreSQL\DBI package) to access PostgreSQL
| on my Mac OSX. I saw that this error appears to have been documented in
| the README file as affecting versions of Linux running SELinux. However,

(That was an educated guess by Sameer at the time. He had other issues too...)

| since my OSX Server is not running SELinux, I wanted to see if anyone
| else has had success using 'dbWriteTable' on a Mac with RPostgreSQL.
| 
| I can update a database using INSERT commands, but am looking for a
| faster solution. Also RODBC crashes my R session when executing the
| 'sqlTables' function--presumably due to an ODBC driver problem--so
| switching packages won't easily help.
| 
| Thanks, Robert
| 
| ---------------------
| > dbWriteTable(pcon, "test", data)
| Error in postgresqlExecStatement(conn, statement, ...) : 
|   RS-DBI driver: (could not Retrieve the result : ERROR:  could not open
| file "/tmp/RtmprNh2yy/rsdbi60b7acd9" for reading: Permission denied
| )
| Warning in postgresqlWriteTable(conn, name, value, ...) :
|   could not load data into table

That looks straightforward. Me thinks that

  a) R creates its tempdir, so R owns /tmp/RtmprNh2yy/

  b) we're talking to Pg so the default Pg user will need to read that
     file -- my guess right now is that the default postgres user running
     the db is simply prohibited from reading inside that directory.

This gives you an idea of how to fix it short term -- maybe by using another
tempdir, maybe by using another fudge.   Alternatively, dump your data
outside of R and use Pg's native bulk copy tools ...   That said, I don't
quite recall by this doesn't bite us on Linux.

RPostgreSQL, I should add, is in a state of graceful unmaintainedness.  I
sort-of look after it as I once suggested for R's participation in the Google
Summer of Code as feel attached. But as I don;t currently run much code
against it, so my usage is light at best.  I have Neil helping me OS X
specific issue (ie he repaired/enhanced the build), Joe helps with real Pg
nitty gritty and Jeff offers some general database / DBI smart.

So with that:  if anyone knows and loves Postgresql and wants to help, please
do not hold back. We'd love to have you on the team.

Dirk

-- 
Three out of two people have difficulties with fractions.



From k@p@tp @end|ng |rom gm@||@com  Thu Nov  5 04:58:28 2009
From: k@p@tp @end|ng |rom gm@||@com (Prasenjit Kapat)
Date: Wed, 4 Nov 2009 22:58:28 -0500
Subject: [R-sig-DB] dbWriteTable permission problem on Mac OSX
In-Reply-To: <19184.43865.926410.871364@ron.nulle.part>
References: <EEBC169715EB8C438D3C9283AF0F201C0724A7A2@MSGBOSCLM2WIN.DMN1.FMR.COM>
	<19184.43865.926410.871364@ron.nulle.part>
Message-ID: <de8c7cb40911041958y445d88bre0bb3e6199d93648@mail.gmail.com>

Hi,

On Tue, Nov 3, 2009 at 5:14 PM, Dirk Eddelbuettel <edd at debian.org> wrote:
> | ---------------------
> | > dbWriteTable(pcon, "test", data)
> | Error in postgresqlExecStatement(conn, statement, ...) :
> | ? RS-DBI driver: (could not Retrieve the result : ERROR: ?could not open
> | file "/tmp/RtmprNh2yy/rsdbi60b7acd9" for reading: Permission denied
> | )
> | Warning in postgresqlWriteTable(conn, name, value, ...) :
> | ? could not load data into table
>
> That looks straightforward. Me thinks that
>
> ?a) R creates its tempdir, so R owns /tmp/RtmprNh2yy/
>
> ?b) we're talking to Pg so the default Pg user will need to read that
> ? ? file -- my guess right now is that the default postgres user running
> ? ? the db is simply prohibited from reading inside that directory.
>
> This gives you an idea of how to fix it short term -- maybe by using another
> tempdir, maybe by using another fudge. ? Alternatively, dump your data
> outside of R and use Pg's native bulk copy tools ... ? That said, I don't
> quite recall by this doesn't bite us on Linux.

I had a similar situation sometime back. And to this day the only
solution was to give global read/write permissions (on RedHat, SELinux
was disabled). Giving read perm is fine but not so for write. So:

If exporting directly from postgres to disk via any query then I use
/tmp. Otherwise, read them into R's data frame and then write to
anywhere on the disk from R.

-- 
Prasenjit



From edd @end|ng |rom deb|@n@org  Thu Nov  5 05:25:59 2009
From: edd @end|ng |rom deb|@n@org (Dirk Eddelbuettel)
Date: Wed, 4 Nov 2009 22:25:59 -0600
Subject: [R-sig-DB] dbWriteTable permission problem on Mac OSX
In-Reply-To: <de8c7cb40911041958y445d88bre0bb3e6199d93648@mail.gmail.com>
References: <EEBC169715EB8C438D3C9283AF0F201C0724A7A2@MSGBOSCLM2WIN.DMN1.FMR.COM>
	<19184.43865.926410.871364@ron.nulle.part>
	<de8c7cb40911041958y445d88bre0bb3e6199d93648@mail.gmail.com>
Message-ID: <19186.21463.574294.446926@ron.nulle.part>


On 4 November 2009 at 22:58, Prasenjit Kapat wrote:
| Hi,
| 
| On Tue, Nov 3, 2009 at 5:14 PM, Dirk Eddelbuettel <edd at debian.org> wrote:
| > | ---------------------
| > | > dbWriteTable(pcon, "test", data)
| > | Error in postgresqlExecStatement(conn, statement, ...) :
| > | ? RS-DBI driver: (could not Retrieve the result : ERROR: ?could not open
| > | file "/tmp/RtmprNh2yy/rsdbi60b7acd9" for reading: Permission denied
| > | )
| > | Warning in postgresqlWriteTable(conn, name, value, ...) :
| > | ? could not load data into table
| >
| > That looks straightforward. Me thinks that
| >
| > ?a) R creates its tempdir, so R owns /tmp/RtmprNh2yy/
| >
| > ?b) we're talking to Pg so the default Pg user will need to read that
| > ? ? file -- my guess right now is that the default postgres user running
| > ? ? the db is simply prohibited from reading inside that directory.
| >
| > This gives you an idea of how to fix it short term -- maybe by using another
| > tempdir, maybe by using another fudge. ? Alternatively, dump your data
| > outside of R and use Pg's native bulk copy tools ... ? That said, I don't
| > quite recall by this doesn't bite us on Linux.
| 
| I had a similar situation sometime back. And to this day the only
| solution was to give global read/write permissions (on RedHat, SELinux
| was disabled). Giving read perm is fine but not so for write. So:
| 
| If exporting directly from postgres to disk via any query then I use
| /tmp. Otherwise, read them into R's data frame and then write to
| anywhere on the disk from R.

And just to follow-up, now that I am home where Pg runs:

a) dbWriteTable() works for me under Debian

b) the per-R-session tempdir is clearly mode 0700:
drwx------  2 edd  edd     1024 2009-11-04 22:18 RtmpzGf6Ef

c) I am not sure how Pg gets to the data ... but it does for me as the
unittest tests/dbWriteTableTest.R in the sources also attests

Dirk

-- 
Three out of two people have difficulties with fractions.



From Robert@McGehee @end|ng |rom geodec@p|t@|@com  Thu Nov  5 14:39:19 2009
From: Robert@McGehee @end|ng |rom geodec@p|t@|@com (McGehee, Robert)
Date: Thu, 5 Nov 2009 08:39:19 -0500
Subject: [R-sig-DB] dbWriteTable permission problem on Mac OSX
References: <EEBC169715EB8C438D3C9283AF0F201C0724A7A2@MSGBOSCLM2WIN.DMN1.FMR.COM><19184.43865.926410.871364@ron.nulle.part><de8c7cb40911041958y445d88bre0bb3e6199d93648@mail.gmail.com>
	<19186.21463.574294.446926@ron.nulle.part>
Message-ID: <EEBC169715EB8C438D3C9283AF0F201C0724A94E@MSGBOSCLM2WIN.DMN1.FMR.COM>

So following the spirit of Dirk's advice, I resolved the problem by running the PostgreSQL database from my user account, rather than the 'postgres' user I had been using. Imports happened smoothly afterwards. Presumably 'postgres' couldn't see the temp files that my user was creating. 

Also on this note, the first error I encountered with 'dbWriteTable' when running from my user account and pg from the 'postgres' account informed me that the '\copy' command required superuser access. Granting my pg account Postgres superuser abilities fixed that, but I think it should be noted that the current implementation seems to require a wide-open set-up to use bulk copy.

I'm still keen to try out Whit's solution as well as a direct binary connection may get around the permission problems and has the potential to be faster. I'll report back if I discover anything interesting for the group.

Thanks to all.

Cheers,
Robert

-----Original Message-----
From: Dirk Eddelbuettel [mailto:edd at debian.org] 
Sent: Wednesday, November 04, 2009 11:26 PM
To: Prasenjit Kapat
Cc: McGehee, Robert; r-sig-db at stat.math.ethz.ch
Subject: Re: [R-sig-DB] dbWriteTable permission problem on Mac OSX


On 4 November 2009 at 22:58, Prasenjit Kapat wrote:
| Hi,
| 
| On Tue, Nov 3, 2009 at 5:14 PM, Dirk Eddelbuettel <edd at debian.org> wrote:
| > | ---------------------
| > | > dbWriteTable(pcon, "test", data)
| > | Error in postgresqlExecStatement(conn, statement, ...) :
| > | ? RS-DBI driver: (could not Retrieve the result : ERROR: ?could not open
| > | file "/tmp/RtmprNh2yy/rsdbi60b7acd9" for reading: Permission denied
| > | )
| > | Warning in postgresqlWriteTable(conn, name, value, ...) :
| > | ? could not load data into table
| >
| > That looks straightforward. Me thinks that
| >
| > ?a) R creates its tempdir, so R owns /tmp/RtmprNh2yy/
| >
| > ?b) we're talking to Pg so the default Pg user will need to read that
| > ? ? file -- my guess right now is that the default postgres user running
| > ? ? the db is simply prohibited from reading inside that directory.
| >
| > This gives you an idea of how to fix it short term -- maybe by using another
| > tempdir, maybe by using another fudge. ? Alternatively, dump your data
| > outside of R and use Pg's native bulk copy tools ... ? That said, I don't
| > quite recall by this doesn't bite us on Linux.
| 
| I had a similar situation sometime back. And to this day the only
| solution was to give global read/write permissions (on RedHat, SELinux
| was disabled). Giving read perm is fine but not so for write. So:
| 
| If exporting directly from postgres to disk via any query then I use
| /tmp. Otherwise, read them into R's data frame and then write to
| anywhere on the disk from R.

And just to follow-up, now that I am home where Pg runs:

a) dbWriteTable() works for me under Debian

b) the per-R-session tempdir is clearly mode 0700:
drwx------  2 edd  edd     1024 2009-11-04 22:18 RtmpzGf6Ef

c) I am not sure how Pg gets to the data ... but it does for me as the
unittest tests/dbWriteTableTest.R in the sources also attests

Dirk

-- 
Three out of two people have difficulties with fractions.


From edd @end|ng |rom deb|@n@org  Thu Nov  5 15:09:18 2009
From: edd @end|ng |rom deb|@n@org (Dirk Eddelbuettel)
Date: Thu, 5 Nov 2009 08:09:18 -0600
Subject: [R-sig-DB] dbWriteTable permission problem on Mac OSX
In-Reply-To: <EEBC169715EB8C438D3C9283AF0F201C0724A94E@MSGBOSCLM2WIN.DMN1.FMR.COM>
References: <EEBC169715EB8C438D3C9283AF0F201C0724A7A2@MSGBOSCLM2WIN.DMN1.FMR.COM>
	<19184.43865.926410.871364@ron.nulle.part>
	<de8c7cb40911041958y445d88bre0bb3e6199d93648@mail.gmail.com>
	<19186.21463.574294.446926@ron.nulle.part>
	<EEBC169715EB8C438D3C9283AF0F201C0724A94E@MSGBOSCLM2WIN.DMN1.FMR.COM>
Message-ID: <19186.56462.294419.155727@ron.nulle.part>


On 5 November 2009 at 08:39, McGehee, Robert wrote:
| So following the spirit of Dirk's advice, I resolved the problem by running
| the PostgreSQL database from my user account, rather than the 'postgres' user
| I had been using. Imports happened smoothly afterwards. Presumably 'postgres'
| couldn't see the temp files that my user was creating.  

Hm, my postgres servers (running versions 8.2 and 8.3, 8.2 has the default
port)  run as user postgres:

edd at ron:~> pstree -u | grep postgres
     |-postgres(postgres)---2*[postgres]
     |-postgres(postgres)---4*[postgres]
edd at ron:> 

Let's look at modes of /tmp, I have

edd at ron:~/src/debian/CRAN> ls -ld /tmp
drwxrwxrwt 43 root root 19456 2009-11-05 08:04 /tmp

So there is a sticky bit set for 'other' (the trailing t).  What do you have?

| Also on this note, the first error I encountered with 'dbWriteTable' when
| running from my user account and pg from the 'postgres' account informed me
| that the '\copy' command required superuser access. Granting my pg account
| Postgres superuser abilities fixed that, but I think it should be noted
| that the current implementation seems to require a wide-open set-up to use
| bulk copy. 
| 
| I'm still keen to try out Whit's solution as well as a direct binary
| connection may get around the permission problems and has the potential to
| be faster. I'll report back if I discover anything interesting for the
| group. 

Yes, it would also be nice to get that ported into RPostgreSQL.

Dirk

-- 
Three out of two people have difficulties with fractions.



From Robert@McGehee @end|ng |rom geodec@p|t@|@com  Thu Nov  5 16:45:52 2009
From: Robert@McGehee @end|ng |rom geodec@p|t@|@com (McGehee, Robert)
Date: Thu, 5 Nov 2009 10:45:52 -0500
Subject: [R-sig-DB] dbWriteTable permission problem on Mac OSX
References: <EEBC169715EB8C438D3C9283AF0F201C0724A7A2@MSGBOSCLM2WIN.DMN1.FMR.COM><19184.43865.926410.871364@ron.nulle.part><de8c7cb40911041958y445d88bre0bb3e6199d93648@mail.gmail.com><19186.21463.574294.446926@ron.nulle.part><EEBC169715EB8C438D3C9283AF0F201C0724A94E@MSGBOSCLM2WIN.DMN1.FMR.COM>
	<19186.56462.294419.155727@ron.nulle.part>
Message-ID: <EEBC169715EB8C438D3C9283AF0F201C0724A9B1@MSGBOSCLM2WIN.DMN1.FMR.COM>

My /tmp directory also has a sticky bit. 

Here's my guess of the problem. This chunk of code from the
'dbWriteTable' method 'postgresqlWriteTable' specifies different
directories for Linux and non-Linux (e.g. Mac) tempfiles. 
    if(as.character(Sys.info()["sysname"])=="Linux")
        fn <- tempfile("rsdbi","/tmp")
    else
        fn <- tempfile("rsdbi")

This difference is significant because on my Mac, the data file is saved
inside an R tempdir with only user read permissions:
drwx------   2 a347549 pcap       40 Oct 30 14:05 Rtmp8fgNQf/

while on Linux, the file is saved in /tmp, with whatever permissions
that has. So, presumably either patching the code as follows:
-    if(as.character(Sys.info()["sysname"])=="Linux")
+    if(as.character(Sys.info()["sysname"]) %in% c("Linux", "Darwin"))

or finding a way to have R create a tempdir with more generous
permissions would solve the problem. Changing my umask settings didn't
seem to change the permissions of the R tempdir--I'm not sure how that
is specified. Alternatively, I suppose the directory could be an option
to let the user specify somewhere readable by both 'postgres' and the
user's R session.

--Robert

-----Original Message-----
From: Dirk Eddelbuettel [mailto:edd at debian.org] 
Sent: Thursday, November 05, 2009 9:09 AM
To: McGehee, Robert
Cc: Prasenjit Kapat; r-sig-db at stat.math.ethz.ch
Subject: Re: [R-sig-DB] dbWriteTable permission problem on Mac OSX


On 5 November 2009 at 08:39, McGehee, Robert wrote:
| So following the spirit of Dirk's advice, I resolved the problem by
running
| the PostgreSQL database from my user account, rather than the
'postgres' user
| I had been using. Imports happened smoothly afterwards. Presumably
'postgres'
| couldn't see the temp files that my user was creating.  

Hm, my postgres servers (running versions 8.2 and 8.3, 8.2 has the
default
port)  run as user postgres:

edd at ron:~> pstree -u | grep postgres
     |-postgres(postgres)---2*[postgres]
     |-postgres(postgres)---4*[postgres]
edd at ron:> 

Let's look at modes of /tmp, I have

edd at ron:~/src/debian/CRAN> ls -ld /tmp
drwxrwxrwt 43 root root 19456 2009-11-05 08:04 /tmp

So there is a sticky bit set for 'other' (the trailing t).  What do you
have?

| Also on this note, the first error I encountered with 'dbWriteTable'
when
| running from my user account and pg from the 'postgres' account
informed me
| that the '\copy' command required superuser access. Granting my pg
account
| Postgres superuser abilities fixed that, but I think it should be
noted
| that the current implementation seems to require a wide-open set-up to
use
| bulk copy. 
| 
| I'm still keen to try out Whit's solution as well as a direct binary
| connection may get around the permission problems and has the
potential to
| be faster. I'll report back if I discover anything interesting for the
| group. 

Yes, it would also be nice to get that ported into RPostgreSQL.

Dirk

-- 
Three out of two people have difficulties with fractions.



From edd @end|ng |rom deb|@n@org  Thu Nov  5 16:55:56 2009
From: edd @end|ng |rom deb|@n@org (Dirk Eddelbuettel)
Date: Thu, 5 Nov 2009 09:55:56 -0600
Subject: [R-sig-DB] dbWriteTable permission problem on Mac OSX
In-Reply-To: <EEBC169715EB8C438D3C9283AF0F201C0724A9B1@MSGBOSCLM2WIN.DMN1.FMR.COM>
References: <EEBC169715EB8C438D3C9283AF0F201C0724A7A2@MSGBOSCLM2WIN.DMN1.FMR.COM>
	<19184.43865.926410.871364@ron.nulle.part>
	<de8c7cb40911041958y445d88bre0bb3e6199d93648@mail.gmail.com>
	<19186.21463.574294.446926@ron.nulle.part>
	<EEBC169715EB8C438D3C9283AF0F201C0724A94E@MSGBOSCLM2WIN.DMN1.FMR.COM>
	<19186.56462.294419.155727@ron.nulle.part>
	<EEBC169715EB8C438D3C9283AF0F201C0724A9B1@MSGBOSCLM2WIN.DMN1.FMR.COM>
Message-ID: <19186.62860.93463.540794@ron.nulle.part>


On 5 November 2009 at 10:45, McGehee, Robert wrote:
| My /tmp directory also has a sticky bit. 
| 
| Here's my guess of the problem. This chunk of code from the
| 'dbWriteTable' method 'postgresqlWriteTable' specifies different
| directories for Linux and non-Linux (e.g. Mac) tempfiles. 
|     if(as.character(Sys.info()["sysname"])=="Linux")
|         fn <- tempfile("rsdbi","/tmp")
|     else
|         fn <- tempfile("rsdbi")

Spot on!  Sameer added this on my recommendation based on the very same /tmp
issue problem.  Too bad I couldn't remember that this week :-/

| This difference is significant because on my Mac, the data file is saved
| inside an R tempdir with only user read permissions:
| drwx------   2 a347549 pcap       40 Oct 30 14:05 Rtmp8fgNQf/
| 
| while on Linux, the file is saved in /tmp, with whatever permissions
| that has. So, presumably either patching the code as follows:
| -    if(as.character(Sys.info()["sysname"])=="Linux")
| +    if(as.character(Sys.info()["sysname"]) %in% c("Linux", "Darwin"))

Agreed.  Explicitly CC'ing Neil for OS X advice -- Darwin is the best test?

| or finding a way to have R create a tempdir with more generous
| permissions would solve the problem. Changing my umask settings didn't
| seem to change the permissions of the R tempdir--I'm not sure how that
| is specified. Alternatively, I suppose the directory could be an option
| to let the user specify somewhere readable by both 'postgres' and the
| user's R session.

I think you can't easily which is why we created the workaround with the
temporary directory directly below /tmp, rather than by default in R's
tempdir /tmp/Rtmp...../

I think we got this bug!

Dirk


| --Robert
| 
| -----Original Message-----
| From: Dirk Eddelbuettel [mailto:edd at debian.org] 
| Sent: Thursday, November 05, 2009 9:09 AM
| To: McGehee, Robert
| Cc: Prasenjit Kapat; r-sig-db at stat.math.ethz.ch
| Subject: Re: [R-sig-DB] dbWriteTable permission problem on Mac OSX
| 
| 
| On 5 November 2009 at 08:39, McGehee, Robert wrote:
| | So following the spirit of Dirk's advice, I resolved the problem by
| running
| | the PostgreSQL database from my user account, rather than the
| 'postgres' user
| | I had been using. Imports happened smoothly afterwards. Presumably
| 'postgres'
| | couldn't see the temp files that my user was creating.  
| 
| Hm, my postgres servers (running versions 8.2 and 8.3, 8.2 has the
| default
| port)  run as user postgres:
| 
| edd at ron:~> pstree -u | grep postgres
|      |-postgres(postgres)---2*[postgres]
|      |-postgres(postgres)---4*[postgres]
| edd at ron:> 
| 
| Let's look at modes of /tmp, I have
| 
| edd at ron:~/src/debian/CRAN> ls -ld /tmp
| drwxrwxrwt 43 root root 19456 2009-11-05 08:04 /tmp
| 
| So there is a sticky bit set for 'other' (the trailing t).  What do you
| have?
| 
| | Also on this note, the first error I encountered with 'dbWriteTable'
| when
| | running from my user account and pg from the 'postgres' account
| informed me
| | that the '\copy' command required superuser access. Granting my pg
| account
| | Postgres superuser abilities fixed that, but I think it should be
| noted
| | that the current implementation seems to require a wide-open set-up to
| use
| | bulk copy. 
| | 
| | I'm still keen to try out Whit's solution as well as a direct binary
| | connection may get around the permission problems and has the
| potential to
| | be faster. I'll report back if I discover anything interesting for the
| | group. 
| 
| Yes, it would also be nice to get that ported into RPostgreSQL.
| 
| Dirk
| 
| -- 
| Three out of two people have difficulties with fractions.
| 

-- 
Three out of two people have difficulties with fractions.



From ne||t @end|ng |rom ne||t||||n@com  Thu Nov  5 18:43:37 2009
From: ne||t @end|ng |rom ne||t||||n@com (Neil Tiffin)
Date: Thu, 5 Nov 2009 11:43:37 -0600
Subject: [R-sig-DB] dbWriteTable permission problem on Mac OSX
In-Reply-To: <19186.62860.93463.540794@ron.nulle.part>
References: <EEBC169715EB8C438D3C9283AF0F201C0724A7A2@MSGBOSCLM2WIN.DMN1.FMR.COM>
	<19184.43865.926410.871364@ron.nulle.part>
	<de8c7cb40911041958y445d88bre0bb3e6199d93648@mail.gmail.com>
	<19186.21463.574294.446926@ron.nulle.part>
	<EEBC169715EB8C438D3C9283AF0F201C0724A94E@MSGBOSCLM2WIN.DMN1.FMR.COM>
	<19186.56462.294419.155727@ron.nulle.part>
	<EEBC169715EB8C438D3C9283AF0F201C0724A9B1@MSGBOSCLM2WIN.DMN1.FMR.COM>
	<19186.62860.93463.540794@ron.nulle.part>
Message-ID: <69C4B208-93EE-4881-AF02-DB4C3341ACD7@neiltiffin.com>


On Nov 5, 2009, at 9:55 AM, Dirk Eddelbuettel wrote:

>
> On 5 November 2009 at 10:45, McGehee, Robert wrote:
> | My /tmp directory also has a sticky bit.
> |
> | Here's my guess of the problem. This chunk of code from the
> | 'dbWriteTable' method 'postgresqlWriteTable' specifies different
> | directories for Linux and non-Linux (e.g. Mac) tempfiles.
> |     if(as.character(Sys.info()["sysname"])=="Linux")
> |         fn <- tempfile("rsdbi","/tmp")
> |     else
> |         fn <- tempfile("rsdbi")
>
> Spot on!  Sameer added this on my recommendation based on the very  
> same /tmp
> issue problem.  Too bad I couldn't remember that this week :-/
>
> | This difference is significant because on my Mac, the data file is  
> saved
> | inside an R tempdir with only user read permissions:
> | drwx------   2 a347549 pcap       40 Oct 30 14:05 Rtmp8fgNQf/
> |
> | while on Linux, the file is saved in /tmp, with whatever permissions
> | that has. So, presumably either patching the code as follows:
> | -    if(as.character(Sys.info()["sysname"])=="Linux")
> | +    if(as.character(Sys.info()["sysname"]) %in% c("Linux",  
> "Darwin"))
>
> Agreed.  Explicitly CC'ing Neil for OS X advice -- Darwin is the  
> best test?
>
Dirk,

"Darwin" is correct for Mac OS X.

The test dbWriteTableTest.R works for me without this change.  Is the  
test not robust enough?

Neil



From edd @end|ng |rom deb|@n@org  Thu Nov  5 20:42:59 2009
From: edd @end|ng |rom deb|@n@org (Dirk Eddelbuettel)
Date: Thu, 5 Nov 2009 13:42:59 -0600
Subject: [R-sig-DB] dbWriteTable permission problem on Mac OSX
In-Reply-To: <69C4B208-93EE-4881-AF02-DB4C3341ACD7@neiltiffin.com>
References: <EEBC169715EB8C438D3C9283AF0F201C0724A7A2@MSGBOSCLM2WIN.DMN1.FMR.COM>
	<19184.43865.926410.871364@ron.nulle.part>
	<de8c7cb40911041958y445d88bre0bb3e6199d93648@mail.gmail.com>
	<19186.21463.574294.446926@ron.nulle.part>
	<EEBC169715EB8C438D3C9283AF0F201C0724A94E@MSGBOSCLM2WIN.DMN1.FMR.COM>
	<19186.56462.294419.155727@ron.nulle.part>
	<EEBC169715EB8C438D3C9283AF0F201C0724A9B1@MSGBOSCLM2WIN.DMN1.FMR.COM>
	<19186.62860.93463.540794@ron.nulle.part>
	<69C4B208-93EE-4881-AF02-DB4C3341ACD7@neiltiffin.com>
Message-ID: <19187.10947.726056.693744@ron.nulle.part>


Neil,

On 5 November 2009 at 11:43, Neil Tiffin wrote:
| "Darwin" is correct for Mac OS X.

Cool, thanks.

| The test dbWriteTableTest.R works for me without this change.  Is the  
| test not robust enough?

Can you maybe follow-up with Robert about other possible cause of this?  As I
(as a non-Mac-user) understand it, there are numerous ways to install R (and
presumably Pg) onto OS X and the bug may bite only in some.  His suggested
fix strikes me as correct, though, as $TEMPDIR permissions are a general
Unixy way of doing things.

Dirk

-- 
Three out of two people have difficulties with fractions.



From Robert@McGehee @end|ng |rom geodec@p|t@|@com  Thu Nov  5 21:12:10 2009
From: Robert@McGehee @end|ng |rom geodec@p|t@|@com (McGehee, Robert)
Date: Thu, 5 Nov 2009 15:12:10 -0500
Subject: [R-sig-DB] dbWriteTable permission problem on Mac OSX
References: <EEBC169715EB8C438D3C9283AF0F201C0724A7A2@MSGBOSCLM2WIN.DMN1.FMR.COM><19184.43865.926410.871364@ron.nulle.part><de8c7cb40911041958y445d88bre0bb3e6199d93648@mail.gmail.com><19186.21463.574294.446926@ron.nulle.part><EEBC169715EB8C438D3C9283AF0F201C0724A94E@MSGBOSCLM2WIN.DMN1.FMR.COM><19186.56462.294419.155727@ron.nulle.part><EEBC169715EB8C438D3C9283AF0F201C0724A9B1@MSGBOSCLM2WIN.DMN1.FMR.COM><19186.62860.93463.540794@ron.nulle.part><69C4B208-93EE-4881-AF02-DB4C3341ACD7@neiltiffin.com>
	<19187.10947.726056.693744@ron.nulle.part>
Message-ID: <EEBC169715EB8C438D3C9283AF0F201C0724AA33@MSGBOSCLM2WIN.DMN1.FMR.COM>

I think the running hypothesis is that, for Macs, RPostgreSQL saves its
data into an R temp directory that may or may not be readable by the
account that runs the postgres database. If it is, then the test will
run fine (presumably in Neil's case). If it is not, then you will get a
permission error. I don't think there's anything wrong with the test
case, it just won't work for everyone's configuration. With the 'bug
fix', I believe the test will work for more configurations--though still
not all if either there is a Mac system that does not use /tmp, or if
/tmp is not readable by the Postgres user. In Neil's case, I'll guess
that either he runs Postgres out of his user account, or his R temp
directories are group or world readable--mine are not.

Another gotcha not addressed that I think could bite either a Linux or
Mac users is that the user's umask settings can influence whether the
bulk copy works. For instance, (I'm almost certain that) for anyone that
runs R and postgres from different accounts, the bulk import will fail
if their umask is set to 0077, since R temp files seem to follow umask
settings. 

As mentioned before, a better solution, in theory, will likely involve a
direct binary transfer.
--Robert

-----Original Message-----
From: Dirk Eddelbuettel [mailto:edd at debian.org] 
Sent: Thursday, November 05, 2009 2:43 PM
To: Neil Tiffin
Cc: McGehee, Robert; Prasenjit Kapat; r-sig-db at stat.math.ethz.ch
Subject: Re: [R-sig-DB] dbWriteTable permission problem on Mac OSX


Neil,

On 5 November 2009 at 11:43, Neil Tiffin wrote:
| "Darwin" is correct for Mac OS X.

Cool, thanks.

| The test dbWriteTableTest.R works for me without this change.  Is the

| test not robust enough?

Can you maybe follow-up with Robert about other possible cause of this?
As I
(as a non-Mac-user) understand it, there are numerous ways to install R
(and
presumably Pg) onto OS X and the bug may bite only in some.  His
suggested
fix strikes me as correct, though, as $TEMPDIR permissions are a general
Unixy way of doing things.

Dirk

-- 
Three out of two people have difficulties with fractions.



From @eth @end|ng |rom u@erpr|m@ry@net  Fri Nov  6 02:44:59 2009
From: @eth @end|ng |rom u@erpr|m@ry@net (Seth Falcon)
Date: Thu, 05 Nov 2009 17:44:59 -0800
Subject: [R-sig-DB] dbWriteTable() is renaming the 'end' column
In-Reply-To: <264855a00909300919v5062ee85ibb69a784d63a0dad@mail.gmail.com>
References: <4AC2850F.8000302@fhcrc.org>	
	<971536df0909291533k280fecc9tca8baf5ee678a9e2@mail.gmail.com>	
	<4AC29468.7090800@fhcrc.org>	
	<alpine.LFD.2.00.0909300441370.32082@gannet.stats.ox.ac.uk>	
	<4AC2FD17.9050108@fhcrc.org>	
	<486f230c0909300902x445607ah6976a6e7e90bfda7@mail.gmail.com>
	<264855a00909300919v5062ee85ibb69a784d63a0dad@mail.gmail.com>
Message-ID: <4AF37F9B.20403@userprimary.net>

Hello all,

I'm picking up this discussion from late September regarding the name 
mangling behavior of dbWriteTable as seen in RSQLite (and probably 
RMySQL too).

Here's a summary of the current situation:

The DBI package provides a make.db.names generic as well as a default 
function, make.db.names.default.  Both RSQLite and RMySQL define a 
method that simply calls this default function.  Both packages use the 
generic inside their dbWriteTable implementations.

The default function uses base::make.names and a translation of "." to 
"_" to make SQL identifiers.  Any strings that are quoted (either single 
or double) are left as-is.  I'm not sure the quote handling is correct. 
  Single quotes are used for string literals in SQL and are not valid 
identifiers, where as double quotes are used to create delimited 
identifiers.

When SQL keywords are encountered and the allow.keywords argument is 
FALSE, the result is the unfortunate name mangling.

Here are some possible changes:

1. Modify DBI::make.db.names.default such that if allow.keywords=FALSE 
any keywords encountered will be quoted using '"'.

2. Make a similar modification, but make it specific to the 
make.db.names method implemented by RSQLite.

3. Move away from name mangling entirely and rely more heavily on 
quoting.  To keep backwards compatibility, add a new argument, say 
'names.as.is' with default value FALSE.  When TRUE, data.frame column 
names will be quoted instead of mangled.  This could be done in addition 
to items 1 or 2.

4. Instead of a new argument to dbWriteTable, add an argument to 
SQLite() to make this configuration of the driver.

Comments?

Also, below, a note on Sean's suggestion for a quoting global option...

On 9/30/09 9:19 AM, Sean Davis wrote:
> 2009/9/30 Seth Falcon<seth at userprimary.net>:
>> The question to decide is what change should be made?  Such requests
>> could become errors with a message telling the user how to quote
>> problem strings.  Or the mangling could be changed to use the quoted
>> form and a warning issued.
>>
>> I would be interested to hear any opinions from users of RSQLite.
>
> I, personally, don't like name mangling as an option.  Is there room
> for an option() like "RSQLite.quoteSQL" =TRUE to quote all table names
> and column names in generated SQL and =FALSE to generate an error when
> a keyword is used?  (Obviously, sql queries generated by the user
> would not be subject to the quoting).  The reason to use an option()
> to control quoting is that quoting anywhere in SQL generally means
> that it is best to quote everywhere, so if there is a known default
> (that can be changed), users can choose to stick with quoting
> everywhere or nowhere (and deal with the occasional error due to
> trying to use a SQL keyword).

I don't think there is enough SQL generated by RSQLite to make this 
particularly useful.  dbWriteTable is the main SQL generating function I 
think.  I'd prefer to avoid global options whenever possible and allow 
control via function args.  But something along these lines could be useful.

For example, the driver constructor, e.g. SQLite(), could accept an 
argument that set the desired name behavior for dbWriteTable.

In any case, thanks for the feedback.

+ seth



From k@p@tp @end|ng |rom gm@||@com  Fri Nov  6 04:49:10 2009
From: k@p@tp @end|ng |rom gm@||@com (Prasenjit Kapat)
Date: Thu, 5 Nov 2009 22:49:10 -0500
Subject: [R-sig-DB] Some helper functions for RPostgreSQL
Message-ID: <de8c7cb40911051949y3950424agee905a31a9a4a4f4@mail.gmail.com>

Hi,

In light of the recent thread on dbWriteTable() (RPostgreSQL):

https://stat.ethz.ch/pipermail/r-sig-db/2009q4/000741.html
OR
http://article.gmane.org/gmane.comp.lang.r.db/297

I've attached some functions that I had written for my specific use
case (see below). I am not sure if they are at all useful for the
RPostgreSQL package, but if needed I can attempt to clean them up.
Even otherwise, if anyone finds them useful, feel free to use them.
The functions are "sufficiently" commented, I am not saying they are
well documented!

Use case (or more precisely, *restrictions*):
1. RedHat Linux 5.3

2. postgresql 8.1.18-2  (yes, an old version) - some of my issues were
fixed in the newer versions

3. I cannot access the db as 'postgres' user. Instead I'm given a
separate user/role/db wherein I create all my tables/schemas.

4. PostgreSQL server runs on host 'pghost' (say) and my machine is
'myhost' (say) - so transferring data between local disk and the
remote postgres server has to be done over the network fist: between
local disk and remote disk (ie using ssh/scp/rsync). Of course, the
functions are generic enough to handle the situation when the pg
server is on the local machine, without resorting to ssh/rsync.

5. Lastly, the file permissions issues are taken care of using
system(....), eg: 'chmod o+rw' before transferring and changing them
back after transferring.

6. Currently R 2.8.1, so can not upgrade to RPostgreSQL 0.1-6! But
this may change in near future.

Thanks,
-- 
Prasenjit

-------------- next part --------------
A non-text attachment was scrubbed...
Name: RPostgreSQL-helper.R
Type: application/octet-stream
Size: 9895 bytes
Desc: not available
URL: <https://stat.ethz.ch/pipermail/r-sig-db/attachments/20091105/3d24566b/attachment.obj>

From myrque@t|on@ @end|ng |rom gm@||@com  Wed Nov 18 22:14:20 2009
From: myrque@t|on@ @end|ng |rom gm@||@com (helpme)
Date: Wed, 18 Nov 2009 16:14:20 -0500
Subject: [R-sig-DB] Fwd: [R] Error using 32-bit R and RODBC package on
	64-bit Windows Server OS with R version 2.10
In-Reply-To: <d83668f80911181312s19e7f88apb57df3d9cf9bf700@mail.gmail.com>
References: <d83668f80911161239m1f25ed9dl992fe763480f79d6@mail.gmail.com>
	<6AEE705D-FA1E-4715-8973-22CABDE86733@me.com>
	<d83668f80911181312s19e7f88apb57df3d9cf9bf700@mail.gmail.com>
Message-ID: <d83668f80911181314k7d44360cr5ef9233831c503e1@mail.gmail.com>

---------- Forwarded message ----------
From: helpme <myrquestions at gmail.com>
Date: Wed, Nov 18, 2009 at 4:12 PM
Subject: Re: [R] Error using 32-bit R and RODBC package on 64-bit Windows
Server OS with R version 2.10
To: Marc Schwartz <marc_schwartz at me.com>
Cc: r-help at r-project.org


Now that I know RODBC only works with 32-bit ODBC drivers this explains the
problem I was having.

The system has a 64 bit ODBC driver is definitely installed. I can tell
because when you go to system32 folder and click on odbcad32.exe it goes to
the Microsoft ODBC manager where I can select the driver installed for the
64-bit Oracle system.

The system32 folder contains the 64 bit driver for ODBC. When I go to the
syswow64 directory and click on the odbcad32.exe it does not take me to the
Microsoft ODBC manager. Instead I get this error:

Navigate to C:\Windows\syswow64\odbcad32.exe
2.) Select System DSN
3.) Add "Microsoft ODBC for Oracle"
I receive this error: The Oracle(tm) client and networking components were
not found. These components are supplied by Oracle Corporation and are part
of the Oracle Version 7.3 (or greater) client software installation. You
will be unable to use this driver until these components have been
installed.



I don't believe the 32-bit ODBC driver is present. What is the best way to
tell if the 32-bit Oracle client software isn't installed and I'm wondering
if anyone has experience to install it on a 64-bit system and call it from
RODC?



On Mon, Nov 16, 2009 at 4:54 PM, Marc Schwartz <marc_schwartz at me.com> wrote:

> On Nov 16, 2009, at 2:39 PM, helpme wrote:
>
>  I am receiving an error when trying to connect to the Oracle Database
>> using
>> RODBC on a 64-bit Windows Server OS. The version of R is 2.10.0-win32.exe
>>
>> Is this the wrong version. Does RODBC only work with 32-bit ODBC drivers?
>>
>> 've read over all the posts and documentation manuals.
>> The system is Windows Server 2003 with R 2.81. and the latest downloadable
>> RODBC package. The Oracle SID/DSN is mfopdw. I made sure to add it to
>> Control Panel->Administrative Priviledges->Microsoft ODBC system/user DNS.
>>
>> I've also tried the following in no particular order:
>>
>> 1.) Turn on all oracle services in control panel->administrative
>> priviledges.
>> 2.) Checked tsnnames.ora for SID.
>> 3.) Add microsoft ODBC service to Control Panel services for SID
>> 4.) Use Sqldeveler to test connection another way besides R (It was
>> successful)
>> 5.) channel<-odbcDriverConnect(
>> connection="Driver={Microsoft ODBC for Oracle};
>> DSN=abc,UID=abc;PWD=abc;"case="oracle")
>>
>> received error drivers SQLAllocHandle on SQL_HANDLE_ENV failed one time;
>> another time I got the error that Oracle client and networking components
>> 7.3 or greater is not found.
>>
>> 6.) tnsping mfopdw
>>
>> lsnrctl start mfopdw
>>
>> tried to add oracle/bin to path
>>
>> Nothing is working.
>>
>
> Three quick comments:
>
> 1. A better place to post these types of queries would be on the R-SIG-DB
> e-mail list, which is focused in this domain. More info here:
>
>  https://stat.ethz.ch/mailman/listinfo/r-sig-db
>
> 2. Prof. Ripley will be a more definitive resource, so I would wait until
> he might respond.
>
> 3. If you have not yet, be sure to read the RODBC vignette, which is
> available either via:
>
>  vignette("ROBDC")
>
> or online at:
>
>  http://cran.r-project.org/web/packages/RODBC/vignettes/RODBC.pdf
>
>
> That all being said, since you have now posted what may be the root cause
> of your problem, which is the 64/32 bit details, I will venture a guess to
> say that this may be the problem. Since there is not a 64 bit version of R
> for Windows (save I believe the Revolution commercial release), if you are
> using 64 bit Oracle client binaries and ODBC drivers (if they exist), they
> will not be compatible with 32 bit R/RODBC.
>
> I know that on OSX, with 64 bit R/RODBC and 32 bit ODBC drivers for Oracle,
> the connectivity would not work, so it seems reasonable that the reverse
> configuration would not be compatible either.
>
> So, first, I would be sure that you are using 32 bit ODBC drivers for
> Oracle on Windows and not 64 bit. If you installed any other Oracle client
> related software, that likely also needs to be 32 bit as well.
>
> Then I would review the above vignette document and be sure that any
> general installation references and those specifically pertaining to Windows
> have been followed consistently, especially configuring $PATH and other
> environmental configuration items required for Oracle itself, which on some
> platforms usually include things like $ORACLE_HOME, $TNS_ADMIN and so forth.
>  You indicate above:
>
>
>  "tried to add oracle/bin to path"
>
> which does not definitively indicate that you actually did so. Did you?
>  Also, check the capitalization, as the path is normally something like
> c:\Oracle\bin.
>
> If you can connect to the Oracle server using Oracle's own clients such as
> the InstantClient, that typically means that most of the system
> configuration issues are correctly set up. If that connection is successful,
> then it may bring us back to the 32/64 bit conflict.
>
> HTH,
>
> Marc Schwartz
>
>

	[[alternative HTML version deleted]]



From nb@umberger @end|ng |rom y@hoo@com  Thu Nov 26 15:49:54 2009
From: nb@umberger @end|ng |rom y@hoo@com (Nick Baumberger)
Date: Thu, 26 Nov 2009 06:49:54 -0800 (PST)
Subject: [R-sig-DB] Using SpatiaLite 2.3.1 from RSQLite 0.7-3
Message-ID: <340191.72695.qm@web36204.mail.mud.yahoo.com>

Dear all,

I was wondering whether I can use the spatialite extension in combination with RSQLite. 

1) In theory libspatialite-2.dll and the other dll's should load dynamically, but in practice I am not able to correctly setup this thing or get any reasonable error message when doing "loadable.extensions = TRUE"

2) The other question is how SpatiaLite can work without having defined the my table called "geometry_columns"

setup
*****

Im am using R 2.9.1, RSQLite 0.7-3 on a Windows XP and have installed the following dll's on an accessible directory (to which I point a user defined env path variable)

RSQLite.dll
libspatialite-2.dll
libgeos-3-0-0.dll 
libgeos_c-1.dll
libproj-0.dll


preparation: get the spatial dataset
************************

install.packages("SQLiteMap")
library(RSQLite)

sqli.db <- system.file("sqlimaps/sids.db3", package="SQLiteMap")
drv <- dbDriver("SQLite")
con.1 <- dbConnect(drv, dbname = sqli.db)

sql <- 'select sidsmap.* from sidsmap'
rs <- dbSendQuery(con.1, sql)
my.spatial.data <- fetch(rs, n = -1)

-> has  one column geom

geom : chr  "POLYGON ((-79.2461929321289 35.8681526184082, -79.2379913330078 ......


prepare SpatiaLite 2.3.1 evironment
***********************************

see: http://www.gaia-gis.it/spatialite/

IMPORTANT CONFIGURATION STEP ! -> setting loadable.extensions = TRUE in order to dynamically load below dll's


m <- dbDriver("SQLite", max.con = 25)
con <- dbConnect(m, dbname="sqlite.db", loadable.extensions = TRUE)

sql <- 'SELECT load_extension("libspatialite-2.dll")'
rs <- dbSendQuery(con, sql)
dbGetException(con)
dbClearResult(rs)

sql <- 'SELECT load_extension("libgeos-3-0-0.dll")'
rs <- dbSendQuery(con, sql)
dbGetException(con)
dbClearResult(rs)

sql <- 'SELECT load_extension("libgeos_c-1.dll")'
rs <- dbSendQuery(con, sql)
dbGetException(con)
dbClearResult(rs)

sql <- 'SELECT load_extension("libproj-0.dll")'
rs <- dbSendQuery(con, sql)
dbGetException(con)
dbClearResult(rs)

-> don't get any error message

load my.spatial.data into sqlite database
****************************************

dbWriteTable(con, "mygeom", my.spatial.data)

operate on table
****************

dbGetQuery(con, "select  * from mygeom")[1:2,]    # this works

[[elided Yahoo spam]]
-> see

dbGetQuery(con, "select  ST_Centroid(geom) from mygeom")

msg

Fehler in sqliteExecStatement(con, statement, bind.data) : 
  RS-DBI driver: (error in statement: no such function: ST_Centroid)


obviously I have a problem binding the spatial functions / loading the dlls. Is there anybody out there who can help ?

best regards
Nick



From r@j@r@h|@guh@ @end|ng |rom gm@||@com  Sat Nov 28 22:19:47 2009
From: r@j@r@h|@guh@ @end|ng |rom gm@||@com (Rajarshi Guha)
Date: Sat, 28 Nov 2009 16:19:47 -0500
Subject: [R-sig-DB] problems with dbWriteTable in ROracle
Message-ID: <773cea9e0911281319y5155ff4bh386d7fb2fa01ca08@mail.gmail.com>

Hi, I'm running ROracle (0.5-9) on R 2.9.0 (OS X 10.5.4) with Oracle 10g R2.

I have a data.frame which I'd like to insert into a table. If I construct
INSERT statements by hand, and use dbSendQuery, it works fine (but slow).
However, I tried to use dbWriteTable and I get the error below:

Error in oraExecStatement(ps, data, ...) :
  RS-DBI driver: (ORA-02005: implicit (-1) length not valid for this bind or
define datatype
)

I have also tried using a prepared statement and then usign dbExecStatement,
but I get the same error. Googling around for this specific Oracle error
doesn't turn up anything useful. The only possible reason I can think that'd
cause this error is the fact that the DB table has 32 columns, but the last
24 have default values and don't need to be specified at INSERT time. Thus
my manual INSERT looks like

insert into follow_pivot (protocol_name, npoint, row_idx, col_idx,
plate_series, seq, symbol, ncgcidr,
value01,value02,value03,value04,value05,value06,value07,value08,value09,value10)
values ('cpt-hdg-followup', 10, 1, 10, 'FCP112409-1',
'CACAGCCAGGACATCCTGGAA' 'RPSAP58',97788,
56.1732988189,55.9832217164,48.1326145496,28.5420717346,19.7739828032,21.8269105525,16.4192873455,14.6544541707,7.0792574342,4.6970933227)

Does the dbWriteTable method take into account that tjhe input data.frame
might not have all 32 columns, and do the INSERT appropriately?

Any pointers would be appreciated.

-- 
Rajarshi Guha
NIH Chemical Genomics Center

	[[alternative HTML version deleted]]



From Seb@@t|@n@Ruecker @end|ng |rom commerzb@nk@com  Mon Nov 30 17:28:23 2009
From: Seb@@t|@n@Ruecker @end|ng |rom commerzb@nk@com (Ruecker, Sebastian)
Date: Mon, 30 Nov 2009 17:28:23 +0100
Subject: [R-sig-DB] FW: RSQLite does not read very large values correctly
Message-ID: <A38500AE28F91940BD346C4BE3A6950F01CB4D05@SE000282.ztb.icb.commerzbank.com>

Hello,

I posted this in the regular r-help list, but it may be more appropriate
here. Sorry about the double posting!

I am trying to import data from an SQLite database to R.
Unfortunately, I seem to get wrong data when I try to import very large
numbers.

For example:
I look at the database via SQLiteStudio(v.1.1.3) and I see the following
values:

OrderID	Day		TimeToclose
1		2009-11-25	29467907000		
2		2009-11-25	29467907000		
3		2009-11-25	29467907000		


Now I run this R Code:

> library("DBI")
> library("RSQLite")
> 
> # DB Connection
> con <- dbConnect(dbDriver("SQLite"), "C:/Temp/TickDB01.db")
> raw_Data <- dbGetQuery(con, "SELECT OrderID, Day, TimeToClose FROM
Tr_TickData WHERE OrderID in (1,2,3)")
> raw_Data
  OrderID        Day TimeToClose
1       1 2009-11-25  -596864072 
2       2 2009-11-25  -596864072
3       3 2009-11-25  -596864072


The values are totally wrong... Is it because RSQLite has a problem with
big numbers? 
TimeToClose is microseconds till 17:00. 

When I make the numbers smaller, it works again:

> raw_Data <- dbGetQuery(con, "SELECT TimeToClose/1000 as TTC FROM
Tr_TickData WHERE OrderID in (1,2,3)")
> raw_Data
       TTC
1 29467907
2 29467907
3 29467907


I would appreciate any help with this problem!

Thanks and regards,

Sebastian



From edd @end|ng |rom deb|@n@org  Mon Nov 30 18:04:52 2009
From: edd @end|ng |rom deb|@n@org (Dirk Eddelbuettel)
Date: Mon, 30 Nov 2009 11:04:52 -0600
Subject: [R-sig-DB] FW: RSQLite does not read very large values correctly
In-Reply-To: <A38500AE28F91940BD346C4BE3A6950F01CB4D05@SE000282.ztb.icb.commerzbank.com>
References: <A38500AE28F91940BD346C4BE3A6950F01CB4D05@SE000282.ztb.icb.commerzbank.com>
Message-ID: <19219.64308.149142.506524@ron.nulle.part>


On 30 November 2009 at 17:28, Ruecker, Sebastian wrote:
| Hello,
| 
| I posted this in the regular r-help list, but it may be more appropriate
| here. Sorry about the double posting!
| 
| I am trying to import data from an SQLite database to R.
| Unfortunately, I seem to get wrong data when I try to import very large
| numbers.
| 
| For example:
| I look at the database via SQLiteStudio(v.1.1.3) and I see the following
| values:
| 
| OrderID	Day		TimeToclose
| 1		2009-11-25	29467907000		
| 2		2009-11-25	29467907000		
| 3		2009-11-25	29467907000		
| 
| 
| Now I run this R Code:
| 
| > library("DBI")
| > library("RSQLite")
| > 
| > # DB Connection
| > con <- dbConnect(dbDriver("SQLite"), "C:/Temp/TickDB01.db")
| > raw_Data <- dbGetQuery(con, "SELECT OrderID, Day, TimeToClose FROM
| Tr_TickData WHERE OrderID in (1,2,3)")
| > raw_Data
|   OrderID        Day TimeToClose
| 1       1 2009-11-25  -596864072 
| 2       2 2009-11-25  -596864072
| 3       3 2009-11-25  -596864072
| 
| 
| The values are totally wrong... Is it because RSQLite has a problem with
| big numbers? 
| TimeToClose is microseconds till 17:00. 
| 
| When I make the numbers smaller, it works again:
| 
| > raw_Data <- dbGetQuery(con, "SELECT TimeToClose/1000 as TTC FROM
| Tr_TickData WHERE OrderID in (1,2,3)")
| > raw_Data
|        TTC
| 1 29467907
| 2 29467907
| 3 29467907
| 
| 
| I would appreciate any help with this problem!

Hm, can you cast them to floating point representation?  Or else, convert to
proper time types before storing or maybe before retrieving (if SQLite let's
you).

If these are seconds from/to the epoch, then double is not a bad choice. R
itself does the same for POSIXct values to give us fractional time up to
millisecond resolution. .

Dirk

-- 
Three out of two people have difficulties with fractions.



From @eth @end|ng |rom u@erpr|m@ry@net  Mon Nov 30 18:43:24 2009
From: @eth @end|ng |rom u@erpr|m@ry@net (Seth Falcon)
Date: Mon, 30 Nov 2009 09:43:24 -0800
Subject: [R-sig-DB] FW: RSQLite does not read very large values correctly
In-Reply-To: <19219.64308.149142.506524@ron.nulle.part>
References: <A38500AE28F91940BD346C4BE3A6950F01CB4D05@SE000282.ztb.icb.commerzbank.com>
	<19219.64308.149142.506524@ron.nulle.part>
Message-ID: <4B14043C.5030909@userprimary.net>

Hi,

On 11/30/09 9:04 AM, Dirk Eddelbuettel wrote:
>
> On 30 November 2009 at 17:28, Ruecker, Sebastian wrote:
> | Hello,
> |
> | I posted this in the regular r-help list, but it may be more appropriate
> | here. Sorry about the double posting!
> |
> | I am trying to import data from an SQLite database to R.
> | Unfortunately, I seem to get wrong data when I try to import very large
> | numbers.
> |
> | For example:
> | I look at the database via SQLiteStudio(v.1.1.3) and I see the following
> | values:
> |
> | OrderID	Day		TimeToclose
> | 1		2009-11-25	29467907000		
> | 2		2009-11-25	29467907000		
> | 3		2009-11-25	29467907000		
> |
> |
> | Now I run this R Code:
> |
> |>  library("DBI")
> |>  library("RSQLite")
> |>
> |>  # DB Connection
> |>  con<- dbConnect(dbDriver("SQLite"), "C:/Temp/TickDB01.db")
> |>  raw_Data<- dbGetQuery(con, "SELECT OrderID, Day, TimeToClose FROM
> | Tr_TickData WHERE OrderID in (1,2,3)")
> |>  raw_Data
> |   OrderID        Day TimeToClose
> | 1       1 2009-11-25  -596864072
> | 2       2 2009-11-25  -596864072
> | 3       3 2009-11-25  -596864072
> |
> |
> | The values are totally wrong... Is it because RSQLite has a problem with
> | big numbers?
> | TimeToClose is microseconds till 17:00.
> |
> | When I make the numbers smaller, it works again:
> |
> |>  raw_Data<- dbGetQuery(con, "SELECT TimeToClose/1000 as TTC FROM
> | Tr_TickData WHERE OrderID in (1,2,3)")
> |>  raw_Data
> |        TTC
> | 1 29467907
> | 2 29467907
> | 3 29467907
> |
> |
> | I would appreciate any help with this problem!
>
> Hm, can you cast them to floating point representation?  Or else, convert to
> proper time types before storing or maybe before retrieving (if SQLite let's
> you).
>
> If these are seconds from/to the epoch, then double is not a bad choice. R
> itself does the same for POSIXct values to give us fractional time up to
> millisecond resolution. .

I think the issue here is that SQLite's integer type allows for much 
larger integers than R's integer type can handle.

One quick solution is to convert to double on the way out.  In a small 
test db this seemed to work from RSQLite.   Use a query like  "SELECT 
1.0 * bigIntCol from someTable"

+ seth

-- 
Seth Falcon | @sfalcon | http://userprimary.net/users



From ggrothend|eck @end|ng |rom gm@||@com  Mon Nov 30 18:00:36 2009
From: ggrothend|eck @end|ng |rom gm@||@com (Gabor Grothendieck)
Date: Mon, 30 Nov 2009 12:00:36 -0500
Subject: [R-sig-DB] FW: RSQLite does not read very large values correctly
In-Reply-To: <A38500AE28F91940BD346C4BE3A6950F01CB4D05@SE000282.ztb.icb.commerzbank.com>
References: <A38500AE28F91940BD346C4BE3A6950F01CB4D05@SE000282.ztb.icb.commerzbank.com>
Message-ID: <971536df0911300900rd5aeef8n25323163b8f2fc3b@mail.gmail.com>

I am able to create sqlite tables with large numbers and read them back,
e.g.

> Lines <- "OrderID Day             TimeToclose
+ 1               2009-11-25      29467907000
+ 2               2009-11-25      29467907000
+ 3               2009-11-25      29467907000"
> DF <- read.table(textConnection(Lines), header = TRUE)
> library(sqldf)
>
> # sqldf creates an sqlite database with table DF
> #   uploading DF to it and reads it back
>
> DF2 <- sqldf("select * from DF")
>
> all.equal(DF, DF2, check.attributes = FALSE)
[1] TRUE
> DF
  OrderID        Day TimeToclose
1       1 2009-11-25 29467907000
2       2 2009-11-25 29467907000
3       3 2009-11-25 29467907000
> DF2
  OrderID     Day__1 TimeToclose
1       1 2009-11-25 29467907000
2       2 2009-11-25 29467907000
3       3 2009-11-25 29467907000

> R.version.string # Windows Vista
[1] "R version 2.10.0 Patched (2009-11-21 r50532)"
> packageDescription("RSQLite")$Version
[1] "0.7-3"
> packageDescription("DBI")$Version
[1] "0.2-4"
> packageDescription("sqldf")$Version
[1] "0-1.7"


On Mon, Nov 30, 2009 at 11:28 AM, Ruecker, Sebastian <
Sebastian.Ruecker at commerzbank.com> wrote:

> Hello,
>
> I posted this in the regular r-help list, but it may be more appropriate
> here. Sorry about the double posting!
>
> I am trying to import data from an SQLite database to R.
> Unfortunately, I seem to get wrong data when I try to import very large
> numbers.
>
> For example:
> I look at the database via SQLiteStudio(v.1.1.3) and I see the following
> values:
>
> OrderID Day             TimeToclose
> 1               2009-11-25      29467907000
> 2               2009-11-25      29467907000
> 3               2009-11-25      29467907000
>
>
> Now I run this R Code:
>
> > library("DBI")
> > library("RSQLite")
> >
> > # DB Connection
> > con <- dbConnect(dbDriver("SQLite"), "C:/Temp/TickDB01.db")
> > raw_Data <- dbGetQuery(con, "SELECT OrderID, Day, TimeToClose FROM
> Tr_TickData WHERE OrderID in (1,2,3)")
> > raw_Data
>  OrderID        Day TimeToClose
> 1       1 2009-11-25  -596864072
> 2       2 2009-11-25  -596864072
> 3       3 2009-11-25  -596864072
>
>
> The values are totally wrong... Is it because RSQLite has a problem with
> big numbers?
> TimeToClose is microseconds till 17:00.
>
> When I make the numbers smaller, it works again:
>
> > raw_Data <- dbGetQuery(con, "SELECT TimeToClose/1000 as TTC FROM
> Tr_TickData WHERE OrderID in (1,2,3)")
> > raw_Data
>       TTC
> 1 29467907
> 2 29467907
> 3 29467907
>
>
> I would appreciate any help with this problem!
>
> Thanks and regards,
>
> Sebastian
>
> _______________________________________________
> R-sig-DB mailing list -- R Special Interest Group
> R-sig-DB at stat.math.ethz.ch
> https://stat.ethz.ch/mailman/listinfo/r-sig-db
>

	[[alternative HTML version deleted]]



From j@nko@thy@on @end|ng |rom ku-e|ch@t@ett@de  Tue Dec  1 12:04:49 2009
From: j@nko@thy@on @end|ng |rom ku-e|ch@t@ett@de (Janko Thyson)
Date: Tue, 1 Dec 2009 12:04:49 +0100
Subject: [R-sig-DB] RMySQL and xampp: crash upon reading tables
Message-ID: <001101ca7276$1a09aeb0$4e1d0c10$@thyson@ku-eichstaett.de>

Hi everyone,

I am trying to get RMySQL to work with the MySQL version(s) that ship with
xampp.

What is strange is the fact that establishing a connection and writing
content to tables work just fine. However, R crashes when trying to read
tables from a database. I used MySQL-Server (5.0.67) prior to xampp and
everything worked fine.

The Xampp versions I tried: 
1.7.2 (MySQL 5.1.37), 1.7.1 (MySQL 5.1.33) and finally 1.7.0 (MySQL 5.0.67).
The last two xampp versions should definitely work, shouldn't they? Yet I've
experienced the same behavior also with the newest version, just having
RMySQL throw an error (which it should). Xampp is located at "C:\xampp

Here's some information that is probably relevant:

Code Snippets
#############

install.packages("RMySQL", repos=repos.cran,
	configure.args="--with-mysql-dir=C:/xampp/mysql/bin")

sessionInfo()
R version 2.9.2 (2009-08-24) 
i386-pc-mingw32 

locale:
LC_COLLATE=German_Germany.1252;LC_CTYPE=German_Germany.1252;LC_MONETARY=Germ
an_Germany.1252;LC_NUMERIC=C;LC_TIME=German_Germany.1252

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

.libPaths()
"C:/R/R-2.9.2/library"

Sys.getenv('MYSQL_HOME')
MYSQL_HOME 
        "" 
utils::readRegistry("SOFTWARE\\MySQL AB", hive="HLM", maxdepth=2)
$`MySQL Server 5.0`
$`MySQL Server 5.0`$DataLocation
[1] "C:\\xampp\\mysql\\data\\"

$`MySQL Server 5.0`$FoundExistingDataDir
[1] "1"

$`MySQL Server 5.0`$Location
[1] "C:\\xampp\\mysql\\"

$`MySQL Server 5.0`$Version
[1] "5.0.67"

Additional comments
####################

I also tried this:
set MYSQL_HOME=C:\xampp\mysql

But still Sys.getenv("MySQL_HOME") gave me "". What did I do wrong?

I think the registry entries were still there from the "old" MySQL-Server
5.0 installation and I simply adapted the values to the current xampp
configuration. If that was wrong, what do I do to get a "fresh and correct"
registry entry?

Best regards,
Janko Thyson



From j@nko@thy@on @end|ng |rom ku-e|ch@t@ett@de  Tue Dec  1 12:59:32 2009
From: j@nko@thy@on @end|ng |rom ku-e|ch@t@ett@de (Janko Thyson)
Date: Tue, 1 Dec 2009 12:59:32 +0100
Subject: [R-sig-DB] RMySQL and xampp: crash upon reading tables
Message-ID: <001401ca727d$bef842e0$3ce8c8a0$@thyson@ku-eichstaett.de>

Hi again,

I'm really sorry, but I was "one off" concerning the versions of xampp. The
one that includes MySQL 5.0.67 was version 1.6.8 and not 1.7.0.

It works fine now! 

One last question, though: It says at
http://biostat.mc.vanderbilt.edu/wiki/Main/RMySQL that RMySQL (0.7-4) should
also work with MySQL 5.1.33. I found that's not the case. Did I do something
wrong?

Best regards,
Janko Thyson

-----Urspr?ngliche Nachricht-----
Von: Janko Thyson [mailto:janko.thyson at ku-eichstaett.de] 
Gesendet: Dienstag, 1. Dezember 2009 12:05
An: 'r-sig-db at stat.math.ethz.ch'
Cc: 'janko.thyson at kuei.de'
Betreff: RMySQL and xampp: crash upon reading tables

Hi everyone,

I am trying to get RMySQL to work with the MySQL version(s) that ship with
xampp.

What is strange is the fact that establishing a connection and writing
content to tables work just fine. However, R crashes when trying to read
tables from a database. I used MySQL-Server (5.0.67) prior to xampp and
everything worked fine.

The Xampp versions I tried: 
1.7.2 (MySQL 5.1.37), 1.7.1 (MySQL 5.1.33) and finally 1.7.0 (MySQL 5.0.67).
The last two xampp versions should definitely work, shouldn?t they? Yet I?ve
experienced the same behavior also with the newest version, just having
RMySQL throw an error (which it should). Xampp is located at "C:\xampp

Here?s some information that is probably relevant:

Code Snippets
#############

install.packages("RMySQL", repos=repos.cran,
	configure.args="--with-mysql-dir=C:/xampp/mysql/bin")

sessionInfo()
R version 2.9.2 (2009-08-24) 
i386-pc-mingw32 

locale:
LC_COLLATE=German_Germany.1252;LC_CTYPE=German_Germany.1252;LC_MONETARY=Germ
an_Germany.1252;LC_NUMERIC=C;LC_TIME=German_Germany.1252

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

.libPaths()
"C:/R/R-2.9.2/library"

Sys.getenv('MYSQL_HOME')
MYSQL_HOME 
        "" 
utils::readRegistry("SOFTWARE\\MySQL AB", hive="HLM", maxdepth=2)
$`MySQL Server 5.0`
$`MySQL Server 5.0`$DataLocation
[1] "C:\\xampp\\mysql\\data\\"

$`MySQL Server 5.0`$FoundExistingDataDir
[1] "1"

$`MySQL Server 5.0`$Location
[1] "C:\\xampp\\mysql\\"

$`MySQL Server 5.0`$Version
[1] "5.0.67"

Additional comments
####################

I also tried this:
set MYSQL_HOME=C:\xampp\mysql

But still Sys.getenv(?MySQL_HOME?) gave me ??. What did I do wrong?

I think the registry entries were still there from the ?old? MySQL-Server
5.0 installation and I simply adapted the values to the current xampp
configuration. If that was wrong, what do I do to get a ?fresh and correct?
registry entry?

Best regards,
Janko Thyson



From n_torenv||et @end|ng |rom hotm@||@com  Tue Dec  8 04:01:15 2009
From: n_torenv||et @end|ng |rom hotm@||@com (Nick Torenvliet)
Date: Mon, 7 Dec 2009 22:01:15 -0500
Subject: [R-sig-DB] dynamic sql statements and dbGetQuery
Message-ID: <BAY143-W56527C1A9A878B07E1CF8FE8F0@phx.gbl>


Hi, 

Just new to R and rmysql and trying to run the same query on a list of databases as per the following...

library(RMySQL)
drv = dbDriver("MySQL")
dbList = c("TSE")

for (market in dbList){
    con = dbConnect(drv, host="localhost", dbname=market, user="root", pass="rootPass")
    dbTableList <- dbListTables(con)
    for (symbol in dbTableList){
        #get the latest date in the table
        string <- c("select max(date(dayDate)) from ", symbol)
        sql <- toString(string)
        tableData = dbGetQuery( con, sql)
        cat(tableData)        
    }
    dbDisconnect(con)
}

When I run the script I get the following error... 

select max(date(dayDate)) from  AABError in mysqlExecStatement(conn, statement, ...) : 
  RS-DBI driver: (could not run statement: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''select max(date(dayDate)) from , AAB, '' at line 1)


Where AAB is the first table in dbTableList... I'm kind of stumped as to why this won't work -- the sql itself checks out fine... I've experimented with parenthesis and a semicolon... and dbSendQuery as well... all to no avail...

Any help would be appreciated, 

Nick
 		 	   		  
	[[alternative HTML version deleted]]



From @d@v|@2 @end|ng |rom m@||@n|h@gov  Tue Dec  8 04:13:48 2009
From: @d@v|@2 @end|ng |rom m@||@n|h@gov (Sean Davis)
Date: Mon, 7 Dec 2009 22:13:48 -0500
Subject: [R-sig-DB] dynamic sql statements and dbGetQuery
In-Reply-To: <BAY143-W56527C1A9A878B07E1CF8FE8F0@phx.gbl>
References: <BAY143-W56527C1A9A878B07E1CF8FE8F0@phx.gbl>
Message-ID: <264855a00912071913r699ba50dtc303760227a14d06@mail.gmail.com>

On Mon, Dec 7, 2009 at 10:01 PM, Nick Torenvliet
<n_torenvliet at hotmail.com> wrote:
>
> Hi,
>
> Just new to R and rmysql and trying to run the same query on a list of databases as per the following...
>
> library(RMySQL)
> drv = dbDriver("MySQL")
> dbList = c("TSE")
>
> for (market in dbList){
> ? ?con = dbConnect(drv, host="localhost", dbname=market, user="root", pass="rootPass")
> ? ?dbTableList <- dbListTables(con)
> ? ?for (symbol in dbTableList){
> ? ? ? ?#get the latest date in the table
> ? ? ? ?string <- c("select max(date(dayDate)) from ", symbol)

Basic R problem here--c() forms a vector and is not a string
concatenation.  You want to use paste():

sql <- paste("select max(date(dayDate)) from",symbol)

> ? ? ? ?sql <- toString(string)

Now, you don't need this line.

Untested, so let us know if there is still a problem after switching
to using paste().  The other way to do this is to use sprintf().

Sean

> ? ? ? ?tableData = dbGetQuery( con, sql)
> ? ? ? ?cat(tableData)
> ? ?}
> ? ?dbDisconnect(con)
> }
>
> When I run the script I get the following error...
>
> select max(date(dayDate)) from ?AABError in mysqlExecStatement(conn, statement, ...) :
> ?RS-DBI driver: (could not run statement: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''select max(date(dayDate)) from , AAB, '' at line 1)
>
>
> Where AAB is the first table in dbTableList... I'm kind of stumped as to why this won't work -- the sql itself checks out fine... I've experimented with parenthesis and a semicolon... and dbSendQuery as well... all to no avail...
>
> Any help would be appreciated,
>
> Nick
>
> ? ? ? ?[[alternative HTML version deleted]]
>
> _______________________________________________
> R-sig-DB mailing list -- R Special Interest Group
> R-sig-DB at stat.math.ethz.ch
> https://stat.ethz.ch/mailman/listinfo/r-sig-db
>



From @m@rt|n @end|ng |rom |@|oc@com  Fri Dec 11 14:41:36 2009
From: @m@rt|n @end|ng |rom |@|oc@com (Alberto Martin)
Date: Fri, 11 Dec 2009 14:41:36 +0100
Subject: [R-sig-DB]  RMySQL for windows
Message-ID: <20091211133824.2A845E38025@smtpauth02.csee.onr.siteprotect.com>

 

            Dear Peter,

 

My name is Alberto and I would like to connect to SQL data base running R on
Windows and I have read that you got it by means of installing RMySQL. I
have downlodaded the binary version of MySQL for Windows (RMySQL
<http://stat.bell-labs.com/RS-DBI/download/RMySQL/RMySQL_0.5-7.tar.gz>
0.5-7) from http://stat.bell-labs.com/RS-DBI/download/ but I have no idea
how can I use it, because I have two main problems:

 

*         Error: package 'DBI' 0.1-10 was found, but >= 0.2.2 is required by
'RMySQL': Do you know where can I find the DBIv0.2.2 or latest for Windows?
and,
*         I need the function dbDriver and I do not where can I get it
from?.
 
               Sorry if you find the questions a little silly but I have
been working with R only a few weeks.
 
               Best regards,           

 

 


Alberto Mart?n Garc?a 


Meteorology and Radiation


R & D Department


______________________________________________________________________ 




Instituto de Sistemas Fotovoltaicos de concentraci?n S.A ISFOC S.A. 


C/ Juan Bravo 22 - 13500 PUERTOLLANO      (Ciudad Real) SPAIN 


Phone: +34 926441673 - Fax: +34 926429142 


e-mail:  <mailto:amartin at isfoc.com> amartin at isfoc.com 


____________________________________________________________________________
____ 





Aviso Legal.


La informaci?n contenida en este mensaje de correo electr?nico es
confidencial. Este mensaje y los documentos adjuntos a ?l van dirigidos
exclusivamente a su destinatario y contiene informaci?n confidencial, cuya
divulgaci?n no est? permitida por la ley, por lo que el acceso a este
mensaje por otras personas no est? autorizado. En caso de haber recibido
este mensaje por error, le rogamos que lo notifique inmediatamente al
remitente y se ponga en contacto con el INSTITUTO DE SISTEMAS FOTOVOLTAICOS
DE CONCENTRACI?N, S.A.U, v?a e-mail remitido a nuestra atenci?n y proceda a
su eliminaci?n, sin revisarlo ni reenviarlo. as? como a la de cualquier
documento adjunto al mismo. Igualmente, le informamos que la distribuci?n,
copia o utilizaci?n de este mensaje, o de cualquier documento adjunto al
mismo, cualquiera que fuera su finalidad, est?n prohibidas por la ley. 


De igual modo, si usted es el destinatario de este mensaje, le informamos
que INSTITUTO DE SISTEMAS FOTOVOLTAICOS DE CONCENTRACI?N, S.A., no asume
responsabilidad alguna sobre la confidencialidad, integridad, veracidad o
correcta recepci?n de este mensaje pues el correo electr?nico y las
comunicaciones a trav?s de Internet no permiten asegurar ni garantizar tales
extremos. Por ello, si usted no consintiese el empleo de los medios de
comunicaci?n antes referidos, les rogamos que nos lo comunique de manera
fehaciente e inmediata en la siguiente direcci?n: C/ Juan Bravo, 22 (13.500)
de Puertollano (Ciudad Real).




-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://stat.ethz.ch/pipermail/r-sig-db/attachments/20091211/1dd4c257/attachment.html>

-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.jpg
Type: image/jpeg
Size: 16911 bytes
Desc: not available
URL: <https://stat.ethz.ch/pipermail/r-sig-db/attachments/20091211/1dd4c257/attachment.jpg>

-------------- next part --------------
A non-text attachment was scrubbed...
Name: image002.jpg
Type: image/jpeg
Size: 1791 bytes
Desc: not available
URL: <https://stat.ethz.ch/pipermail/r-sig-db/attachments/20091211/1dd4c257/attachment-0001.jpg>

From je||rey@horner @end|ng |rom gm@||@com  Fri Dec 11 17:57:52 2009
From: je||rey@horner @end|ng |rom gm@||@com (Jeffrey Horner)
Date: Fri, 11 Dec 2009 10:57:52 -0600
Subject: [R-sig-DB] R-sig-DB Digest, Vol 62, Issue 4
In-Reply-To: <mailman.4754.1260547332.4157.r-sig-db@stat.math.ethz.ch>
References: <mailman.4754.1260547332.4157.r-sig-db@stat.math.ethz.ch>
Message-ID: <a8cee7640912110857wd32cc6g7f22f86869ec82ae@mail.gmail.com>

You will want to download R packages from CRAN:

http://cran.r-project.org/

as that site has the latest versions of all packages, including RMySQL and DBI.

Best,

Jeff

> My name is Alberto and I would like to connect to SQL data base running R on
> Windows and I have read that you got it by means of installing RMySQL. I
> have downlodaded the binary version of MySQL for Windows (RMySQL
> <http://stat.bell-labs.com/RS-DBI/download/RMySQL/RMySQL_0.5-7.tar.gz>
> 0.5-7) from http://stat.bell-labs.com/RS-DBI/download/ but I have no idea
> how can I use it, because I have two main problems:
>
>
>
> * ? ? ? ? Error: package 'DBI' 0.1-10 was found, but >= 0.2.2 is required by
> 'RMySQL': Do you know where can I find the DBIv0.2.2 or latest for Windows?
> and,
> * ? ? ? ? I need the function dbDriver and I do not where can I get it
> from?.
>
> ? ? ? ? ? ? ? Sorry if you find the questions a little silly but I have
> been working with R only a few weeks.
>
> ? ? ? ? ? ? ? Best regards,
>
>
>
>
>
>
> Alberto Mart?n Garc?a
>
>
> Meteorology and Radiation
>
>
> R & D Department
>
>
> ______________________________________________________________________
>
>
>
>
> Instituto de Sistemas Fotovoltaicos de concentraci?n S.A ISFOC S.A.
>
>
> C/ Juan Bravo 22 - 13500 PUERTOLLANO ? ? ?(Ciudad Real) SPAIN
>
>
> Phone: +34 926441673 - Fax: +34 926429142
>
>
> e-mail: ?<mailto:amartin at isfoc.com> amartin at isfoc.com

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



From @tephen@we||er @end|ng |rom revo|ut|on-comput|ng@com  Fri Dec 11 19:22:47 2009
From: @tephen@we||er @end|ng |rom revo|ut|on-comput|ng@com (Stephen Weller)
Date: Fri, 11 Dec 2009 10:22:47 -0800
Subject: [R-sig-DB] 1. RMySQL for windows (Alberto Martin)
Message-ID: <5d07dbf0912111022q10ce8ab5uf9be9ee87748f648@mail.gmail.com>

You need a newer version of the 'RMySQL' package for the version of R you
are running. Try installing 'RMySQL_0.7-4.zip' from the
CRAN website(http://cran.r-project.org/).


Stephen Weller
QA and Technical Support Engineer
Revolution Computing




On Fri, Dec 11, 2009 at 8:02 AM, <r-sig-db-request at stat.math.ethz.ch> wrote:

> Send R-sig-DB mailing list submissions to
>        r-sig-db at stat.math.ethz.ch
>
> To subscribe or unsubscribe via the World Wide Web, visit
>        https://stat.ethz.ch/mailman/listinfo/r-sig-db
> or, via email, send a message with subject or body 'help' to
>        r-sig-db-request at stat.math.ethz.ch
>
> You can reach the person managing the list at
>        r-sig-db-owner at stat.math.ethz.ch
>
> When replying, please edit your Subject line so it is more specific
> than "Re: Contents of R-sig-DB digest..."
>
>
> Today's Topics:
>
>   1.  RMySQL for windows (Alberto Martin)
>
>
> ----------------------------------------------------------------------
>
> Message: 1
> Date: Fri, 11 Dec 2009 14:41:36 +0100
> From: "Alberto Martin" <amartin at isfoc.com>
> Subject: [R-sig-DB]  RMySQL for windows
> To: <r-sig-db at stat.math.ethz.ch>
> Message-ID:
>        <20091211133824.2A845E38025 at smtpauth02.csee.onr.siteprotect.com>
> Content-Type: text/plain; charset="iso-8859-1"
>
>
>
>            Dear Peter,
>
>
>
> My name is Alberto and I would like to connect to SQL data base running R
> on
> Windows and I have read that you got it by means of installing RMySQL. I
> have downlodaded the binary version of MySQL for Windows (RMySQL
> <http://stat.bell-labs.com/RS-DBI/download/RMySQL/RMySQL_0.5-7.tar.gz>
> 0.5-7) from http://stat.bell-labs.com/RS-DBI/download/ but I have no idea
> how can I use it, because I have two main problems:
>
>
>
> *         Error: package 'DBI' 0.1-10 was found, but >= 0.2.2 is required
> by
> 'RMySQL': Do you know where can I find the DBIv0.2.2 or latest for Windows?
> and,
> *         I need the function dbDriver and I do not where can I get it
> from?.
>
>               Sorry if you find the questions a little silly but I have
> been working with R only a few weeks.
>
>               Best regards,
>
>
>
>
>
>
> Alberto Mart?n Garc?a
>
>
> Meteorology and Radiation
>
>
> R & D Department
>
>
> ______________________________________________________________________
>
>
>
>
> Instituto de Sistemas Fotovoltaicos de concentraci?n S.A ISFOC S.A.
>
>
> C/ Juan Bravo 22 - 13500 PUERTOLLANO      (Ciudad Real) SPAIN
>
>
> Phone: +34 926441673 - Fax: +34 926429142
>
>
> e-mail:  <mailto:amartin at isfoc.com> amartin at isfoc.com
>
>
>
> ____________________________________________________________________________
> ____
>
>
>
>
>
> Aviso Legal.
>
>
> La informaci?n contenida en este mensaje de correo electr?nico es
> confidencial. Este mensaje y los documentos adjuntos a ?l van dirigidos
> exclusivamente a su destinatario y contiene informaci?n confidencial, cuya
> divulgaci?n no est? permitida por la ley, por lo que el acceso a este
> mensaje por otras personas no est? autorizado. En caso de haber recibido
> este mensaje por error, le rogamos que lo notifique inmediatamente al
> remitente y se ponga en contacto con el INSTITUTO DE SISTEMAS FOTOVOLTAICOS
> DE CONCENTRACI?N, S.A.U, v?a e-mail remitido a nuestra atenci?n y proceda a
> su eliminaci?n, sin revisarlo ni reenviarlo. as? como a la de cualquier
> documento adjunto al mismo. Igualmente, le informamos que la distribuci?n,
> copia o utilizaci?n de este mensaje, o de cualquier documento adjunto al
> mismo, cualquiera que fuera su finalidad, est?n prohibidas por la ley.
>
>
> De igual modo, si usted es el destinatario de este mensaje, le informamos
> que INSTITUTO DE SISTEMAS FOTOVOLTAICOS DE CONCENTRACI?N, S.A., no asume
> responsabilidad alguna sobre la confidencialidad, integridad, veracidad o
> correcta recepci?n de este mensaje pues el correo electr?nico y las
> comunicaciones a trav?s de Internet no permiten asegurar ni garantizar
> tales
> extremos. Por ello, si usted no consintiese el empleo de los medios de
> comunicaci?n antes referidos, les rogamos que nos lo comunique de manera
> fehaciente e inmediata en la siguiente direcci?n: C/ Juan Bravo, 22
> (13.500)
> de Puertollano (Ciudad Real).
>
>
>
> -------------- next part --------------
> An HTML attachment was scrubbed...
> URL: <
> https://stat.ethz.ch/pipermail/r-sig-db/attachments/20091211/1dd4c257/attachment.html
> >
> -------------- next part --------------
> A non-text attachment was scrubbed...
> Name: not available
> Type: image/jpeg
> Size: 16911 bytes
> Desc: not available
> URL: <
> https://stat.ethz.ch/pipermail/r-sig-db/attachments/20091211/1dd4c257/attachment.jpe
> >
> -------------- next part --------------
> A non-text attachment was scrubbed...
> Name: not available
> Type: image/jpeg
> Size: 1791 bytes
> Desc: not available
> URL: <
> https://stat.ethz.ch/pipermail/r-sig-db/attachments/20091211/1dd4c257/attachment-0001.jpe
> >
>
> ------------------------------
>
> _______________________________________________
> R-sig-DB mailing list
> R-sig-DB at stat.math.ethz.ch
> https://stat.ethz.ch/mailman/listinfo/r-sig-db
>
>
> End of R-sig-DB Digest, Vol 62, Issue 4
> ***************************************
>

	[[alternative HTML version deleted]]



From @eth @end|ng |rom u@erpr|m@ry@net  Tue Dec 15 00:36:57 2009
From: @eth @end|ng |rom u@erpr|m@ry@net (Seth Falcon)
Date: Mon, 14 Dec 2009 15:36:57 -0800
Subject: [R-sig-DB] Release candidates for DBI and RSQLite
Message-ID: <4B26CC19.1020806@userprimary.net>

Hello all,

I've made available release candidates for DBI and RSQLite (URLs are 
below) and would appreciate feedback from users able to test these new 
versions.  My plan is to push updates to CRAN at the end of this week.

Here is a list of what's new (extracted from each package's NEWS file):

*** DBI ***
Version 0.2-5

* Code cleanups contributed by Matthias Burger: avoid partial argument
   name matching and use TRUE/FALSE, not T/F.

* Change behavior of make.db.names.default to quote SQL keywords if
   allow.keywords is FALSE.  Previously, SQL keywords would be name
   mangled with underscores and a digit.  Now they are quoted using
   '"'.

*** RSQLite ***

Version 0.8-0

- Upgrade to SQLite 3.6.19

- Apply some code and Rd cleanups contributed by Mattias Burger.
   Avoid partial argument name matching, use TRUE/FALSE not T/F and
   improve Rd markup.

- Integrate RUnit unit tests so that they run during R CMD check.
   Small improvements to make the tests run more quietly.  You can run
   the unit tests by calling RSQLite:::.test_RSQLite() (require latest
   version of the RUnit package).  Also had to disable some unit tests
   on Windows.  Investigation of details is on the TODO list.

- Add sqliteCopyDatabase, a function that uses SQLite's online backup
   API to allow a specified database to be copied to a file.  This can
   be used to create a file copy of an in memory database.

- Increase the default max connections from 16 to 200 in SQLite().
   Connections to SQLite are inexpensive and there is no longer any
   hard-coded limit to the number of connections you can have.
   However, memory is allocated at driver initialization time based on
   the maximum, so it is best not to use very large values unless you
   really need the connections.

- sqliteTransactionStatement, which is used by dbCommit, dbRollback,
   and dbBeginTransaction, now passes silent=TRUE to try() to suppress
   error messages.  Without this, code that could otherwise handle a
   failed commit gracefully had no way to suppress the error message.
   You can use dbGetException to see the error when
   sqliteTransactionStatement returns FALSE.

- dbConnect/sqliteNewConnection now throws an error if dbname argument
   is NA.  Previously, if as.character(NA) was provided, a database
   with filename "NA" was created.

- dbConnect/sqliteNewConnection now accepts two new arguments:

   flags:
       provides additional control over connetion details.  For
       convenience, you can specify one of SQLITE_RWC (default),
       SQLITE_RW, or SQLITE_RO to obtain a connection in
       read/write/create, read/write, or read only mode, respectively.
       See http://sqlite.org/c3ref/open.html for details.
   vfs:
       controls the virtual filesystem used by SQLite.  The default,
       NULL, lets SQLite select the appropriate vfs for the system.
       You can specify one of "unix-posix", "unix-afp", "unix-flock",
       "unix-dotfile", or "unix-none".  For details, see
       http://www.sqlite.org/compile.html.  This functionality is not
       available on Windows and non-NULL values of the vfs argument
       will be ignored with a warning.



URLs:

source:  http://bioconductor.org/packages/misc/DBI_0.2-41.tar.gz
windows: http://bioconductor.org/packages/misc/DBI_0.2-41.zip

source:  http://bioconductor.org/packages/misc/RSQLite_0.7-97.tar.gz
windows: http://bioconductor.org/packages/misc/RSQLite_0.7-97.zip


+ seth

-- 
Seth Falcon | @sfalcon | http://userprimary.net/users



From @eth @end|ng |rom u@erpr|m@ry@net  Tue Dec 22 15:21:18 2009
From: @eth @end|ng |rom u@erpr|m@ry@net (Seth Falcon)
Date: Tue, 22 Dec 2009 06:21:18 -0800
Subject: [R-sig-DB] Release candidates for DBI and RSQLite
In-Reply-To: <4B26CC19.1020806@userprimary.net>
References: <4B26CC19.1020806@userprimary.net>
Message-ID: <486f230c0912220621u691fba46y53decf156665a172@mail.gmail.com>

Hi again,

DBI 0.2-5 and RSQLite 0.8-0 are now on CRAN.

See the NEWS files for the final list of changes...

http://cran.fhcrc.org/web/packages/RSQLite/NEWS

+ seth