LinuxQuestions.org
Help answer threads with 0 replies.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices


Reply
  Search this Thread
Old 11-19-2016, 07:39 AM   #1
sysmicuser
Member
 
Registered: Mar 2010
Posts: 332

Rep: Reputation: 0
Unhappy Sourcing a sql file in a bash script throws errors


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.
 
Old 11-19-2016, 07:50 AM   #2
grail
LQ Guru
 
Registered: Sep 2009
Location: Perth
Distribution: Manjaro
Posts: 9,245

Rep: Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684
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.
 
Old 11-19-2016, 07:53 AM   #3
Turbocapitalist
Member
 
Registered: Apr 2005
Distribution: Ubuntu, Devuan, OpenBSD
Posts: 956
Blog Entries: 3

Rep: Reputation: 391Reputation: 391Reputation: 391Reputation: 391
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?
 
Old 11-19-2016, 08:05 AM   #4
sysmicuser
Member
 
Registered: Mar 2010
Posts: 332

Original Poster
Rep: Reputation: 0
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
 
Old 11-19-2016, 08:18 AM   #5
grail
LQ Guru
 
Registered: Sep 2009
Location: Perth
Distribution: Manjaro
Posts: 9,245

Rep: Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684
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.
 
Old 11-19-2016, 08:50 AM   #6
sundialsvcs
LQ Guru
 
Registered: Feb 2004
Location: SE Tennessee, USA
Distribution: Gentoo, LFS
Posts: 7,151

Rep: Reputation: 2203Reputation: 2203Reputation: 2203Reputation: 2203Reputation: 2203Reputation: 2203Reputation: 2203Reputation: 2203Reputation: 2203Reputation: 2203Reputation: 2203
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."

Last edited by sundialsvcs; 11-19-2016 at 08:51 AM.
 
Old 11-19-2016, 11:12 AM   #7
JeremyBoden
Member
 
Registered: Nov 2011
Posts: 938

Rep: Reputation: 174Reputation: 174
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.
 
Old 11-21-2016, 06:36 AM   #8
sysmicuser
Member
 
Registered: Mar 2010
Posts: 332

Original Poster
Rep: Reputation: 0
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
 
Old 11-21-2016, 06:55 AM   #9
jpollard
Senior Member
 
Registered: Dec 2012
Location: Washington DC area
Distribution: Fedora, CentOS, Slackware
Posts: 4,601

Rep: Reputation: 1241Reputation: 1241Reputation: 1241Reputation: 1241Reputation: 1241Reputation: 1241Reputation: 1241Reputation: 1241Reputation: 1241
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).
 
Old 11-21-2016, 08:34 AM   #10
grail
LQ Guru
 
Registered: Sep 2009
Location: Perth
Distribution: Manjaro
Posts: 9,245

Rep: Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684
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
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
Need help with sourcing a file inside a bash script. sysmicuser Linux - Newbie 4 06-02-2016 10:12 AM
Sourcing a file in a script TenaciousRock Linux - Software 9 10-08-2013 08:47 PM
Sourcing Environment File in Shell Script linux098 Linux - Newbie 6 10-22-2012 03:38 AM
Bash Shell Script - Check SQL file for corruption RML1992 Linux - General 3 09-14-2012 01:47 AM
Iterate over SQL results in bash script. Is there a better way to do this? word_virus Programming 4 11-09-2008 11:15 AM


All times are GMT -5. The time now is 08:12 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration