Case sensitive R in sp_execute_external_script
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'
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.
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!
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.