HDBC-mysql is an HDBC driver for the MySQL database. It's available via Hackage, so the easiest way to install it is with Cabal:
cabal install HDBC-mysql
It is licensed under LGPL2*, and you are free to examine the source in the darcs repository:
darcs get http://www.maubi.net/~waterson/REPO/HDBC-mysql
(Note that this repository is a darcs 2.0 repository, and you can't pull it with darcs 1.x.)
It is a "native" HDBC driver for MySQL that makes use of libmysqlclient to communicate with a MySQL server. By way of synopsis:
import Control.Monad
import Database.HDBC
import Database.HDBC.MySQL
main = do conn <- connectMySQL defaultMySQLConnectInfo {
mysqlHost = "db1.example.com",
mysqlUser = "scott",
mysqlPassword = "tiger"
}
rows <- quickQuery' conn "SELECT 1 + 1" []
forM_ rows $ \row -> putStrLn $ show row
At the moment, please consider this to be "alpha" software. As far as I can tell, it works. There are some limitations that you should be aware of.
Compatibility. This works with MySQL server and client libraries 5.0.75. I haven't tried with 4.x nor 5.1. I suspect that using a server version less than 4.1 won't work, due to lack of support for prepared statements.
Transactions. Out of the box, MySQL probably uses MyISAM tables to store its data, and MyISAM tables don't support transactions. Yet, I'm going to blindly respond yes if you ask whether the driver itself supports transactions, and assume that you know enough to use InnoDB tables in the database if you want to make use of HDBC's transactional support.
That said, MySQL issues a warning if you try to ROLLBACK a transaction that included tables that don't support transactions. I could detect this, and throw an exception when it happens. This differs from the "you're using it wrong" behavior of the MySQL ODBC driver, and it's not completely clear to me that it's better.
Statements. The MySQL statement API only allows one statement to be "active" at a time (e.g., see this post). This means that you must consume all the data from a query, or explicitly finish the statement before you issue another statement on the same connection. Failure to do so will result in a "commands out of sync" error.
As an extreme example, this code won't work, because quickQuery produces its results lazily:
as <- quickQuery conn "SELECT a FROM foo" []
bs <- quickQuery conn "SELECT b FROM foo" []
forM_ (zip as bs) $ \(a, b) ->
putStrLn $ "a=" ++ (show a) ++ ", b=" ++ (show b)
On the other hand, using quickQuery' will work, because it forces the entire result set to be produced immediately.
Time Zones. MySQL DATETIME and TIMESTAMP columns have no time zone information, because they just don't. Instead, the database has a default time zone, and each connection can change the time zone to suit its needs.
At the moment, I'm blindly converting these data types to SqlEpochTime values, assuming the times are UTC. This is all fine if you're actually running your server in UTC, but it will probably be confusing if you're not.
In the future, I think it would probably make sense to detect the connection's time zone setting, and to interpret the time zone using that instead.
Non-ASCII Text. I have not tested this with non-ASCII data. I don't pay any attention to the connection character set. I use withCString, which doesn't pay any attention, either. It may be that this total lack of attention results in things serendipitously working if the MySQL connection defaults to using UTF-8 as the encoding.
Schema Metadata. The statement and table metadata could stand to be improved a bit. In particular, it would be nice if describeTable foo and describeResults on SELECT * FROM foo returned the same thing. (They're sorta close, I guess...)
BLOB and TEXT metadata. I'm not sure that I can tell the difference between a MySQL TEXT and a MySQL BLOB column. If you ask about the metadata of either, I'll tell you it's a SqlBinaryT.
Thread-safety could be an issue. In the driver code, there's definitely a race condition between prepare and disconnect, for example. I haven't even looked at thread-safety issues for the MySQL driver. I'm not sure if I should worry about it, or if we assume that's going to be dealt with at a higher level.
Embedded nulls. I need to verify that embedded null characters on bound parameter strings work correctly. I'm using withCString, when I probably should be using withCStringLen.
Efficiency with BLOBs. It probably makes sense to marshal to the SqlByteString type when retrieving BLOB data.
As time permits, I'll attempt to address each of these shortcomings, or any others that you may find. Please feel free to contact me if you have any questions, suggestions, or discover problems!
* It's licensed under LGPL2 for no other reason than this is the same license that is used by libmysqlclient. If you think you need to use another license, I'm really not particular: just ask.