Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Can't create stored procedure in mysql

 
Volodymyr Lysenko
Ranch Hand
Posts: 511
1
Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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!
 
Campbell Ritchie
Sheriff
Pie
Posts: 49447
62
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Is the double dollar a permissible delimiter?
 
Paul Clapham
Sheriff
Posts: 21142
32
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 Lysenko
Ranch Hand
Posts: 511
1
Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Posts: 21142
32
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic