It seems a simple enough use case:
I want to run a query against the database from the command line.
It should accept a query via STDIN (or as an argument) and return the result to STDOUT (or to a file).
It should be able to specify which database you want to connect to (or optionally configure a default.)
I seem to remember a tool called “isql” for informix that worked like this. It was great for ad hoc queries, shell scripts, batch processes, and cron jobs. There may have been something like this for Oracle as well, but I doubt it.
Sadly, neither MySQL or PostgreSQL seem to have this feature. They both have “command line clients” but they’re not really command line tools. They’re text-based shells that establish a persistent connection to the database and have their own commands. Most importantly, they don’t work with standard input and standard output. You could use your favorite scripting language to build queries, or even a generic query tool, but that’s not really portable.
I did a search on “isql” and it turns out that unixodbc has a tool with just that name. This stems from Microsoft SQL Server’s heritage from Sybase which included a port of isql. ODBC is, of course, Microsoft’s database connection standardization, and probably one of the main reasons they won the enterprise application development wars (the other reason obviously being that users were familiar with Windows UI.)
It’s not identical to the old command line tool, which my faulty memory tells me could spit out raw results (while walking uphill in the snow), but it’s fairly customizable, and with a little bit of help from standard tools like grep and sed, can give me what I want.
First, install unixodbc and the appropriate driver (e.g. MySQL or PostgreSQL.)
On Debian-based systems (including Ubuntu)
apt-get install unixodbc
On Redhat-based systems (including Fedora and Centos)
yum install unixodbc
To install the mysql odbc driver
(on Debian)
apt-get install libmyodbc
(on Redhat)
yum install mysql-connector-odbc
or the postgresql odbc driver
(on Debian)
apt-get install odbc-postgresql
(on Redhat)
yum install postgresql-odbc
Now run the command ‘odbcinst -j‘ to see where it looks for configuration files. You should see something like this:
unixODBC 2.2.11
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
USER DATA SOURCES..: /home/aaron/.odbc.ini
Drivers need to be installed by root (or someone with permissions to edit /etc/odbcinst.ini.) An example driver (on my Ubuntu system is available at /usr/share/libmyodbc/odbcinst.ini) and looks like this:
[MySQL]
Description = MySQL driver
Driver = /usr/lib/odbc/libmyodbc.so
Setup = /usr/lib/odbc/libodbcmyS.so
CPTimeout =
CPReuse =
The trick is to find where your driver and setup files are (libmyodbc.so and libodbcmyS.so) and point to them in /etc/odbcinst.ini
Now you can create a DSN that references your driver. (Ubuntu provides another example at /usr/share/doc/libmyodbc/examples/odbc.ini) Copy this to either your global /etc/odbc.ini or create one specific to your user at ~/.odbc.ini
[myodbc]
Driver = MySQL
Description = MySQL ODBC Driver DSN
Server = localhost
Port =
User = root
Password = secret
Database = test
Option = 3
Socket =
Now you should be good to go, provided you have the right server/user/password/database/port. Port defaults to 3306 (for MySQL) and I don’t Know what Option 3 does (MySQL 3.x?) The driver should be the same as the heading name specified in /etc/odbcinst.ini. Go ahead and try executing isql from the command line now
The syntax is
echo $query | isql $dsn
$query of course being replace by an sql statement such as “select * from user” and $dsn is the name of the heading in your odbc.ini file.
+---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> +---------------------------------------+ ... your results in table format here ... SQL>
If you run which gives you output — but by default, it’s in a format similar to what you’d see from the mysql or psql tool. Let’s add a few options to tell it to give us a comma separated output and include the column names as the first record (standard CSV output.)
query="select host,user,password from mysql.user where host != 'localhost'"
dsn="myodbc"
echo $query | isql $dsn -d, -c
That’s better, but we’ve still got all that junk. I originally piped it through a couple of shell scripts that looked like this
echo $query | isql $dsn -c -d, | grep -v ^"[\+\|]" | sed s/^SQL\>//g
which gets the job done (sortof) but a little bit of experimentation showed me that ‘-b’ (for “batch”) is what I want. So now my final command looks like
echo $query | isql $dsn -b -c -d, > file.csv
One problem that comes up is when you do “select * from tablename” in a shell variable, it gets expanded into a file glob, but that issue only comes up when you assign a variable named $query from the command line. It won’t come up in scripts or if you include the content of your query like
echo "select * from tablename" | isql -b -c -d,
or place your query in a file (recommended if you reuse it)
cat my_query.sql | isql $dsn -b -c -d, > file.csv
