[R] Re: Not able to write to PostgreSQL database using dbWriteTable
Dear R User's,
Thank you, Mark. The following code suggested by you worked for me.
dbGetQuery(connAE1, sprintf(INSERT INTO test1 (id) VALUES ( %d
);, i))
But I have a issue in passing date and time data as variable.If I
hard code the value like bellow it workings.
dbGetQuery(connAE1, sprintf(INSERT INTO test1 (id) VALUES ( %s
);, ,'\'2012-06-10 16:36:00+05:30\''))
Can some body please help me with the code where I need to read
from a variable(i) which has data time (2012-06-10 16:36:00+05:30). R is
throwing error for space as shown in output bellow
**Console code
output***
if(dbExistsTable(connAE1, test1)){
+ dbGetQuery(connAE1, sprintf(INSERT INTO test1 (id) VALUES ( %s );, i))
+ }
Error in postgresqlExecStatement(conn, statement, ...) :
RS-DBI driver: (could not Retrieve the result : ERROR: syntax error at
or near 16
LINE 1: INSERT INTO test1 (id) VALUES ( 2012-06-10 16:36:00+05:30 );
^
)
NULL
*
Thanks Regards,
Thomas
On Wed, Jun 13, 2012 at 2:25 AM, Mark Dalphin
mark.dalp...@pacificedge.co.nz wrote:
I just tested your code and I _think_ you have a misconception about
dbWriteTable().
Your code has some oddities so I am only guessing; for example, what is
zz and why is it in this snippet?
In the absence of information on the database TABLE, it is even harder to
guess what you are doing, but I guess you are trying to use dbWriteTable to
add a small amount of data to an existing table since previously you select
from a similarly named table, test1. The dbWriteTable function is writing
to the table called test1.id not to test1, column id. If you check
your PostgreSQL schema, you will see that you have created a new table
called test1.id (which you will be required to quote to remove as the
DOT is an operator: DROP TABLE test1.id;).
I think you are trying to add a new row to the existing database table.
Try using (untested):
dbGetQuery(connAE1, sprintf(INSERT INTO test1 (id) VALUES ( %d );, i))
and you will find things go better, assuming I grasped the problem you are
having correctly.
Regards,
Mark Dalphin
Prakash Thomas wrote:
Dear R User's
Please help me to debug this issue. I am trying to write some data ( i=
6) to PostgreSQL database, but it not writing.
Is there any issue in the way I use dbWriteTable?
++ Source Code
library(DBI)
library(RPostgreSQL)
drv1 - dbDriver(PostgreSQL)
i=6
connAE1 - dbConnect(drv1,host = xx.xxx.xxx.xxx, port = 6443,
dbname=DB,user = x,password = xxx)
as.data.frame(zz[1])
dbGetQuery(connAE1,'SELECT id FROM \test1\')
if(dbExistsTable(connAE1, test1)){
dbWriteTable(con=connAE1,name=**'test1.id',value=as.data.**
frame(i),row.names=T
,overwrite=F ,append=T)
}
dbDisconnect(connAE1)
dbUnloadDriver(drv1)
++**
Following is the past of the console Log for your Reference
++ console log
dbGetQuery(connAE1,'SELECT id FROM \test1\')
id
1 1
2 2
if(dbExistsTable(connAE1, test1)){
+ dbWriteTable(con=connAE1,name=**'test1.id',value=as.data.**
frame(i),row.names=T
,overwrite=F ,append=T)
+ [TRUNCATED]
id
1 1
2 2
++**+
Thanks Regards,
Thomas
[[alternative HTML version deleted]]
__**
R-help@r-project.org mailing list
https://stat.ethz.ch/mailman/**listinfo/r-helphttps://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/**
posting-guide.html http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
--
Mark Dalphin Ph.D.
Director of Bioinformatics
mark.dalp...@pacificedge.co.nz
mailto:mark.dalphin@**pacificedge.co.nzmark.dalp...@pacificedge.co.nz
*Ph:* +64-3-479-5805
*Cell:* +64-21-156-7625
*Skype:* mdalphin
http://www.facebook.com/**pages/Pacific-Edge/**111356775582456http://www.facebook.com/pages/Pacific-Edge/111356775582456
http://twitter.com/#%21/**pacificEdgeLtdhttp://twitter.com/#%21/pacificEdgeLtd
http://www.youtube.com/**PacificEdgeLtdhttp://www.youtube.com/PacificEdgeLtd
87 St David St, PO Box 56, Dunedin, New Zealand 9016www.pacificedge.co.nz
[[alternative HTML version deleted]]
__
R-help@r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
[R] Not able to write to PostgreSQL database using dbWriteTable
Dear R User's
Please help me to debug this issue. I am trying to write some data ( i=
6) to PostgreSQL database, but it not writing.
Is there any issue in the way I use dbWriteTable?
++ Source Code
library(DBI)
library(RPostgreSQL)
drv1 - dbDriver(PostgreSQL)
i=6
connAE1 - dbConnect(drv1,host = xx.xxx.xxx.xxx, port = 6443,
dbname=DB,user = x,password = xxx)
as.data.frame(zz[1])
dbGetQuery(connAE1,'SELECT id FROM \test1\')
if(dbExistsTable(connAE1, test1)){
dbWriteTable(con=connAE1,name='test1.id',value=as.data.frame(i),row.names=T
,overwrite=F ,append=T)
}
dbDisconnect(connAE1)
dbUnloadDriver(drv1)
++
Following is the past of the console Log for your Reference
++ console log
dbGetQuery(connAE1,'SELECT id FROM \test1\')
id
1 1
2 2
if(dbExistsTable(connAE1, test1)){
+
dbWriteTable(con=connAE1,name='test1.id',value=as.data.frame(i),row.names=T
,overwrite=F ,append=T)
+ [TRUNCATED]
id
1 1
2 2
+++
Thanks Regards,
Thomas
[[alternative HTML version deleted]]
__
R-help@r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
[R] Need package which can parse linux configuration file '.conf'
Hi, Please let me whether there is a package which can parse linux configuration file '.conf. If there is no specific package can any of the generic package handle this? Thanks Regards, Thomas [[alternative HTML version deleted]] __ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
[R] Error while trying to install RPostgreSQL.
Hi, I am getting the following error while trying to install RPostgreSQL. I tried all three methods(install.packages(RPostgreSQL, type = source),install.packages(RPostgreSQL) R CMD INSTALL RPostgreSQL_0.3-1.tar.gz ) , but none of them is working. ++ Error gcc -std=gnu99 -I/usr/share/R/include -DNDEBUG -I -fpic -O3 -pipe -g -c RS-PQescape.c -o RS-PQescape.o In file included from RS-PQescape.c:7:0: RS-PostgreSQL.h:23:26: fatal error: libpq-fe.h: No such file or directory compilation terminated. make: *** [RS-PQescape.o] Error 1 ERROR: compilation failed for package RPostgreSQL * removing /home/intel/R/x86_64-pc-linux-gnu-library/2.15/RPostgreSQL The downloaded source packages are in /tmp/Rtmpz4zF04/downloaded_packages Warning message: In install.packages(RPostgreSQL, type = source) : installation of package RPostgreSQL had non-zero exit status ++ Thanks Regards, Thomas [[alternative HTML version deleted]] __ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
[R] Need help in doing EMA(Exponential Mean Average).
Can somebody help me in finding package/Example in R which could do EMA(Exponential Mean Average). I installed TTR package but the 'EMA function which I was trying to use is giving the following error. Error: Could not find function EMA Thanks Regards, Thomas [[alternative HTML version deleted]] __ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
Re: [R] how to find outliers from the list of values
Thank you Robert,Petr and Bert. I know my question is generic one, which does specify what is range to values which comes as unusual value. I start with R a week ago and trying to learn it. Can somebody tell me how to set the range (say 700) for the outlier value? Thanks Regards, Prakash On Thu, May 17, 2012 at 9:38 PM, Robert Baer rb...@atsu.edu wrote: Petr and Bert offer sound advice. At the risk of getting completely ostracized, here's how you could find outliers using the definition of 'outlier' used by R's boxplot function and at the same time see your data. dat = c(11489, 11008, 11873, 8000, 9558, 8645, 8024, 8371) a = boxplot(dat) a$out [1] 8e+07 -Original Message- From: Prakash Thomas Sent: Tuesday, May 15, 2012 7:00 AM To: r-help@r-project.org Subject: [R] how to find outliers from the list of values Hi, I am new to R and I would like to get your help in finding 'outliers'. I have mvoutlier package installed in my system and added the package . But I not able find a function from 'mvoutlier' package which will identify 'outliers'. This is the sample list of data I have got which has one out-lier. 11489 11008 11873 8000 9558 8645 8024 8371 It will be of great help if somebody have got an example script for the same. Thanks Regards, Thomas [[alternative HTML version deleted]] __** R-help@r-project.org mailing list https://stat.ethz.ch/mailman/**listinfo/r-helphttps://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/** posting-guide.html http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code. --** Robert W. Baer, Ph.D. Professor of Physiology Kirksville College of Osteopathic Medicine A. T. Still University of Health Sciences 800 W. Jefferson St. Kirksville, MO 63501 660-626-2322 FAX 660-626-2965 [[alternative HTML version deleted]] __ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
[R] how to find outliers from the list of values
Hi, I am new to R and I would like to get your help in finding 'outliers'. I have mvoutlier package installed in my system and added the package . But I not able find a function from 'mvoutlier' package which will identify 'outliers'. This is the sample list of data I have got which has one out-lier. 11489 11008 11873 8000 9558 8645 8024 8371 It will be of great help if somebody have got an example script for the same. Thanks Regards, Thomas [[alternative HTML version deleted]] __ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
