CODEWITHSUNDEEP
mysqlstored-procedures
Adam Lenda
Adam Lenda

Reputation: 800

Calling MySQL Stored Procedure with escaped database name

I am using MySQL: 5.1.69

I have created stored procedures and called them many times. In this case, I appear to have hit a snag related to escape characters in the schema name.

our MySQL database / schema is named like this: www.company.com

Due to this, we must always escape the database name like this: `www.company.com`.table_name

I have created a procedure from within MySQLWorkbench

CREATE PROCEDURE usp_backfill_data
(p_var1 INT(11)
,p_var2 DATETIME)
BEGIN
/*do lots of work here*/
END

And from within MySQL Workbench I am able to call the procedure using:

use `www.company.com`;
CALL usp_backfill_data(5000, '2012-01-01');

Under these conditions the procedure works exactly as expected.

However, when I try to call the procedure from the command line:

%> mysql -uuser -ppassword -Dwww.company.com -e "CALL usp_backfill_data(5000, '2012-01-01');"

or when I try

%> mysql -uuser -ppassword -e "CALL \`www.company.com\`.usp_backfill_data(5000, '2012-01-01');"

or when I log into the mysql command line and use:

mysql> use `www.company.com`;
mysql> CALL usp_backfill_data(5000, '2012-01-01');

I always get the following error:

ERROR 1305 (42000) at line 1: PROCEDURE www.company.com.usp_backfill_data does not exist

I am hoping that there is something super obvious that I'm overlooking here.

Thank you very much for your time

Upvotes: 0

Views: 631

Answers (1)

peterm
peterm

Reputation: 92785

To escape database name in command line use double quotes

$ mysql -uuser -ppassword -D"www.company.com" -e "CALL usp_backfill_data(5000, '2012-01-01');"
                            ^               ^

Other two methods

$ mysql -uuser -ppassword -e "CALL \`www.company.com\`.usp_backfill_data(5000, '2012-01-01');"

and

$ mysql -uuser -ppassword
mysql> USE `www.company.com`;
Database changed
mysql> CALL usp_backfill_data(5000, '2012-01-01');

work for me just fine

Upvotes: 1

Related Questions