R help - Oct 2006 - sqlSave, fast=F option, bug?

Hi,

Using the fast=F option, sqlSave saves without matching column names.
It looks like a bug to me..........

Here's a simple (artificial) example.
-----------------------------------------
Create a dataframe and save it to a database table "test" as follows:

df <- data.frame(T=1, S=10)
sqlSave(channel, df, "test", rownames=F)

The table now looks like

T  S
1  10

If I create another dataframe and save as follows

df <- data.frame(S=20, T=2)
sqlSave(channel, df, "test", rownames=F, append=T)

Then table "test" now looks like

T  S
1  10
2  20

The important point is that although S was the first column of df,
sqlSave checked the column names and matched the corresponding columns
of df and table "test".

However, if I now create another dataframe and save it using the
fast=F option as follows

df <- data.frame(S=30, T=3)
sqlSave(channel, df, "test", rownames=F, append=T, fast=F)

the table test now looks like

T  S
1  10
2  20
30 3

In other words, sqlSave didn't check column names, it simply mapped
column 1 to column 1 and column 2 to column 2.
---------------------------------

This cannot be right. Opinions?

I'm using R 2.3.0 and package RODBC 1.1-7 on Windows XP with MS SQL Server

Paul MacManus <paul.macmanus <at> gmail.com> writes:
> Using the fast=F option, sqlSave saves without matching column names.
> It looks like a bug to me..........
.... 
> In other words, sqlSave didn't check column names, it simply mapped
> column 1 to column 1 and column 2 to column 2.
This seems to be a problem with SQLServer. The following example with 
Access works correctly for me, even if I close the connection in between. 
(Win2k, R 2.4.0)

Dieter Menne

-----
library(RODBC)
channel <- odbcConnectAccess("db1.mdb")
df <- data.frame(T=1, S=10)
sqlSave(channel, df, "test", rownames=F)
#odbcClose(channel)

#channel <- odbcConnectAccess("db1.mdb")
df <- data.frame(S=20, T=2)
sqlSave(channel, df, "test", rownames=F, append=T,fast=T)
odbcClose(channel)

I've also reproduced this behaviour on WinXP + MS SQL Server and would agree
that it's a bug with the "fast=FALSE" option. 

Have the package maintainers been contacted about this? 
>-----Original Message-----
>From: r-help-bounces@stat.math.ethz.ch 
>[mailto:r-help-bounces@stat.math.ethz.ch] On Behalf Of chao gai
>Sent: 18 October 2006 08:10 PM
>To: r-help@stat.math.ethz.ch
>Subject: Re: [R] sqlSave, fast=F option, bug?
>
>The bug spot seems to be the fast option
>sqlDrop(chan,'test')
>df <- data.frame(T=1, S=10)
>sqlSave(chan, df, "test", rownames=F)
>df <- data.frame(S=20, T=2)
>sqlSave(chan, df, "test", rownames=F, append=T,fast=T) df <- 
>data.frame(S=30, T=3) sqlSave(chan, df, "test", rownames=F, 
>append=T,fast=F)
>sqlFetch(chan,'test')
>   T  S
>1  1 10
>2  2 20
>3 30  3
>On Linux & MySQL  
>
>
>On Wednesday 18 October 2006 17:06, Dieter Menne wrote:
>> Paul MacManus <paul.macmanus <at> gmail.com> writes:
>> > Using the fast=F option, sqlSave saves without matching 
>column names.
>> > It looks like a bug to me..........
>>
>> ....
>>
>> > In other words, sqlSave didn't check column names, it 
>simply mapped 
>> > column 1 to column 1 and column 2 to column 2.
>>
>> This seems to be a problem with SQLServer. The following 
>example with 
>> Access works correctly for me, even if I close the 
>connection in between.
>> (Win2k, R 2.4.0)
>>
>> Dieter Menne
>>
>> -----
>> library(RODBC)
>> channel <- odbcConnectAccess("db1.mdb") df <-
data.frame(T=1, S=10)
>> sqlSave(channel, df, "test", rownames=F)
>> #odbcClose(channel)
>>
>> #channel <- odbcConnectAccess("db1.mdb") df <-
data.frame(S=20, T=2)
>> sqlSave(channel, df, "test", rownames=F, append=T,fast=T)
>> odbcClose(channel)
>>
>> ______________________________________________
>> R-help@stat.math.ethz.ch 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@stat.math.ethz.ch 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.
>
********************
Nedbank Limited Reg No 1951/000009/06. The following link displays the names of
the Nedbank Board of Directors and Company Secretary. [
http://www.nedbank.co.za/terms/DirectorsNedbank.htm ]
This email is confidential and is intended for the addressee only. The following
link will take you to Nedbank's legal notice. [
http://www.nedbank.co.za/terms/EmailDisclaimer.htm ]
********************

	[[alternative HTML version deleted]]