From dre@hk@te @end|ng |rom gm@||@com Sat Aug 25 01:10:28 2012 From: dre@hk@te @end|ng |rom gm@||@com (Kate Dresh) Date: Fri, 24 Aug 2012 18:10:28 -0500 Subject: [R-sig-DB] data frame remove column Message-ID: *Hi all,* I'm trying to filter a file through the columns. This file below is a example of my data frame. My true data frame has seven hundred thousand columns and 500 hundred lines. I need to identify and to remove all columns that all elements equal a number 1. In this my case, columns were deleted are number 1,5 and 10. file <-read.table(text=" 1 0 2 2 1 1 5 1 1 1 1 0 2 2 1 1 5 1 1 1 1 0 2 2 1 2 5 2 2 1 1 0 2 2 1 1 5 1 1 1 1 0 2 2 1 0 5 0 2 1 1 0 2 2 1 1 5 1 0 1 ",sep="",header=FALSE) the result after the filter will be 0 2 2 1 5 1 1 0 2 2 1 5 1 1 0 2 2 2 5 2 2 0 2 2 1 5 1 1 0 2 2 0 5 0 2 0 2 2 1 5 1 0 I used this commands idlength<-sapply(file,function(x) length(unique(x)) ), but came an error message: caught segfault *** address 0x4, cause 'memory not mapped' *My question: is it possible to remove the all columns from above file to *achieve* the desired result?* * * * * *Thank you for help* Kate [[alternative HTML version deleted]] From r|p|ey @end|ng |rom @t@t@@ox@@c@uk Sat Aug 25 08:14:52 2012 From: r|p|ey @end|ng |rom @t@t@@ox@@c@uk (Prof Brian Ripley) Date: Sat, 25 Aug 2012 07:14:52 +0100 Subject: [R-sig-DB] data frame remove column In-Reply-To: References: Message-ID: <50386D5C.3000107@stats.ox.ac.uk> This is off-topic for this list. Please use R-help. The list moderator. On 25/08/2012 00:10, Kate Dresh wrote: > *Hi all,* > > > > I'm trying to filter a file through the columns. This file below is a > example of my data frame. My true data frame has seven hundred thousand > columns and 500 hundred lines. I need to identify and to remove all columns > that all elements equal a number 1. In this my case, columns were deleted > are number 1,5 and 10. > > > > > > file <-read.table(text=" > > 1 0 2 2 1 1 5 1 1 1 > > 1 0 2 2 1 1 5 1 1 1 > > 1 0 2 2 1 2 5 2 2 1 > > 1 0 2 2 1 1 5 1 1 1 > > 1 0 2 2 1 0 5 0 2 1 > > 1 0 2 2 1 1 5 1 0 1 > > ",sep="",header=FALSE) > > > > the result after the filter will be > > > > 0 2 2 1 5 1 1 > > 0 2 2 1 5 1 1 > > 0 2 2 2 5 2 2 > > 0 2 2 1 5 1 1 > > 0 2 2 0 5 0 2 > > 0 2 2 1 5 1 0 > > > > I used this commands idlength<-sapply(file,function(x) length(unique(x)) > ), > > but came an error message: > > > > caught segfault *** > > address 0x4, cause 'memory not mapped' > > > > *My question: is it possible to remove the all columns from above file > to *achieve* the desired result?* > > * * > > * * > > *Thank you for help* > > > > Kate > > [[alternative HTML version deleted]] > > _______________________________________________ > R-sig-DB mailing list -- R Special Interest Group > R-sig-DB at r-project.org > https://stat.ethz.ch/mailman/listinfo/r-sig-db > -- Brian D. Ripley, ripley at stats.ox.ac.uk Professor of Applied Statistics, http://www.stats.ox.ac.uk/~ripley/ University of Oxford, Tel: +44 1865 272861 (self) 1 South Parks Road, +44 1865 272866 (PA) Oxford OX1 3TG, UK Fax: +44 1865 272595 From |ur|d@o @end|ng |rom gm@||@com Fri Sep 7 15:26:01 2012 From: |ur|d@o @end|ng |rom gm@||@com (Luis Ridao) Date: Fri, 7 Sep 2012 14:26:01 +0100 Subject: [R-sig-DB] RODBC connection linux (ubuntu) Message-ID: HI, i think this problem has come up pretty often on this and other lists and still have not managed to solved it. i need to connect to a an Oracle database through R. I have installed the oracle install client and the package RODBC in R but still get the same error: [RODBC] ERROR: state IM002, code 0, message [unixODBC][Driver Manager]Data source name not found, and no default driver specified 2: In odbcDriverConnect("DSN=MAGNUS;UID=my_uid;PWD=my_pwd") : ODBC connection failed /etc/odbc.ini looks like: odbc.ini --------------------------------------- [ORACLE_SAMPLE] driver = Easysoft ODBC-Oracle WP description = Easysoft Oracle ODBC WP driver server = oserver port = 1521 sid = 192.168.20.129/MAGNUS user = luisr password =my_pwd logging = No logfile = enable_user_catalog = yes enable_synonyms = yes metadata_dont_change_case = no metadata_dont_do_schema = no metadata_id = no limit_long = 0 i know i need another odbcinst.ini file but i don't know a stanza for it (i actually made the odbc.ini file above from some other source) the unixODBC driver is also installed in my linux system any ideas? thanks in advance [[alternative HTML version deleted]] From m@rc_@chw@rtz @end|ng |rom me@com Fri Sep 7 16:20:36 2012 From: m@rc_@chw@rtz @end|ng |rom me@com (Marc Schwartz) Date: Fri, 7 Sep 2012 09:20:36 -0500 Subject: [R-sig-DB] RODBC connection linux (ubuntu) In-Reply-To: References: Message-ID: <83F634A6-A1E4-4B41-A46F-CA4EE127AA88@me.com> On Sep 7, 2012, at 8:26 AM, Luis Ridao wrote: > HI, > > i think this problem has come up pretty often on this and other lists > and still have not managed to solved it. > > i need to connect to a an Oracle database through R. > > I have installed the oracle install client and the package RODBC in R > but still get the same error: > > [RODBC] ERROR: state IM002, code 0, message [unixODBC][Driver Manager]Data > source name not found, and no default driver specified > 2: In odbcDriverConnect("DSN=MAGNUS;UID=my_uid;PWD=my_pwd") : > ODBC connection failed > > /etc/odbc.ini looks like: > > odbc.ini > --------------------------------------- > [ORACLE_SAMPLE] > driver = Easysoft ODBC-Oracle WP > description = Easysoft Oracle ODBC WP driver > server = oserver > port = 1521 > sid = 192.168.20.129/MAGNUS > user = luisr > password =my_pwd > logging = No > logfile = > enable_user_catalog = yes > enable_synonyms = yes > metadata_dont_change_case = no > metadata_dont_do_schema = no > metadata_id = no > limit_long = 0 > > i know i need another odbcinst.ini file > but i don't know a stanza for it (i actually made the odbc.ini file above > from some other source) > > the unixODBC driver is also installed in my linux system > > any ideas? > > thanks in advance The content above is unlikely to be correct, as the EasySoft ODBC driver for Oracle is rather expensive as I recall, so I would doubt that you are using it on Ubuntu. The errors above suggest that you have not configured the DNS connection properly. In R use: vignette("RODBC") which will bring up the package vignette, which has additional details on configuring the DNS settings. As noted in the above document, if you have the Oracle client set up correctly, you should be able to login to the server from the command line. If you can, then that leaves RODBC related configuration issues to resolve. If you cannot, then you still have Oracle client related configuration issues and you may need assistance from your SysAdmin/DBAdmin. Regards, Marc Schwartz From pg||bert902 @end|ng |rom gm@||@com Tue Sep 18 17:40:26 2012 From: pg||bert902 @end|ng |rom gm@||@com (Paul Gilbert) Date: Tue, 18 Sep 2012 11:40:26 -0400 Subject: [R-sig-DB] RMySQL and RPostgreSQL interaction Message-ID: <505895EA.9040309@gmail.com> I am doing some comparison for which I load both RMySQL and RPostgreSQL from my own packages TSMySQL and TSPostgreSQL and I am getting the following warning building a vignette. I think with package namespaces / S4 definitions it is a bit difficult to generate a self contained example, at least I have not yet managed to do that, but I would appreciate any suggestions about what needs to be done. Loading required package: TSPostgreSQL Loading required package: RPostgreSQL Warning in .simpleDuplicateClass(def, prev) : A specification for class "dbObjectId" in package 'RPostgreSQL' seems equivalent to one from package 'RMySQL' and is not turning on duplicate class definitions for this class (I'm using R version 2.15.1 on Ubuntu.) Thanks, Paul From @pencer@gr@ve@ @end|ng |rom @tructuremon|tor|ng@com Tue Sep 18 18:34:01 2012 From: @pencer@gr@ve@ @end|ng |rom @tructuremon|tor|ng@com (Spencer Graves) Date: Tue, 18 Sep 2012 09:34:01 -0700 Subject: [R-sig-DB] RMySQL and RPostgreSQL interaction In-Reply-To: <505895EA.9040309@gmail.com> References: <505895EA.9040309@gmail.com> Message-ID: <5058A279.2060901@structuremonitoring.com> On 9/18/2012 8:40 AM, Paul Gilbert wrote: > I am doing some comparison for which I load both RMySQL and > RPostgreSQL from my own packages TSMySQL and TSPostgreSQL and I am > getting the following warning building a vignette. I think with > package namespaces / S4 definitions it is a bit difficult to generate > a self contained example, at least I have not yet managed to do that, > but I would appreciate any suggestions about what needs to be done. If someone else identifies your problem, great. Otherwise, have you tried creating a toy package containing only the minimum? If you get that to work, then I'd create a second toy package starting as a full copy of your existing package, then start throwing things out until you find the problem. I know that's a lot of work, but if you use a manual binary search technique (discard roughly half of the package at each step -- or discard first the 90% you think is least likely to contain the problem), it could go fairly fast. If you do it on R-Forge, then it's easy to give others access to your toy examples. I hope someone else helps you fix this without the route I suggested. If not, I hope this helps. Spencer > > Loading required package: TSPostgreSQL > Loading required package: RPostgreSQL > Warning in .simpleDuplicateClass(def, prev) : > A specification for class "dbObjectId" in package 'RPostgreSQL' > seems equivalent to one from package 'RMySQL' and is not turning on > duplicate class definitions for this class > > (I'm using R version 2.15.1 on Ubuntu.) > > Thanks, > Paul > > _______________________________________________ > R-sig-DB mailing list -- R Special Interest Group > R-sig-DB at r-project.org > https://stat.ethz.ch/mailman/listinfo/r-sig-db -- Spencer Graves, PE, PhD President and Chief Technology Officer Structure Inspection and Monitoring, Inc. 751 Emerson Ct. San Jos?, CA 95126 ph: 408-655-4567 web: www.structuremonitoring.com From @p|uque @end|ng |rom gm@||@com Tue Sep 18 19:30:00 2012 From: @p|uque @end|ng |rom gm@||@com (Sebastian P. Luque) Date: Tue, 18 Sep 2012 12:30:00 -0500 Subject: [R-sig-DB] RMySQL and RPostgreSQL interaction References: <505895EA.9040309@gmail.com> Message-ID: <87mx0n9r3b.fsf@kolob.subpolar.dyndns.org> On Tue, 18 Sep 2012 11:40:26 -0400, Paul Gilbert wrote: > I am doing some comparison for which I load both RMySQL and > RPostgreSQL from my own packages TSMySQL and TSPostgreSQL and I am > getting the following warning building a vignette. I think with > package namespaces / S4 definitions it is a bit difficult to generate > a self contained example, at least I have not yet managed to do that, > but I would appreciate any suggestions about what needs to be done. > Loading required package: TSPostgreSQL Loading required package: > RPostgreSQL Warning in .simpleDuplicateClass(def, prev) : A > specification for class "dbObjectId" in package 'RPostgreSQL' seems > equivalent to one from package 'RMySQL' and is not turning on > duplicate class definitions for this class I've seen this with a vignette using both the maptools and trip packages, but it was just a warning and the vignette built just fine. -- Seb From pg||bert902 @end|ng |rom gm@||@com Tue Sep 18 19:46:06 2012 From: pg||bert902 @end|ng |rom gm@||@com (Paul Gilbert) Date: Tue, 18 Sep 2012 13:46:06 -0400 Subject: [R-sig-DB] RMySQL and RPostgreSQL interaction In-Reply-To: <87mx0n9r3b.fsf@kolob.subpolar.dyndns.org> References: <505895EA.9040309@gmail.com> <87mx0n9r3b.fsf@kolob.subpolar.dyndns.org> Message-ID: <5058B35E.3060805@gmail.com> On 12-09-18 01:30 PM, Sebastian P. Luque wrote: > On Tue, 18 Sep 2012 11:40:26 -0400, > Paul Gilbert wrote: > >> I am doing some comparison for which I load both RMySQL and >> RPostgreSQL from my own packages TSMySQL and TSPostgreSQL and I am >> getting the following warning building a vignette. I think with >> package namespaces / S4 definitions it is a bit difficult to generate >> a self contained example, at least I have not yet managed to do that, >> but I would appreciate any suggestions about what needs to be done. > >> Loading required package: TSPostgreSQL Loading required package: >> RPostgreSQL Warning in .simpleDuplicateClass(def, prev) : A >> specification for class "dbObjectId" in package 'RPostgreSQL' seems >> equivalent to one from package 'RMySQL' and is not turning on >> duplicate class definitions for this class > > I've seen this with a vignette using both the maptools and trip > packages, but it was just a warning and the vignette built just fine. > Yes, it is just a warning, and the vignette builds fine. It also looks like there is probably a simple fix to remove the problem that causes the warning, since that is what the warning suggests. However, as Spencer pointed out, isolating a simple example is not so easy. Paul From @pencer@gr@ve@ @end|ng |rom @tructuremon|tor|ng@com Tue Sep 18 20:51:31 2012 From: @pencer@gr@ve@ @end|ng |rom @tructuremon|tor|ng@com (Spencer Graves) Date: Tue, 18 Sep 2012 11:51:31 -0700 Subject: [R-sig-DB] RMySQL and RPostgreSQL interaction In-Reply-To: <5058B35E.3060805@gmail.com> References: <505895EA.9040309@gmail.com> <87mx0n9r3b.fsf@kolob.subpolar.dyndns.org> <5058B35E.3060805@gmail.com> Message-ID: <5058C2B3.5000405@structuremonitoring.com> On 9/18/2012 10:46 AM, Paul Gilbert wrote: > On 12-09-18 01:30 PM, Sebastian P. Luque wrote: >> On Tue, 18 Sep 2012 11:40:26 -0400, >> Paul Gilbert wrote: >> >>> I am doing some comparison for which I load both RMySQL and >>> RPostgreSQL from my own packages TSMySQL and TSPostgreSQL and I am >>> getting the following warning building a vignette. I think with >>> package namespaces / S4 definitions it is a bit difficult to generate >>> a self contained example, at least I have not yet managed to do that, >>> but I would appreciate any suggestions about what needs to be done. >> >>> Loading required package: TSPostgreSQL Loading required package: >>> RPostgreSQL Warning in .simpleDuplicateClass(def, prev) : A >>> specification for class "dbObjectId" in package 'RPostgreSQL' seems >>> equivalent to one from package 'RMySQL' and is not turning on >>> duplicate class definitions for this class >> >> I've seen this with a vignette using both the maptools and trip >> packages, but it was just a warning and the vignette built just fine. >> > Yes, it is just a warning, and the vignette builds fine. It also looks > like there is probably a simple fix to remove the problem that causes > the warning, since that is what the warning suggests. However, as > Spencer pointed out, isolating a simple example is not so easy. CRAN policy, at least as of August 30, 2012, was to reject packages that generate either Warnings or Notes. Thus, "just a warning" is a show-stopper if you plan to submit the package to CRAN. There is now a "CRAN" function in the development version of "fda" on R-Forge that we use to reduce the test time with "R CMD check --as-cran". There was as thread on the R-Devel list, subject: "if(--as-cran)?", which I thought mentioned a similar function in a development version of R. Unfortunately, I can't find it now. Spencer > > Paul > > _______________________________________________ > R-sig-DB mailing list -- R Special Interest Group > R-sig-DB at r-project.org > https://stat.ethz.ch/mailman/listinfo/r-sig-db > > -- Spencer Graves, PE, PhD President and Chief Technology Officer Structure Inspection and Monitoring, Inc. 751 Emerson Ct. San Jos?, CA 95126 ph: 408-655-4567 web: www.structuremonitoring.com From @p|uque @end|ng |rom gm@||@com Tue Sep 18 20:58:30 2012 From: @p|uque @end|ng |rom gm@||@com (Sebastian P. Luque) Date: Tue, 18 Sep 2012 13:58:30 -0500 Subject: [R-sig-DB] RMySQL and RPostgreSQL interaction References: <505895EA.9040309@gmail.com> <87mx0n9r3b.fsf@kolob.subpolar.dyndns.org> <5058B35E.3060805@gmail.com> Message-ID: <877grr9mzt.fsf@kolob.subpolar.dyndns.org> On Tue, 18 Sep 2012 13:46:06 -0400, Paul Gilbert wrote: > On 12-09-18 01:30 PM, Sebastian P. Luque wrote: >> On Tue, 18 Sep 2012 11:40:26 -0400, >> Paul Gilbert wrote: >>> I am doing some comparison for which I load both RMySQL and >>> RPostgreSQL from my own packages TSMySQL and TSPostgreSQL and I am >>> getting the following warning building a vignette. I think with >>> package namespaces / S4 definitions it is a bit difficult to >>> generate a self contained example, at least I have not yet managed >>> to do that, but I would appreciate any suggestions about what needs >>> to be done. >>> Loading required package: TSPostgreSQL Loading required package: >>> RPostgreSQL Warning in .simpleDuplicateClass(def, prev) : A >>> specification for class "dbObjectId" in package 'RPostgreSQL' seems >>> equivalent to one from package 'RMySQL' and is not turning on >>> duplicate class definitions for this class >> I've seen this with a vignette using both the maptools and trip >> packages, but it was just a warning and the vignette built just fine. > Yes, it is just a warning, and the vignette builds fine. It also looks > like there is probably a simple fix to remove the problem that causes > the warning, since that is what the warning suggests. However, as > Spencer pointed out, isolating a simple example is not so easy. I had actually built such an example with those packages (available from CRAN): ---<--------------------cut here---------------start------------------->--- \documentclass{article} \usepackage{Sweave} \begin{document} <>= library(maptools) library(trip) @ \end{document} ---<--------------------cut here---------------end--------------------->--- which produces the warnings: ---<--------------------cut here---------------start------------------->--- Warning in .simpleDuplicateClass(def, prev) : ?A specification for class ?owin? in package ?trip? seems equivalent to one from package ?maptools? and is not turning on duplicate class definitions for this class Warning in .simpleDuplicateClass(def, prev) : ?A specification for class ?ppp? in package ?trip? seems equivalent to one from package ?maptools? and is not turning on duplicate class definitions for this class Warning in .simpleDuplicateClass(def, prev) : ?A specification for class ?psp? in package ?trip? seems equivalent to one from package ?maptools? and is not turning on duplicate class definitions for this class ---<--------------------cut here---------------end--------------------->--- Both packages have the same definitions for these classes. A quick search on this warning points to https://stat.ethz.ch/pipermail/r-devel/2011-July/061628.html, which suggests that adding an importClassesFrom() directive in NAMESPACE and removing the class definition from one of the packages, might solve this. A dependency may be needed though... -- Seb From pg||bert902 @end|ng |rom gm@||@com Tue Sep 18 23:04:01 2012 From: pg||bert902 @end|ng |rom gm@||@com (Paul Gilbert) Date: Tue, 18 Sep 2012 17:04:01 -0400 Subject: [R-sig-DB] RMySQL and RPostgreSQL interaction In-Reply-To: <877grr9mzt.fsf@kolob.subpolar.dyndns.org> References: <505895EA.9040309@gmail.com> <87mx0n9r3b.fsf@kolob.subpolar.dyndns.org> <5058B35E.3060805@gmail.com> <877grr9mzt.fsf@kolob.subpolar.dyndns.org> Message-ID: <5058E1C1.90401@gmail.com> So it has to do with Sweave. Following Sebastian's model, This vignette produces the warning \documentclass{article} \usepackage{Sweave} \begin{document} \begin{Scode} library("RMySQL") library("RPostgreSQL") \end{Scode} \end{document} but running the those lines in an R session does not. I think I will re-phrase the issue and move it to R-devel. Paul On 12-09-18 02:58 PM, Sebastian P. Luque wrote: > On Tue, 18 Sep 2012 13:46:06 -0400, > Paul Gilbert wrote: > >> On 12-09-18 01:30 PM, Sebastian P. Luque wrote: >>> On Tue, 18 Sep 2012 11:40:26 -0400, >>> Paul Gilbert wrote: > >>>> I am doing some comparison for which I load both RMySQL and >>>> RPostgreSQL from my own packages TSMySQL and TSPostgreSQL and I am >>>> getting the following warning building a vignette. I think with >>>> package namespaces / S4 definitions it is a bit difficult to >>>> generate a self contained example, at least I have not yet managed >>>> to do that, but I would appreciate any suggestions about what needs >>>> to be done. > >>>> Loading required package: TSPostgreSQL Loading required package: >>>> RPostgreSQL Warning in .simpleDuplicateClass(def, prev) : A >>>> specification for class "dbObjectId" in package 'RPostgreSQL' seems >>>> equivalent to one from package 'RMySQL' and is not turning on >>>> duplicate class definitions for this class > >>> I've seen this with a vignette using both the maptools and trip >>> packages, but it was just a warning and the vignette built just fine. > >> Yes, it is just a warning, and the vignette builds fine. It also looks >> like there is probably a simple fix to remove the problem that causes >> the warning, since that is what the warning suggests. However, as >> Spencer pointed out, isolating a simple example is not so easy. > > I had actually built such an example with those packages (available from > CRAN): > > ---<--------------------cut here---------------start------------------->--- > \documentclass{article} > \usepackage{Sweave} > > \begin{document} > > <>= > library(maptools) > library(trip) > > @ > > \end{document} > ---<--------------------cut here---------------end--------------------->--- > > which produces the warnings: > > ---<--------------------cut here---------------start------------------->--- > Warning in .simpleDuplicateClass(def, prev) : > A specification for class ?owin? in package ?trip? seems equivalent to one from package ?maptools? and is not turning on duplicate class definitions for this class > Warning in .simpleDuplicateClass(def, prev) : > A specification for class ?ppp? in package ?trip? seems equivalent to one from package ?maptools? and is not turning on duplicate class definitions for this class > Warning in .simpleDuplicateClass(def, prev) : > A specification for class ?psp? in package ?trip? seems equivalent to one from package ?maptools? and is not turning on duplicate class definitions for this class > ---<--------------------cut here---------------end--------------------->--- > > Both packages have the same definitions for these classes. A quick > search on this warning points to > https://stat.ethz.ch/pipermail/r-devel/2011-July/061628.html, which > suggests that adding an importClassesFrom() directive in NAMESPACE and > removing the class definition from one of the packages, might solve > this. A dependency may be needed though... > From tomo@k|n @end|ng |rom @t@||@k@n@z@w@-u@@c@jp Wed Sep 19 04:33:48 2012 From: tomo@k|n @end|ng |rom @t@||@k@n@z@w@-u@@c@jp (NISHIYAMA Tomoaki) Date: Wed, 19 Sep 2012 11:33:48 +0900 Subject: [R-sig-DB] RMySQL and RPostgreSQL interaction In-Reply-To: <505895EA.9040309@gmail.com> References: <505895EA.9040309@gmail.com> Message-ID: <4356BEF4-7789-4E28-B78B-6186E6EA5E3D@staff.kanazawa-u.ac.jp> Hi, It is a pity that such interference between different database driver happens. We can exclude export of dbObjectId from RPostgreSQL, but I reallized that would cause a warning on TSPostgreSQL. One of the possible solution may moving the definition of db* to DBI and exclude from individual database drivers. Other routines that may be imported to DBI includes dbBeginTransaction(), now deleted from RPostgreSQL, but some request are coming. DBI is last published on 2009-12-22, but not changed thereafter. http://cran.r-project.org/web/packages/DBI/index.html Author: R Special Interest Group on Databases (R-SIG-DB) So, it appears the DBI can be updated based on discussion on this mailing-list. But I do not know how do we make agreement and update. Best regards, On 2012/09/19, at 0:40, Paul Gilbert wrote: > I am doing some comparison for which I load both RMySQL and RPostgreSQL from my own packages TSMySQL and TSPostgreSQL and I am getting the following warning building a vignette. I think with package namespaces / S4 definitions it is a bit difficult to generate a self contained example, at least I have not yet managed to do that, but I would appreciate any suggestions about what needs to be done. > > Loading required package: TSPostgreSQL > Loading required package: RPostgreSQL > Warning in .simpleDuplicateClass(def, prev) : > A specification for class "dbObjectId" in package 'RPostgreSQL' seems equivalent to one from package 'RMySQL' and is not turning on duplicate class definitions for this class > > (I'm using R version 2.15.1 on Ubuntu.) > > Thanks, > Paul > > _______________________________________________ > R-sig-DB mailing list -- R Special Interest Group > R-sig-DB at r-project.org > https://stat.ethz.ch/mailman/listinfo/r-sig-db From r|p|ey @end|ng |rom @t@t@@ox@@c@uk Wed Sep 19 10:26:05 2012 From: r|p|ey @end|ng |rom @t@t@@ox@@c@uk (Prof Brian Ripley) Date: Wed, 19 Sep 2012 09:26:05 +0100 Subject: [R-sig-DB] RMySQL and RPostgreSQL interaction In-Reply-To: <4356BEF4-7789-4E28-B78B-6186E6EA5E3D@staff.kanazawa-u.ac.jp> References: <505895EA.9040309@gmail.com> <4356BEF4-7789-4E28-B78B-6186E6EA5E3D@staff.kanazawa-u.ac.jp> Message-ID: <5059819D.9020209@stats.ox.ac.uk> On 19/09/2012 03:33, NISHIYAMA Tomoaki wrote: > Hi, > > It is a pity that such interference between different database driver happens. > We can exclude export of dbObjectId from RPostgreSQL, but I reallized that would > cause a warning on TSPostgreSQL. > One of the possible solution may moving the definition of db* to DBI and exclude from > individual database drivers. Other routines that may be imported to DBI includes > dbBeginTransaction(), now deleted from RPostgreSQL, but some request are coming. > > DBI is last published on 2009-12-22, but not changed thereafter. > http://cran.r-project.org/web/packages/DBI/index.html > Author: R Special Interest Group on Databases (R-SIG-DB) > > So, it appears the DBI can be updated based on discussion on this mailing-list. > But I do not know how do we make agreement and update. > Not so: CRAN packages can only be updated by their maintainer (Cc:ed here). If David James does not want to update DBI, he maybe could be persuaded to hand over the maintainership. -- Brian D. Ripley, ripley at stats.ox.ac.uk Professor of Applied Statistics, http://www.stats.ox.ac.uk/~ripley/ University of Oxford, Tel: +44 1865 272861 (self) 1 South Parks Road, +44 1865 272866 (PA) Oxford OX1 3TG, UK Fax: +44 1865 272595 From j@me@@d@v|d@@m|th @end|ng |rom gm@||@com Fri Sep 28 16:14:51 2012 From: j@me@@d@v|d@@m|th @end|ng |rom gm@||@com (James David Smith) Date: Fri, 28 Sep 2012 15:14:51 +0100 Subject: [R-sig-DB] R and PostgreSQL - Writing data? Message-ID: Dear all, Sorry if this isn't quite the right place, but it's the first time I've posted here. My issue is to do with writing to a PostgreSQL database from within R. My situation is best explained by some R code to start: #Connect to the database con <- dbConnect(PostgreSQL(), user="postgres", password="password", dbname="database") #Get some data out of the database. ucam_no2$original_data <- dbGetQuery(con, "select ucam_no2 FROM table") This returns say 10000 rows of data, but there is only data in about half of those rows. What I want to do is interpolate the missing data so I do this: #Generate some data ucam_no2$interpolated_data <- na.approx(ucam_data$ucam_no2, na.rm = FALSE) This works well and I now have 10000 rows of data with no empty cells. I now want to write this back into my PostgresSQL database. Into the same row that I took the data from in the first place. But I don't know how. I can write to a new table with something like the below, but what I'd really like to do is put the data back into the table I got it from. # Try to write the data back dbWriteTable(con, "new_data", ucam_no2$interpolated_data) Thanks James From @d@v|@2 @end|ng |rom m@||@n|h@gov Fri Sep 28 16:19:47 2012 From: @d@v|@2 @end|ng |rom m@||@n|h@gov (Sean Davis) Date: Fri, 28 Sep 2012 10:19:47 -0400 Subject: [R-sig-DB] R and PostgreSQL - Writing data? In-Reply-To: References: Message-ID: On Fri, Sep 28, 2012 at 10:14 AM, James David Smith wrote: > Dear all, > > Sorry if this isn't quite the right place, but it's the first time > I've posted here. My issue is to do with writing to a PostgreSQL > database from within R. My situation is best explained by some R code > to start: > > #Connect to the database > con <- dbConnect(PostgreSQL(), user="postgres", password="password", > dbname="database") > > #Get some data out of the database. > ucam_no2$original_data <- dbGetQuery(con, "select ucam_no2 FROM table") > > This returns say 10000 rows of data, but there is only data in about > half of those rows. What I want to do is interpolate the missing data > so I do this: > > #Generate some data > ucam_no2$interpolated_data <- na.approx(ucam_data$ucam_no2, na.rm = FALSE) > > This works well and I now have 10000 rows of data with no empty cells. > I now want to write this back into my PostgresSQL database. Into the > same row that I took the data from in the first place. But I don't > know how. I can write to a new table with something like the below, > but what I'd really like to do is put the data back into the table I > got it from. > > # Try to write the data back > dbWriteTable(con, "new_data", ucam_no2$interpolated_data) Hi, James. You'll need to look into doing a SQL UPDATE. That is the standard way to "put data back into the table I got it from". Sean From j@me@@d@v|d@@m|th @end|ng |rom gm@||@com Fri Sep 28 16:36:20 2012 From: j@me@@d@v|d@@m|th @end|ng |rom gm@||@com (James David Smith) Date: Fri, 28 Sep 2012 15:36:20 +0100 Subject: [R-sig-DB] R and PostgreSQL - Writing data? In-Reply-To: References: Message-ID: Hi Sean, Thanks for the reply. I'm familiar with UPDATE queries when working in PostgreSQL, but not from within R. Would it look something like this? dbWriteTable(con, " UPDATE table SET ucam_no2 = 'ucam_no2$interpolated_data' ") My problem is how to get the R data 'within' my SQL statement I think. Thanks James On 28 September 2012 15:19, Sean Davis wrote: > On Fri, Sep 28, 2012 at 10:14 AM, James David Smith > wrote: >> Dear all, >> >> Sorry if this isn't quite the right place, but it's the first time SendSave NowDiscardDraft autosaved at 15:36 (0 minutes ago) 33% full Using 3.4 GB of your 10.1 GB ?2012 Google - Terms & Privacy Last account activity: 50 minutes ago Details People (2) Sean Davis Add to circles Show details Ads ? Why these ads? Big Data Too Slow? Real-Time Analytics for Big Data. Visual Drag & Drop UI. Quick & Easy PentahoBigData.com Talend Open Source ESB Open Source ESB Based on Apache CXF and Apache Camel. Free Download! www.talend.com/Free_ESB_Software Warp I/O for SQL Server Speed SQL Server performance 3x Faster I/O, reduced storage www.confio.com/warp-io Storage Container Sussex Ex-Shipping Containers Sale & Hire Storage Container 0800 043 6311 www.CsShippingContainers.co.uk More about... MS Access Database SQL ? Database ? Excel Database Query ? Oracle Database Problems ? >> I've posted here. My issue is to do with writing to a PostgreSQL >> database from within R. My situation is best explained by some R code >> to start: >> >> #Connect to the database >> con <- dbConnect(PostgreSQL(), user="postgres", password="password", >> dbname="database") >> >> #Get some data out of the database. >> ucam_no2$original_data <- dbGetQuery(con, "select ucam_no2 FROM table") >> >> This returns say 10000 rows of data, but there is only data in about >> half of those rows. What I want to do is interpolate the missing data >> so I do this: >> >> #Generate some data >> ucam_no2$interpolated_data <- na.approx(ucam_data$ucam_no2, na.rm = FALSE) >> >> This works well and I now have 10000 rows of data with no empty cells. >> I now want to write this back into my PostgresSQL database. Into the >> same row that I took the data from in the first place. But I don't >> know how. I can write to a new table with something like the below, >> but what I'd really like to do is put the data back into the table I >> got it from. >> >> # Try to write the data back >> dbWriteTable(con, "new_data", ucam_no2$interpolated_data) > > Hi, James. > > You'll need to look into doing a SQL UPDATE. That is the standard way > to "put data back into the table I got it from". > > Sean From @d@v|@2 @end|ng |rom m@||@n|h@gov Fri Sep 28 17:43:22 2012 From: @d@v|@2 @end|ng |rom m@||@n|h@gov (Sean Davis) Date: Fri, 28 Sep 2012 11:43:22 -0400 Subject: [R-sig-DB] R and PostgreSQL - Writing data? In-Reply-To: References: Message-ID: On Fri, Sep 28, 2012 at 10:36 AM, James David Smith wrote: > Hi Sean, > > Thanks for the reply. I'm familiar with UPDATE queries when working in > PostgreSQL, but not from within R. Would it look something like this? > > dbWriteTable(con, " UPDATE table SET ucam_no2 = 'ucam_no2$interpolated_data' ") > > My problem is how to get the R data 'within' my SQL statement I think. To do an update, you'll need to loop through your data.frame and issue a dbSendQuery(). To create the SQL string, I often use something like: sprintf("UPDATE originalTable SET ucam_no2=%f WHERE originalTable.id = %d",....) You can't do this in one step, unfortunately. This is how UPDATE works and has nothing to do with R. Sean > > On 28 September 2012 15:19, Sean Davis wrote: >> On Fri, Sep 28, 2012 at 10:14 AM, James David Smith >> wrote: >>> Dear all, >>> >>> Sorry if this isn't quite the right place, but it's the first time > SendSave NowDiscardDraft autosaved at 15:36 (0 minutes ago) > 33% full > Using 3.4 GB of your 10.1 GB > ?2012 Google - Terms & Privacy > Last account activity: 50 minutes ago > Details > People (2) > Sean Davis > Add to circles > > Show details > Ads ? Why these ads? > Big Data Too Slow? > Real-Time Analytics for Big Data. Visual Drag & Drop UI. Quick & Easy > PentahoBigData.com > Talend Open Source ESB > Open Source ESB Based on Apache CXF and Apache Camel. Free Download! > www.talend.com/Free_ESB_Software > Warp I/O for SQL Server > Speed SQL Server performance 3x Faster I/O, reduced storage > www.confio.com/warp-io > Storage Container Sussex > Ex-Shipping Containers Sale & Hire Storage Container 0800 043 6311 > www.CsShippingContainers.co.uk > More about... > MS Access Database SQL ? > Database ? > Excel Database Query ? > Oracle Database Problems ? > >>> I've posted here. My issue is to do with writing to a PostgreSQL >>> database from within R. My situation is best explained by some R code >>> to start: >>> >>> #Connect to the database >>> con <- dbConnect(PostgreSQL(), user="postgres", password="password", >>> dbname="database") >>> >>> #Get some data out of the database. >>> ucam_no2$original_data <- dbGetQuery(con, "select ucam_no2 FROM table") >>> >>> This returns say 10000 rows of data, but there is only data in about >>> half of those rows. What I want to do is interpolate the missing data >>> so I do this: >>> >>> #Generate some data >>> ucam_no2$interpolated_data <- na.approx(ucam_data$ucam_no2, na.rm = FALSE) >>> >>> This works well and I now have 10000 rows of data with no empty cells. >>> I now want to write this back into my PostgresSQL database. Into the >>> same row that I took the data from in the first place. But I don't >>> know how. I can write to a new table with something like the below, >>> but what I'd really like to do is put the data back into the table I >>> got it from. >>> >>> # Try to write the data back >>> dbWriteTable(con, "new_data", ucam_no2$interpolated_data) >> >> Hi, James. >> >> You'll need to look into doing a SQL UPDATE. That is the standard way >> to "put data back into the table I got it from". >> >> Sean From den|@@x@mukh|n @end|ng |rom or@c|e@com Fri Sep 28 18:13:41 2012 From: den|@@x@mukh|n @end|ng |rom or@c|e@com (Denis Mukhin) Date: Fri, 28 Sep 2012 09:13:41 -0700 (PDT) Subject: [R-sig-DB] R and PostgreSQL - Writing data? In-Reply-To: References: Message-ID: James, I have never tried RPostgreSQL before but in ROracle which is also a DBI based interface you can do something like this: library(ROracle) con <- dbConnect(Oracle(), "scott", "tiger") dbGetQuery(con, "create table foo as select * from emp") dat <- dbGetQuery(con, "select * from foo") dat$SAL <- dat$SAL*10 dbGetQuery(con, "update foo set sal = :1 where empno = :2", data = dat[,c("SAL","EMPNO")]) dbCommit(con) dbGetQuery(con, "select * from foo") dbGetQuery(con, "drop table foo purge") dbDisconnect(con) Denis -----Original Message----- From: Sean Davis [mailto:sdavis2 at mail.nih.gov] Sent: Friday, September 28, 2012 11:43 AM To: James David Smith Cc: r-sig-db at r-project.org Subject: Re: [R-sig-DB] R and PostgreSQL - Writing data? On Fri, Sep 28, 2012 at 10:36 AM, James David Smith wrote: > Hi Sean, > > Thanks for the reply. I'm familiar with UPDATE queries when working in > PostgreSQL, but not from within R. Would it look something like this? > > dbWriteTable(con, " UPDATE table SET ucam_no2 = > 'ucam_no2$interpolated_data' ") > > My problem is how to get the R data 'within' my SQL statement I think. To do an update, you'll need to loop through your data.frame and issue a dbSendQuery(). To create the SQL string, I often use something like: sprintf("UPDATE originalTable SET ucam_no2=%f WHERE originalTable.id = %d",....) You can't do this in one step, unfortunately. This is how UPDATE works and has nothing to do with R. Sean > > On 28 September 2012 15:19, Sean Davis wrote: >> On Fri, Sep 28, 2012 at 10:14 AM, James David Smith >> wrote: >>> Dear all, >>> >>> Sorry if this isn't quite the right place, but it's the first time > SendSave NowDiscardDraft autosaved at 15:36 (0 minutes ago) 33% full > Using 3.4 GB of your 10.1 GB > ?2012 Google - Terms & Privacy > Last account activity: 50 minutes ago > Details > People (2) > Sean Davis > Add to circles > > Show details > Ads ? Why these ads? > Big Data Too Slow? > Real-Time Analytics for Big Data. Visual Drag & Drop UI. Quick & Easy > PentahoBigData.com Talend Open Source ESB Open Source ESB Based on > Apache CXF and Apache Camel. Free Download! > www.talend.com/Free_ESB_Software > Warp I/O for SQL Server > Speed SQL Server performance 3x Faster I/O, reduced storage > www.confio.com/warp-io Storage Container Sussex Ex-Shipping Containers > Sale & Hire Storage Container 0800 043 6311 > www.CsShippingContainers.co.uk More about... > MS Access Database SQL ? > Database ? > Excel Database Query ? > Oracle Database Problems ? > >>> I've posted here. My issue is to do with writing to a PostgreSQL >>> database from within R. My situation is best explained by some R >>> code to start: >>> >>> #Connect to the database >>> con <- dbConnect(PostgreSQL(), user="postgres", password="password", >>> dbname="database") >>> >>> #Get some data out of the database. >>> ucam_no2$original_data <- dbGetQuery(con, "select ucam_no2 FROM >>> table") >>> >>> This returns say 10000 rows of data, but there is only data in about >>> half of those rows. What I want to do is interpolate the missing >>> data so I do this: >>> >>> #Generate some data >>> ucam_no2$interpolated_data <- na.approx(ucam_data$ucam_no2, na.rm = >>> FALSE) >>> >>> This works well and I now have 10000 rows of data with no empty cells. >>> I now want to write this back into my PostgresSQL database. Into the >>> same row that I took the data from in the first place. But I don't >>> know how. I can write to a new table with something like the below, >>> but what I'd really like to do is put the data back into the table I >>> got it from. >>> >>> # Try to write the data back >>> dbWriteTable(con, "new_data", ucam_no2$interpolated_data) >> >> Hi, James. >> >> You'll need to look into doing a SQL UPDATE. That is the standard >> way to "put data back into the table I got it from". >> >> Sean _______________________________________________ R-sig-DB mailing list -- R Special Interest Group R-sig-DB at r-project.org https://stat.ethz.ch/mailman/listinfo/r-sig-db