Inserting into SQL Server VARBINARY column from R

Question

I have a plots table, whose columns include plot, which stores the binary data of an image file. I'm running a T-SQL query which calls an R script and gets back a data frame of the data to insert. The data frame looks like this:

    plot     name  date_from    date_to
1 ABCDEF  plot1   2016-08-25   2016-08-31
2 AAAAAA  plot2   2016-08-25   2016-08-31

As you can see, the plot column contains raw data already.

To clarify, what I want to do is insert two rows into the database with the data in the data frame (the data frame column names match the database columns).

The problem I'm getting with

INSERT INTO dbo.plots
EXECUTE sp_execute_external_script
    @language = N'R'
    ,@script = N'source("path/to/r/script.R")'
    ,@output_data_1_name = N'output_dataset'

is "Implicit conversion from data type nvarchar(max) to varbinary(max) is not allowed. Use the CONVERT function to run this query".

However I'm not sure how I can rectify this error. Where would I put the CONVERT function? Or is there some other way?

Answer 1

Unfortunately, my version of SQL Server doesn't do all the cool R stuff yours does. So the best I can offer is an R Script that can successfully import binary data into a table and hope you are able to make the necessary adaptations.

I'm using a table on SQL Server defined as

CREATE TABLE [dbo].[InsertFile](
    [OID] [int] IDENTITY(1,1) NOT NULL,
    [filename] [varchar](50) NULL,
    [filedata] [varbinary](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

My R script is

library(RODBCext)
library(magrittr)

# My example just grabs all the text files out of a directory,
# but as long as you have the full filename, this will work.

file_name <- list.files([directory_to_files],
                        pattern = "[.]txt$",
                        full.names = TRUE)

file_content <- 
  vapply(
    file_name,
    function(x)
    {
      # read the binary data from the file
      readBin(x,
              what = "raw",
              n = file.info(x)[["size"]]) %>%
        # convert the binary data to a character string suitable for import
        as.character() %>%
        paste(collapse = "")
    },
    character(1)
  )

channel <- odbcConnect(...) # Create your connection object here

sqlExecute(
  channel = channel,
  query = paste0("INSERT INTO dbo.InsertFile ",
                 "(filename, filedata) ",
                 "VALUES ",
                 "(?, ?)"),
  data = list(filename = basename(file_name),
              filedata = file_content)
)

After executing that script, I have a new line in my dbo.InsertFile table for each file in file_name.

Answer 2

For SQL Server R services, character type maps to VARCHAR and raw type maps to VARBINARY (see Working with R Data Types). To store data as VARBINARY, the hex string has to be converted to raw bytes, which can be done either in R or SQL. Here is an example with conversion done in SQL using a temporary table (inspired by scsimon's comment)

CREATE TABLE #test
(
    [data] VARBINARY(MAX),
)


CREATE TABLE #temp
(
    [data] VARCHAR(MAX),
)

INSERT INTO #temp
EXEC sp_execute_external_script
@language=N'R',
@script=N'OutputDataSet <- as.data.frame("ABCDEF")' 

INSERT INTO #test SELECT CONVERT(VARBINARY(MAX), data, 2) FROM #temp;