Haskell Unicode column name in ODBC

Question

I am trying to query some data from a MSSQL database through HDBC and ODBC. I have however run into a problem when trying to query data from a table with unicode in the column names.

Consider the following MWE:

mwe :: IConnection conn => conn -> IO [[SqlValue]]
mwe conn =
  do r <- quickQuery' conn
          "SELECT [Højde] FROM [Table]"
          []
     return r

When executing the above and passing it a connection object to the database i get the following error message:

*** Exception: SqlError {seState = "[\"42S22\",\"42000\"]", seNativeError = -1, seErrorMsg = "execute execute: [\"207: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid column name 'H\\195\\184jde'.\",\"8180: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared.\"]"}

The relevant part most likely being that H\\195\\184jde is not a valid column name.

My research has mostly led to results about unicode in the parameters to the query. I have tried to use bytestrings instead of normal strings, but since the argument for QuickQuery' is a string that did not help.

Perhaps it's a good idea to check the *UNICODE AND BYTESTRINGS* section in the [Database.HDBC.SqlValue](https://hackage.haskell.org/package/HDBC-2.4.0.3/docs/Database-HDBC-SqlValue.html) page. — Redu, Mar 10 '20 at 13:00
@Redu I think the problem there is that while the parameters are of type `SqlValue` the query itself is just of type `String`: `quickQuery' :: IConnection conn => conn -> String -> [SqlValue] -> IO [[SqlValue]] ` — simwir, Mar 10 '20 at 13:06
An idea. Perhaps you make `[Højde]` like `[?]` in the `String` argument and pass a `[toSql ("Højde" :: ByteString)]` as `SqlValue` argument instead of an empty list. — Redu, Mar 10 '20 at 14:02

score 0 · Answer 1 · answered Mar 11 '20 at 16:54

I don't have an MS SQL instance to test this, but the code in HDBC-odbc encodes the query using UTF-8. Meanwhile, this documentation suggests that for modern ODBC drivers, the character set used for queries depends on the process locale at the time the driver is initialized. If it's "C", which is the usual process default, then the driver will use the UTF-8 character set. However, if the process executes:

setlocale(LC_ALL,"")

before initializing the driver, and the current Windows locale is, say, English using the Latin-1 1252 code page, then the ODBC driver will expect Latin-1 encoded queries. Unfortunately for you, the GHC runtime does run setlocale(LC_ALL,""), so that's probably what's going wrong.

If you reset the locale to "C" at the start of "main", that may fix the issue:

{-# LANGUAGE ForeignFunctionInterface #-}

import Control.Monad
import Foreign.C
import Foreign.Ptr

foreign import ccall "locale.h setlocale" c_setlocale :: CInt -> CString -> IO CString

setCLocale :: IO ()
setCLocale = do
  rc <- withCString "C" $ c_setlocale 0
  when (rc == nullPtr) $ error "setCLocale failed"

main = do
  setCLocale
  print "whatever"

I'm not sure if this will cause other problems (e.g., with terminal input/output). If it does, you may be able to set the locale to "C" before initializing the driver and then reset it to "" right after.

That did not seem to do the trick. I tried both your code as well as the [setlocale](https://hackage.haskell.org/package/setlocale-1.0.0.9) hackage I found by searching setLocale. Using this package i was able to determine what my current locale string is: `LC_COLLATE=C;LC_CTYPE=Danish_Denmark.1252;LC_MONETARY=C;LC_NUMERIC=C;LC_TIME=C` — simwir, Mar 16 '20 at 08:03

Haskell Unicode column name in ODBC

1 Answers1