debugging odbc connections in Linux

[jdlong]

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?

[jdlong]

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...

[jdlong]

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:

Changing the table quoting with SQL Translation

I'm using RJDBC to access a SQL Server database. Everything works pretty well except for one thing (that keeps it from working at all). dbplyr is defaulting to quoting table aliases in the resulting SQL like this: SELECT top 5 * FROM mydb.myschema.mytable "mt" join mydb.myschema.myothertable "mot" on "mt".key = "mot".key My SQL Server thinks that's a really bad idea and desperately wants me to put table aliases in square brackets: SELECT top 5 * FROM mydb.myschema.mytable [mt] join mydb.myschema.myothertable [mot] on [mt].key = [mot].key I can see from the documentation that DBI:: dbQuoteIdentifier() is involved in this process, but I can't figure out how to tell it to use square …

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.

[jdlong]

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.

[jdlong]

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

SQL Server requires SET QUOTED_IDENTIFIER ON