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]
