File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes export just procedures from a mysql database using mysqldump Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "export just procedures from a mysql database using mysqldump" Watch "export just procedures from a mysql database using mysqldump" New topic
Author

export just procedures from a mysql database using mysqldump

James.HD Li
Greenhorn

Joined: May 11, 2009
Posts: 10
Guys

I'm looking for a way to dump all the procedures from a mysql database with mysqldump. Just procedures, no tables.

Currently, I can export all the procedures with --routine option, but I just can not figure out how to remove those 'table related' statement. If anyone knows, please help.

James
Peter Johnson
author
Bartender

Joined: May 14, 2008
Posts: 5823
    
    7

You would have better luck asking in the JDBC forum, since that is where database-related questions go. Moving.


JBoss In Action
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 38353
    
  23
Am I right in thinking that all procedures start with CREATE PROCEDURE and end with ; ? If you do an SQL dump, you get the SQL for the entire database in a plain text file. You can easily scan it for its text, and it shouldn't be hard to create a regular expression to find procedures. Finding the end of a procedure might be more awkward because you might have DELIMITER instructions so there are ; in the text. You might also have ; inside 'strings' with 'quote marks' like this: 'Campbell Ritchie should never be trusted; he makes mistakes all the time.' You can probably permit quotes and DELIMITER instructions in your regular expression. You never know, there might be one ready-made somewhere. If you are looking for one, try here.
James.HD Li
Greenhorn

Joined: May 11, 2009
Posts: 10
Peter, thanks for take this topic to a better place.

Campbell, thank you so much. This is quite a professional way and should be smart enough to resolve this problem.

But I just wander if there is a simple way like a command line that just dump all the procedures?

J
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 38353
    
  23
You're welcome. try the MySQL manual; if you find "mysqldump" in the index (here for MySQL5.0) or look for newer versions, there appears to be a routines option which you may find very helpful.
James.HD Li
Greenhorn

Joined: May 11, 2009
Posts: 10
I think I have too name a table after the database name and manually delete that 'create table script'
Thanks a lot for you reply.
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 38353
    
  23
You're welcome
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: export just procedures from a mysql database using mysqldump