12

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

RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
neotam
  • 271
  • 2
  • 3
  • 7

4 Answers4

13

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

Community
  • 1
RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
7

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.

altmannmarcelo
  • 1,167
  • 8
  • 26
  • 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 . – neotam May 02 '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 02 '13 at 20:35
3

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
user38380
  • 31
  • 1
0

I know this is an old question but the following might be useful for someone in future:

#!/bin/bash

echo "Started ..."
date
host=localhost
user=dbusername
password=dbpassword
database=databasename

time /usr/bin/mysql --host=$host --user=$user --password=$password --database=$database<<EOFMYSQL
DELIMITER $$
create procedure test_pro()
BEGIN
SELECT 'hello pro';
END$$
DELIMITER ;
SHOW WARNINGS;
SHOW COUNT(*) WARNINGS;
EOFMYSQL

echo "Finished "
date

Tested and working against Maria DB.

After it finished you can just call the procedure using:

call test_pro();

Hope that helps :)

justMe
  • 101