Oh. If only I'd tried ONE more thing before mailing that out!
If anyone does have the same problem, the vital (missing) piece of information is that I was using MySQLCC. It seems to have problems with SPs unless you open a new query window after changing the contents of a procedure.. Thanks, Matt > -----Original Message----- > From: Matt Chatterley [mailto:[EMAIL PROTECTED] > Sent: 23 April 2004 02:08 > To: 'MySQL List' > Subject: LAST_INSERT_ID() and Stored Procs > > Hi all. > > Another hiccup along the happy road with MySQL 5.0! > > The last bit of a stored procedure I have just put together does this: > > -- insert cluster row > INSERT INTO clusters (Name) VALUES (sName); > SELECT LAST_INSERT_ID() INTO iNewClusterID; > > -- insert map row > INSERT INTO map (X, Y) VALUES (iX,iY); > SELECT LAST_INSERT_ID() INTO iNewMapID; > > -- insert map_clusters row > INSERT INTO map_clusters (MapID, ClusterID) VALUES (iNewMapID, > iNewClusterID); > > The last table mentioned, map_clusters has an FK on either column - each > pointing to one of the other two tables. The procedure always fails on > this > insert, citing that there has been an FK violation. > > I've returned the values of iNewClusterID and iNewMapID out as parameters, > and they always seem to be 0. > > However, I tried this: > > Create procedure id_test (out id int) > Begin > Select last_insert_id() into id; > End > > And this correctly returns the last insert_id for the current connection. > > Most puzzling - I saw a closed bug from March on mysql.com which would > have > explained this, however, then, the above short procedure would have failed > as well! > > Has anyone out there run into similar troubles? > > > Cheers, > > Matt > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]