I still don't have any idea about your schema [eg CREATE TABLE (blah,
blah, ...);], but I guess you don't have the right database type for
"id" when you are storing a date.
In PostgreSQL:
CREATE TABLE myTable (
id INTEGER PRIMARY KEY,
aDate TIMESTAMP
);
In R:
dbGetQuery(conn, paste("INSERT INTO myTable (id, aDate)\n",
"VALUES (1, '2012-06-14
11:18:36');\n",
sep=''))
All untested. If you want/need to use the sprintf() form, then just wrap
the time variable in single quotes:
sql <- sprintf("INSERT INTO myTable (id, aDate)\nVALUES (%d, '%s');\n",
1, '2011-06-14 11:18:36');
dbGetQuery(conn, sql)
Mark
Prakash Thomas wrote:
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 = "xxxxx",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<https://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.nz<mark.dalp...@pacificedge.co.nz>
*Ph:* +64-3-479-5805
*Cell:* +64-21-156-7625
*Skype:* mdalphin
<http://www.facebook.com/**pages/Pacific-Edge/**111356775582456<http://www.facebook.com/pages/Pacific-Edge/111356775582456>>
<http://twitter.com/#%21/**pacificEdgeLtd<http://twitter.com/#%21/pacificEdgeLtd>>
<http://www.youtube.com/**PacificEdgeLtd<http://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.
--
Mark Dalphin Ph.D.
Director of Bioinformatics
mark.dalp...@pacificedge.co.nz <mailto:mark.dalp...@pacificedge.co.nz>
*Ph:* +64-3-479-5805
*Cell:* +64-21-156-7625
*Skype:* mdalphin
<http://www.facebook.com/pages/Pacific-Edge/111356775582456>
<http://twitter.com/#%21/pacificEdgeLtd>
<http://www.youtube.com/PacificEdgeLtd>
87 St David St, PO Box 56, Dunedin, New Zealand 9016www.pacificedge.co.nz
______________________________________________
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.
