Rodbc SQLsave Table Creation Problems

RODBC sqlSave table creation problems

After re-reading the RODBC vignette and here's the simple solution that worked:

sqlDrop(db, "df", errors = FALSE)
sqlSave(db, df)

Done.

After experimenting with this a lot more for several days, it seems that the problems stemmed from the use of the additional options, particularlly table = or, equivalently, tablename =. Those should be valid options but somehow they manage to cause problems with my particular version of RStudio ((Windows, 64 bit, desktop version, current build), R (Windows, 64 bit, v3), and/or MS SQL Server 2008.

sqlSave(db, df) will also work without sqlDrop(db, "df") if the table has never existed, but as a best practice I'm writing try(sqlDrop(db, "df", errors = FALSE), silent = TRUE) before all sqlSave statements in my code.

Problems creating and populating tables to HANA RODBC R

Generally, it's a good idea to specify the target table in SAP HANA beforehand. That way things like COLUMN/ROW store setting and the specific data types for each column can be set as they should be (e.g. sqlSave doesn't seem to create NVARCHAR columns even when UNICODE data needs to be saved).

This is an example that just works out of the box for me (also SPS11):

library("RODBC") 
ch<-odbcConnect("SK1", uid="DEVDUDE",pwd="*******")

table.for.save <- 'AIRQUALITY'
aqdata <- airquality
sqlSave(ch,dat = aqdata, tablename = table.for.save, verbose = TRUE, rownames =  FALSE)
odbcClose(ch)

Query: CREATE TABLE "AIRQUALITY" ("Ozone" INTEGER, "SolarR" INTEGER, "Wind" DOUBLE, "Temp" INTEGER, "Month" INTEGER, "Day" INTEGER)
Query: INSERT INTO "AIRQUALITY" ( "Ozone", "SolarR", "Wind", "Temp", "Month", "Day" ) VALUES ( ?,?,?,?,?,? )

Binding: 'Ozone' DataType 4, ColSize 10
Binding: 'SolarR' DataType 4, ColSize 10
Binding: 'Wind' DataType 8, ColSize 15
Binding: 'Temp' DataType 4, ColSize 10
Binding: 'Month' DataType 4, ColSize 10
Binding: 'Day' DataType 4, ColSize 10
Parameters:
no: 1: Ozone 41//no: 2: SolarR 190//no: 3: Wind 7.4//no: 4: Temp 67//no: 5: Month 5//no: 6: Day 1//
...

Issues with sqlSave in R?

Figured out a work around.

For the first iteration of my loop, I used the following command:

sqlSave(conn, dat = df, rownames = FALSE)

For iterations 2 through n of my loop, I used the following command:

sqlSave(conn, dat = df, append = TRUE, rownames = FALSE)

This created a table [dbo].[df] in the database [PL_DEV] for me (since I set up my RODBC directly to that database).

Insert R dataframe into SQL (RODBC) - error table not found

To avoid the error, you could specify the database in the connection string:

Driver=ODBC Driver 17 for SQL Server; Server = someserveraddress; database = some_db; Uid = user_login; Pwd = some_password

and avoid using brackets:

sqlSave(myconn, mydf, tablename = 'some_schema.my_table',  append = F, rownames = F,  verbose=TRUE)

---

Related Topics