How do I store shell command results in a variable in sql?-CodePudding

My system is Ubuntu 16.04 and MariaDB version is 10.2.38.

I want to get Ubuntu version from shell and save the result in sql variable.

MariaDB [(none)]> \! lsb_release -r;
Release:        16.04

I executed the sentence below.

MariaDB [(none)]> set @ver = '\! lsb_release -r';
Query OK, 0 rows affected (0.00 sec)

But the value stored in the @ver variable disappointed me.

MariaDB [(none)]> select @ver;
 ------------------ 
| @ver             |
 ------------------ 
| ! lsb_release -r |
 ------------------ 
1 row in set (0.00 sec)

I wonder how I can save the value 16.04 in a variable. Thank you.

CodePudding user response：

If you where using a not end-of-maintaince version where MariaDB was packaged by upstream, then in the last 1-2 releases it was stored in @@version at the end:

select RIGHT(@@version,4)

RIGHT(@@version,4)
2204

CodePudding user response：

\! is a mariadb (and mysql) command line extension to execute shell commands for maintenance. It will be executed on the client, not on the server and cannot be mixed with SQL commands.

SQL doesn't support execution of other programs - this feature would be a huge security risk.

To store output from a shell command in a SQL variable you can use the --init_command option when starting the command line client:

$ mariadb --init-command="set @a:=right(\"`lsb_release -r`\",5)"
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3938
Server version: 10.10.3-MariaDB-log Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> select @a;
 ------- 
| @a    |
 ------- 
| 20.04 |
 -------

CodePudding user response：

Answered on the POSIX shell level: Since lsb_release -r prints Release: 16.04 on standard output, the command

lsb_release -r | cut -d " " -f 2

should print just 16.04.