Architecting, installing and maintaining your SAS environment

ODBC Connection to SPDS from R - no data or tables.

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

ODBC Connection to SPDS from R - no data or tables.

We have recently setup and ODBC connection to our SPDS server to allow more products to access data directly.  It took a bit of work but we have both 32bit and 64bit connections working to our 64bit SPDS 5.1 server.  We can access files from MS Access and Excel, along with STATA (sort of).   In all cases access is not quite as expected but at least we can get data pulled.

 

The problem that we have run into is accessing data from R.  I am hoping someone has done this with SPDS and can provide some example code that pulls data - I don't know if this is a a problem with RODBC or with the SPDS ODBC connection.

 

Getting a connection with ch <- odbcConnect("spdsserv") works fine.

Issuing a sqlTables(ch) returns 0 tables (I know tables exist because I can load them from Access/Excel/STATA)

If I use sqlQuery(ch,"select * from pccf_1986)  all of the fields are pulled but no records (table is found but no data loaded)

if I use sqlFetch(ch,"pccf_1986") the table is not found.

 

I don't use ODBC very much but there might be a direct execute command that will load data rather than the interpretation.

 

Thanks for any advice or direction.

 

I realize that this may be an R problem so I will go to the R boards but I am trying to figure out if it is R or SPDS.


Accepted Solutions
Solution
‎09-25-2015 06:23 AM
Occasional Contributor
Posts: 5

Re: ODBC Connection to SPDS from R - no data or tables.

Thanks for the reply - yes RODBC (odbcConnect) allows you to define a schema.  I had tried this without success.

I had a reply from the r-help list suggesting :
ch <- odbcConnect('tmp', believeNRows=FALSE, colQuote=NULL)

This is the connection used when connecting via odbc to base SAS tables.  It worked with SPDS as well - the colQuote=NULL doesn't make a difference but the believeNRows does.

 

CB

View solution in original post


All Replies
Super User
Posts: 9,687

Re: ODBC Connection to SPDS from R - no data or tables.

It seems a Schema problem.
In SAS you can define it as
libname xx odbc dsn=xxxx schema=dbo ;

I don't know if R function odbcConnect() has an option to define Schema. Or you could check ODBC connection if it could define it .
Solution
‎09-25-2015 06:23 AM
Occasional Contributor
Posts: 5

Re: ODBC Connection to SPDS from R - no data or tables.

Thanks for the reply - yes RODBC (odbcConnect) allows you to define a schema.  I had tried this without success.

I had a reply from the r-help list suggesting :
ch <- odbcConnect('tmp', believeNRows=FALSE, colQuote=NULL)

This is the connection used when connecting via odbc to base SAS tables.  It worked with SPDS as well - the colQuote=NULL doesn't make a difference but the believeNRows does.

 

CB

Community Manager
Posts: 352

Re: ODBC Connection to SPDS from R - no data or tables.

Hi, 

 

This is Shelley Sessoms, the community manager for the Admin community. Since it looks like this question has been answered, I'm going to mark it as "Accept as Solution" so that it may help others with a similar question.

 

Thanks,

Shelley

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 551 views
  • 1 like
  • 3 in conversation