Select database record for variable

Question

The code below aims to select a database record and save it in a variable for later use of the data:

#!/bin/bash

dbName='basedados'
dbUser='utilizador'
dbPass='password'

row=$(echo "select file_id, file from catalogue__pdf_file WHERE is_converted='no' ORDER BY file_id DESC LIMIT 0,1" | mysql $dbName -u $dbUser --password=$dbPass)

arr=($row)

The code above selects a record and passes it to the variable row which is as follows:

echo $row
# Saída: file_id file 29 ficheiro.jpg

Then, when passing the variable value row to a matrix via arr=($row):

echo ${arr[3]}
# Saída: ficheiro.jpg

Question

For what has been described, the process is proceeding efficiently or the same can be simplified?

Answer 1

For the purpose you describe is correct. You can however make a small change by removing the use of echo, if you are using a sufficiently current version of the bash shell that supports "here strings"

row=$(mysql $dbName -u $dbUser --password=$dbPass <<< "select file_id, file from catalogue__pdf_file WHERE is_converted='no' ORDER BY file_id DESC LIMIT 0,1")

I leave just one suggestion with regard to the data dbname, dbUser, dbPass. Usually what I do is to store this information in a configuration file, instead of including this information in the script where you run the command. This can be useful if in the future you have several scripts that use the same credentials and need to make a change. This way you only change your configuration file and not a set of individual scripts.

I store the data in the file ~/.my.cnf with the structure.

[client]
user = 'utilizador'
password = 'password'
database= 'basedados'

This way your command would be:

row=$(mysql <<< "select file_id, file from catalogue__pdf_file WHERE is_converted='no' ORDER BY file_id DESC LIMIT 0,1")

If the script is used by more than one user, you can centralize this information in a common directory and that multiple users have access to and use flag --defaults-file=/path1/path2/ficheiro_configuracao in your script to indicate its location.

Your command would be:

row=$(mysql --defaults-file=/path1/path2/ficheiro_configuracao <<< "select file_id, file from catalogue__pdf_file WHERE is_converted='no' ORDER BY file_id DESC LIMIT 0,1")