Mysql – How to create a thesql stored procedure through linux terminal

MySQLstored-procedures

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

Best Answer

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 !!!

Related Question