[R] sqlSave writes, but only for sqlFetch

[Dave Mitchell]

I've been having some issues with sqlSave, and I think I've found an clue
that may identify the problem.  In the code at http://pastebin.com/0USzNFfq,
I connect to a Netezza instance, make a simple table, and query it.
Despite sqlSave succeeding without error, sqlQuery tells me the table
doesn't exist.  Also, when I query the database outside of R, I see that
the table doesn't exist.  However, using sqlFetch works, and (perhaps, as
expected) the table is dropped upon evaluating close(myConn).  Also,
sqlDrop works as expected.   Thanks for your time.  Any thoughts are
appreciated.


Dave Mitchell

[[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.

[R] sqlSave writes, but only for sqlFetch

[Dave Mitchell]

I've been having some issues with sqlSave, and I think I've found an clue
that may identify the problem.  In the code at
http://pastebin.com/vTAKt4bL,
I connect to a Netezza instance, make a simple table, and query it.
Despite sqlSave succeeding without error, sqlQuery tells me the table
doesn't exist.  Also, when I query the database outside of R, I see that
the table doesn't exist.  However, using sqlFetch works, and (perhaps, as
expected) the table is dropped upon evaluating close(myConn).  Also,
sqlDrop works as expected.   Thanks for your time.  Any thoughts are
appreciated.

Dave Mitchell

[[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.

Re: [R] sqlSave()

[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

--
View this message in context: 
http://r.789695.n4.nabble.com/sqlSave-tp892040p4635387.html
Sent from the R help mailing list archive at Nabble.com.

__
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.

Re: [R] sqlSave()

[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  

--
View this message in context: 
http://r.789695.n4.nabble.com/sqlSave-tp892040p4635087.html
Sent from the R help mailing list archive at Nabble.com.

__
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.

Re: [R] sqlSave()

[Julien Moeys]

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: r-help-boun...@r-project.org [mailto:r-help-boun...@r-project.org]
> On Behalf Of cindy.dol
> Sent: 29 June 2012 15:52
> To: r-help@r-project.org
> 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.
> 
> __
> 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.
__
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.

Re: [R] sqlSave()

[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?



--
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.

__
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.

Re: [R] sqlsave and mysql database with autoincremental column

[PtitBleu]

I found a solution (certainly not the best one but it works).
I add a 45th column full of 0 to the 44-columns data.frame.
When I copy the data.frame to the database with sqlsave, the last column of
the table is not full of 0, but it autoincrements.

Another small question: what is the command to copy, for example, the 5th
and the 7th column to the table ?

Have a nice end of day,
Ptit Bleu.
-- 
View this message in context: 
http://r.789695.n4.nabble.com/sqlsave-and-mysql-database-with-autoincremental-column-tp3251425p3258351.html
Sent from the R help mailing list archive at Nabble.com.

__
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.

[R] sqlsave and mysql database with autoincremental column

[PtitBleu]

Hello,

I'm trying to modify my r-script to use RODBC instead of DBI/RMySQL (no more
ready-to-use package for windows).

I would like to copy a data.frame of 44 columns to a table of 45 columns
(the 45th is an autoincremental column).

With the following commands, 

colnames(df)<- a vector with the names of the 44 columns
ch<-odbcDriverConnect(connection="SERVER=localhost;DRIVER=MySQL ODBC 5.1
Driver;DATABASE=my_db;UID=usercount;PWD=my_pwd;case=tolower")
sqlSave(ch,my_df, "my_table", append = TRUE, rownames = FALSE, fast=FALSE)
odbcClose(ch)

I got the following message (in french, sorry): 
Erreur dans dimnames(x) <- dn :  la longueur de 'dimnames' [2] n'est pas
égale à l'étendue du tableau
It means something like: the length of 'dimnames' [2] is not equal to the
array extent

If I do the same with a table with only 44 columns, without the
autoincremental column, it works ... but I need this last column.

Could you please help me to modify my commands so that I can copy 44-columns
data.frame to the 45-columns MySQL table ?

Thanks in advance,
Ptit Bleu.





-- 
View this message in context: 
http://r.789695.n4.nabble.com/sqlsave-and-mysql-database-with-autoincremental-column-tp3251425p3251425.html
Sent from the R help mailing list archive at Nabble.com.

__
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.

[R] sqlSave data into multiple *.mdb files

[Ryusuke Kenji]

Hi All,

con <- member <- class <- list()
for(i in 1:5)
 { con[[i]] <- 
odbcConnectAccess(paste("C:/Desktop/Data/source",i,".mdb",sep = '')) #read data 
from Access files
   member[[i]] <- sqlFetch(con[[i]],'member') #get table data from *.mdb 
files 
   observe[[i]] <- sqlFetch(con[[i]],'observe') #get table data from *.mdb 
files
  }; rm(i)

memberID <- Reduce(function(x, y) merge(x, y, all=T), member, accumulate=F) 
#merge members to be unique memberID
observe <- Reduce(function(x, y) merge(x, y, all=T), observe, accumulate=F) 
#merge observation data files

attach(member)
params <- glm(Ranking~Andy+Jackie+Kelly+Aaron...) #start calculation from 
merged data...
output <- params$coef  #get the coef of every single member
detach(member)

result <- sqlSave(con[[i]].) #save the coef of every single member into 
pre-merge source *mdb filesFrom the above coding, I got some data files from 
*.mdb files and merge it to calculate, but I was wondering how to save the 
result into the table of pre-merge *.mdb files.


Best Regards,
Ryusuke
http://soccer.oksport.cn
  
_
$B:#G/$...@als$7$h$&!*$^$:$O%M%C%HBe$+$i!#(B

[[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.

Re: [R] sqlSave()

[Dieter Menne]

Felipe Carrillo  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.

__
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.

[R] 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-5lattice_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





__
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.

[R] sqlSave()

[Felipe Carrillo]

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.
?



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




__
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.

Re: [R] sqlSave (RODBC) question

[Olivier Lefevre]

FWIW, this is on SQL Server.

-- O.L.

__
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.

[R] sqlSave (RODBC) question

[Olivier Lefevre]

I am having a hard time understanding how to make sure the right types 
(esp. in the case of dates) are passed to the db, partly because of a lack 
of concrete examples in the docs and partly because the docs are a bit 
elliptic: the docs merely say that "the types are selected by consulting 
arguments varTypes and typeInfo", which is not nearly enough to understand 
what is actually happening. Where can I find either more examples or a 
fuller discussion?

Thanks,

-- O.L.

__
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.