Truncated updated string with R DBI package

Question

I need to update a wide table on an SQL SERVER from R. So the package DBI seems to be very useful for that. The problem is that the R data.frame contains strings of more than 3000 characters and when I use the DBI dbSendQuery function, all strings are truncated to 256 characters.

Here could be a code example :

con <- odbc::dbConnect(drv = odbc::odbc(),
                   dsn = '***',
                   UID = '***',
                   PWD = '***')
df = data.frame(TEST = paste(rep("A", 300), collapse=""),
            TEST_ID = 1068858)
df$TEST = df$TEST %>% as.character
query = paste0('UPDATE MY_TABLE SET "TEST"=? WHERE TEST_ID=?')
update <- DBI::dbSendQuery(con, query)
DBI::dbBind(update, df) 
DBI::dbClearResult(update) 
odbc::dbDisconnect(con)

Then the following request return 256 instead of 300 :

SELECT LEN(TEST) FROM MY_TABLE WHERE TEST_ID = 1068858

NB : TEST is of type (varchar(max), NULL) and already contains strings of more than 256 chars.

Thanks in advance for any advice

Thanks for the link, as it was mentioned I tried to cast as text before updating : 'One solution is to cast the JSON as text before collecting the result from the database.' But it does not change anything `"TEST"=CAST(? AS VARCHAR(MAX))` — Ben, Sep 06 '18 at 08:21

score 1 · Answer 1 · answered Sep 17 '18 at 08:09

In the end, I choose to get rid of sophisticated functions. A solution was to write the table in .csv file and bulk insert it into the database. Here is an example using RODBC package :

write.table(x = df,
        file = "/path/DBI_error_test.csv",
        sep = ";",
        row.names = FALSE, col.names = FALSE,
        na = "NULL",
        quote = FALSE)

Query = paste("CREATE TABLE #MY_TABLE_TMP (
            TEST varchar(max),
            TEST_ID int 
          );

          BULK INSERT #MY_TABLE_TMP
          FROM 'C:\\DBI_error_test.csv'
          WITH
          (
          FIELDTERMINATOR = ';', 
          ROWTERMINATOR = '\n',
          BATCHSIZE = 500000,
          CHECK_CONSTRAINTS
          )

          UPDATE R
          SET R.TEST = #MY_TABLE_TMP.TEST
          FROM MY_TABLE AS R
          INNER JOIN #MY_TABLE_TMP ON #MY_TABLE_TMP.TEST_ID = R.TEST_ID;

          DROP TABLE #MY_TABLE_TMP; 
          ")

channel <- RODBC::odbcConnect(dsn = .DB_DSN_NAME,
                          uid = .DB_UID, 
                          pwd = .DB_PWD)
RODBC::sqlQuery(channel = channel, query = query, believeNRows = FALSE)
RODBC::odbcClose(channel = channel)

Truncated updated string with R DBI package

1 Answers1

Linked