[Haskelldb-users] auto_increment fields

[Jeremy S. <jer...@li...>]

Hello,

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 "jer...@li..."
	     , 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))))
	where
	  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.

See:
http://www.mysql.com/doc/en/Getting_unique_ID.html

I have only tried this with MySQL, so maybe its not a very portable
solution.

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 :)

Jeremy Shaw.