Adding images from R to SQL Server

INSERT INTO dbo.Plots(plot)
EXEC   sp_execute_external_script
@language = N’R’
,@script = N’
library(ggplot2); #import ggplot2 library
image_file = tempfile(); #create a temporary file
jpeg(filename = image_file, width=600, height=800); #create a JPEG graphic device
#qplot = quick plot, a helper function of ggplot2
qplot(Sepal.Length, Petal.Length, data = iris, color = Species,
xlab = “Sepal Length”, ylab = “Petal Length”,
main = “Sepal vs Petal Length in Fishers Iris data”);
dev.off(); #dev.off returns the number and name of the new active device (after the specified device has been shut down). (device = graphical device)
#file() opens a file, in this case the image. rb = read binary
#readBin() reads binary data. what = described the mode of the data. In this case, it”s raw data. n = maximum number of records to read.
#data.frame converts the data to a data frame, which is required as output by SQL 1Server. The result is written to the OutputDataset variable.
OutputDataset <- data.frame(data=readBin(file(image_file,”rb”),what=raw(),n=1e6));

,@input_data_1 = N”
,@output_data_1_name = N’OutputDataset’;

INSERT INTO dbo.Plots(plot)
EXEC   sp_execute_external_script
@language = N’R’
,@script = N’
library(plotly); #import ggplot2 library
image_file = tempfile(); #create a temporary file
jpeg(filename = image_file, width=600, height=800); #create a JPEG graphic device
g <- ggplot(txhousing, aes(x = date, y = sales, group = city)) +
geom_line(alpha = 0.4)
ggplotly(g, tooltip = c(“city”))
dev.off(); #dev.off returns the number and name of the new active device (after the specified device has been shut down). (device = graphical device)
OutputDataset <- data.frame(data=readBin(file(image_file,”rb”),what=raw(),n=1e6));

,@input_data_1 = N”
,@output_data_1_name = N’OutputDataset’;

INSERT INTO dbo.Plots(plot)
EXEC   sp_execute_external_script
@language = N’R’
,@script = N’
library(ggplot2)
g <- ggplot(txhousing, aes(x = date, y = sales, group = city)) +
geom_line(alpha = 0.4)
g
dev.off(); #dev.off returns the number and name of the new active device (after the specified device has been shut down). (device = graphical device)
OutputDataset <- data.frame(data=readBin(file(image_file,”rb”),what=raw(),n=1e6));

,@input_data_1 = N”
,@output_data_1_name = N’OutputDataset’;

library(plotly)
g <- ggplot(txhousing, aes(x = date, y = sales, group = city)) +
geom_line(alpha = 0.4)
ggplotly(g, tooltip = c(“city”))

library(ggplot2)
g <- ggplot(txhousing, aes(x = date, y = sales, group = city)) +
geom_line(alpha = 0.4)
g

— truncate table  dbo.Plots

select * From  dbo.Plots

Leave a Reply

Your email address will not be published. Required fields are marked *