Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In mysql console we will use the delimiter command to change the delimiter and it is useful to define the procedures. I understood the command delimiter for only the mysql client(client side command).

but I am using the mysql client which does not have the command delimiter like dbslayer, on this kind of clients how can I define the procedures.

for now consider:

create procedure test_pro()
begin
select 'hello pro';
end

I tried the following :

mysql -u root -pmypass  test < proc_file

where proc_file contains the above procedure;

but that is giving me the following error:

ERROR 1064 (42000) at line 1: 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 '' at line 3

Event I tried the following

 create procedure test_pro()
    begin
    select 'hello pro';
    end;

(added the trailing semicolon) but am getting the same error .

Same I am experiencing with the dbslayer , if I am able to define the above procedure through terminal I think I should be able to do that through the dbslayer

share|improve this question

3 Answers 3

There is no way around it. You must use the DELIMITER command. Why ?

If you ever perform a mysqldump of the stored procedures, each stored procedure begins with

DELIMITER ;;

and ends with

DELIMITER ;

Here is a post where I mentioned this before : DROP PROCEDURE IF EXISTS not included in mysqldump

Try dumping one stored procedure with mysqldump and see for yourself

I also wrote some code to do this:

As for the answer posted by @altmannmarcelo, it directly answers your question (+1 for his answer). Otherwise, mysqldumps could never restore stored procedures.

There are two things you can do to accommodate a new DELIMITER:

TRY THIS #1

Give the delimiter on the command line itself

mysql -u root -pmypass --delimiter="//" test < myproc.sql

It is a command line option for the mysql client program

[root@****]# mysql --help | grep -i delimiter
  --delimiter=name    Delimiter to be used.
delimiter                         ;

TRY THIS #2

You can write the code into a text file and execute against the text file as @altmannmarcelo suggested

echo "DELIMITER //" > myproc.sql
echo "create procedure test_pro()" >> myproc.sql
echo "begin" >> myproc.sql
echo "select 'hello pro';" >> myproc.sql
echo "end" >> myproc.sql
echo "//" >> myproc.sql
mysql -u root -pmypass  test < myproc.sql

GIVE IT A TRY !!!

share|improve this answer

You need to change the delimiter to create a procedure, otherwise MySQL will try to commit your query on select 'hello pro';

Change your procedure to:

DELIMITER //
create procedure test_pro()
begin
select 'hello pro';
end
//

From MySQL Documentation:

If you use the mysql client program to define a stored program containing semicolon characters, a problem arises. By default, mysql itself recognizes the semicolon as a statement delimiter, so you must redefine the delimiter temporarily to cause mysql to pass the entire stored program definition to the server.

share|improve this answer
    
I am aware of this delimiter in mysql console. I am looking for different thing. I have asked that how to create a procedure without logging into the mysql console . If we perform like mysql -u root -ppass db -e "select somecolumn from testtable" if we perform the above command on linux terminal we will get the result for the query specified after -e . it will disconnect after performing query , The fact is in that query the delimiter // will not work . –  usernaveen May 2 '13 at 16:22
    
As @RolandoMySQLDBA says(+1), you can either save the procedure to a file with DELIMITER or change it passing the argument --delimiter on mysql command line. –  altmannmarcelo May 2 '13 at 20:35

This worked for me:

Contents of mysqlfile.sql

USE `dbschemaname`;
DROP procedure IF EXISTS `myprocname`;

DELIMITER $$
USE `dbschemaname`$$
CREATE DEFINER=`myusername`@`%` PROCEDURE `myprocname`(IN myvar VARCHAR(3500))
BEGIN
     DECLARE my_id INT;

     SELECT id INTO veh_id FROM mytable WHERE something = myvar;

END$$

DELIMITER ;

and on the commandline:

$ mysql -u user -pmypass dbschemaname < mysqlfile.sql
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.