Select database record for variable

6

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 row variable that looks like this:

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

Then, passing the value of the variable row to an array via arr=($row) :

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

Question

For what has been described, is the process running efficiently or can it be simplified?

    
asked by anonymous 31.03.2015 / 21:37

1 answer

3

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 only one suggestion regarding the data dbName, dbUser, dbPass . Usually what I do is save this information in a configuration file, instead of including this information in the script where you execute the command. This can be useful if in the future you have multiple 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 scripts individually.

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

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

In 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 have multiple users access and use flag --defaults-file=/path1/path2/ficheiro_configuracao in your script to indicate your 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")
    
10.05.2015 / 11:56