'Linux Bash to mysql: Select or delete record with external value

I have a MariaDB (mysql) database with a table which looks like this:

MariaDB [DevicesPool]> show columns from Dirs_and_Names;
    +-----------------+--------------+------+-----+---------+----------------+
    | Field           | Type         | Null | Key | Default | Extra          |
    +-----------------+--------------+------+-----+---------+----------------+
    | LNR             | int(11)      | NO   | PRI | NULL    | auto_increment |
    | D_Nr            | varchar(2)   | NO   |     | NULL    |                |
    | Filename        | varchar(100) | NO   |     | NULL    |                |
    | Path_and_File   | varchar(250) | NO   |     | NULL    |                |
    +-----------------+--------------+------+-----+---------+----------------+
    4 rows in set (0.000 sec)

Background: In this table are stored all my video files, collected from different devices. My aim is to collect video files within this table to move to another storage device, or delete it including deletion of or change the record in the table. My idea is to collect a group of video files in a (temporary) list and to start a batch then.

But first I made some basic tests forwarding parameters, which don't succeed.

First, I'm using batch to put a number into a string (id) which contains, e.g. 218.

Then I would like to show a record with the mysql -e command to SELECT or DELETE the record which LNR conains the id, which I previously assigned in batch.

Something like this ($id within quotes):

id=218; mysql -uuser -psecret -e 'use DevicesPool; SELECT * FROM Dirs_and_Files WHERE LNR="$id";'

The query gives no result though within mysql environment the record exists.

MariaDB [DevicesPool]> SELECT * FROM Verz_und_Namen WHERE LNR=218;
    +-------+------+------------------+----------------------------------------------------+
    | LNR   | F_Nr | Fileiname        | Path_and_File                                      |
    +-------+------+------------------+----------------------------------------------------+
    | 218   | 01   | King_Kong.mp4    | /home/user/Movies/King_Kong.mp4                    |
    +-------+------+------------------+----------------------------------------------------+
    1 row in set (0.001 sec)

When I execute this (no quotes for $id):

id=218; mysql -uuser -psecret -e 'use DevicesPool; SELECT * FROM Dirs_and_Names WHERE LNR=$id;'

then I receive an error:

ERROR 1054 (42S22) at line 1: Unknown column '$id' in 'where clause'

So, my question here:

How can I use batch external variables (strings?) to be taken as parameters within mysql -e command? Exactly, how can I select or delete a record with an external assigned id?

Thank you in advance.

-Linuxfluesterer

mysqllinuxbashmariadbparameter-passing


Solution 1:[1]

Single quotes are your issue

id=218; mysql -uuser -psecret -e "use DevicesPool; SELECT * FROM Dirs_and_Names WHERE LNR=$id;"
# or
id=218; mysql -uuser -psecret -e 'use DevicesPool; SELECT * FROM Dirs_and_Names WHERE LNR='$id';'


# or
id=218 
query="SELECT * FROM Dirs_and_Names WHERE LNR=$id;"
mysql -uuser -psecret --database DevicesPool -e "$query"

Solution 2:[2]

After you semi-colon ";" the variable vanishes unless you export it using

export id=218; mysql -uuser -psecret -e "use DevicesPool; SELECT * FROM Dirs_and_Names WHERE LNR=$id;"

Please also note the double quotes wrapping the SQL statement, for pash to substitue the variable.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 SouthernSI

Related Questions