Bash - Iterate trough SQLite3 DB-CodePudding

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!

CodePudding user response：

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.

CodePudding user response：

Your problem here is the IFS (internal field seperator) in Bash, which the for -loop counts as a new record.

Your best option is to remove the linefeed in the select statement from sqlite, e.g:

records=(`sqlite3 database.db "SELECT replace(Value, '\n', '') FROM Values"`)

for record in "${records[@]}"; do
   echo $record
done

Alternatively, you could change the IFS in Bash - but you are relying on linefeed as a seperator between records.