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.
