Racket, MS SQL, ODBC and FreeTDS

As I have written before, Racket is used for implementing most of our tooling and helper applications both for the Cryptoucan™ development and for other back-end tasks as well. You can rightfully guess that most of the bookkeeping programs need to access various databases and although accessing databases is super-convenient in Racket, with some databases on weird platforms, strange problems may arise. Read on to see one of the more interesting and also to see how to resolve such issues.

Racket[1] sports an excellent database-connectivity abstraction layer db[2]. As one of our applications needs to access a MS SQL[3] server using the TDS[4] protocol, we happily decided to use the integrated ODBC[5] driver which wraps the TDS line protocol conveniently.

On UNIX-like platforms this means installing the UnixODBC[6] and FreeTDS[7] runtime and libraries. For simple queries, everything worked like a charm. But at one point we started hitting strange TDS protocol errors of malformed frames and data types.

First suspects included outdated versions of these components in Debian[8] stable distribution. And although it was not the primary cause, it turned out that some other bugs that affect our workflow lurk in those versions and therefore we were forced to update TDSODBC link to version 1.1.6 or newer.

But the real issue lied deep inside the FreeTDS implementation of TDS line protocol, which is apparently taken for granted right now as quite some number of software in the wild now rely on the erroneous behavior. The problem lies in transferring DECIMAL and BIT values. You can hit the bug with a simple table like:

CREATE TABLE test(
  amount DECIMAL(10,2),
  active BIT
);

Yes, the BIT type is a MS SQL-specific thing. But as we cannot change the schema of a production database to which we only connect to, we have to live with that. If you try to do a simple SELECT and get results over the TDS connection, you may be quickly screwed.

But luckily, this can be solved easily. Picture a simple SQL SELECT statement:

SELECT * FROM test;

This triggers an error. But if we use the CAST function and convert the values to their VARCHAR representations, we can get the values:

SELECT CAST(amount AS VARCHAR, active AS VARCHAR);

Now this looks sub-optimal - as one might think that the amount gets converted to a floating-point representation at some point. Not with Racket! If you play your cards right, you get the exact same DECIMAL and BOOLEAN (not on the MS SQL side) easily:

#lang racket
(require db)
(define conn (odbc-connect #:dsn "DSN"))
(define res
  (query
   conn
   "SELECT CAST(amount AS VARCHAR, active AS VARCHAR)"))
(parameterize ((read-decimal-as-inexact #f))
  (for ((row (rows-result-rows res)))
    (displayln
     (list
      (string->number (vector-ref row 0))
      (string=? (vector-ref row 1) "1")))))

Yes, by using parameterize[9] for the read-decimal-as-inexact[10] and setting it to #f, the decimal numbers get converted to rational numbers with no precision loss. And although the Boolean conversion looks awful - it works perfectly.

Hope you liked our little venture into the strange land of MS SQL and come back next week for more interesting stuff!

References

1. https://racket-lang.org/

2. https://docs.racket-lang.org/db/index.html

3. Wikipedia contributors. (2020, February 25). Microsoft SQL Server. In Wikipedia, The Free Encyclopedia. Retrieved 20:05, March 5, 2020, from https://en.wikipedia.org/w/index.php?title=Microsoft_SQL_Server&oldid=942517419

4. Wikipedia contributors. (2019, December 20). Tabular Data Stream. In Wikipedia, The Free Encyclopedia. Retrieved 20:06, March 5, 2020, from https://en.wikipedia.org/w/index.php?title=Tabular_Data_Stream&oldid=931685667

5. Wikipedia contributors. (2020, February 11). Open Database Connectivity. In Wikipedia, The Free Encyclopedia. Retrieved 20:06, March 5, 2020, from https://en.wikipedia.org/w/index.php?title=Open_Database_Connectivity&oldid=940281915

6. Wikipedia contributors. (2020, February 11). Open Database Connectivity. In Wikipedia, The Free Encyclopedia. Retrieved 20:06, March 5, 2020, from https://en.wikipedia.org/w/index.php?title=Open_Database_Connectivity&oldid=940281915

7. Wikipedia contributors. (2018, December 23). FreeTDS. In Wikipedia, The Free Encyclopedia. Retrieved 20:07, March 5, 2020, from https://en.wikipedia.org/w/index.php?title=FreeTDS&oldid=875116812

8. Wikipedia contributors. (2020, February 27). Debian. In Wikipedia, The Free Encyclopedia. Retrieved 20:07, March 5, 2020, from https://en.wikipedia.org/w/index.php?title=Debian&oldid=942832799

9. https://docs.racket-lang.org/reference/parameters.html

10. https://docs.racket-lang.org/reference/Reading.html