Issue
quick overview: I got sqlite3 db which contains following structure and data
Id|Name|Value
1|SomeName1|SomeValue1
2|SomeName2|SomeValue2
3|SomeName3|SomeValue3
(continuation of SomeValue3 in here, after ENTER)
Problem is with iteration trough "Value" column, I'm using that code:
records=(`sqlite3 database.db "SELECT Value FROM Values"`)
for record in "${records[@]}"; do
echo $record
done
Problem is there should three values using that iteration, but it is showing four. As result I received:
1 step of loop - SomeValue1
2 step of loop - SomeValue2
3 step of loop - SomeValue3
4 step of loop - (continuation of SomeValue3 in here, after ENTER)
it should end at third step and just show with line break up something like that:
3 step of loop - SomeValue3
(continuation of SomeValue3 in here, after ENTER)
Any suggestion how I can handle it with bash?
Thank you in advance!
Solution
Instead of relying on word splitting to populate an array with the result of a command, it's much more robust to use the readarray
builtin, or read a result at a time with a loop. Examples of both follow, using sqlite3
's ascii output mode, where rows are separated by the byte 0x1E and columns in the rows by 0x1F. This allows the literal newlines in your data to be easily accepted.
#!/usr/bin/env bash
# The -d argument to readarray and read changes the end-of-line character
# from newline to, in this case, ASCII Record Separator
# Uses the `%q` format specifier to avoid printing the newline
# literally for demonstration purposes.
echo "Example 1"
readarray -d $'\x1E' -t rows < <(sqlite3 -batch -noheader -ascii database.db 'SELECT value FROM "Values"')
for row in "${rows[@]}"; do
printf "Value: %q\n" "$row"
done
echo "Example 2"
while read -d $'\x1E' -r row; do
printf "Value: %q\n" "$row"
done < <(sqlite3 -batch -noheader -ascii database.db 'SELECT value FROM "Values"')
outputs
Example 1
Value: SomeValue1
Value: SomeValue2
Value: $'SomeValue2\nand more'
Example 2
Value: SomeValue1
Value: SomeValue2
Value: $'SomeValue2\nand more'
- See Don't Read Lines With
for
for more on why your approach is bad. - Since
VALUES
is a SQL keyword, when using it as a table name (Don't do that!) it has to be escaped by double quotes.
Answered By - Shawn Answer Checked By - Cary Denson (WPSolving Admin)