It is sometimes necessary to use complex select and insert queries with dozens of variables. Writing such queries by hand is a tedious process and the chance of making a mistake is high. A few lines of shell script code can make this task manageble. The following example uses a MySQL database and Korn shell under Solaris 10 running on a SPARC platform.

First, we need to get into the database and get the list of colums for the particular table we are working with. In this example the database name is “sysinfo” and the table name is “security_tbl”.

mysql -uUSER -pPASSWORD DATABASE

mysql> show tables;
+-------------------+
| Tables_in_sysinfo |
+-------------------+
| applications_tbl  |
| dbdesigner4       |
| dr_reports_tbl    |
| security_tbl      |
| support_matrix    |
| sysinfo_tbl       |

mysql> describe security_tbl;
+---------------------------+------------------+------+-----+---------------------+----------------+
| Field                     | Type             | Null | Key | Default             | Extra          |
+---------------------------+------------------+------+-----+---------------------+----------------+
| server_security_id        | int(10) unsigned |      | PRI | NULL                | auto_increment |
| server_short_name         | varchar(255)     |      |     |                     |                |
| server_dns_domain         | varchar(255)     |      |     |                     |                |
| last_updated_datetime     | datetime         | YES  |     | NULL                |                |
| os_name                   | varchar(45)      |      |     |                     |                |
| os_version                | varchar(45)      |      |     |                     |                |
| syslogd_status            | varchar(45)      | YES  |     | NULL                |                |
| etc_utmp                  | varchar(45)      | YES  |     | NULL                |                |
| etc_utmp_m                | datetime         | YES  |     | 0000-00-00 00:00:00 |                |
| etc_utmp_a                | datetime         | YES  |     | 0000-00-00 00:00:00 |                |
| var_adm_wtmp              | varchar(45)      | YES  |     | NULL                |                |
| var_adm_wtmp_m            | datetime         | YES  |     | 0000-00-00 00:00:00 |                |
| var_adm_wtmp_a            | datetime         | YES  |     | 0000-00-00 00:00:00 |                |
| var_adm_btmp              | varchar(45)      | YES  |     | NULL                |                |
| var_adm_btmp_m            | datetime         | YES  |     | 0000-00-00 00:00:00 |                |
| var_adm_btmp_a            | datetime         | YES  |     | 0000-00-00 00:00:00 |                |
| var_adm_sulog             | varchar(45)      | YES  |     | NULL                |                |
| var_adm_sulog_m           | datetime         | YES  |     | 0000-00-00 00:00:00 |                |
| var_adm_sulog_a           | datetime         | YES  |     | 0000-00-00 00:00:00 |                |
| var_adm_syslog_log        | varchar(45)      | YES  |     | NULL                |                |
| var_adm_syslog_log_m      | datetime         | YES  |     | 0000-00-00 00:00:00 |                |
| var_adm_syslog_log_a      | datetime         | YES  |     | 0000-00-00 00:00:00 |                |
| var_adm_messages          | varchar(45)      | YES  |     | NULL                |                |
| var_adm_messages_m        | datetime         | YES  |     | 0000-00-00 00:00:00 |                |
| var_adm_messages_a        | datetime         | YES  |     | 0000-00-00 00:00:00 |                |
| var_log_syslog            | varchar(45)      | YES  |     | NULL                |                |
| var_log_syslog_m          | datetime         | YES  |     | 0000-00-00 00:00:00 |                |
| var_log_syslog_a          | datetime         | YES  |     | 0000-00-00 00:00:00 |                |
| var_adm_utmpx             | varchar(45)      | YES  |     | NULL                |                |
| var_adm_utmpx_m           | datetime         | YES  |     | 0000-00-00 00:00:00 |                |
| var_adm_utmpx_a           | datetime         | YES  |     | 0000-00-00 00:00:00 |                |
| var_adm_wtmpx             | varchar(45)      | YES  |     | NULL                |                |
| var_adm_wtmpx_m           | datetime         | YES  |     | 0000-00-00 00:00:00 |                |
| var_adm_wtmpx_a           | datetime         | YES  |     | 0000-00-00 00:00:00 |                |
| etc_securityfailedlogin   | varchar(45)      | YES  |     | NULL                |                |
| etc_securityfailedlogin_m | datetime         | YES  |     | 0000-00-00 00:00:00 |                |
| etc_securityfailedlogin_a | datetime         | YES  |     | 0000-00-00 00:00:00 |                |
| var_log_messages          | varchar(45)      | YES  |     | NULL                |                |
| var_log_messages_m        | datetime         | YES  |     | 0000-00-00 00:00:00 |                |
| var_log_messages_a        | datetime         | YES  |     | 0000-00-00 00:00:00 |                |
| var_run_utmp              | varchar(45)      | YES  |     | NULL                |                |
| var_run_utmp_m            | datetime         | YES  |     | 0000-00-00 00:00:00 |                |
| var_run_utmp_a            | datetime         | YES  |     | 0000-00-00 00:00:00 |                |
| var_log_wtmp              | varchar(45)      | YES  |     | NULL                |                |
| var_log_wtmp_m            | datetime         | YES  |     | 0000-00-00 00:00:00 |                |
| var_log_wtmp_a            | datetime         | YES  |     | 0000-00-00 00:00:00 |                |
| var_log_btmp              | varchar(45)      | YES  |     | NULL                |                |
| var_log_btmp_m            | datetime         | YES  |     | 0000-00-00 00:00:00 |                |
| var_log_btmp_a            | datetime         | YES  |     | 0000-00-00 00:00:00 |                |
| var_log_mailog            | varchar(45)      | YES  |     | NULL                |                |
| var_log_mailog_m          | datetime         | YES  |     | 0000-00-00 00:00:00 |                |
| var_log_mailog_a          | datetime         | YES  |     | 0000-00-00 00:00:00 |                |
| var_adm_utmp              | varchar(45)      | YES  |     | NULL                |                |
| var_adm_utmp_m            | datetime         | YES  |     | 0000-00-00 00:00:00 |                |
| var_adm_utmp_a            | datetime         | YES  |     | 0000-00-00 00:00:00 |                |
| var_adm_SYSLOG            | varchar(45)      | YES  |     | NULL                |                |
| var_adm_SYSLOG_m          | datetime         | YES  |     | 0000-00-00 00:00:00 |                |
| var_adm_SYSLOG_a          | datetime         | YES  |     | 0000-00-00 00:00:00 |                |
| etc_default_su            | varchar(45)      | YES  |     | NULL                |                |
| etc_default_su_m          | datetime         | YES  |     | 0000-00-00 00:00:00 |                |
| etc_default_su_a          | datetime         | YES  |     | 0000-00-00 00:00:00 |                |
| compliance                | varchar(45)      | YES  |     | NULL                |                |
+---------------------------+------------------+------+-----+---------------------+----------------+

The list above contains the names of all columns in the security_tbl table. You can redirect this into a file or just highlight, copy, and paste it into a temporary file: /tmp/list1

Let’s format this output so we only have column names and none of the other information:

cat /tmp/list1 | awk '{print $2}' > /tmp/list2

Suppose you are doing an insert query. You have variables with the same names as the column names in your table. This is always a good idea as it saves you time. You query should look something like this:

mysql -u$USER -p$PASS $DBNAME << EOF
INSERT INTO
security_tbl(server_short_name,server_dns_domain,...)
values('$server_short_name','$server_dns_domain',...)
EOF

The problem here is the “…” stuff – there’s a lot of it. But with a simple shell script you can generate all of it with no problems:

string=""
cat /tmp/list2 | while read line
do
      string=$(echo "${string},${line}")
done

string2=""
cat /tmp/list2 | while read line
do
      string2=$(echo "${string2}','$${line}")
done

Just make sure to edit the beginning and the end of each string to remove unneeded characters.

If you are writing an update query, it would look something like this:

mysql -u$USER -p$PASS $DBNAME << EOF
UPDATE security_tbl SET
server_short_name='$server_short_name',
...
WHERE server_short_name='$server_short_name' AND server_dns_domain='$server_dns_domain';
EOF

Again, the problem is the “…”. If you try to type each line by hand, you are likely to make an error that will drive you crazy later on. A shell script can help you:

cat /tmp/list2 | while read line
do
      echo "${line}='$${line}'," >> /tmp/list3
done

Now all you have to do is open the /tmp/list3 file, remove the extra comma at the end of the file, and paste everything into your update query. Don’t forget to remove tht extra comma though.