R help - Jun 2012 - 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 ( i6) 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]]

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 at 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.dalphin at pacificedge.co.nz <mailto:mark.dalphin at
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