How to pass dynamic variables in a mysql query using shell script

Question

This is the part of my code:

sample_1=''
sample_1_is_cancelled=''
sample_2=''
sample_2_is_cancelled=''
sample_3=''
sample_3_is_cancelled=''
sample_4=''
sample_4_is_cancelled=''
sample_5=''
sample_5_is_cancelled=''

while read -r insert
do
 eval sample_$i=$(echo $insert| awk -F'|' '{print $1}')
 eval sample_$i_is_cancelled=$(echo $insert| awk -F'|' '{print $2}')
         i=$(( i + 1 ))
         
         done < $logpath/source.txt

mysql -uroot -p -e" insert into ttable(sample_1, sample_1_is_cancelled, sample_2, sample_2_is_cancelled, sample_3, sample_3_is_cancelled, sample_4, sample_4_is_cancelled, sample_5, sample_5_is_cancelled)
                values($sample_1, $sample_1_is_cancelled, $sample_2 $sample_2_is_cancelled, $sample_3, $sample_3_is_cancelled, $sample_4, $sample_4_is_cancelled, $sample_5, $sample_5_is_cancelled);"

There is a maximum of 5 sets of values can be possible. Minimum is one set.

I can echo the variables like below,

eval echo \$sample_$i
eval echo \$sample_${i}_is_cancelled

But I am not able to pass it iside the insert query in the same way. Any suggesstions... Please help.

My recommendation is to not use shell scripting for this. Use a language with good mysql database libraries, including support for placeholders in SQL statements (e.g. perl or python). If you try to do this in shell, you will constantly be fighting quoting and whitespace and word-splitting issues - you will spend much more time on that than on getting your code to do what you want. Shell is a good language for co-ordinating the execution of other programs that process text or data but is not a good language for doing that text/data processing itself. — cas
– cas, Commented Oct 27, 2021 at 4:46
Thanks for your reply, I can't use another language as this is the case. — user3240509
– user3240509, Commented Oct 27, 2021 at 4:50
ok, it's your foot, you can shoot it if you want to. But why are you even using eval? Why aren't you using an array instead of that sample_$i indirection? Even given that shell is a terrible language to do data processing in, you're making it even harder than necessary by doing that. — cas
– cas, Commented Oct 27, 2021 at 5:22
Also, if you must use shell, use printf to construct an SQL statement (printf formatting can be used kind-of-like placeholders, without the automatic quoting) in a variable, say $sql, and then execute that with the mysql CLI - mysql ... -e "$sql" — cas
– cas, Commented Oct 27, 2021 at 5:26

cas · Accepted Answer · 2021-10-27 06:33:13Z

Here's an example of how to do this using two arrays ("fields" and "values").

#!/bin/bash

declare -a fields values

infile="./source.txt"
#infile="$logpath/source.txt"

i=0
while read -r insert; do
  # split "$insert" into a and b, using | as delimiter
  a="${insert%|*}"
  b="${insert#*|}"

  # create the field names from the loop counter $i
  let i++
  sfield="sample_$i"
  cfield="sample_${i}_is_cancelled"

  fields+=("$sfield" "$cfield")
  values+=("$a" "$b")
done < "$infile"


# show what's in the arrays:
declare -p fields
echo
declare -p values

# now build the SQL string, in parts:

# field names don't need to be quoted
f=$(printf "%s, " "${fields[@]}" | sed -e 's/, $//')

# this assumes values are strings and need to be quoted
v=$(printf "'%s', " "${values[@]}" | sed -e 's/, $//')

sql="$(printf "insert into ttable(%s) values (%s);" "$f" "$v")"

echo
echo "mysql -uroot -p -e \"$sql\""

Given the following sources.txt file:

$ cat source.txt 
one|two
three|four
foo|bar
junk|more junk

Running the script will produce the following output:

declare -a fields=([0]="sample_1" [1]="sample_1_is_cancelled" [2]="sample_2" 
  [3]="sample_2_is_cancelled" [4]="sample_3" [5]="sample_3_is_cancelled"
  [6]="sample_4" [7]="sample_4_is_cancelled")

declare -a values=([0]="one" [1]="two" [2]="three" [3]="four"
  [4]="foo" [5]="bar" [6]="junk" [7]="more junk")

mysql -uroot -p -e "insert into ttable(sample_1, sample_1_is_cancelled, sample_2,
  sample_2_is_cancelled, sample_3, sample_3_is_cancelled,
  sample_4, sample_4_is_cancelled) values ('one', 'two', 'three', 'four',
  'foo', 'bar', 'junk', 'more junk');"

(line feeds and indentation added to improve readability)

NOTE: if you needed to do more with either the field names or the values in the shell script itself (i.e. more than just using it in an sql insert statement), then you'd probably be better off using two associative arrays (one for samples, and one for cancelled samples), using the $sfield and $cfield variables as the keys for those arrays. I started writing the script like this, then realised it was overly complicated for the task (and would have required more work to merge the fields and values to construct the sql string), so simplified it to just use indexed arrays $fields and $values.

Stack Exchange Network

How to pass dynamic variables in a mysql query using shell script

1 Answer 1

You must log in to answer this question.

Hot Network Questions

How to pass dynamic variables in a mysql query using shell script

1 Answer 1

You must log in to answer this question.

Related

Hot Network Questions