2

Unfortunately, I will not be able to create a good repro for this issue without sharing confidential creds to the database I am having issues with. Hopefully I have enough information below to flag any obvious problems that ODBC experts will understand.

Background

I am running a MacBook Pro with the following specs: 

  Model Name:       MacBook Pro
  Model Identifier: MacBookPro15,1
  Processor Name:   6-Core Intel Core i7
  Processor Speed:  2.6 GHz
  Number of Processors: 1
  Total Number of Cores:    6
  L2 Cache (per Core):  256 KB
  L3 Cache: 9 MB
  Hyper-Threading Technology:   Enabled
  Memory:   32 GB
  Boot ROM Version: 1037.0.78.0.0 (iBridge: 17.16.10572.0.0,0)

My ODBC connection is set using FreeTDS as specified here.

The relevant portion of freetds.conf is as follows: 

# The POC SQL Server
[POC]
host = 172.22.238.154
port = 1433
tds version = 7.3

My odbcinst.ini file is as follows: 

[FreeTDS]
Description=FreeTDS Driver for Linux & SQL Server 
Driver=/usr/local/lib/libtdsodbc.so
Setup=/usr/local/lib/libtdsodbc.so
UsageCount=1

My odbc.ini file is specified as follows: 

[POC]
Description         = Connecton to Partners for our children SQL Server 
Driver              = FreeTDS
Servername          = POC

I am trying to make a connection to a SQL Server 2012 database (via VPN) using the following connection information in R: 

con <- DBI::dbConnect(odbc::odbc()
                  ,dsn = "POC"
                  ,uid = Sys.getenv("MSSQL_UN")
                  ,database = "CA_ODS"
                  ,pwd = Sys.getenv("MSSQL_PW"))

This generates the following connection object: 

> con
  <OdbcConnection> POC2
  Database: CA_ODS
  Microsoft SQL Server Version: 11.00.7001

In general, this connection works as expected. I can query the database using DBI::dbGetQuery(con, "select * from MyTable"), dplyr::tbl(con, MyTable), etc. without issue.

Problem

RStudio, however, is only displaying every other letter of the database objects, and truncating the object names after the first several letters. The following screenshot should illustrate the issue well:

enter image description here

The database I am trying to connect to is called CA_ODS. However, the RStudio object browser is only displaying every other letter of the database name (i.e. the DB is listed as C_D).

This does not appear to be limited to RStudio per se either. While the results of the actual database queries work fine as described above, the returned names from the INFORMATION_SCHEMA appear to match the information in the object browser. Below, when run directly from SQL Server Management Studio, the returned TABLE_CATALOG is CA_ODS, TABLE_SCHEMA is ndacan, etc. When run via the DB connection, however, I get the following.

> DBI::dbGetQuery(con, "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE 
TABLE_SCHEMA='ndacan'")
   TABLE_CATALOG TABLE_SCHEMA      TABLE_NAME TABLE_TYPE
1            C_D          naa            f21v BASE TABLE

Question

Any suggestions as to how I can respecify my ODBC connection in R or in my FreeTDS configs to get the full name of database objects returned?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
joemienko
  • 2,220
  • 18
  • 27
  • 1
    Likely related: https://github.com/r-dbi/odbc/issues/283. My guess is that your macos is running R-3.6. It appears that two things have worked for some people: (1) downgrading to R-3.5.3; and (2) reinstalling the `odbc` package [**from source**](https://github.com/r-dbi/odbc/issues/283#issuecomment-545469314) (not a CRAN binary). – r2evans Oct 24 '19 at 23:15
  • @r2evans This fixed it for me (i.e. `install.packages("odbc", type = 'source')`) – joemienko Oct 24 '19 at 23:34
  • 1
    If you want to provide an answer, I'll accept it. Unrelated (I was experiencing this issue before upgrading to Catalina, but Catalina appears to have jacked with XCode) - I needed to reinstall XCode before I could get `odbc` to install from source. `xcode-select --install` should do the trick for XCode – joemienko Oct 24 '19 at 23:38
  • I believe you answering yourself (with the steps you took) would be more credible: I don't have macos and was only forwarding a third-party conversation. You, on the other hand, did a little bit more. In addition to self-answering, it would likely be helpful to include the extra Xcode steps you took in a comment on the original issue (https://github.com/r-dbi/odbc/issues/283), as they are still figuring this one out. Thanks! – r2evans Oct 25 '19 at 00:17

1 Answers1

1

As noted in @r2evans comments, this appears to be an issue with odbc, running in R 3.6.0, on a Mac.

In general, it appears that this can be fixed by reinstalling odbc from source install.packages("odbc", type = 'source').

As also noted in the comments, I had recently upgraded my Mac to Catalina. Prior to installing odbc from source I needed to first reinstall XCode using xcode-select --install from my terminal.

As can be seen in the screen capture below, I am now getting the full object names displayed from the odbc connection.

enter image description here

joemienko
  • 2,220
  • 18
  • 27
  • thanks, this really helped me! (https://stackoverflow.com/questions/58777915/truncation-of-database-name-and-columns-while-connecting-to-sql-database-from-r/58788514#58788514) – Dhiraj Nov 10 '19 at 12:15