Solved

MySQL and stored procedures - last inserted identity

Posted on 2004-08-21
5
510 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
  • 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
mySql Syntax 7 53
MySQL Select latest stock count records 11 69
Help With Query That Causes "WARNING: MySQL server has gone away" Error 3 29
Insert values are dynamic 11 41
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…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

777 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