aspose file tools*
The moose likes JDBC and the fly likes Can't create stored procedure in mysql Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Can Watch "Can New topic
Author

Can't create stored procedure in mysql

Volodymyr Levytskyi
Ranch Hand

Joined: Mar 29, 2012
Posts: 505
    
    1

Hello!

I use MySQl 5.1.21 connector. I run this procedure on eclipse and I always get the error :

My sql script looks as follows:

What is wrong with this sql script?

Thank you!

True person is moral, false is right!
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 39834
    
  28
Is the double dollar a permissible delimiter?
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18907
    
    8

Volodymyr Levytskyi wrote:I use MySQl 5.1.21 connector. I run this procedure on eclipse...


Can you clarify the part about Eclipse? I mean, how does Eclipse know how to run SQL scripts?
Volodymyr Levytskyi
Ranch Hand

Joined: Mar 29, 2012
Posts: 505
    
    1

Thanks for replies very much!

In eclipse I created new 'driver definition' by providing mysql-connector-java-5.5.27-win32.jar.
Then I created new connection in Data source explorer that points to my database.

The truth is I've just created my stored procedure successfully in command line. Then I tried to do this in eclipse and It does not work.
So the final working version is :

I also created new simple procedure with $$ delimiter and it works as well.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

I don't use MySQL myself, so I might be wrong, but I suspect that the DELIMITER command is handled by mysql client and not by the database itself. The client parses the SQL script into individual SQL commands and servers them to the database, and the DELIMITER command just overrides the default delimiter. (A similar mechanism - and corresponding class of problems - exists in Oracle, which I'm familiar with.) There might be other client-only commands; see also this discussion.

You need to split your SQL script into individual commands and execute them one after another using JDBC's Statement. There should be just two statements: the DROP PROCEDURE and the CREATE PROCEDURE. I believe that the USE statement should be incorporated into your connect string instead, but again I can be wrong on this.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18907
    
    8

Yes, I had a look at the MySQL documentation and the only place I read about the DELIMITER command was in the context of being entered at the MySQL command line, or what you call the client.

I do use MySQL, but in real life that translates into I created a simple MySQL database about six years ago and that was all.
 
jQuery in Action, 2nd edition
 
subject: Can't create stored procedure in mysql