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/etc 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.
LVL 35
mrichmonAsked:
Who is Participating?
 
CetusMODCommented:
Closed, 500 points refunded.
CetusMOD
Community Support Moderator
0
 
psadacCommented:
i think that you can't submit two sql queries simultaneously with mysql odbc driver 3.51 even if you can do this with mysql 5.0.
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
0
 
mrichmonAuthor Commented:
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
0
 
mrichmonAuthor Commented:
Okay I found out that if you use the 3rd party ByteFX driver then it works.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.