Case sensitive R in sp_execute_external_script

[This article was first published on R – TomazTsql, and kindly contributed to R-bloggers]. (You can report issue about the content on this page here)
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

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 about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

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)