CODEWITHSUNDEEP
rms-accesscrashrodbc
physicistintheory
physicistintheory

Reputation: 361

RODBC sqlSave crashes R front end

i am trying to write to an msaccess database using sqlSave from the RODBC package. i have no problems opening a connection to the database. i am able to read from tables. i can even make certain simple updates using sqlSave, but for the following table and the following update the R front end crashes. This happens both when running this from Rscript with real calculated data and from Rstudio when I run the following commands. i am running R 3.1 in 32 bit mode (for compatibility with access).

i have created a channel 'ch' and i am intending to write to a table called 'Test_Table' in a database called 'data.mdb'

test <- c(1,"Fixed","Upward","Gas", "2014-07-31",14,0.99)

test.df <- as.data.frame(t(test))

names(test.df) <- c("ID","Contract_Type","Direction", "Name_Of_Underlying","Data_Date","Months_To_Maturity","Absolute_Price_Move")

types <- c("integer","varchar","varchar","varchar","datetime","integer","double")
names(types) <- names(test.df)

sqlSave(ch, dat = test.df, tablename = "Test_Table",varTypes = types, append = T, verbose = T,safer = T)

I've also tried this with safer = F.

When I run this in RStudio I get the 'bomb' sign and am asked to start a new session. When I run via Rscript, the command line output indicates that all the correct bindings are occurring and that the parameters are correct. Then a window pops up to say that R for Windows front-end has stopped working and nothing has been written to the database.

Any thoughts are welcomed.

Upvotes: 4

Views: 2718

Answers (3)

Anna Dorrance
Anna Dorrance

Reputation: 1

I was having a similar issue and I added rownames = F and fast = F and it worked. I added both of these in the same edit so I'm not sure which caused the script to work.

Upvotes: 0

Matt O.
Matt O.

Reputation: 11

I have had this happen a few times when appending rows. In my cases, having extra columns in the dataframe seemed to be the issue. when I removed the columns from the dataframe that were not in the table, it worked.

Upvotes: 1

Jan Manď&#225;k
Jan Manď&#225;k

Reputation: 131

I had similar problem when I was trying to append data to existing table in MS SQL using sqlSave(). It has started to work when I added argument rownames=FALSE into sqlSave. In the RODBC package description there is:

rownames: If logical, save the row names as the first column rownames in the table?

The default value is TRUE, so I guess that RStudio crashed because I had no column for row names in table in database.

Upvotes: 13

Related Questions