[R-sig-DB] Error in postgresqlExecStatement...RS-DBI driver: (could not Retrieve the result...)

[Rex Hu]

You're totally right!!! It is working now!!! Thank you so much!!!

--Rex


On Feb 11, 2011, at 2:27 AM, Tomoaki NISHIYAMA wrote:

> Hi,
>
> Some part of this error seems to be made by
> the confusion of "public.tmptable" and public.tmptable.
>
> The meaning of the tablename handled by dbWriteTable, dbRemoveTable,  
> etc.
> changed relatively recently,
> Now "public.tmptable" means literaly "public.tmptable" in the  
> current schema,
> while public.tmptable in the sql means tmptable in public schema.
> This may relate to possible cause of not being to be able to create  
> table, if
> the current schema is changed in some other place.  Also, what  
> access privilege
> the connecting postgresql user have on each database/schema  
> combination should be checked.
>
>>> The environment where I ran the program is Red Hat Enterprise  
>>> Linux 6, and PostgreSQL 8.4.7. R version is  2.12.1.
>
> and what version for RPostgreSQL?
>
>>> I ran into this error when I was trying to run an R program  
>>> written by someone else.
>
>
> Do you have any estimate when it was written?
> -- 
> Tomoaki NISHIYAMA
>
> Advanced Science Research Center,
> Kanazawa University,
> 13-1 Takara-machi,
> Kanazawa, 920-0934, Japan
>
>
> On 2011/02/09, at 13:00, Rex Hu wrote:
>
>> Sorry, my bad. Forgot the attachment...
>>
>> --Rex
>>
>> <loop.R>
>>
>>
>>
>> On Feb 8, 2011, at 10:55 PM, Rex Hu wrote:
>>
>>> Hi all!
>>>
>>> I am a total newb in database programming, but unfortunately, I am  
>>> working on a project that requires database programming using R. I  
>>> hope that some one could help me about this. Thank you all in  
>>> advance!
>>>
>>> I ran into this error when I was trying to run an R program  
>>> written by someone else. It occurs when executing this 'big  
>>> loop' (the R code of this loop is in the attachment). I have  
>>> absolutely no clue what is going on.
>>>
>>> The environment where I ran the program is Red Hat Enterprise  
>>> Linux 6, and PostgreSQL 8.4.7. R version is  2.12.1.
>>>
>>> The following are error messages.
>>>
>>> 64490 :class(mydata):  data.frame
>>>  cleaning.out.cols: geofencename, geofencereason
>>>  prod(u.gfn == ""): 0
>>>  prod(u.gfr == ""): 0
>>>  dim(mydata): 185 7
>>>  Creating hourflag, odoflag, stateflag...done.
>>>  Appending them to the data.frame...done.
>>>  Sum:: hourflag: 10 , odoflag: 10 , stateflag: 12 , hrOdoflag: 10
>>>  Starting hrodoflag.index loop...
>>>  hrodoflag.index loop finished.
>>> Error in postgresqlExecStatement(conn, statement, ...) :
>>> RS-DBI driver: (could not Retrieve the result : ERROR:  relation  
>>> "public.tmptable" already exists
>>> )
>>> In addition: Warning messages:
>>> 1: In dir.create(datadumpdir, showWarnings = TRUE, recursive =  
>>> TRUE,  :
>>> '/scratch/kan/datadump' already exists
>>> 2: In dir.create(misclwritedir, showWarnings = TRUE, recursive =  
>>> TRUE,  :
>>> '/scratch/kan/work/bordercrossing/miscl/AU08_100301' already exists
>>> 3: In dir.create(datawritedir, showWarnings = TRUE, recursive =  
>>> TRUE,  :
>>> '/scratch/kan/work/bordercrossing/data/cevaAU08_100301' already  
>>> exists
>>> Error in postgresqlExecStatement(conn, statement, ...) :
>>> RS-DBI driver: (could not Retrieve the result : ERROR:  relation  
>>> "public.tmptable" does not exist
>>> )
>>> In addition: Warning message:
>>> In postgresqlQuickSQL(conn, statement, ...) :
>>> Could not create executeCREATE TABLE "public.tmptable"
>>> ( "id_tb100301trp" bigint,
>>>  "bridge_new" text,
>>>  "tripscore" float8,
>>>  "stflag" float8,
>>>  "hrflag" float8,
>>>  "odflag" float8
>>> )
>>> Error in postgresqlExecStatement(conn, statement, ...) :
>>> RS-DBI driver: (could not Retrieve the result : ERROR:  relation  
>>> "public.tmptable" does not exist
>>> )
>>> In addition: Warning message:
>>> In postgresqlQuickSQL(conn, statement, ...) :
>>> Could not create executeCOPY public.tmptable FROM '/tmp/ 
>>> rpsql.G19577' WITH CSV HEADER QUOTE AS '"'
>>> Error in postgresqlExecStatement(conn, statement, ...) :
>>> RS-DBI driver: (could not Retrieve the result : ERROR:  relation  
>>> "public.tmptable" does not exist
>>> )
>>> In addition: Warning message:
>>> In postgresqlQuickSQL(conn, statement, ...) :
>>> Could not create executeUPDATE  au08_100301.tb100301trp  SET   
>>> bridge_new  =  public.tmptable.bridge_new FROM  public.tmptable
>>> WHERE  au08_100301.tb100301trp.id_tb100301trp  =   
>>> public.tmptable.id_tb100301trp ;
>>> Error in postgresqlExecStatement(conn, statement, ...) :
>>> RS-DBI driver: (could not Retrieve the result : ERROR:  relation  
>>> "public.tmptable" does not exist
>>> )
>>> In addition: Warning message:
>>> In postgresqlQuickSQL(conn, statement, ...) :
>>> Could not create executeUPDATE  au08_100301.tb100301trp  SET   
>>> tripscore  =  public.tmptable.tripscore FROM public.tmptable
>>> WHERE  au08_100301.tb100301trp.id_tb100301trp  =   
>>> public.tmptable.id_tb100301trp ;
>>> Error in postgresqlExecStatement(conn, statement, ...) :
>>> RS-DBI driver: (could not Retrieve the result : ERROR:  relation  
>>> "public.tmptable" does not exist
>>> )
>>> In addition: Warning message:
>>> In postgresqlQuickSQL(conn, statement, ...) :
>>> Could not create executeUPDATE  au08_100301.tb100301trp  SET   
>>> stflag  =  public.tmptable.stflag FROM public.tmptable
>>> WHERE  au08_100301.tb100301trp.id_tb100301trp  =   
>>> public.tmptable.id_tb100301trp ;
>>> Error in postgresqlExecStatement(conn, statement, ...) :
>>> RS-DBI driver: (could not Retrieve the result : ERROR:  relation  
>>> "public.tmptable" does not exist
>>> )
>>> In addition: Warning message:
>>> In postgresqlQuickSQL(conn, statement, ...) :
>>> Could not create executeUPDATE  au08_100301.tb100301trp  SET   
>>> hrflag  =  public.tmptable.hrflag FROM public.tmptable
>>> WHERE  au08_100301.tb100301trp.id_tb100301trp  =   
>>> public.tmptable.id_tb100301trp ;
>>>
>>>
>>>
>>> ***********************************************
>>> Hu, Zhengyu (Rex)
>>> Department of Statistics
>>> The Ohio State University
>>> Columbus, OH 43210
>>>
>>
>> _______________________________________________
>> R-sig-DB mailing list -- R Special Interest Group
>> R-sig-DB using r-project.org
>> https://stat.ethz.ch/mailman/listinfo/r-sig-db
>