4

Hi I am using R to save a data frame into a DB2 SQL table. I seem to be able to create the table skeleton, but not append the data to the table -

>df <- read.csv("dat.csv")

where dat.csv is a csv with no headers, just raw data in two columns

then i create the table:

>sqlQuery(channel, "create table sqltable 
                   (
                   col1  int,
                   col2  float
                   )"
         (

where I confirm the table is created by being able to select the empty table "sqltable" on the database

so now I need to add the data from "dat.csv" into "sqltable" by doing:

>sqlSave(channel, df, "sqltable", verbose=T, fast=T, append=T)

no: 1 rownames 1/***/no: 2 col1 31105/***/no: 3 col2 0.001/***/
no: 2 rownames 1/***/no: 2 col1 31106/***/no: 3 col2 0.023/***/
no: 3 rownames 1/***/no: 2 col1 31107/***/no: 3 col2 1.456/***/
no: 4 rownames 1/***/no: 2 col1 31108/***/no: 3 col2 0.001/***/
no: 5 rownames 1/***/no: 2 col1 31109/***/no: 3 col2 2.102/***/

all seems good until I do:

>sqlQuery(channel,"select * from sqltable")

[1] COL1     COL2
<0 rows> or 0-length row.names

the sqlSave command clearly picks up the data from dat.csv, so why is it not added to the table? what am I doing wrong?

Karsten W.
  • 17,826
  • 11
  • 69
  • 103
brucezepplin
  • 9,202
  • 26
  • 76
  • 129
  • 1
    I don't know anything about `R`, but do you perhaps have to `COMMIT` your insert, or else it will automatically roll back? – bhamby Feb 21 '14 at 14:07
  • What type of connection are you using, and what sort of OS is your DB2 server running on? – WarrenT Feb 21 '14 at 23:10

2 Answers2

8

It would be a good idea to define varTypes within sqlSave. Here's how I do it. :-) 

columnTypes <- list(ColumnName="varchar(255)", NumberColumn="float", datecolumn="date")

This defines the column types for you data so you can add the argument within sqlSave. So lets do the sqlSave now. 

sqlSave(channel, YourTable, rownames = FALSE, varTypes = columnTypes)

and if the table has already been added then I would do an sqlDrop before that.

Why is this an answer?

Well, I had the same problem. I was getting an error saying it couldn't execute the sqlSave but it was putting in my columns in the database. So I had to fix the columnTypes. R tries to do it manually but it's not perfect. :-)

Nedinator
  • 1,052
  • 13
  • 24
1

Use rownames = FALSE inside your sqlSave().

Since you do not provide a sample of the data from dat.csv, I am assuming it has 2 columns based on your sqlSave code block. From your create statement, sqltable has only 2 columns; sqlSave defaults to rownames = TRUE which will make the data you enter from the dataframe have 3 columns, instead of the 2 you desire. By attempting to place 3 columns into a Microsoft SQL Server table with 2 columns without rownames = FALSE my R session gets aborted with a fatal error.

Jared Marx
  • 53
  • 7
  • I was getting an error, `"Error in cbind_all(x) : Argument 1 must have names".` setting `rownames = F` fixed the error or me – Nova Mar 27 '19 at 18:36
  • @Nova, in case you are trying to figure out what ODBC connector to use inside R, I recommend using DBI instead of RODBC . DBI allows for a finer control of the schema used, while RODBC's sqlSave only uses the default schema. DBI also likely has more active developers than RODBC. – Jared Marx Mar 28 '19 at 19:51
  • Thanks Jared, that's so thoughtful! – Nova Mar 29 '19 at 12:44