[Haskell-cafe] HDBC-ODBC and SqlValues

[Jason Dagit]

On Wed, Apr 7, 2010 at 9:08 PM, Tim Docker <twd2 at 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:
http://msdn.microsoft.com/en-us/library/ms709280(v=VS.85).aspx

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:
http://software.complete.org/static/hdbc/doc/Database-HDBC.html#v%3AfromSql

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:
  darcs get --lazy http://darcs.haskell.org/takusen

I hope that helps,
Jason
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://www.haskell.org/pipermail/haskell-cafe/attachments/20100408/cf5a8323/attachment.html