rhandsontable writing NULL values to SQL database using dbWriteTable

Question

I'm creating the following table using library(rhandsontable)

rhandsontable(df, className = "htCenter", stretchH = "all", columnSorting = TRUE) %>%
         hot_col("Date", type = "date", dateFormat = "YYYY-MM-DD") %>%
         hot_cols(format = '1')

The table is converted to a data frame dfHot <- hot_to_r(input$hot) and then copied to a SSMS SQL database with

dbWriteTable(con, "TEST", dfHot, append = TRUE, overwrite = FALSE, row.names = FALSE)

The problem is the table can contain NULL values which appear as NA in R, but I need them to come across as NULL into SQL.

I'm getting this error when NA values are present in the table:

Warning: Error in .local: execute JDBC update query failed in dbSendUpdate (The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 6 (""): The supplied value is not a valid instance of data type float. Check the source data for invalid values. An example of an invalid value is data of numeric type with scale greater than precision.)

If no NA values are present, the table imports without any issues.

I found this related post, but still doesn't seem there is an answer when using library(RJDBC): https://stackoverflow.com/questions/51598952/dbwritetable-cannot-write-nulls-into-sql-server-table — lumiukko, Dec 03 '19 at 14:26

score 0 · Accepted Answer · answered Dec 03 '19 at 15:35

0

Instead of using dbWriteTable, I resolved this issue by inserting the VALUES into SQL. Details found here: https://www.pmg.com/blog/insert-r-data-frame-sql%EF%BB%BF/

answered Dec 03 '19 at 15:35

lumiukko

249
3
13

rhandsontable writing NULL values to SQL database using dbWriteTable

1 Answers1