Solved

MySQL and stored procedures - last inserted identity

Posted on 2004-08-21
5
513 Views
Last Modified: 2008-02-26
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.
0
Comment
Question by:mrichmon
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
5 Comments
 
LVL 14

Expert Comment

by:psadac
ID: 11864160
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
 
LVL 35

Author Comment

by:mrichmon
ID: 11864864
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
 
LVL 35

Author Comment

by:mrichmon
ID: 11866216
Okay I found out that if you use the 3rd party ByteFX driver then it works.
0
 

Accepted Solution

by:
CetusMOD earned 0 total points
ID: 11917118
Closed, 500 points refunded.
CetusMOD
Community Support Moderator
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question