Solved

MySQL and stored procedures - last inserted identity

Posted on 2004-08-21
5
503 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Closed, 500 points refunded.
CetusMOD
Community Support Moderator
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
How would I write this SELECT statement? 6 46
MySQL going to sleep 4 62
Need help with a query 8 50
mysql to mysqli problems 4 35
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now