Solved

MySQL and stored procedures - last inserted identity

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

707 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