sqlSave()

[Felipe Carrillo]

Sorry, I'am resending it because I forgot to send my system info(below)

Hi all:
I have created a MS Access table named 'PredictedValues' through the statement below:
myDB <- odbcConnectAccess("C:/Documents and Settings/Owner/Desktop/Rpond Farming.mdb",uid="admin",pwd="")    
sqlSave(myDB,PredictedValues,rownames=FALSE)
  close(myDB)

But if I run the code again with new values I get the message below:
Error in sqlSave(myDB, PredictedValues, rownames = FALSE) :
  table ‘PredictedValues’ already exists
and my new records don't get updated.

I was under the impression that 'sqlSave' would copy new data on top of the existing one or if the table didn't exist it would create one with the new values. I tried 'sqlUpdate' but my existing 'PredictedValues' didn't update. What am I doing wrong.
?
 

sessionInfo()
R version 2.9.0 (2009-04-17)
i386-pc-mingw32

locale:
LC_COLLATE=English_United States.1252;LC_CTYPE=English_United States.1252;LC_MONETARY=English_United States.1252;LC_NUMERIC=C;LC_TIME=English_United States.1252

attached base packages:
[1] graphics  grDevices datasets  tools     stats     grid      utils     methods   base    

other attached packages:
 [1] RODBC_1.2-5     forecast_1.23   tseries_0.10-11 quadprog_1.4-10 zoo_1.3-1       hexbin_1.17.0   xtable_1.5-5    lattice_0.17-22 plyr_0.1.8      ggplot2_0.8.3   reshape_0.8.0   proto_0.3-7    
[13] rcom_2.1-3      rscproxy_1.3-1


Felipe D. Carrillo  
Supervisory Fishery Biologist  
Department of the Interior  
US Fish & Wildlife Service  
California, USA





______________________________________________
[hidden email] 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.

[Dieter Menne]

Felipe Carrillo <mazatlanmexico <at> yahoo.com> writes:

> I have created a MS Access table named 'PredictedValues' through the statement
below:

> myDB <- odbcConnectAccess("C:/Documents and Settings/Owner/Desktop/Rpond
> Farming.mdb",uid="admin",pwd="")    
> sqlSave(myDB,PredictedValues,rownames=FALSE)
>   close(myDB)
>
> But if I run the code again with new values I get the message below:
> Error in sqlSave(myDB, PredictedValues, rownames = FALSE) :
>   table ‘PredictedValues’ already exists
> and my new records don't get updated.
>
> I was under the impression that 'sqlSave' would copy new data on top of the

existing one or if the table didn't
> exist it would create one with the new values. I tried 'sqlUpdate' but my
existing 'PredictedValues'
> didn't update. What am I doing wrong.

Either try safer = FALSE (great white shark) or append=TRUE (depending on what
you want).

sqlSave(safer = FALSE) uses the 'great white shark' method of testing tables
(bite it and see). The logic will unceremoniously DROP the table and create it
anew with its own choice of column types in its attempt to find a writable
solution. test = TRUE will not necessarily predict this behaviour. Attempting to
write indexed columns or writing to pseudo-columns are less obvious causes of
failed writes followed by a DROP. If your table structure is precious to you
back it up.

______________________________________________
[hidden email] 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.

[cindy.dol]

Hello,

I have an issue with SqlUpdate and perhaps you can help me.
I have created a table with sqlSave.
My table on Access is "PRT".
My dataframe on R : RESULTS.
My channel : C2.
sqlSave(C2, RESULTS, tablename = "PRT",rownames=FALSE, safer = FALSE)
And it works.

Now I would like to update it, to keep the old values and add new values.
I tried :
sqlUpdate(C2, RESULTS, tablename="PRT", append=TRUE)
But it doesn't work, the error message is :"cannot update ‘PRT’ without unique column"

Do you know how I can do it?

[Julien Moeys-2]

Hi Cindy

I assume you talk about sqlUpdate() in RODBC

You probably need to set the argument "index" in sqlUpdate, so RODBC knows what column it should use to update the  values (any column that can identify the rows to be updated).

Here is a small example (here with an MS Access database, but I suppose it would work with other ODBC databases)

# --------------------------------
tbl <- data.frame( "ID" = 1:10, "VALUE" = rnorm(10) )

con <- odbcConnectAccess( "soils.mdb" ) # Could be any database

sqlSave( channel = con, dat = tbl, tablename = "myNewTable",
    rownames = FALSE, append = FALSE )

sqlFetch( channel = con, sqtable = "myNewTable" )

tbl[, "VALUE" ] <- rnorm(10)

# Generate error
sqlUpdate( channel = con, dat = tbl, tablename = "myNewTable" )
# Error in sqlUpdate(channel = con, dat = tbl, tablename = "myNewTable") :
#   cannot update 'myNewTable' without unique column

# But this is ok
sqlUpdate( channel = con, dat = tbl, tablename = "myNewTable",
    index = "ID" )

sqlFetch( channel = con, sqtable = "myNewTable" )

odbcClose( con )
# --------------------------------

Generally speaking, if you want relevant answers to your questions, it is always a good idea to say which package you are using and which database you are working with. Knowing the structure of your table and providing the output of your sessionInfo() would help too.

All the best

Julien

> -----Original Message-----
> From: [hidden email] [mailto:[hidden email]]
> On Behalf Of cindy.dol
> Sent: 29 June 2012 15:52
> To: [hidden email]
> Subject: Re: [R] sqlSave()
>
> Hello,
>
> I have an issue with SqlUpdate and perhaps you can help me.
> I have created a table with sqlSave.
> My table on Access is "PRT".
> My dataframe on R : RESULTS.
> My channel : C2.
> sqlSave(C2, RESULTS, tablename = "PRT",rownames=FALSE, safer = FALSE)
> And it works.
>
> Now I would like to update it, to keep the old values and add new values.
> I tried :
> sqlUpdate(C2, RESULTS, tablename="PRT", append=TRUE) But it doesn't
> work, the error message is :"cannot update ‘PRT’ without unique column"
>
> Do you know how I can do it?
>
>
>
> --
> View this message in context: http://r.789695.n4.nabble.com/sqlSave-
> tp892040p4634881.html
> Sent from the R help mailing list archive at Nabble.com.
>
> ______________________________________________
> [hidden email] 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.

______________________________________________
[hidden email] 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.

[cindy.dol]

Hi,

I tried your example but I have an error message:
sqlUpdate( channel = con, dat = tbl, tablename = "myNewTable", index = "ID" )
Erreur dans sqlUpdate(channel = con, dat = tbl, tablename = "myNewTable",  :
  [RODBC] Failed exec in Update

I work with this:
sessionInfo()
R version 2.15.0 (2012-03-30)
Platform: i386-pc-mingw32/i386 (32-bit)

attached base packages:
[1] tcltk     stats     graphics  grDevices utils     datasets  methods  
[8] base    

other attached packages:
 [1] sqldf_0.4-6.4         gsubfn_0.6-3          proto_0.3-9.2        
 [4] chron_2.3-42          RSQLite.extfuns_0.0.1 RSQLite_0.11.1      
 [7] RODBC_1.3-5           RJDBC_0.2-0           rJava_0.9-3          
[10] DBI_0.2-5          

Do you know what is the problem?

Thank you for your answer  

[cindy.dol]

Julien Moeys:

ok, I see what is the problem,

Your example does not work because MS Access is trying to "update" values in your table according to the ID you provide

So when you provide for instance ID = 1, MS Access will look in the table for an existing record(s) that have an ID of 1, and replace the existing value by the new one
When you provide ID = 16, MS Access will look in the table for an existing record(s) that have an ID of 16, but will not find it, thus the error

So you should:
- use sqlSave() for the records that have an ID that is *not yet* in the database table (in your example below, all ID > 10)
- use sqlUpdate() for the records that have an ID that is *already* in the database table (and for which you want to update the values)

If you don't know in advance which ID are already in the database table, you need to read the table first (to fetch existing ID's), and use that to divide your table in 2 sets: one for already existing ID (for sqlUpdate), and one for new ID (for sqlSave)

I hope that will help

Cheers

Julien