RODBC Error: 'getCharCE' must be called on a CHARSXP

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

RODBC Error: 'getCharCE' must be called on a CHARSXP

Mike Pfeiff
I am trying to connect to an internal database and use the sqlQuery command to reduce and retrieve data using the following code:

channel <-odbcConnect("some_dsn", uid="", pwd="")
txt<-'SELECT Date, Region, Price FROM TableXYZ WHERE Type="Domestic"'
sqlQuery(channel, cat(txt,"\n"),errors=TRUE,)
close(channel)

However, I get the following error immediately after sqlQuery command:

Error in odbcQuery(channel, query, rows_at_time) :
  'getCharCE' must be called on a CHARSXP

I believe my connection is good because I used the following commands to successfully view the columns:

sqlColumns(channel, TableXYZ)

There doesn't seem to be much info on "getCharCE" and/or "CHARSXP.  Any guidance the group could provide this vey new user to R, would be greatly appreciated




        [[alternative HTML version deleted]]

______________________________________________
[hidden email] mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Reply | Threaded
Open this post in threaded view
|

Re: RODBC Error: 'getCharCE' must be called on a CHARSXP

Uwe Ligges-3


On 20.12.2011 14:55, Mike Pfeiff wrote:
> I am trying to connect to an internal database and use the sqlQuery command to reduce and retrieve data using the following code:
>
> channel<-odbcConnect("some_dsn", uid="", pwd="")
> txt<-'SELECT Date, Region, Price FROM TableXYZ WHERE Type="Domestic"'
> sqlQuery(channel, cat(txt,"\n"),errors=TRUE,)

sqlQuery(channel, txt, errors=TRUE)

seems more plausible (since cat returns NULL).

Uwe Ligges



> close(channel)
>
> However, I get the following error immediately after sqlQuery command:
>
> Error in odbcQuery(channel, query, rows_at_time) :
>    'getCharCE' must be called on a CHARSXP
>
> I believe my connection is good because I used the following commands to successfully view the columns:
>
> sqlColumns(channel, TableXYZ)
>
> There doesn't seem to be much info on "getCharCE" and/or "CHARSXP.  Any guidance the group could provide this vey new user to R, would be greatly appreciated
>
>
>
>
> [[alternative HTML version deleted]]
>
> ______________________________________________
> [hidden email] mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.

______________________________________________
[hidden email] mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Reply | Threaded
Open this post in threaded view
|

Re: RODBC Error: 'getCharCE' must be called on a CHARSXP

Mike Pfeiff
Thanks.   But if I do as you suggested...

        txt<-'SELECT Date,  Region, Price FROM TableXYZ WHERE Type="Domestic"'

returns...

        "SELECT TradeDate, Hub, SettlementPrice FROM PowerSettlements WHERE Contract=\"ERN\""

Which because of the extra "\"' is improper SQL form.

Maybe I should be asking what is the proper way to return "" inside of a char string?

-----Original Message-----
From: Uwe Ligges [mailto:[hidden email]]
Sent: Tuesday, December 20, 2011 8:46 AM
To: Mike Pfeiff
Cc: '[hidden email]'
Subject: Re: [R] RODBC Error: 'getCharCE' must be called on a CHARSXP



On 20.12.2011 14:55, Mike Pfeiff wrote:
> I am trying to connect to an internal database and use the sqlQuery command to reduce and retrieve data using the following code:
>
> channel<-odbcConnect("some_dsn", uid="", pwd="") txt<-'SELECT Date,
> Region, Price FROM TableXYZ WHERE Type="Domestic"'
> sqlQuery(channel, cat(txt,"\n"),errors=TRUE,)

sqlQuery(channel, txt, errors=TRUE)

seems more plausible (since cat returns NULL).

Uwe Ligges



> close(channel)
>
> However, I get the following error immediately after sqlQuery command:
>
> Error in odbcQuery(channel, query, rows_at_time) :
>    'getCharCE' must be called on a CHARSXP
>
> I believe my connection is good because I used the following commands to successfully view the columns:
>
> sqlColumns(channel, TableXYZ)
>
> There doesn't seem to be much info on "getCharCE" and/or "CHARSXP.  
> Any guidance the group could provide this vey new user to R, would be
> greatly appreciated
>
>
>
>
> [[alternative HTML version deleted]]
>
> ______________________________________________
> [hidden email] mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide
> http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.

______________________________________________
[hidden email] mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Reply | Threaded
Open this post in threaded view
|

Re: RODBC Error: 'getCharCE' must be called on a CHARSXP

Uwe Ligges-3


On 20.12.2011 16:27, Mike Pfeiff wrote:

> Thanks.   But if I do as you suggested...
>
> txt<-'SELECT Date,  Region, Price FROM TableXYZ WHERE Type="Domestic"'
>
> returns...
>
> "SELECT TradeDate, Hub, SettlementPrice FROM PowerSettlements WHERE Contract=\"ERN\""
>
> Which because of the extra "\"' is improper SQL form.
>
> Maybe I should be asking what is the proper way to return "" inside of a char string?
>

That *is* the proper way: R jsut prints a " by \" in order to show tha
those double quotes are within a string that is enclosed in double
quotes. It is correctly passed to the SQL interface.

Uwe ligges


> -----Original Message-----
> From: Uwe Ligges [mailto:[hidden email]]
> Sent: Tuesday, December 20, 2011 8:46 AM
> To: Mike Pfeiff
> Cc: '[hidden email]'
> Subject: Re: [R] RODBC Error: 'getCharCE' must be called on a CHARSXP
>
>
>
> On 20.12.2011 14:55, Mike Pfeiff wrote:
>> I am trying to connect to an internal database and use the sqlQuery command to reduce and retrieve data using the following code:
>>
>> channel<-odbcConnect("some_dsn", uid="", pwd="") txt<-'SELECT Date,
>> Region, Price FROM TableXYZ WHERE Type="Domestic"'
>> sqlQuery(channel, cat(txt,"\n"),errors=TRUE,)
>
> sqlQuery(channel, txt, errors=TRUE)
>
> seems more plausible (since cat returns NULL).
>
> Uwe Ligges
>
>
>
>> close(channel)
>>
>> However, I get the following error immediately after sqlQuery command:
>>
>> Error in odbcQuery(channel, query, rows_at_time) :
>>     'getCharCE' must be called on a CHARSXP
>>
>> I believe my connection is good because I used the following commands to successfully view the columns:
>>
>> sqlColumns(channel, TableXYZ)
>>
>> There doesn't seem to be much info on "getCharCE" and/or "CHARSXP.
>> Any guidance the group could provide this vey new user to R, would be
>> greatly appreciated
>>
>>
>>
>>
>> [[alternative HTML version deleted]]
>>
>> ______________________________________________
>> [hidden email] mailing list
>> https://stat.ethz.ch/mailman/listinfo/r-help
>> PLEASE do read the posting guide
>> http://www.R-project.org/posting-guide.html
>> and provide commented, minimal, self-contained, reproducible code.

______________________________________________
[hidden email] mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.