From: Jeremy Shaw <jeremy.shaw@li...> - 2004-03-16 05:22:29
One problem I ran into using haskelldb (both the old and new
versions), is how do you deal with auto_increment fields?
Once in a fit of insanity I figured out this little hack:
sqlnull = (Expr (ConstExpr "NULL"))
newprofile = ( userid = sqlnull
, login_name = constant "jeremy.shawNULL@..."
, cryptpassword = sqlnull
, realname= constant "boing"
, groupset= constant 0
, disabledtext= constant "dis"
, mybugslink=constant (1 :: Int)
, blessgroupset= constant 0
, emailflags=constant "no"
But then I was immediatly faced with a second problem -- how do I find
out what the autogenerated ID is ?
So I hacked this up:
mysqlInsertNewId connection table assoc
= do mysqlPrimCommand connection sql
res <- mysqlPrimQuery connection "select LAST_INSERT_ID();"
fields <- mysqlPrimFields res
return (getVal (MysqlValue (head (head fields))))
sql = show (ppInsert (toInsertNew table assoc))
A hack indeed! But, at the time I had to get things working fast, so I
didn't care ;) I should point out this this hack is actually fairly
safe. Note that the insert and the select LAST_INSERT_ID() are both
done on the same connection. As long as I hold that connection open,
LAST_INSERT_ID() will return the last autogenerated ID that was
inserted on that connection, even if records have been submitted in
the meantime on other connections.
I have only tried this with MySQL, so maybe its not a very portable
In any case, I am now faced with similar problems using the new
haskellDB. This time around, I thought it might be worthwhile to think
about the problem a bit longer :)