0

I have one sql file on which I'm doing cat and assigning to a variable say "SQL". I have different DATABASES and different TABLES in sql file. I'm trying to replace those tables and databases value from the file which have there values stored in it using sed, but I'm getting weird result every time I'm running it. Below is the approach I've tried:

sql file query:

SELECT *from DATABASE1.TABLE1
UNION ALL
SELECT *from DATABASE2.TABLE2
UNION ALL
SELECT *from DATABASE3.TABLE3
UNION ALL
SELECT *from DATABASE4.TABLE4

.sh file containing database and table details:

#!/bin/bash
DATABASE1="hr"
TABLE1="emp"
DATABASE2="account"
TABLE2="employee_details"
DATABASE3="payable"
TABLE3="job_details"
DATABASE4="dummy"
TABLE4="basic_details"

before using above value I'm sourcing .sh file so that I can get the value details on shell.

SQL=`cat query.sql`

query=$(echo $SQL| sed -e "s/DATABASE1/${DATABASE1}/;s/DATABASE2/${DATABASE2}/;
s/DATABASE3/${DATABASE3}/;s/DATABASE4/${DATABASE4}/;s/TABLE1/${TABLE1}/;
s/TABLE2/${TABLE2}/;s/TABLE3/${TABLE3}/;s/TABLE4/${TABLE4}/")

By using above approach I'm getting weird result but when I'm assigning database and table variables on shell and using in above query then I'm able to achieve the desired result. Please let me know what wrong I'm doing in case of .sh file approach.

sam
  • 171
  • 1
    $query should probably be $SQL? And I think there is a closing brace missing. – markgraf Aug 28 '19 at 06:19
  • @markgraf yep,Updated the code. – sam Aug 28 '19 at 06:24
  • 2
    I have no idea, why you cat the file, put it into a variable, echo that variable and pipe it into sed while you could directly give the file name as an argument to sed instead. Same weird result if you do it that way? – Philippos Aug 28 '19 at 06:24
  • @Philippos, yep I have tried with giving file in sed command. same weird result I'm getting with that as well. – sam Aug 28 '19 at 06:39
  • what exactly is your "weird result"? is it that the output is all on one line? if so, you need to double-quote your strings. e.g. query="$(sed -e "........" query.sql)". You really don't need the $SQL variable....and if you insist on having it, you'd also need to double-quote that. e.g. SQL="$(cat query.sql)" and query="$(echo "$SQL" | sed -e ".......")" – cas Aug 28 '19 at 07:54
  • 1
    related: Why does my shell script choke on whitespace or other special characters? – cas Aug 28 '19 at 08:01
  • @cas, I have added the quotes, but still I'm getting the same result – sam Aug 28 '19 at 08:50
  • 1
    and what is that result, exactly? "weird results" is kind of vague and useless, about as useless as "it doesn't work". What result do you expect? What result are you getting? – cas Aug 28 '19 at 08:52
  • @cas Thanks for your feedback, There was some issue with quotes and file formatting. I have made the changes and now it's working. – sam Aug 28 '19 at 12:49

1 Answers1

1

There is already a tool for this: envsubst. It'll replace strings like $foo with the value of that variable, handling special characters nicely.

In any case you have a SQL syntax error: there should be a space after *.

l0b0
  • 51,350