debugging odbc connections in Linux

Posit Professional & Hosted Products
,
1

My system admins have updated our production Linux environment to accommodate a number of system changes unrelated to my R stack which lives on the same machine. I wasn't expecting any impact on my tooling, but ever since the upgrade, I've been unable to get odbc to connect to SQL Server using FreeTDS. What's so weird is that I can connect just fine. But any attempt to execute a query results in:

> dbGetQuery(con, "select 'hi';")
Error in new_result(connection@ptr, statement, immediate) :
  nanodbc/nanodbc.cpp:1345: 00000: [FreeTDS][SQL Server]Unknown error
In addition: Warning message:
In new_result(connection@ptr, statement, immediate) :
  Cancelling previous query

shockingly my Google Fu is failing me in figuring out what's going on.

Here's my system info:

> Sys.info()
                              sysname                               release
                              "Linux"          "3.10.0-957.12.1.el7.x86_64"
                              version                              nodename
"#1 SMP Wed Mar 20 11:34:37 UTC 2019"                        "e8063f1b7fb9"
                              machine

My connection string looks like this (with server, database, user, and pwd passed in as strings:

con <- DBI::dbConnect(
      odbc::odbc(),
      Servername  = server,
      Driver      = "FreeTDS",
      Database    = database,
      Port        = 1433,
      UID         = user,
      PWD         = pwd )

what's puzzling me how do I even debug an issue where I can connect, but not execute. Any ideas of how I even approach debugging?

2

so one natural test is to use the tsql command to connect from the command prompt... so I did that

$ tsql -S my_server -D my_db -U jdlong -P xxxxxx
locale is "C"
locale charset is "ANSI_X3.4-1968"
using default charset "ISO-8859-1"
Setting my_db as default database in login packet
1>

then I could do stuff like this:

1> select 'hello world!'
2> GO

hello world!
(1 row affected)

and that works... so clearly FreeTDS is cruising along. So I think I'm having an ODBC issue...

3

so the ODBC test is to use isql command line tool to issue queries. I had to set up a DNS entry in order to do that because sql requites a DNS. When I did that I could then look at my DNS entry:

$ cat ~/.odbc.ini
[MYSERVER]
Description         = Test to SQLServer
Driver              = FreeTDS
Trace               = Yes
TraceFile           = /tmp/sql.log
Database            = analysts
Servername          = myserver
Port                = 1433
Protocol            = 7.2
ReadOnly            = No
RowVersioning       = No
ShowSystemTables    = No
ShowOidColumn       = No
FakeOidIndex        = No

then try isql

$ isql MYSERVER my_uid super_secret -v
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select 'hello world'
+------------+
|            |
+------------+
| hello world|
+------------+
SQLRowCount returns 1
1 rows fetched
SQL>

so ODBC works... and I'm flummoxed. Why does ODBC work, from command line, but not from my connection string in R? Hmmmmm

I guess my next test is to try and use the connection DSN from within R instead of using the connection string approach.

4

ok.. if I hand roll my connection string in R the bloody thing works.

so this is a bug in my library that handles my credentials & logging in.

so I went through that code and I discovered that after I set up the connection I send a single command over that connection:

odbc::dbSendQuery(con, "SET QUOTED_IDENTIFIER ON")

Well it looks like that quoted identifiers bit was carried over from this experience:

It looks like TDS does quoting by default so I need to not pass this param. Though I have no idea why it used to work and now breaks.

5

Here's more info on what's going on with setting the quoted identifier