On Wed, Apr 7, 2010 at 9:08 PM, Tim Docker
<twd2@dockerz.net> wrote:
I'm experimenting with haskell and relational databases. I have
successfully coupled unixodbc + freetds + hdbc-odbc, and can make
trivial queries. However, I'm surprised at the result types:
$ ghci
GHCi, version 6.10.3: http://www.haskell.org/ghc/ :? for help
Loading package ghc-prim ... linking ... done.
Loading package integer ... linking ... done.
Loading package base ... linking ... done.
Prelude> c <- Database.HDBC.ODBC.connectODBC
"DSN=xxxxx;UID=xxxxx;PWD=xxxx"
Loading package syb ... linking ... done.
Loading package base-3.0.3.1 ... linking ... done.
Loading package array-0.2.0.0 ... linking ... done.
Loading package containers-0.2.0.1 ... linking ... done.
Loading package bytestring-0.9.1.4 ... linking ... done.
Loading package old-locale-1.0.0.1 ... linking ... done.
Loading package old-time-1.0.0.2 ... linking ... done.
Loading package mtl-1.1.0.2 ... linking ... done.
Loading package utf8-string-0.3.5 ... linking ... done.
Loading package time-1.1.4 ... linking ... done.
Loading package convertible-1.0.9 ... linking ... done.
Loading package HDBC-2.2.4 ... linking ... done.
Loading package HDBC-odbc-2.2.3.0 ... linking ... done.
Prelude> Database.HDBC.quickQuery c "select 1+3" []
[[SqlByteString "4"]]
Prelude>
Why do I see an SqlByteString returned, rather than a numeric type?
The query consists of one column and one row of an integer whose value is 4. But, ODBC lets you request your data as some other type if you like during the call to SQLFetch. You set this up during SQLBindCol. It's up to the database to support (or not) the conversion from the type it has to the type you requested. I think pretty much every database supports converting its types to string. What I'm getting at, is that yes the database should be holding an int for you to fetch, but the code is requesting it as a string.
You can read more about how it works here:
Furthermore, your specific case is not giving the database any hint as to what type you're expecting. There is no table schema to go by, no cast in the query, etc. You might try your example using the odbc interactive sql prompt 'isql' and see what type it gets from your database.
Taking a closer look at the HDBC-ODBC implementation it would appear
that data will always be returned as SqlByteString, or as SqlNull.
I can't really comment here because I'm not familiar with the internals of HDBC, but I'm familiar with the internals of Takusen. In Takusen, the type of the function you use to fetch data determines if there is a conversion during the SQLFetch. This is setup with some type classes.
Is this the intended behaviour, or just a sign of an incomplete
implementation of the ODBC driver? It would certainly seem possible for
the ODBC driver to return more specific types.
I suspect the solution is to correctly tell Haskell what type you expect and then hopefully HDBC will do the conversion. For example, using fromSql:
Alternatively, you could try Takusen. The types are a bit harder to understand at first compared to HDBC, but it seems to really work well once you get the hang of it. If you're using the ODBC backend you'll need the darcs version of Takusen. The version on hackage has at least a few ODBC bugs that are show stoppers, but are corrected in the darcs version:
I hope that helps,
Jason