mrichmon
asked on
MySQL and stored procedures - last inserted identity
As we understand it stored procedures are not available in MySQL until version 5.
However version 5 is still alpha.
Has anyone been running version 5 and what were the results?
Also we keep getting the error:
ERROR [HY000] [MySQL][ODBC 3.51 Driver][mysqld-5.0.0-alpha -max-debug ]SELECT in a stored procedure must have INTO
The procedure looks like :
CREATE procedure sprtest()
BEGIN
SELECT * FROM databasename.mytable
END
We get the same error running from MySQL Query Browser
Ideally what we are trying to eventually do is to just insert a record and return the id of the record that was just inserted to use in other inserts/updates/methods/et c in the web page.
We also thought about just doing the equivalent of this in MS SQL :
INSERT INTO mytable (field1, field2) VALUES('test', 99); SELECT @@Identity
But this gives the following error in MySQL :
ERROR [42000] [MySQL][ODBC 3.51 Driver][mysqld-5.0.0-alpha -max-debug ]You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '; SELECT @@Identity' at line 1
I know that in MySQL it is not @@Identity, but is Last_Insert_ID(), but it really makes no difference. Even if I do the following :
INSERT INTO mytable (field1, field2) VALUES('test', 99); SELECT * FROM mytable
I get the error
ERROR [42000] [MySQL][ODBC 3.51 Driver][mysqld-5.0.0-alpha -max-debug ]You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '; SELECT * FROM mytable' at line 1
Any ideas on how to do something so simple as getting the last inserted value - whether via stored procedure - or simply a two-part command?
Thanks.
However version 5 is still alpha.
Has anyone been running version 5 and what were the results?
Also we keep getting the error:
ERROR [HY000] [MySQL][ODBC 3.51 Driver][mysqld-5.0.0-alpha
The procedure looks like :
CREATE procedure sprtest()
BEGIN
SELECT * FROM databasename.mytable
END
We get the same error running from MySQL Query Browser
Ideally what we are trying to eventually do is to just insert a record and return the id of the record that was just inserted to use in other inserts/updates/methods/et
We also thought about just doing the equivalent of this in MS SQL :
INSERT INTO mytable (field1, field2) VALUES('test', 99); SELECT @@Identity
But this gives the following error in MySQL :
ERROR [42000] [MySQL][ODBC 3.51 Driver][mysqld-5.0.0-alpha
I know that in MySQL it is not @@Identity, but is Last_Insert_ID(), but it really makes no difference. Even if I do the following :
INSERT INTO mytable (field1, field2) VALUES('test', 99); SELECT * FROM mytable
I get the error
ERROR [42000] [MySQL][ODBC 3.51 Driver][mysqld-5.0.0-alpha
Any ideas on how to do something so simple as getting the last inserted value - whether via stored procedure - or simply a two-part command?
Thanks.
ASKER
Yes but 3.52 is not available yet and I have heard that they are not even releasing 3.5.2, but going straight to 3.5.3
ASKER
Okay I found out that if you use the 3rd party ByteFX driver then it works.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
if you look at this link, you will see that binary mysql protocol for 4.1 databases and higher will only be available with mysql odbc driver 3.52 :
http://dev.mysql.com/downloads/connector/odbc/3.52.html