1
Vote

Error: Value of SET_STRING_ELT() must be a 'CHARSXP' not a 'character'

description

I'm using a very useful package to work with SqlServer (rsqlserver package). Extracting information for queries works fine and fast, but when i work with big numbers of records (millions) this error appear:
Error: Value of SET_STRING_ELT() must be a 'CHARSXP' not a 'character'
I have reviewed the code of the package but can not find the error, which makes me assume that this occurs in the conversion in rClr package
thanks for the help

file attachments

comments

jperraud wrote Jul 15, 2015 at 7:06 AM

You'll need to provide more information:

Do you have more information in the error message?

rClr is good at showing the .NET stack trace. That said, the look of the error message suggests this is the R engine itself which calls the error function directly.

if you can reproduce the issue, you can retrieve the R stack trace with the R command traceback()

you can also use the following option to interactively debug the R code.
options(error=recover)

pfv007 wrote Jul 16, 2015 at 5:19 PM

I posts all elements inthe process...

SQL SERVER VERSION

select @@VERSION
Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
May 14 2014 18:34:29 
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

TABLE INFO

sp_help EML_ADR

Name Owner Type Created_datetime

EML_ADR dbo user table 2013-07-03 11:52:13.327

Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation

EML_ADR_ID int no 4 10 0 no (n/a) (n/a) NULL
EML_USR_ID char no 60 yes no yes SQL_Latin1_General_CP1_CI_AS
ELC_ADR_DMN_ID int no 4 10 0 yes (n/a) (n/a) NULL
ELC_ADR_TP_ID int no 4 10 0 yes (n/a) (n/a) NULL
ADR_TP_ID int no 4 10 0 yes (n/a) (n/a) NULL

Identity Seed Increment Not For Replication

No identity column defined. NULL NULL NULL

RowGuidCol

No rowguidcol column defined.

Data_located_on_filegroup

PRIMARY


index_name index_description index_keys

EML_ADR_PK clustered, unique, primary key located on PRIMARY EML_ADR_ID


constraint_type constraint_name delete_action update_action status_enabled status_for_replication constraint_keys

FOREIGN KEY EML_ADR_ADR_TP_FK No Action No Action Enabled Is_For_Replication ADR_TP_ID
                                                                                                             REFERENCES BDWH_SOR.dbo.CV (CL_ID)
FOREIGN KEY EML_ADR_ELC_ADR_DMN_FK No Action No Action Enabled Is_For_Replication ELC_ADR_DMN_ID
                                                                                                             REFERENCES BDWH_SOR.dbo.ELC_ADR_DMN (ELC_ADR_DMN_ID)
FOREIGN KEY EML_ADR_ELC_ADR_TP_FK No Action No Action Enabled Is_For_Replication ELC_ADR_TP_ID
                                                                                                             REFERENCES BDWH_SOR.dbo.CV (CL_ID)
FOREIGN KEY EML_ADR_LO_FK No Action No Action Enabled Is_For_Replication EML_ADR_ID
                                                                                                             REFERENCES BDWH_SOR.dbo.LO (LO_ID)
PRIMARY KEY (clustered) EML_ADR_PK (n/a) (n/a) (n/a) (n/a) EML_ADR_ID


No foreign keys reference table 'EML_ADR', or you do not have permissions on referencing tables.
No views with schema binding reference table 'EML_ADR'.

select count(*) from EML_ADR

2692800

(1 row(s) affected)

R CODE

PACKAGES

require(data.table,quietly=TRUE)
require(rsqlserver,quietly= TRUE)

CONEXION SQLSERVER

chn01 <- dbConnect(rsqlserver::SqlServer(),url="Server=ECBPPRQ75\Q75,10500;Database=BDWH_SOR;Trusted_Connection=True;")

EXTRACT EMAIL

email01 <- data.table(dbGetQuery(chn01,
"select
 ltrim(rtrim(eml_usr_id)) as email
from eml_adr",stringsAsFactors=FALSE))

R CONSOLE

R version 3.2.0 (2015-04-16) -- "Full of Ingredients"
Copyright (C) 2015 The R Foundation for Statistical Computing
Platform: x86_64-w64-mingw32/x64 (64-bit)

R is free software and comes with ABSOLUTELY NO WARRANTY.
You are welcome to redistribute it under certain conditions.
Type 'license()' or 'licence()' for distribution details.

R is a collaborative project with many contributors.
Type 'contributors()' for more information and
'citation()' on how to cite R or R packages in publications.

Type 'demo()' for some demos, 'help()' for on-line help, or
'help.start()' for an HTML browser interface to help.
Type 'q()' to quit R.

Revolution R Open 3.2.0
Using CRAN snapshot taken on 2015-05-01
The enhanced R distribution from Revolution Analytics
Visit mran.revolutionanalytics.com/open for information
about additional features and technical support options.
require(data.table,quietly=TRUE)
data.table 1.9.5 For help type ?data.table or https://github.com/Rdatatable/data.table/wiki
require(rsqlserver,quietly= TRUE)
Loading the dynamic library for Microsoft .NET runtime...
Loaded Common Language Runtime version 4.0.30319.34209

chn01 <- dbConnect(rsqlserver::SqlServer(),url="Server=ECBPPRQ75\Q75,10500;Database=BDWH_SOR;Trusted_Connection=True;")
email01 <- data.table(dbGetQuery(chn01,
  • "select
  • ltrim(rtrim(eml_usr_id)) as email
  • from eml_adr",stringsAsFactors=FALSE))
    Error: Value of SET_STRING_ELT() must be a 'CHARSXP' not a 'integer'
traceback(max.lines=30)
7: .sqlServerFetch(res, n, ...)
6: fetch(rs, n = -1)
5: fetch(rs, n = -1)
4: sqlServerExecRetrieve(conn, statement, ...)
3: dbGetQuery(chn01, "select\n ltrim(rtrim(eml_usr_id)) as email\n from eml_adr",
   stringsAsFactors = FALSE)
2: dbGetQuery(chn01, "select\n ltrim(rtrim(eml_usr_id)) as email\n from eml_adr",
   stringsAsFactors = FALSE)
1: data.table(dbGetQuery(chn01, "select\n ltrim(rtrim(eml_usr_id)) as email\n from eml_adr",
   stringsAsFactors = FALSE))

pfv007 wrote Jul 16, 2015 at 8:32 PM

Attachment the code and outputs