0

I'm trying to figure out how to store the table names containing a geometry field from a sqlite3 database into a bash array that I can loop on afterwards.

Here's what I have but it's empty for the moment:

CMD="SELECT f_table_name FROM geometry_columns;"    
readarray -t arr < <( `sqlite3 db.sqlite "$CMD"` )

I also tried:

readarray -t arr < <( sqlite3 db.sqlite ".tables" )

But it contains only 1 element when I print echo ${arr}, where there should be many. Which I don't understand because

echo $(sqlite3 db.sqlite ".tables" )

dumps all the table names to stdout.

Any idea on how to achieve that in the most simple and efficient way?
Ideally using readarray, or any other single-line universal enough solution to work in a simple Debian docker container.

I don't see any trouble writing a temporary file holding the necessary data on the disk.

5
  • store to a temporary file and read from that could be an option? Commented Feb 20, 2021 at 17:34
  • i see table in the CMD= and tables after, perhaps a typo ? Commented Feb 20, 2021 at 17:48
  • Updated. I figure out how to get names of tables which actuall contain a geometry field. If I paste that SELECT statement when I'm logged into the sqlite3 db, it prints what it does.
    – s.k
    Commented Feb 20, 2021 at 17:51
  • it works for me too, i used printf "%s\n" "${arr[@]}" to dump the content of the array on stdout. Commented Feb 20, 2021 at 17:59
  • Nevermind, I think the problem was with echo which only prints the first line of the array (where I was expecting echo to print all the content of the array).
    – s.k
    Commented Feb 20, 2021 at 18:06

1 Answer 1

2

For the posterity

CMD="SELECT f_table_name FROM geometry_columns;"

readarray -t arr < <( sqlite3 db.sqlite "${CMD}" )

for t in "${arr[@]}"; 
do
  echo "${t}"
done

actually prints each line of the array (it's a structure composed of lines, apparently). I was expecting a simple echo ${arr} to print all the content to stdout by default, which is not the case, you have to loop over it.

Please notice to not include the backticks ` ` or to equivalently put the whole sqlite3 command in $() for this to work.

1
  • 1
    "it's a structure composed of lines, apparently" -- yes, that's exactly what readarray is for, reading lines into an array. And ${arr} is the same as ${arr[0]}, so it only prints the first element. This would be different in Zsh, though.
    – ilkkachu
    Commented Feb 20, 2021 at 18:37

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .