Case sensitive R in sp_execute_external_script

June 21, 2016
By

(This article was first published on R – TomazTsql, and kindly contributed to R-bloggers)

R Language is case sensitive! Any R-coder can tell you this and share the experience. Case sensitive in R can be a challenge as with any case sensitive language.

This also applies to SP_EXECUTE_EXTERNAL_SCRIPT stored procedure in T-SQL. This is a simple demo that will support this. This script will return normal result.

EXECUTE sp_execute_external_script    
       @language = N'R'    
      ,@script=N'OutputDataSet<-c1'
      ,@input_data_1 = N'SELECT 1234 as number'
      ,@input_data_1_name = N'c1'

 

2016-06-21 21_33_05-SQL_Queries.sql - SICN-00031_SQLSERVER2016RC3.WideWorldImporters (SPAR_si0101798

With slight modification of parameter @input_data_1_name I will change the name from c1 (small caps) to C1 (all caps).

-- modification of @input_data_1_name  from "c1" (small caps) to "C1" ( all caps)
EXECUTE sp_execute_external_script    
       @language = N'R'    
      ,@script=N'OutputDataSet<-c1'
      ,@input_data_1 = N'SELECT 1234 as cifra'
      ,@input_data_1_name = N'C1'

It can be seen the difference of name of input data set (C1) with parameter @input_data_1_name and the dataset introduced into R with @script parameter.

Capture

In this case R output result shown in Message window of SSMS is at least informative that it will tell you: object ‘c1’ not found. It might be that some other time the error message will not be that straightforward.

Despite relative “oh, yes. I know this error” I am posting this, as I have found myself numerous times forgetting the fact, R is case sensitive. In opposite to @script or @input_data_1_name parameters, all other parameters (@input_data_1, @params, @output_data,..) are not case sensitive as the not go through R engine.

So spear yourself minutes of searching for errors in R script by keeping in mind that all code passed through @script or @input_data_1_name are case sensitive!

Unfortunately SSMS does not have debugger for R code (yet!), so you can always copy/paste the R code into RTVS or into RStudio (or any other) and validate and debug your R code.

Happy R-SQLing!

To leave a comment for the author, please follow the link and comment on their blog: R – TomazTsql.

R-bloggers.com offers daily e-mail updates about R news and tutorials on topics such as: Data science, Big Data, R jobs, visualization (ggplot2, Boxplots, maps, animation), programming (RStudio, Sweave, LaTeX, SQL, Eclipse, git, hadoop, Web Scraping) statistics (regression, PCA, time series, trading) and more...



If you got this far, why not subscribe for updates from the site? Choose your flavor: e-mail, twitter, RSS, or facebook...

Comments are closed.

Search R-bloggers


Sponsors

Never miss an update!
Subscribe to R-bloggers to receive
e-mails with the latest R posts.
(You will not see this message again.)

Click here to close (This popup will not appear again)