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? Taking a closer look at the HDBC-ODBC implementation it would appear that data will always be returned as SqlByteString, or as SqlNull. 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. Tim
On Wed, Apr 7, 2010 at 9:08 PM, Tim Docker
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
On Wed, Apr 7, 2010 at 9:45 PM, Jason Dagit
darcs get --lazy http://darcs.haskell.org/takusen
Oops. That's a dead link, try this instead: darcs get --lazy http://code.haskell.org/takusen/ http://code.haskell.org/takusen/
Jason: Thanks for the reply.
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
Yes. I can use fromSql to convert the result back to an appropriate numerical type. But internally the numeric data has still been converted to an intermediate string representation. I'm wondering if this is intentional, and whether it matters. Tim
Tim Docker wrote:
Jason:
Thanks for the reply.
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
Yes. I can use fromSql to convert the result back to an appropriate numerical type. But internally the numeric data has still been converted to an intermediate string representation. I'm wondering if this is intentional, and whether it matters.
Yes and no, in that order. A ByteString is a pretty universal holder for various types of data. As someone else pointed out, at query time, we don't really have access to what type you will eventually want to use it as, and supporting the vast number of different ways to get things out of databases -- with the corresponding complexities of how a database driver can convert between them -- was just not worth it. It is generally assumed that the end user will be using fromSql anyhow, so it is not particularly relevant if it's a SqlByteString or a SqlInteger internally. -- John
John Goerzen wrote:
Tim Docker wrote:
Yes. I can use fromSql to convert the result back to an appropriate numerical type. But internally the numeric data has still been converted to an intermediate string representation. I'm wondering if this is intentional, and whether it matters.
Yes and no, in that order.
A ByteString is a pretty universal holder for various types of data. As someone else pointed out, at query time, we don't really have access to what type you will eventually want to use it as, and supporting the vast number of different ways to get things out of databases -- with the corresponding complexities of how a database driver can convert between them -- was just not worth it.
It is generally assumed that the end user will be using fromSql anyhow, so it is not particularly relevant if it's a SqlByteString or a SqlInteger internally.
I was wondering whether going via an intermediate string might cause issues relating to loss of precision in floating point values, or possible date conversion problems if locales were not set correctly. I'm seeing problems with date conversion, and wonder if this is related. Depending on my intended result type, I see conversion errors, in particular the 3rd example below seem to be related to formatting of the intermediate string: *Main> fmap (fromSql.head.head) $ quickQuery c "select getdate()" [] :: IO Data.Time.Clock.UTCTime 2010-04-09 09:59:20.67 UTC *Main> fmap (fromSql.head.head) $ quickQuery c "select getdate()" [] :: IO Data.Time.LocalTime 2010-04-09 09:59:26.313 *Main> fmap (fromSql.head.head) $ quickQuery c "select getdate()" [] :: IO System.Time.CalendarTime *** Exception: Convertible: error converting source data SqlString "2010-04-09 09:59:37.460" of type SqlValue to type Data.Time.LocalTime.LocalTime.ZonedTime: Cannot parse using default format string "%Y-%m-%d %T%Q %z" *Main> fmap (fromSql.head.head) $ quickQuery c "select getdate()" [] :: IO System.Time.ClockTime *** Exception: Convertible: error converting source data SqlString "2010-04-09 09:59:49.940" of type SqlValue to type Integer: Cannot read source value as dest type Thanks, Tim
Tim Docker wrote:
*Main> fmap (fromSql.head.head) $ quickQuery c "select getdate()" [] :: IO Data.Time.Clock.UTCTime 2010-04-09 09:59:20.67 UTC *Main> fmap (fromSql.head.head) $ quickQuery c "select getdate()" [] :: IO Data.Time.LocalTime 2010-04-09 09:59:26.313 *Main> fmap (fromSql.head.head) $ quickQuery c "select getdate()" [] :: IO System.Time.CalendarTime *** Exception: Convertible: error converting source data SqlString "2010-04-09 09:59:37.460" of type SqlValue to type
That is to be expected. You are converting data from the underlying database that does not contain timezone information. Thus it is not possible to populate ctTZ in CalendarTime.
Data.Time.LocalTime.LocalTime.ZonedTime: Cannot parse using default format string "%Y-%m-%d %T%Q %z" *Main> fmap (fromSql.head.head) $ quickQuery c "select getdate()" [] :: IO System.Time.ClockTime *** Exception: Convertible: error converting source data SqlString "2010-04-09 09:59:49.940" of type SqlValue to type Integer: Cannot read source value as dest type
And here you don't have something in seconds-since-epoch format. What you have is an unzoned date and time. Therefore it makes sense that you can convert it to a LocalTime. It does not have enough information to make it into a CalendarTime because it lacks a zone. It also isn't in seconds-since-epoch format, which is what a ClockTime is. The conversions to UTCTime and LocalTime work because they do not require a timezone to be present in the input data. -- John
participants (3)
-
Jason Dagit -
John Goerzen -
Tim Docker