odbc sqlserver notes

ODBC SQL Server notes

Summary for users

Using #:strict-parameter-types? #t seems to work.

Notes to self

Fetching NUMERIC/DECIMAL fields works using SQL_ARD_TYPE

FIXME: add quirk to send times with fractional times with SQL_C_SS_TIME2?

---

Setting up a testing env

Setup

• install SQL Server Express 2017
  • picked Basic, no customization
    • Server=localhost\SQLEXPRESS;Database=master;Trusted_Connection=True;
• install SSMS 17.5
• turn on protocols

ODBC configuration

• Driver = ODBC Driver 13 for SQL Server or Driver = SQL Server Native Client 11.0
• Server = localhost\SQLEXPRESS

Issue: Several concurrent tests fail with Connection is busy with results for another command (HY000)

• need to set MARS_Connection = Yes option (can't use gui, must edit registry entry!)
• refs: https://stackoverflow.com/questions/9017264/why-only-some-users-get-the-error-connection-is-busy-with-results-for-another, https://github.com/catherinedevlin/ipython-sql/issues/54, https://serverfault.com/questions/302169/odbc-sql-server-how-do-i-turn-on-multiple-active-result-setsmars-for-a-syst

Issues

Issue: time roundtrip causes error: "unsupported type (typeid: -154)"

• extension: https://docs.microsoft.com/en-us/sql/relational-databases/native-client-odbc-date-time/data-type-support-for-odbc-date-and-time-improvements
• added SQL_SS_TIME2 support

Issue: Multi-statement string doesn't raise error.

• workaround: skip test

Issue: tinyint roundtrip -1 causes overflow error (SQLSTATE 22003)

• workaround: test as if smallint

Issue: timestamp test causes error: "Explicit conversion from data type datatime2 to timestamp is not allowed. (SQLSTATE: 22018)"

• workaround: test as if datetime