[SOLVED] Sourcing a sql file in a bash script throws errors

sysmicuser · 11-19-2016, 06:39 AM

Hello Guys,

I'm having a sql script which is basically a stored procedure which I am sourcing in a bash script and its throwing errors. Any idea on how to resolve them ?

sql script is below:

Code:

use mydb;
set autocommit=0;
drop procedure delete_table_incrementally;
delimiter //
create procedure delete_table_incrementally()
modifies sql data
begin
select count(*) FROM mytable where mycondition='ERROR-5000';
DELETE FROM mytable where mycondition='ERROR-5000' order by id limit 3;
commit;
select count(*) FROM mytable where mycondition='ERROR-5000';
end;
//
delimiter ;
call delete_table_incrementally();

And bash script below:

Code:

#!/bin/bash

set -x
source /home/jim/my_script.sql

Output:

Code:

use: command not found
line 3: drop: command not found
++ delimiter //
line 4: delimiter: command not found
line 5: syntax error near unexpected token `('
line 5: `create procedure delete_table_incrementally()'
line 6: unexpected EOF while looking for matching `''
line 29: syntax error: unexpected end of file

Any pointers would be sincerely appreciated.

grail · 11-19-2016, 06:50 AM

I am not sure why you thought that would work seeing as none of the commands in my_script.sql are known by bash which is what is required if a file is sourced??

You need to pass the information in the file to something that can understand the commands written.

Turbocapitalist · 11-19-2016, 06:53 AM

Specifically you'll have to pass the SQL statements to an SQL client of some kind. Which database are you using, MySQL or Mariadb or Postgresql?

sysmicuser · 11-19-2016, 07:05 AM

Guys,

Scratch that, I now the issue and fix. The procedure as long as its compiled and stored on DB I can directly use with mysql client command line options. So all good and thanks for your help !

Cheers

grail · 11-19-2016, 07:18 AM

I am not sure I follow? Before marking as SOLVED maybe you can explain so others may benefit from your knowledge, because as it stands bash will never understand that file irrelevant of where it is stored.

sundialsvcs · 11-19-2016, 07:50 AM

I suspect that what was going wrong here was that you were attempting to present SQL commands to Bash, which of course had no idea what you were talking about.

But, yes, stored procedures are ... well ... stored! They're compiled by the SQL engine and then stored in a system database ready to be executed via an SQL command.

Also: remember that you can use any programming language to "write a shell script," thanks to the #!command_processor "shebang" syntax. Bash looks for that line and, if it finds it, invokes the specified command-processor to do the work. So, don't waste time and effort trying to make Bash do stuff that it was never designed to do, given that you have literally dozens of alternatives which do support SQL interaction (without resorting to external commands to do it). Perl, PHP, Ruby, Java. Etcetera. One of the greatest strengths of the Unix/Linux environment is just how many ways there are to "do it."

JeremyBoden · 11-19-2016, 10:12 AM

sqlite3 allows the entry of SQL commands from the terminal.
I would think that there's a good chance that it could be used in a BASH script.

sysmicuser · 11-21-2016, 05:36 AM

Hi the solution was commented in my comment itself.

mysql> source /pathe/to/sql file
This would allow it to run the sql.

mysql -uuser -ppasssword mydb --execute='select count(*) from mytable'

The above is the answer to invoke from shell/bash

jpollard · 11-21-2016, 05:55 AM

All that does is invoke the "mysql" program with given parameters (and giving the password that way advertises it to anyone logged in that uses a "ps -ef", so it is not secure).

grail · 11-21-2016, 07:34 AM

Well colour me still confused as I am not sure how the solution provided has anything to do with the original problem. That being said, if you have a solution, please remember to mark the question as SOLVED